Display query result with PHP and MySQL

Question

I'm learning this new langage PHP in order to develop modules from this software : Dolibarr

It's the first time I'm using PHP and I don't overcome to display query result in my view.

I would like to know if I wrote something wrong in my script because I don't understand all up to now. I would like to display the number of users in my software. I have to query my llx_user table and display the result in my array.

This is the part of my code :

/*
 * View
 */

//Display number of users

$sql = "SELECT COUNT(u.rowid) as total";
$sql.= " FROM ".MAIN_DB_PREFIX."user as u";

$result = $db->query($sql);

print '<table class="noborder" width="100%">';
print '<tr class="liste_titre"><th colspan="2">'.$langs->trans("Statistics").'</th></tr>';
if (! empty($conf->user->enabled))
{
        $statUsers = '<tr class="oddeven">';
        $statUsers.= '<td><a href="index.php">'.$langs->trans("Number of Users").'</a></td><td align="right">'.round($result).'</td>';
        $statUsers.= "</tr>";

}

$total=0;
if ($entity == '0')
{
        print $statUsers;
        $total=round($result);
}
print '<tr class="liste_total"><td>'.$langs->trans("Total").'</td><td align="right">';
print $total;
print '</td></tr>';
print '</table>';


print '</div></div></div>';

llxFooter();

$db->close();

As I said, it's the first time I'm handling php file and I began to learn php 3 hours ago.

This is what I got :

If I comment like this :

$total=0;
//if ($entity == '0')
//{
        print $statUsers;
        $total=round($result);
//}

I'm getting this :

But I have 2 users in my table :

Thank you if you could help me

score 5 · Accepted Answer · answered Oct 25 '17 at 10:56

5

You're doing a good job for that you just started with PHP. Anyway, there's a little mistake in your code.

You actually query the database, but you don't fetch the result.

You have to do the following after your query:

$row = $result->fetch_row();
print $row[0]; // $row[0] will contain the value you're looking for

Also it seems that your $entity is not equal to 0. I don't see you initializing this variable anywhere, are you sure you have defined it? May you want to show us some mor of your code..

answered Oct 25 '17 at 10:56

Twinfriends

1,972
1
14
34

Thank you your answer works pretty well ! But I have a question : What is `fetch_row()` ? And why I have to use `$row[0]` and not just `$row` – Essex Oct 25 '17 at 11:01
1

`fetch_row()` gets you an array containing each column requested. Since you only request one column `$row[0]` returns the value in that column (because arrays start counting at zero). – Jay Blanchard Oct 25 '17 at 11:36
Ok so if I understand well, if my query was : `$sql = "SELECT COUNT(u.rowid), u.name as total"; $sql.= " FROM ".MAIN_DB_PREFIX."user as u";` I should write `$row[0]` for `rowid` and `$row[1]` for `name` ? – Essex Oct 25 '17 at 12:28
@Deadpool Sadly not. As far as I understand you, you want to get a list with the total count of your users, and you want to get a list with all names of your users. You should separate this two into two queries. One with only the count, the other with the name select. Thats simply because you can't use a count and select values at the same time (as far as I know, but I'm not really that good in SQL).. so I would recommend you doing a first query with the count, and a second with the `SELECT name FROM users` and there you can simply acces `$row[0]` for the first username, `$row[1]` for the next – Twinfriends Oct 25 '17 at 12:52
@Deadpool The SELECT name query will return one row for each user. So lets say, you do something like `SELECT name, email FROM users` then you'll have `$row[0][name]` for the first username, `$row[0][email]` for first users mail etc. If you do this in a while($result->fetch_row()) {` you can then acces all the attributes from the user by `$row["name"]` - so if you do a `print $row["name"]` it will print every username, since its inside a while loop, and that loop will be executed for each row your query returns (and since there one row for each user, it will itterate over every user) – Twinfriends Oct 25 '17 at 12:54
@Deadpool I hope its a bit more clear for you now and I also hope I'm right with what I'm saying... If you have more questions, feel free to ask them, I'll try to help as good as I can. – Twinfriends Oct 25 '17 at 12:55
I understand pretty well your very interesting answer ! Thank you very much :) If I separate my 2 queries, can you say me if my EDIT in my question is good ? – Essex Oct 25 '17 at 13:01
@Deadpool As far as I know SQL, I would say yes, its fine what you did there. I don't think you can do this in one query, since there's a `WHERE` statement that you don't want to include on both of these counts. – Twinfriends Oct 25 '17 at 13:14
@Twinfriends Ok so I will open a new question based on my edit in order to get a working script ;) Thank you for all anyway ! – Essex Oct 25 '17 at 13:16
@Deadpool You're welcome :) I'll keep an eye for your new question, I'll try to help there as well :) – Twinfriends Oct 25 '17 at 13:23

Display query result with PHP and MySQL

1 Answers1