Store number of rows in SQL query as a variable

Question

I am trying to write some PHP that will run an SQL query and then count the number of rows that query returned and store it so i can use it in an if statement. Currently i am outputting the number onto the screen for debug reasons and it always outputs as 0 whereas when i run the query directly in the database it returns over 600 rows. Why is this?

$result = $db->query('SELECT * FROM `bets` WHERE `user` = 76561198223084096');
$row_cnt = $result->mysql_num_rows;
printf("Result set has %d rows.\n",$row_cnt);

Any help is appreciated

change $row_cnt = $result->mysql_num_rows; to $row_cnt = mysqli_num_rows($result); — Tirth Patel
– Tirth Patel, Commented May 12, 2016 at 19:20
I tried "change $row_cnt = $result->mysql_num_rows; to $row_cnt = mysqli_num_rows($result);" but i still get 0 rows in the output — Tyler
– Tyler, Commented May 12, 2016 at 19:34

Rajdeep Paul · Accepted Answer · 2016-05-12 19:29:18Z

1

... it always outputs as 0 whereas when i run the query directly in the database it returns over 600 rows.

The problem is because of this statement,

$row_cnt = $result->mysql_num_rows;

It should be,

$row_cnt = $result->num_rows;

Here's the reference:

mysqli_result::$num_rows

answered May 12, 2016 at 19:29

Rajdeep Paul

16.9k3 gold badges21 silver badges39 bronze badges

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

4 Comments

Tyler Over a year ago

I still get 0 as an output from this

Rajdeep Paul Over a year ago

@Tyler Run this query SELECT * FROM `bets` WHERE `user` = 76561198223084096; in your MySQL console and see if it returns any row or not.

Tyler Over a year ago

Just ran it and: "Showing rows 0 - 99 (646 total, Query took 0.0008 seconds.)"

Rajdeep Paul Over a year ago

@Tyler hmm, strange. Try to debug this issue using mysqli::$error. Also check whether you're properly connected to database or not i.e check the status of $db variable.

Aparna · Accepted Answer · 2016-05-12 19:50:51Z

1

You can use the following solution:

$counter = mysql_query("SELECT COUNT(*) AS cnt FROM `bets` WHERE `user` = '76561198223084096'") ;
$num = mysql_fetch_array($counter);
$row_cnt= $num["cnt"];
printf("Result set has %d rows.\n",$row_cnt);

edited May 12, 2016 at 19:50

answered May 12, 2016 at 19:36

Aparna

2531 silver badge8 bronze badges

6 Comments

Aparna Over a year ago

Are you sure you're using the same db with the code, otherwise you should have got the same result with your code too.

Tyler Over a year ago

I use $db 100s of times in the code and it works fine for all the rest, just not at this point

Aparna Over a year ago

Check my edit, use a single quote before the no. in the query.

Aparna Over a year ago

Try to run my query in your db and check what it will return.

Tyler Over a year ago

Just ran it and: "Showing rows 0 - 99 (646 total, Query took 0.0008 seconds.)"

|

Naisa purushotham · Accepted Answer · 2016-05-12 19:42:44Z

0

$result = $db->query("select count(*) as c from `bets` WHERE `user` = '76561198223084096'");
$count = $result->fetch_object()->c;

echo "Result set has {$count} rows ";

edited May 12, 2016 at 19:42

answered May 12, 2016 at 19:21

Naisa purushotham

91310 silver badges19 bronze badges

3 Comments

Naisa purushotham Over a year ago

can you check it once

Tyler Over a year ago

I just tried the edited version and still got the error

Naisa purushotham Over a year ago

$db instead of $mysqli

Collectives™ on Stack Overflow

Store number of rows in SQL query as a variable

3 Answers 3

4 Comments

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related