MySQL

Trying to fetch menu names from the database

[ronny998]

Database query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Please i need help. This my code:

<?php
//3. Perform database query
$categories_name = mysql_query("SELECT name FROM categories", $connection);
if(!$categories_name)
{
die("Database query failed: " . mysql_error());
}

//4. Use the returned data
while($catname = mysql_fetch_array($categories_name))
{
echo "<li>{$catname["name"]}</li>";


$menu = mysql_query("SELECT name FROM categories_menu WHERE categories_reletetoCategories_id = {$catname["categories_id"]}", $connection);
if(!$menu)
{
die("Database query failed: " . mysql_error());
}

//4. Use the returned data

while($menu_set = mysql_fetch_array($menu))
{

echo $menu_set["name"];
}



}

?>
Best Regards

[Matthew1980]

Hi there ronny998,

Welcome to WebmasterWorld [webmasterworld.com ]

I am assuming that you have got a connection set & database selected before the query is sent.

Try this:-

$SQ = "SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = '".$catname['categories_id']."' ";
$menu = mysql_query($SQ, $connection);

NOTE: I prefer to 'build' the query into a variable, this makes it easier to debug, because it's easier to echo the constructed var outside the function.

It looks like you hadn't quoted the value you were sending into the query.

Hope that helps.

Cheers,
MRb

[ronny998]

Hi! You are great. The error is gone but it didn't fetch the menu name i wanted it to. It only fetch this:$categories_name = mysql_query("SELECT name FROM categories", $connection); and
not this: $SQ = "SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = '".$catname['categories_id']."' ";
$menu = mysql_query($SQ, $connection);

[Matthew1980]

Hi there ronny998,

Ok. Check that the query is being constructed correctly first off:-

$SQ = "SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = '".$catname['categories_id']."' ";
echo $SQ;
exit;//use the exit to kill the script as you are only debugging at the moment
$menu = mysql_query($SQ, $connection);

This will now echo the contents of $SQ to the screen so that you can see whether or not the statement is being set/populated as expected.

I'm not sure what you mean by this though:-

It only fetch this:$categories_name = mysql_query("SELECT name FROM categories", $connection);

I checked out the second query as that looked erroneous, the first query looks fine to me.

Cheers,
MRb

[ronny998]

Thanks one more time! This is what i got
# Fruits
# SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = ''Icecream
# SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = ''Popcorn
# SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = ''Snacks
# SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = ''Vegetables
SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = ''

That means everything is fine till There

[ronny998]

Fruits
SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = ''

Icecream
SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = ''

Popcorn
SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = ''

Snacks
SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = ''

Vegetables
SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = ''

This clearer!

[ronny998]

It seems that this code is not working for me:

$menu = mysql_query($SQ, $connection);
if(!$menu)
{
die("Database query failed: " . mysql_error());
}

//4. Use the returned data

while($menu_set = mysql_fetch_array($menu))
{

echo $menu_set["name"];
}
cheers

[Matthew1980]

Hi there ronny998,

Yes that's clearer! It means that the $catname array isn't set (or reference incorrectly); do you have echo "<li>{$catname["name"]}</li>"; echoing as it should (I guess as it is from your last post :)), if that's doing as it should, I would check the name that you are referencing in the key: $catname['categories_id'] to make sure that it is the same as what the column name is in the Sql Database ;) That has a tendency to catch us all out from time to time.

Alternatively you could do this:-

//4. Use the returned data
while($catname = mysql_fetch_array($categories_name))
{
print_r($catname);
}
exit;

This *should* print to screen the contents of the data in the array that has been retrieved from the database, always a good method of checking to see what's actually there...

Hope that makes sense anyway.

Cheers,
MRb

[ronny998]

Hi again! This is driving me crazy, i don't know what to do anymore. I am trying to do a while loop in a while loop and it doesn't seems to work out for me. This my code again:

<?php
//3. Perform database query
$categories_name = mysql_query("SELECT name FROM categories", $connection);
if(!$categories_name)
{
die("Database query failed: " . mysql_error());
}

//4. Use the returned data
while($catname = mysql_fetch_array($categories_name))
{
echo "<li>{$catname["name"]}</li>";

//$menu = mysql_query("SELECT name FROM categories_menu WHERE categories_reletetoCategories_id = categories_id", $connection);
$SQ = "SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = '".$catname['categories_id']."'";

//echo $SQ;
$menu = mysql_query($SQ, $connection);

if(!$menu)
{
die("Database query failed: " . mysql_error());
}

//4. Use the returned data
//echo $menu;
while($menu_set = mysql_fetch_array($menu))
{

echo $menu_set["name"];
}




}

?>

The first while loop worked out fine, that is where this statement is :
$categories_name = mysql_query("SELECT name FROM categories", $connection); It fetches the main category names but trying to do the second while loop which should have fetch the subcategories to line out under the main categories, nothing shows up and there is no error. There are two tables in the datbase, one for categories and the other for categories_menu.

Best Regards

[rocknbil]

Here it is. You're selecting name,

SELECT name FROM categories

then do a while,
while($catname = mysql_fetch_array($categories_name))

then inside the while you do a query on a column that does not exist.

SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = '".$catname['categories_id']."'";

Where did you get $catname['categories_id']? You didn't. :-) This one will always, always slip by you when you quote queries on numeric fields. There's nothing wrong with quoting numeric fields, but they are not necessary and had you left them off, it would have errored, alerting you to the problem. Essentially your select is

SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = ''

and being quoted, it will match on no rows as they all have values.

Try changing this first line like so.

<?php 
//3. Perform database query 
$categories_name = mysql_query("SELECT categories_id, name FROM categories", $connection); 
if(!$categories_name) { 
 die("Database query failed: " . mysql_error()); 
} 
//4. Use the returned data 
while($catname = mysql_fetch_array($categories_name)) { 
 echo "<li>{$catname["name"]}"; 
 // You want your LI to encapsulate the submenu - watch 
 $SQ = "SELECT `name` FROM `categories_menu` WHERE `categories_reletetoCategories_id` = '" . 
 $catname['categories_id'] . "'"; 
 $menu = mysql_query($SQ, $connection); 
 if(!$menu) { 
 die("Database query failed: " . mysql_error()); 
 } 
 while($menu_set = mysql_fetch_array($menu)) { 
 echo $menu_set["name"]; 
 } 
 echo "</li>\n"; 
} 
?> 

[ronny998]

Thanks a million times! My problem was that i didn't include categories_id in first while loop. Thank you, my whole weekend was realy bad trying to solve this problem

Best Regards