Html table returns empty after SQL query

Question

I'm having problems with SQL query that returns no results instead of the data from the tables. I have two tables on my DB, one is for Products and the other is Basket. The idea is to take the product id's from the basket and retrieve all the rest of the data from the product table. This is what i did:

$sql = sprintf("SELECT * FROM Basket");

$result = mysql_query($sql, $link);

while ($row = mysql_fetch_array($result)) {

$my_id = $row["Id"];

$prod_s=sprintf("SELECT * FROM Products WHERE Id='%s'",$my_id) ;
$prod= mysql_fetch_array($prod_s);

echo "<td>" . htmlentities($prod["Name"],ENT_QUOTES,"UTF-8") . "</td>";
echo "<td>" . htmlentities($prod["Size"]) . "</td>";

. . . The table is being created but all fields are empty.

Thank you!

You should also not use SELECT *, but use SELECT ID instead. — Sam
– Sam, Commented Jun 13, 2013 at 21:57
Can you query the data you're expecting using a plain old SQL query rather than this script? It looks like you're trying to do a lot more here beyond what your description suggests, since you're including HTML in your output, something you should reconsider doing. — Derek
– Derek, Commented Jun 13, 2013 at 22:00

Jan Krüger · Accepted Answer · 2013-06-13 21:59:20Z

1

First of all, your current code is vulnerable to second-level SQL injections: if one of the IDs in the database is a malicious string (e.g. the good old ; DROP DATABASE foo), you're screwed.

Now, your actual problem is that you're not actually sending the second query to the SQL server. You'll want to run mysql_query() on it and use the result handle with mysql_fetch_array. You're already doing it correctly with the initial query. Just do the same thing again.

Finally, you might want to know that all of this can be done in a single SQL query, using joins. You may want to ask your favourite search engine about those. Good luck!

answered Jun 13, 2013 at 21:59

Jan Krüger

18.6k3 gold badges64 silver badges51 bronze badges

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

Comments

notblakeshelton · Accepted Answer · 2013-06-13 22:00:01Z

1

I think you still have to add a mysql_query for prod_s.

    $my_id = $row["Id"];

    $prod_s=sprintf("SELECT * FROM Products WHERE Id='%s'",$my_id) ;
    $prod_q=mysql_query($prod_s);
    $prod= mysql_fetch_array($prod_q);

    echo "<td>" . htmlentities($prod["Name"],ENT_QUOTES,"UTF-8") . "</td>";
    echo "<td>" . htmlentities($prod["Size"]) . "</td>";

answered Jun 13, 2013 at 22:00

notblakeshelton

3631 gold badge8 silver badges22 bronze badges

Collectives™ on Stack Overflow

Html table returns empty after SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related