MySQL/PHP Question

twilitegxa · September 4, 2008

I am working with this code from a tutorial:

<?php
//connect to database
$conn = mysql_connect("localhost", "root", "")
or die(mysql_error());
mysql_select_db("smrpg",$conn) or die(mysql_error());

$display_block = "<h1>My Categories</h1>
<p>Select a category to see its items.</p>";

//show categories first
$get_cats = "select id, cat_title, cat_desc from
store_categories order by cat_title";
$get_cats_res = mysql_query($get_cats) or die(mysql_error());

if (mysql_num_rows($get_cats_res) < 1) {
$display_block = "<p><em>Sorry, no categories to browse.</em></p>";
} else {

while ($cats = mysql_fetch_array($get_cats_res)) {
$cat_id = $cats[id];
$cat_title = strtoupper(stripslashes($cats[cat_title]));
$cat_desc = stripslashes($cats[cat_desc]);

$display_block .= "<p><strong><a
href=\"$_SERVER[php_SELF]?cat_id=$cat_id\">$cat_title</a></strong>
<br />$cat_desc</p>";

if ($_GET[cat_id] == $cat_id) {
//get items
$get_items = "select id, item_title, item_price
from store_items where cat_id = $cat_id
order by item_title";
$get_items_res = mysql_query($get_items) or die(mysql_error());

if (mysql_num_rows($get_items_res) < 1) {
$display_block = "<p><em>Sorry, no items in this category.</em></p>";
} else {

$display_block .= "<ul>";

while ($items = mysql_fetch_array($get_items_res)) {
$item_id = $items[id];
$item_title = stripslashes($items[item_title]);
$item_price = $items[item_price];

$display_block .= "<li><a
href=\"showitem.php?item_id=$item_id\">$item_title</a>
</strong> (\$$item_price)";
}

$display_block .= "</ul>";
}
}
}
}
?>
<html>
<head>
<title>My Categories</title>
</head>
<body>
<?php print $display_block; ?>
</body>
</html>

And the second page:

<?php
//connect to database
$conn = mysql_connect("localhost", "root", "")
or die(mysql_error());
mysql_select_db("smrpg",$conn) or die(mysql_error());

$display_block = "<h1>My Store - Item Detail</h1>";

//validate item
$get_item = "select c.cat_title, si.item_title, si.item_price,
si.item_desc, si.item_image from store_items as si left join
store_categories as c on c.id = si.cat_id where si.id = 

$_GET[item_id]";

$get_item_res = mysql_query($get_item) or die(mysql_error());

if (mysql_num_rows($get_item_res) < 1) {
//invalid item
$display_block .= "<p><em>Invalid item selection.</em></p>";
} else {
//valid item, get info
$cat_title = strtoupper(stripslashes(
mysql_result($get_item_res,0,'cat_title')));
$item_title = 

stripslashes(mysql_result($get_item_res,0,'item_title'));
$item_price = mysql_result($get_item_res,0,'item_price');
$item_desc = 

stripslashes(mysql_result($get_item_res,0,'item_desc'));
$item_image = mysql_result($get_item_res,0,'item_image');

//make breadcrumb trail
$display_block .= "<p><strong>You are viewing:</em><br />
<a href=\"seestore.php?cat_id=$cat_id\">$cat_title</a>
> $item_title</strong></p>

<table cellpadding=3 cellspacing=3>
<tr>
<td valign=middle align=center><img src=\"$item_image\"></td>
<td valign=middle><p><strong>Description:</strong><br 

/>$item_desc</p>
<p><strong>Price:</strong> \$$item_price</p>";

//get colors
$get_colors = "select item_color from store_item_color where 

item_id = $item_id order by item_color";
$get_colors_res = mysql_query($get_colors) or die(mysql_error());

if (mysql_num_rows($get_colors_res) > 0) {

$display_block .= "<p><strong>Available Colors:</strong><br />";

while ($colors = mysql_fetch_array($get_colors_res)) {
$item_color = $colors['item_color'];

$display_block .= "$item_color<br />";
}
}

//get sizes
$get_sizes = "select item_size from store_item_size where
item_id = $item_id order by item_size";
$get_sizes_res = mysql_query($get_sizes) or die(mysql_error());

if (mysql_num_rows($get_sizes_res) > 0) {

$display_block .= "<p><strong>Available Sizes:</strong><br />";

while ($sizes = mysql_fetch_array($get_sizes_res)) {
$item_size = $sizes['item_size'];

$display_block .= "$item_size<br />";
}
}

$display_block .= "
</td>
</tr>
</table>";

}
?>
<html>
<head>
<title>My Store</title>
</head>
<body>
<?php print $display_block; ?>
</body>
</html>

That problem is, the second page displays an error:

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 'order by item_color' at line 1

I can't figure out what is causing the error I've looked over the code several times, and still don't see it. Can anyone help me figure out the problem?

dv90 · September 4, 2008

I would guess that you forgot the beginning " in:

$item_id order by item_color";

I think it should be:

$item_id . "order by item_color";

twilitegxa · September 4, 2008

But if this is the code:

//get colors
$get_colors = "select item_color from store_item_color where 
item_id = $item_id order by item_color";
$get_colors_res = mysql_query($get_colors) or die(mysql_error());

Then the first " is where is says $get_colors = "select item_color...

Any other suggestions?

elflacodepr · September 5, 2008

try this:

$get_colors = "SELECT item_color FROM store_item_color WHERE item_id = '$item_id' ORDER BY item_color";

if not, echo the query to see what it shows, example:

echo $get_colors = "select item_color from store_item_color where item_id = $item_id order by item_color";

twilitegxa · September 6, 2008

I tried the first suggestion:

