Hi all I want try sql query this
SELECT COUNT(*) as nb ,U.lastname
FROM mdl_user U
INNER JOIN mdl_log L
ON U.id = L.userid
AND L.module = 'user' AND L.action='login'
GROUP BY L.userid order by nb desc
how can write in php ?
i try this
$result = $DB->get_records_sql('SELECT COUNT(*) as nb ,U.lastname
FROM mdl_user U
INNER JOIN mdl_log L
ON U.id = L.userid
AND L.module = 'user' AND L.action='login'
GROUP BY L.userid order by nb desc');
but moodle cant' read bdd
thinks for reply
thanks
is your script ok
<?php
require('../config.php');
require_once($CFG->libdir.'/adminlib.php');
$sql = "SELECT COUNT(*) as nb ,U.lastname
FROM {user} U
INNER JOIN mdl_log L ON U.id = L.userid
AND L.module = 'user'
AND L.action='login'
GROUP BY L.userid order by nb desc";
echo $sql;
$result = $DB->get_records_sql($sql);
print_r($result);
?>
How i can integer in layout moodle ? How My own layout moodle ?
I try this but I use Layout rapport
<?php
require('../config.php');
require_once($CFG->libdir.'/adminlib.php');
$id = optional_param('id', $SITE->id, PARAM_INT);
$page = optional_param('page', 0, PARAM_INT);
$course = $DB->get_record('course', array('id'=>$id), '*', MUST_EXIST);
require_login($course);
$context = context_course::instance($course->id);
if ($course->id == SITEID) {
admin_externalpage_setup('reportloglive', '', null, '', array('pagelayout'=>'report'));
echo $OUTPUT->header();
}
echo "<h1>Nombre total de connexion par membres </h1>";
$sql = "SELECT COUNT(*) as nb ,U.lastname FROM {user} U INNER JOIN mdl_log L ON U.id = L.userid AND L.module = 'user' AND L.action='login' GROUP BY L.userid order by nb desc";
$result = $DB->get_records_sql($sql);
$query = $bdd->query($sql);
echo "
<table>
<tr class='entete'>
<td >nom</td><td '>nombre de connexion</td>
</td>
";
while($tuple = $query->fetch(PDO::FETCH_OBJ))
{
echo "<tr>";
echo "<td>".$tuple->lastname."</td>";
echo "<td>".$tuple->nb."</td>";
echo "</tr>";
}
echo "</table>";
echo $OUTPUT->container_end();
echo $OUTPUT->footer();
?>
How a can call header, block and footer ?
In my script:
replace ... INNER JOIN mdl_log L ON U.id = L.userid ...
with ... INNER JOIN {log} L ON U.id = L.userid ...
It'll be work fine, when you want to change DB table's prefix.
Your problem is that you are using single quotes to surround your query string, but you have single quotes within the string, so PHP can't parse it properly.
By the way, make sure you have developer debugging turned on, so that you get more detailed error messages.
