Display loop over PHP/MySQL query in array

Question

I'm learning PHP in order to develop some modules into my software Dolibarr and I would like to know How I can loop over my query result and display each row in my array.

This is my script :

/*
 * View
 */

$query = "SELECT u.lastname as nom, u.firstname as prenom, u.datelastlogin as lastlogin";
$query.= " FROM ".MAIN_DB_PREFIX."user as u";
$query.= " ORDER BY lastlogin DESC LIMIT 2";

$resql = $db ->query($query);
$num = $db->fetch_row($resql);

print '<table class="noborder" width="100%">';
print '<tr class="liste_titre"><th colspan="2">'.$langs->trans("Affichage des 2 dernières connexions").'</th></tr>';
if (! empty($conf->user->enabled))
{
        $LastLogin = '<tr class="oddeven">';
        $LastLogin.= '<td><a href="index.php">'.$langs->trans("Utilisateur").'</a></td><td align="right">'.$num[0]." ".$num[1]." ".$num[2].'</td>';
        $LastLogin.= "</tr>";
}

print $LastLogin;
print '</table>';

Up to now, I return only the first row from my query. I have never programmed in PHP before and I would like to know How I can display each line from my query into my array ?

I have to change fetch_row to fetch_array ?

Or, maybe better, split my array in 3 columns (column name, column firstname, column datelastlogin)

Something like this :

I don't really understand how I can do that.

Thank you for your help

EDIT :

I edited my script :

$query = "SELECT u.lastname as nom, u.firstname as prenom, u.datelastlogin as lastlogin";
$query.= " FROM ".MAIN_DB_PREFIX."user as u";
$query.= " ORDER BY lastlogin DESC LIMIT 2";

$resql = $db ->query($query);
//$num = $db->fetch_array($resql);
while($num = $resql->fetch_assoc())
{
    $nums[] = $num;
}

print '<table class="noborder" width="100%">';
print '<tr class="liste_titre"><th colspan="2">'.$langs->trans("Dernières connexions").'</th></tr>';
if (! empty($conf->user->enabled))
{
    foreach($nums as $num)
    {
        $LastLogin = '<tr class="oddeven">';
        $LastLogin.= '<td><a href="index.php">'.$num['nom'].'</a></td><td>'.$num['prenom'].'</td><td>'.$num['lastlogin'].'</td>';
        $LastLogin.= "</tr>";
    }
}
print $LastLogin;
print '</table>';

But the array is not well-displayed :

`while($num = $db->fetch_row($resql)) { /* output one table row with row data */ }` — CBroe, Oct 25 '17 at 14:59
Problem with the code now is of course that you overwrite `$LastLogin` in each loop iteration. Initialize it with an empty string before the loop, and inside append only. This is all so basic btw., that you should perhaps rather go read a proper book/tutorial on the issue. — CBroe, Oct 25 '17 at 15:22
It's not according to the same thing. Here I'm trying to display result from a query and loop over each row in my array. Sorry if it's very basic ... But I'm Python developer not PHP. So I'm beginning from few time — Essex, Oct 25 '17 at 15:23

score 1 · Accepted Answer · answered Oct 26 '17 at 07:42

Helloo again :)

First of all I have to say that your error has nothing to do with the fact that you're new to PHP - but I think you're missing some basics in programming, because phyton works the same way for this scenario.

Lets look at your code:

if (! empty($conf->user->enabled))
{
    foreach($nums as $num)
    {
        $LastLogin = '<tr class="oddeven">';
        $LastLogin.= '<td><a href="index.php">'.$num['nom'].'</a></td><td>'.$num['prenom'].'</td><td>'.$num['lastlogin'].'</td>';
        $LastLogin.= "</tr>";
    }
}
print $LastLogin;

The problem here is that you overwrite $LastLogin all the time. That would work the same way in phyton. So you actually have two choices:

Place the print inside the forloop.
Output the result directly inside the loop.

I won't do an example for 1 since its only placing print $LastLogin inside the foreach loop - you don't need an example for that.

What I'll show you here bellow is the Code how I would write it in your case:

$query = "SELECT u.lastname as nom, u.firstname as prenom, u.datelastlogin as lastlogin";
$query.= " FROM ".MAIN_DB_PREFIX."user as u";
$query.= " ORDER BY lastlogin DESC LIMIT 2";

$resql = $db ->query($query);
//$num = $db->fetch_array($resql);

print '<table class="noborder" width="100%">';
print '<tr class="liste_titre"><th colspan="2">'.$langs->trans("Dernières connexions").'</th></tr>';

if (! empty($conf->user->enabled)) {

    while($num = $resql->fetch_assoc()) {
        print '<tr class="oddeven">';
        print '<td><a href="index.php">'.$num['nom'].'</a></td><td>'.$num['prenom'].'</td><td>'.$num['lastlogin'].'</td>';
        print '</tr>';
    }
}

print '</table>';

There's actually no need to save all your results into a var and append all the time to it. Simply print the results directly, there's nothing that hold you back. For sure we could alk now about separating logic & views - but in this case there's not really a need for it.

Hope it helped :)

Display loop over PHP/MySQL query in array

1 Answers1