Hey all,
Trying to convert the result of a SELECT COUNT() statement into a $ variable.
Currently either returns 1 or 0 or an error about being unable to convert to string.
For example
select count(name) where name = ‘john’
returns 20 in phpmyadmin and appears to work on my test page.
Just need help converting that result into a $result variable.
where echo $result;
would show ‘20’.
Beginner and would very much appreciate help on this.
:- )
so you want to fetch data from a database into your PHP script, read a tutorial:
mysqli_fetch_assoc, mysqli_fetch_row, mysqli_fetch_array and mysqli_fetch_object functions do always return an array or object when there is a(nother) row available. That is because a row can contain multiple columns. I like mysqli_fetch_assoc the most because you will get a associative array which is easier to understand by people. If there is a change that you would get more than one rows you should use a while loop like so:
while($row = mysqli_fetch_assoc($result) ) {
// do something with the $row array
}
If you expect ONE or NONE rows you can replace the while for a if.
if($row = mysqli_fetch_assoc($result) ) {
// do something with the $row array
}
However if you are 100% sure that there will be exactly one row you can also forget the whole if condition.
$row = mysqli_fetch_assoc($result) ;
// do something with the $row array
In all situations the $row variable will be an array (except with mysqli_fetch_object where you will get an object). So to get the variable that you want out of the array you will have to write something like:
echo $row['columnname']; // where columnname is the name of the column in the database table
In your case the columname would be something like $row['COUNT()']. And if you doubt about the right columnname you could use a print_r($row); or you could use an alias in your query e.g. SELECT COUNT() AS total and use the same name in your associative array: $row['total'].
I dont know what im doing wrong but i’m just getting errors.
" Warning : mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in…"
The query is most likely failing, which returns a false value, rather than a mysqli result (if you make use of the php.net documentation to learn about each statement you are using, you would already know this.) The current error is a follow-on error, not the actual problem.
To get php to help you, you ALWAYS need error handling for statements that can fail. The easiest way of adding error handling for database statements is to use exceptions for errors and in most cases let php catch the exception where it will use its error related setting to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed or logged the same as php errors.)
To enable exceptions for errors for the mysqli extension, add the following line of code before the point where you make the database connection -
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);If you switch to use the much simpler PDO extension, see the link that @chorn posted, you will have less code to write, you will be able to treat the result from both prepared and non-prepared queries the same, and the php statements you learn to use can be used with about 12 different database types.
YES! Working now!!
Echoing $row[‘COUNT(id)’]; instead of $row[‘total’]; has it returning the correct count.
Thank you frankbeen, phdr and chorn for your help!
This has made my entire month and has gotten my project back on track!
something so simple had been plaguing me and you guys solved it for me!
eternally grateful.
This is why you would use an alias name in the query, or use a fetch… statement that returns a single column value.
I’m such a noob that i dont really understand haha.
Could you clarify why an alias is ideal rather than directly using the column name?
Is it best practice or a security concern?
I will be using prepared statements and am just testing at the moment.
also to keep in line with the original example $row[‘COUNT(id)’] should be $row[‘COUNT(name)’], in case i’ve caused confusion.
Once again im a noob so im sorry if i dont make any sense at all.
just have a look at the manual
https://dev.mysql.com/doc/refman/5.7/en/select.html
hit ctrl+f alias_name - it’s mostly about encapsulation and abstraction. as you see, if you change the column counted, the key you access changes. that might be correct, but mostly you just want to rely on one count value within your script, no matter what how that is really calculated, so you use a unique alias.