PHP/MySQL not returning number of rows in database

Question

I'm attempting to count the number of rows in my database for a pagination script. My entire script works until it comes down to counting the rows.

This is the code that should be counting my rows[$conn is a variable set with my database login - it is used throughout this code and works, except for this bit].

$limit = 2;
$rows = mysql_num_rows(mysqli_query($conn, "SELECT count(*) FROM pilotOperators"));
$total=ceil($rows/$limit);

To test my code I added this to the end of my page:

echo "Total:" . $total . "<br>";
echo "Rows:" . $rows . "<br>";
echo "Limit:" . $limit . "<br>";

And this is the result taken from my source code:

Total:0<br>Rows:<br>Limit:2<br>

I've tried several variations and such, but nothing is returning a number.

SOLVED: Thanks to two different answers(apparently I had two mistakes).

One of my lines was changed to:

$rows = mysqli_num_rows(mysqli_query($conn, "SELECT * FROM pilotOperators"));

And it works like a charm now.

Thanks Mureinik, and EyasSH

You're mixing calls to mysql_*() with calls to mysqli_*(). The two APIs are different and can't be intermixed. You also shouldn't use mysql_*() for new code. It's deprecated and will be removed from PHP 7 later this year. — user1864610
– user1864610, Commented Apr 29, 2015 at 23:48
Selecting count(row_name) causes the query to return a single row with a field named count containing your desired value. This may differ from your expected behavior. — Harvtronix
– Harvtronix, Commented Apr 29, 2015 at 23:50

EyasSH · Accepted Answer · 2015-04-29 23:57:32Z

3

This line is your problem:

$rows = mysql_num_rows(mysqli_query($conn, "SELECT count(*) FROM pilotOperators"));

You are asking for _num_rows() of a COUNT query. If oyu loo kat the result of

mysqli_query($conn, "SELECT count(*) FROM pilotOoperators");

You'll find out that you will get one row and one column:

|----------|
| count(*) |
|----------|
| 233      | etc
|----------|

What you want is:

$rows_result = mysqli_query($conn, "SELECT count(*) FROM pilotOoperators")->fetch_row();
$rows = $rows_result[0];

It is generally always better to use a COUNT aggregate query than num_rows. When is the right time to use mysqli_num_rows? Well, if you are actually using the query for something else, but need to know the number of rows in advance, then its best not to make two separate queries here.

edited Apr 29, 2015 at 23:57

answered Apr 29, 2015 at 23:51

EyasSH

3,84925 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jesse Elser Over a year ago

This worked but as Mureinik said I also had an issue using mysqli and mysql together. Both of your solutions helped and got it working. Thanks!

Mureinik · Accepted Answer · 2015-04-29 23:48:28Z

2

Don't mix mysql and mysqli functions. If you're performing the query with mysqli_query, you should use mysqli_num_rows to get the number of rows:

$limit = 2;
$rows = mysqli_num_rows(mysqli_query($conn, "SELECT count(*) FROM pilotOperators"));
$total=ceil($rows/$limit);

answered Apr 29, 2015 at 23:48

Mureinik

316k54 gold badges400 silver badges405 bronze badges

2 Comments

StackSlave Over a year ago

Of course, you should learn the Object Oriented approach, unless you like typing more.

Jesse Elser Over a year ago

I changed my code to what you posted and for the most part it worked. I was about to post about the new issue but EyasSH noticed the problem. Thanks to your post and his my code is now $rows = mysqli_num_rows(mysqli_query($conn, "SELECT * FROM pilotOperators")); and it works. Thanks so much.

Collectives™ on Stack Overflow

PHP/MySQL not returning number of rows in database

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related