SQL won't return values

Question

Let me first start to say my SQL is correct and checked it more than once.

I have 2 methods in my file calling more than once for the SQL but when it comes to the second part it won't return any value to fill in the fields.

Here is the part that is giving me issues:

$query = mysql_query("SELECT * FROM MegaFilter WHERE Parent = '".$ThisPage."' ");
while($query2 = mysql_fetch_assoc($query)) {
    $SubID = $query2['Filter'];
    $query3 = mysql_query("SELECT * FROM SubCategories WHERE ID = '".$SubID."' ");
    while($query4 = mysql_fetch_assoc($query3)) {
        $SubCatID = $query4['ID'];
        $query5 = mysql_query("SELECT * FROM Web_Pages WHERE SubCat = '".$SubCatID."' ");
    }
    while($query6 = mysql_fetch_assoc($query5)) {
        $ProductName = $query6['Title'];
        $ProductID = $query6['ID'];
    }
    echo '<li class="item" data-id="id-'.$Productid.'" data-type="'.$SubID.'">'.$Productname.'</li>';
}

It does not log any errors except that the last 2 variables are not defined.

You are leaving yourself wide open to SQL injection. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. bobby-tables.com/php has examples to get you started. — Andy Lester
– Andy Lester, Commented Mar 10, 2013 at 5:50
You should look into using SQL joins. You are making far too many calls to the database. If you have 5 rows in MegaFilter and 7 rows in SubCategories for each of those, and then 10 rows in Web_pages for each of those, you're going to be making 350 SELECT calls to your database. Using SQL JOINs will let you get all the data in one call to the database. — Andy Lester
– Andy Lester, Commented Mar 10, 2013 at 6:54

Aiias · Accepted Answer · 2013-03-10 05:41:45Z

1

Variable names are case sensitive in php. You are assigning values to $ProductName and $ProductID but you are using $Productid (lowercase i) and $Productname (lowercase n).

answered Mar 10, 2013 at 5:41

Aiias

4,7481 gold badge20 silver badges34 bronze badges

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

Comments

Ed Heal · Accepted Answer · 2013-03-10 06:00:53Z

Should not be using the mysql_ libraries any more - they are deprecated.
Your braces are all over the place (if I understand what you are trying to achieve see below).
PHP variables are case sensitive.

I think that the code that you require is (just need the one select statement):

$connection = new mysqli('localhost', 'my_user', 'my_password', 'my_db'); 
$stmt = $connection -> mysqli_prepare('SELECT m.Filter AS SubID, w.Title AS ProductName, w.ID AS ProductID FROM MegaFilter AS m, INNER JOIN SubCategories AS sc ON (sc.ID = m.Filter) INNER JOIN Web_Pages AS w ON (w.SubCat = sc.ID) WHERE Parent = ?');
$stmt->bind_param('s', $ThisPage);
$stmt->bind_result($SubID, $ProductName, $ProductID);
$stmt->execute();
while ($stmt->fetch())
{
    echo '<li class="item" data-id="id-'.$ProductID.'" data-type="'.$SubID.'">'.$ProductName.'</li>'
}

Collectives™ on Stack Overflow

SQL won't return values

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related