SQL syntax near in PHP script
Posted: Sun Mar 26, 2006 11:24 am
im currently doing my dissertation, and iv made a simple MySql database containing the following categories, items, item_size, item_colour tables, the php code for see store is working,, as follows:
.. that code is working fine and my html page is pulling out the necessary data, however the see item.php is not working at all, it has an error message stating You have an error in your SQL syntax near '' at line 3, the code is as follows:
i was wondering if anyone knows the root of this problem, i have proof read the script a zillion times,, im sure theres nothing wrong with it, however i am stil an amateur, so any help or suggestions would be very welcome.
Code: Select all
<?php
//connect database
$conn = mysql_connect ("localhost", "", "")
or die(mysql_error());
mysql_select_db("tiles",$conn);
$display_block = "<h1>Tiles Categories</h1>
<P>Select a category to see items</P>";
//show categories first
$get_cats = "select id, cat_title, cat_desc from
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 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> Tiles Library</TITLE>
</HEAD>
<BODY>
<?php echo $display_block; ?>
</BODY>
</HTML>Code: Select all
<?php
//connect database
$conn = mysql_connect("localhost", "", "")
or die(mysql_error());
mysql_select_db("tiles",$conn) or die(mysql_error());
$display_block = "<h1>Items - Tile Details</h1>";
//validate item
$get_item = "select c.id as cat_id, c.cat_title, si.item_title,
si.item_price, si.item_desc, si.item_image from items as si left join
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_id = mysql_result($get_item_res,0,'cat_id');
$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 trail
$display_block .= "<P><strong><em>You are viewing:</em><br>
<a href=\"seestore1.php?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 colours
$get_colours = "select item_colour from item_colour where
item_id = $item_id order by item_colour";
$get_colours_res = mysql_query($get_colours) or die(mysql_error());
if (mysql_num_rows($get_colours_res) > 0) {
$display_block .= "<P><strong>available colours:</strong><br>";
while ($colours = mysql_fetch_array($get_colours_res)) {
$item_colour = $colours['item_colour'];
$display_block .= "$item_colour<br>";
}
}
//get item width
$get_width = "select item_width from item_width where
item_id = $item_id order by item_width";
$get_width_res = mysql_query($get_width) or die(mysql_error());
if (mysql_num_rows($get_width_res) > 0) {
$dispay_block .= "<P><strong>Available sizes:</strong><br>";
while ($width = mysql_fetch_array($get_width_res)) {
$item_width = $width['item_width'];
$display_block .= "$item_width<br>";
}
}
//get item height
$get_height = "select item_height from item_width where
item_id = $item_id order by item_height";
$get_height_res = mysql_query($get_height) or die(mysql_error());
if (Mysql_num_rows($get_height_res) > 0) {
$dispay_block .= "<P><strong>Available sizes:</strong><br>";
while ($height = mysql_fetch_array($get_height_res)) {
$item_height = $height['item_height'];
$display_block .= "$item_height<br>";
}
}
$display_block .= "
</td>
</tr>
</table";
}
?>
<HTML>
<HEAD>
<TITLE>TILES</TITLE>
</HEAD>
<BODY>
<?php echo $display_block; ?>
</BODY>
</HTML>