MySQL: Num Rows not working

Question

I've stumbled upon a simple MySQL error, and it seems my attempts of fixing it are effortless. The problem, it's not counting.

Before I go further, I know that mysql_* is Deprecated, and that I shouldn't use it. I should use mysqli_* or PDO.

This is my Query, and yes, the echo is just for testing.

$ms_sql = mysql_query("SELECT * FROM mijnsp WHERE sp_username = '".$user['username']."'");
    while ($mijnspusers = mysql_fetch_assoc($ms_sql)) {

    $ms_count = mysql_num_rows($ms_sql);

    if($ms_count <= 0){
        echo "Result is empty";
    }else{
        echo $mijnspusers['new_username'];
    }

I've tried to change the IF, but with no effect;

if($ms_count <= "0"){

or, like this

if($ms_count <= '0'){

Thank you in advance, Pascal

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Feb 05 '16 at 20:21
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 05 '16 at 20:21
You're just assuming your query is working, add error checking or look in your error logs. — Jay Blanchard, Feb 05 '16 at 20:21
@Jay Blanchard Like I said, I know that. However, the system itself is not mine, and that's a long time of converting everything. Thanks for the Filtering tips, but this is for now only on my personal computer. The Query itself works, when there is a Result, it shows it. — Pascal Boschma, Feb 05 '16 at 20:23
You need to cleanse your query before using it. Otherwise, what you have is a big SQL Inject Opening. — Evan Carslake, Feb 05 '16 at 20:24
I hate when people say *"I'm not that far along..."* or *"This site will not be public..."* or *"It's only for school, so security doesn't matter..."*. If teachers and professors are not talking about security from day one, they're doing it wrong. They're teaching sloppy and dangerous coding practices which students will have to unlearn later. I also hate it when folks say, *"I'll add security later..."*. If you don't have time to do it right the first time, when will you find the time to add it later? ¯\\_(ツ)_/¯ — Jay Blanchard, Feb 05 '16 at 20:24
Use $ms_count = mysql_num_rows($ms_sql); outside the loop... In second iteration it will nothing return becuase u can't get mysql resource in second iteration — devpro, Feb 05 '16 at 20:26
It's not just that it's deprecated, it doesn't even work in a current version of PHP! — miken32, Feb 05 '16 at 20:26
what output are you getting? what output are you expecting? Also, you should declare $ms_count outside the while loop to improve speed. — JAX, Feb 05 '16 at 20:27
@miken32 This server doesn't run PHP 7. It's an old version. — Pascal Boschma, Feb 05 '16 at 20:27
@jay-blanchard one more thing sir... Students using deprecated things from day one y not new things — devpro, Feb 05 '16 at 20:29
No doubt @devpro and we should enable students to have their teachers come up to date. — Jay Blanchard, Feb 05 '16 at 20:32
Off course I m very scare if teachers out dated .. Or maybe students using short cuts @jay-blanchard — devpro, Feb 05 '16 at 20:33

score 3 · Accepted Answer · answered Feb 05 '16 at 20:25

3

Call

$ms_count = mysql_num_rows($ms_sql);

before the while() loop.

answered Feb 05 '16 at 20:25

MonkeyZeus

20,375
4
36
77

1

Thanks. Stupid of me. I Do know this, just it's getting forgotten by time. Bad memory. This fixed it, ofcourse. – Pascal Boschma Feb 05 '16 at 20:28
@PascalBoschma nice! – MonkeyZeus Feb 05 '16 at 20:28
Most important is that if OP use inside the loop it will return nothing in second iteration – devpro Feb 05 '16 at 20:32

score 2 · Answer 2 · answered Feb 05 '16 at 20:26

You should do the mysql_num_rows() before going into the loop. If the num is 0, then you'll never run the loop to begin with.

Your code should be:

$ms_sql = mysql_query("SELECT * FROM mijnsp WHERE sp_username = '".$user['username']."'");

$ms_count = mysql_num_rows($ms_sql);

    if($ms_count == 0){
        echo "Result is empty";
    }else{
        while ($mijnspusers = mysql_fetch_assoc($ms_sql)) {
           echo $mijnspusers['new_username'];
        }
    }`

MySQL: Num Rows not working

2 Answers2