Page 1 of 2

SQL syntax near in PHP script

Posted: Sun Mar 26, 2006 11:24 am
by m_haj
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:

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>
.. 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:

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>
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.

Posted: Sun Mar 26, 2006 11:43 am
by feyd
echo your query string to see what is being passed.

On a side note, your code would allow someone to inject SQL commands quite easily. Read the security board's threads on injection to find out more.

Posted: Sun Mar 26, 2006 11:52 am
by m_haj
do you mean like this ::

Code: Select all

$get_item_res = mysql_query($get_item) or die("Query failed: $get_item - " . mysql_error());

Posted: Sun Mar 26, 2006 12:07 pm
by jrd
When you access Mysql, do you log in as root?

Posted: Sun Mar 26, 2006 12:10 pm
by m_haj
no.. $conn = mysql_connect ("localhost", "", "") thats what i use.

Posted: Sun Mar 26, 2006 12:15 pm
by John Cartwright
m_haj wrote:do you mean like this ::

Code: Select all

$get_item_res = mysql_query($get_item) or die("Query failed: $get_item - " . mysql_error());
Or simply..

Code: Select all

echo $get_item;
$get_item_res = mysql_query($get_item) or die("Query failed" . mysql_error());
Generally not best to expose the query to the public, so don't include it in errors.

Posted: Sun Mar 26, 2006 12:19 pm
by jrd

Code: Select all

//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]";
try changing it to...

Code: Select all

//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']. "'";

so you don't use a username and password in your script? You should make 1 for your db.

Posted: Sun Mar 26, 2006 1:10 pm
by m_haj
that actually worked.. but now the page is displaying "Items - Tile Details
Invalid item selection."

Posted: Sun Mar 26, 2006 1:27 pm
by jrd
try replacing

Code: Select all

$_GET[item_id]
with a number and see if it runs, also look at your other select statements.

Posted: Sun Mar 26, 2006 1:36 pm
by m_haj
sorry.. i dont know what you mean.

Posted: Sun Mar 26, 2006 1:38 pm
by feyd
have you echoed the query string like I originally told you to? There will likely be a syntax error in there involving the data in "item_id" .. post it and we'll be able to tell you for sure.

Posted: Sun Mar 26, 2006 1:40 pm
by jrd

Code: Select all

$get_height = "select item_height from item_width where 
    item_id = $item_id order by item_height"
where is $item_id coming from?

Posted: Sun Mar 26, 2006 1:48 pm
by m_haj
thats the result from the echo.. 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 = ''

and $item_id came from the first syntax which displays the categories.

Posted: Sun Mar 26, 2006 1:53 pm
by feyd
as you should see by the echo, $item_id would appear to be blank, possibly nonexistant. I'd verify your previous code to make sure it is being initialized among whatever other things you require. You may have a logic error.

Posted: Sun Mar 26, 2006 2:04 pm
by m_haj
do you mean blank in the database?? or not specified in the previous page?