$get_colors = "SELECT item_color FROM store_item_color WHERE item_id = '$item_id' ORDER BY item_color";

But that just generated another error, so I tried the second suggestion, but I don't know if I did it right because it still had errors. How can I echo it without the errors? What code should I get rid of to see what the echo shows so the errors won't be in the way?

fenway · September 6, 2008

I don't know what you mean... echo that string.

twilitegxa · September 20, 2008

When I tried echoing that string, it still would not show anything because of the error it was already showing. But, I ried taking out the lines that weer showing the errors, which are these listed below:

//get colors
$get_colors = "select item_color from store_item_color where
item_id = $item_id order by item_color";
$get_colors_res = mysql_query($get_colors) or die(mysql_error());

if (mysql_num_rows($get_colors_res) > 0) {

$display_block .= "<p><strong>Available 

Colors:</strong><br>";

while ($colors = mysql_fetch_array($get_colors_res)) {
	$item_color = $colors['item_color'];

	$display_block .= "$item_color<br>";
}
}

//get sizes
$get_sizes = "select item_size from store_item_size where
$item_id = $item_id order by item_size";
$get_sizes_res = mysql_query($get_sizes) or die(mysql_error());

if (mysql_num_rows($get_sizes_res) > 0) {

$display_block .= "<p><strong>Available 

Sizes:</strong><br>";

while ($sizes = mysql_fetch_array($get_sizes_res)) {
	$item_size = $sizes['item_size'];

	$display_block .= "$item_size<br>";
}
}

Now the code works just fine, without errors, but since I took this code out, it's not displaying the colors available or sizes available. How can I fix the above code to include tghe sizes and colors available on my page? Here is the entire code that generates the error:

<?php

//Access Tracking Snippet

//set up static variables
$page_title = "showitem.php";
$user_agent = getenv("HTTP_USER_AGENT");
$date_accessed = date("Y-m-d");

//connect to server and select database
$conn = mysql_connect("localhost", "root", "")
or die(mysql_error());
$db = mysql_select_db("smrpg", $conn) or die(mysql_error());

//create and issue query
$sql = "insert into access_tracker values
('', '$page_title', '$user_agent', '$date_accessed')";
mysql_query($sql,$conn);
?>

<?php
//connect to database
$conn = mysql_connect("localhost", "root", "")
or die(mysql_error());
mysql_select_db("smrpg", $conn) or die(mysql_error());

$display_block = "<h1>My Store - Item Detail</h1>";

//validate item
$get_item = "select c.cat_title, si.item_title, si.item_price,
si.item_desc, si.item_image from store_items as si left join
store_categories as c on c.id = si.cat_id where si.id = $_GET[item_id]";

$get_item_res = mysql_query($get_item) or die(mysql_error());

if (mysql_num_rows($get_item_res) < 1) {
//invalid item
$display_block .= "<p><em>Invalid item selection.</em></p>";
} else {
//valid item, get info
$cat_title = strtoupper(stripslashes(
mysql_result($get_item_res,0,'cat_title')));
$item_title = stripslashes(mysql_result($get_item_res,0,'item_title'));
$item_price = mysql_result($get_item_res,0,'item_price');
$item_desc = stripslashes(mysql_result($get_item_res,0,'item_desc'));
$item_image = mysql_result($get_item_res,0,'item_image');

//make breadcrumb trail
$display_block .= "<p><strong><em>You are viewing:</em><br>
<a href=\"seestore.php?cat_id=$cat_id\">$cat_title</a>
> $item_title</strong></p>

<table cellpadding=3 cellspacing=3>
<tr>
<td valign=middle align=center><img src=\"$item_image\"></td>
<td valign=middle><p><strong>Description:</strong><br>$item_desc</p>
<p><strong>Price:</strong> \$$item_price</p>";

//get colors
$get_colors = "select item_color from store_item_color where
item_id = $item_id order by item_color";
$get_colors_res = mysql_query($get_colors) or die(mysql_error());

if (mysql_num_rows($get_colors_res) > 0) {

$display_block .= "<p><strong>Available Colors:</strong><br>";

while ($colors = mysql_fetch_array($get_colors_res)) {
	$item_color = $colors['item_color'];

	$display_block .= "$item_color<br>";
}
}

//get sizes
$get_sizes = "select item_size from store_item_size where
$item_id = $item_id order by item_size";
$get_sizes_res = mysql_query($get_sizes) or die(mysql_error());

if (mysql_num_rows($get_sizes_res) > 0) {

$display_block .= "<p><strong>Available Sizes:</strong><br>";

while ($sizes = mysql_fetch_array($get_sizes_res)) {
	$item_size = $sizes['item_size'];

	$display_block .= "$item_size<br>";
}
}

$display_block .= "
</td>
</tr>
</table>";

}

echo $get_colors = "select item_color from store_item_color where item_id = $item_id order by item_color";
?>
<html>
<head>
<title>My Store</title>
</head>
<body>
<?php print $display_block; ?>
</body>
</html>

And again, here is the error it generates:

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 'order by item_color' at line 2

Any help would be greatly appreciated!

twilitegxa · September 20, 2008

Here is the actual tutorial for this particular page in question (I found it online!):

http://etutorials.org/Programming/PHP+MySQL+and+Apache+in+24+hours/Part+IV+Simple+Projects/Hour+

20.+Creating+an+Online+Storefront/Displaying+Items/

fenway · September 29, 2008

You're echoing a statement... just echo the right-hand side:

echo "select item_color from store_item_color where item_id = $item_id order by item_color";

Sign In

MySQL/PHP Question

Recommended Posts

twilitegxa

Link to comment

Share on other sites

dv90

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

elflacodepr

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

twilitegxa

Link to comment

Share on other sites

fenway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information