HELP - My query works in MYSQL, but in PHP file I get different results

[edjbruner]

When I log into MYSQL (via command line) and use the following query, I get a result set that I expect:

SELECT DISTINCT c.hits, c.cid, c.name, c.style, c.town, c.deliver, m.pid, m.pname, m.lgprice, s.sid, s.sname, s.sprice FROM customers AS c, menus AS m, specials AS s WHERE m.cid=2 ORDER BY c.hits;

 

In my PHP file, the query runs successfully, but produces a completely different erroneous result set:

        // 1.QUERY DB WITH INPUT

        $queryVendor = "SELECT DISTINCT c.hits, c.cid, c.name, c.style, c.town, c.deliver, m.pid, m.pname, m.lgprice, s.sid, s.sname, s.sprice FROM customers AS c, menus AS m, specials AS s WHERE m.cid=$id ORDER BY c.hits";

 

        $result = mysql_query($queryVendor);

 

Thanks,

Please help.  Does PHP interpret SQL queries and modify them when it sends them to MYSQL?

 

[trq]

Does PHP interpret SQL queries and modify them when it sends them to MYSQL?

 

No, both queries should return the same result. What exactly is the difference in the results?

[edjbruner]

Thanks.  The PHP file returns the following:

Hits NAME         STYLE      TOWN DEL PRODUCT PRICE SPECIAL        PRICE

0 sals pizza italian palmyra 1 pizza 8.99 Cheese steak and Fries 6

0 sals pizza italian palmyra 1 pizza 8.99 Cheese steak and Fries 6

0 sals pizza italian palmyra 1 pizza 8.99 Cheese steak and Fries 6

0 sals pizza italian palmyra 1 pizza 8.99 Cheese steak and Fries 6

0 sals pizza italian palmyra 1 pizza 8.99 Cheese steak and Fries 6

0 sals pizza italian palmyra 1 pizza 8.99 Cheese steak and Fries 6

 

Basically, my menus table has 3 PRODUCTS for sals.  The query from the PHP file gives me a ton of hits for the same menu entry.  sals also has fries as a PRODUCT and Cheese steak.

 

I've echo'ed out the query string to the screen and it appears normal.  If PHP doesn't modify a query string, and you don't see any red flags in the query string, then I'm screwed.  I'll have to come up with an alternative method.

THanks for looking at this problem.  I really appreciate it.

[edjbruner]

In case you are interested, here is the exact output from MYSQL:

mysql> SELECT DISTINCT c.hits, c.cid, c.name, c.style, c.town, c.deliver, m.pid, m.pname, m.lgprice, s.sid, s.sname, s.sprice FROM customers AS c, menus AS m, specials AS s WHERE m.cid=2 ORDER BY c.hits;

+------+-----+-------------------------+---------+----------+---------+-----+--------------+---------+-----+------------------------+--------+

| hits | cid | name                    | style  | town    | deliver | pid | pname        | lgprice | sid | sname                  | sprice |

+------+-----+-------------------------+---------+----------+---------+-----+--------------+---------+-----+------------------------+--------+

|    0 |  3 | sals pizza              | italian | palmyra  |      1 |  1 | pizza        | 8.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  4 | bruno italian restarant | italian | palmyra  |      1 |  2 | cheese steak | 5.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  5 | dragon restarant        | chinese | palmyra  |      1 |  3 | french fries | 3.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  2 | vito pizza              | italian | palmyra  |      1 |  1 | pizza        | 8.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  3 | sals pizza              | italian | palmyra  |      1 |  2 | cheese steak | 5.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  4 | bruno italian restarant | italian | palmyra  |      1 |  3 | french fries | 3.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  6 | peking restarant        | chinese | mt.holly |      1 |  1 | pizza        | 8.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  2 | vito pizza              | italian | palmyra  |      1 |  2 | cheese steak | 5.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  3 | sals pizza              | italian | palmyra  |      1 |  3 | french fries | 3.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  5 | dragon restarant        | chinese | palmyra  |      1 |  1 | pizza        | 8.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  6 | peking restarant        | chinese | mt.holly |      1 |  2 | cheese steak | 5.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  2 | vito pizza              | italian | palmyra  |      1 |  3 | french fries | 3.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  4 | bruno italian restarant | italian | palmyra  |      1 |  1 | pizza        | 8.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  5 | dragon restarant        | chinese | palmyra  |      1 |  2 | cheese steak | 5.99    |  1 | Cheese steak and Fries |      6 |

|    0 |  6 | peking restarant        | chinese | mt.holly |      1 |  3 | french fries | 3.99    |  1 | Cheese steak and Fries |      6 |

+------+-----+-------------------------+---------+----------+---------+-----+--------------+---------+-----+------------------------+--------+

15 rows in set (0.00 sec)

[PFMaBiSmAd]

It's fairly clear from the output you posted that your php code that is retrieving and outputting the results is the problem. You would need to post it if you expect someone else to help with what it is doing.

[edjbruner]

Thanks, but I don't think it is my presentation code.  Here it is:

if (!$result) {

    die("I'm sorry we are currently improving STO to make it better: " . mysql_error());

}

else {

$num=mysql_numrows($result);

$i=0;

echo "<table border=1><th>Hits</th><th>NAME</th><th>STYLE</th><th>TOWN</th><th>DELIVER?</th><th>PID</th><th>PRODUCT</th><th>LG PRICE</th><th>SID</th><th>SPECIAL</th><th>PRICE</th>";

while ($i < $num) {

$hits = mysql_result($result, $count, "hits");

$cid = mysql_result($result, $count, "cid");

$name = mysql_result($result, $count, "name");

$style = mysql_result($result, $count, "style");

$town = mysql_result($result, $count, "town");

$deliver = mysql_result($result, $count, "deliver");

$pid = mysql_result($result, $count, "pid");

$pname = mysql_result($result, $count, "pname");

$lgprice = mysql_result($result, $count, "lgprice");

$sid = mysql_result($result, $count, "sid");

$sname = mysql_result($result, $count, "sname");

$sprice = mysql_result($result, $count, "sprice");

echo "<tr><td>$hits<td>$name<td>$style<td>$town<td>$deliver<td>$pid<td>$pname<td>$lgprice<td>$sid<td>$sname<td>$sprice</tr>";

$i++;

}

echo "</table>";

}

 

The query's results gets pasted to the method above.  Thanks for looking.

[PFMaBiSmAd]

Yea, well, your code is using $count in the mysql_result() statements to access each row, but you are using $i as the variable that gets incremented.

 

Know thy own code (or at least read it to make sure it is doing what you expect, especially when it does not work as expected.)

[edjbruner]

Oh crap!!  Good eyes.  That has to be the problem

"Know thy own code"  LOL