mysql_query function only works with mysql_num_rows

Question

I have a very strange problem today. I have a section of code which queries a table based on a GET variable passed from a user input (pretty standard).
However for this to work I have to include a redundant mysql_num_rows variable....

This Works;

   <?php
   1. $cat = strval($_GET['c']);
   2. $query = "SELECT * FROM stock WHERE Category = '$cat'";
   3. $num_rows = mysql_num_rows(mysql_query($query)); 
   4. $values = mysql_query($query);
   ?>

For some reason without line 3 it doesn't work.
By the way this is ultimately used to create an array passed to a google chart which is all fine. What am I doing wrong?

All code for reference;

   <?php
   $cat = strval($_GET['c']);
   $query = "SELECT * FROM stock WHERE Category = '$cat'";
   LINE UNDER QUESTION --> $num_rows = mysql_num_rows(mysql_query($query)); 
   $values = mysql_query($query);
   $material = array();
   $quantity = array();
   $colour = array();
   while ($row = mysql_fetch_array($values)) {
       array_push($material, $row['Material']);
       array_push($quantity, $row['Quantity']);
       array_push($colour, $row['Colour']);
   }
   ...Then all the google chart stuff....
   ?>

***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 27 '17 at 19:12
Ok that's great but that's not the problem here. Although not ideal old mysql still works fine and changing to mysqli isn't going to fix this problem. — Benjamino, Feb 27 '17 at 19:21
I cannot replicate your issue, it works fine for me *even* when I revert to the old MySQL API. There must be something else going on in your code. — Jay Blanchard, Feb 27 '17 at 19:24
I've added more code but I know all the google chart stuff and the GET passing works fine. The issues lies somewhere in fetching the query. — Benjamino, Feb 27 '17 at 19:30

score 0 · Answer 1 · answered Feb 27 '17 at 19:55

0

Specify columns in the select and use an index. And make sure the $_GET value is validated. You probably already have done this, but try the query in e.g. phpMyAdmin. Maybe all this will help.

SELECT
    material,
    quantity,
    colour 
FROM
    stock
WHERE 
    Category = ...
ORDER BY ..
;

answered Feb 27 '17 at 19:55

Annemieke Buijs

80
7

I tested the query in phpMyAdmin and it works but I tried implementing your query and that stopped it working at all. Really strange. – Benjamino Feb 27 '17 at 20:47
For reference, I'm counting the error as the google chart not loading (error data 0 cannot be string). – Benjamino Feb 27 '17 at 20:48
Did you get an error in phpMyAdmin? What does it say? – Annemieke Buijs Feb 27 '17 at 20:53
Well I queried what you suggested (ignoring the order by) and it returned the correct results. But when I then took that statement and replaced the $query line above (remembering to put the variable $cat back in) it stopped working alltogether – Benjamino Feb 27 '17 at 21:21
I'm starting to think it might be a google charts thing, but that doesn't make sense because I have fully functioning datatype checks in that section, and it also doesn't explain why line 3 is making it work? – Benjamino Feb 27 '17 at 21:23
Check the arrays if they are not empty after the loop. – Annemieke Buijs Feb 27 '17 at 22:23
And maybe 'my' query doesn't work because of the uppercases. – Annemieke Buijs Feb 27 '17 at 22:25
var_dump is very handy for this sort of thing, and / or a debugger. – Annemieke Buijs Feb 27 '17 at 22:27

mysql_query function only works with mysql_num_rows

This Works;

All code for reference;

1 Answers1