SQL statement

ok well i've created a table using this code:

CREATE TABLE `stock`(
`item_id` INT NOT NULL AUTO_INCREMENT,
`item_code` VARCHAR(20) NOT NULL ,
`item_desc` VARCHAR(60) NOT NULL ,
`item_stock` INT(20) NOT NULL ,
PRIMARY KEY (`item_id`));

Code (markup):

ive created a form where the user inserts a search code for item_code,
all i wont is the sql to search item_code value and then echo the row onto the page.

this is the SQl i attempted:

$query = "SELECT * FROM $mysql_table WHERE item_code = $item_id";
$result = mysql_query($query) or die(mysql_error());

Code (markup):

but all i get is Resource id #3 when i echo $result

 

here is a update to the code ive done, but something is still wrong...

<?php
$search_db = htmlspecialchars(addslashes($_POST['search_db']), ENT_QUOTES);

if(isset($_POST['search_db'])) {
if(!$item_search) {

echo "No search entered, please go back and fill in the fields properly.";
}
else {
$query = mysql_query("SELECT * FROM stock WHERE item_code LIKE '%$search_db%'");
$resultnum = mysql_num_rows($query);

if($resultnum>0) { 
while($row=mysql_fetch_array($query)) {
echo "$row";
}
}
}

}
?>

Code (markup):

 

The $row is an array, so I guess that's why you can't echo it directly.

Try a little something like this:

while($row = mysql_tech_array($query)) {
$item_id = $row['item_id'];
$item_code = $row['item_code'];
etc.

echo "$item_id $item_code<br>";
}

 

ok ive got my code working fine now !!!!


just one more thing, i wont to be able to update the fields,

item_desc, item_code, item_stock

so some sql using the update statement that will update into these 3 groups on the same row. i suk at mysql !!!!

 

mysql_query("UPDATE stock SET item_desc = $item_desc, item_code = $item_code, item_stock = $item_stock, WHERE item_id = $item_id");

if i understand your question correctly

 

ok this is the code i used,

i dont get any errors or nothing i know everything works in it apart from the sql,

if(isset($_POST['update_db'])) {
if(!$item_code) {
echo "<span class=\"red\">Please fill the item code field</span>";
}
else if(!$item_desc) {
	echo"<span class=\"red\">Please fill in the item description field</span>";  
}
else if (!$item_stock || $item_stock > 100) {
 echo '<span class="red">Stock must be a number between [1 - 100]</span>';
} 
else {

$sql=("UPDATE stock SET item_desc = $item_desc, item_stock = $item_stock, WHERE item_code = $item_code");
	
	 	 $result=mysql_query($sql);


} 

Code (markup):

can anything think what could be wrong ??

 

i think you forgot to put another bracket '}' after the else statement.

 

nope there is another bracket just havent pasted it in, if i did miss a bracket i wld get errors.

 

try this version of code

<?php
if(isset($_POST['update_db'])) 
{
	$item_code = $_POST['item_code'];
	$item_desc = $_POST['item_desc'];
	$item_stock = $_POST['item_stock'];
	
	if(!$item_code || !$item_desc || !$item_stock || ($item_stock > 100)) 
	{
		if(!$item_code)
		echo "<span class=\"red\">Please fill the item code field</span><br>";
		if(!$item_desc)
		echo"<span class=\"red\">Please fill in the item description field</span><br>";
		if (!$item_stock || $item_stock > 100)
		echo "<span class=\"red\">Stock must be a number between [1 - 100]</span><br>";
	}
	else 
	{
		$sql=("UPDATE stock SET item_desc = $item_desc, item_stock = $item_stock, WHERE item_code = $item_code");
		$result=mysql_query($sql);
	}

}
?>

Code (markup):

 

nope that code doesnt work either, i know its deffently the mysql code not working because i echod back the variables and worked fine, so if this is the structure of my table:

CREATE TABLE `stock`(
`item_id` INT NOT NULL AUTO_INCREMENT,
`item_code` VARCHAR(20) NOT NULL ,
`item_desc` VARCHAR(60) NOT NULL ,
`item_stock` INT(20) NOT NULL ,
PRIMARY KEY (`item_id`));

Code (markup):

and im using this update command

$sql=("UPDATE stock SET item_desc = $item_desc, item_stock = $item_stock, WHERE item_code = $item_code");
		$result=mysql_query($sql);

Code (markup):

 

could it be because item_code isnt my primary key and item_id is ?

 

not neccessarily, but it could be.

try to add this line at the end of the script and see what it says:
print mysql_error();

 

Try..

$sql= "
UPDATE stock 
SET item_desc = '$item_desc', 
item_stock = '$item_stock '
WHERE item_code = '$item_code'";
		
$result = mysql_query($sql) or die(mysql_error());

PHP:

 

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 'WHERE item_code = 111' at line 1

 

the variable, $item_desc. does it has any special chars in it? like " or ' or anything similar? a small thing like this could also mess up mysql queries.

 

Agent_Smith, i could possibly kiss you haha, thanks alot works now. knew it was the sql and thanks to bluecape for helping me aswel !!!!

 

nah wasn't any characters screwing it up, i escaped all dangerous charactors, was just the sql syntex

 

No problem.

You needed some ' in the variables.

 

glad u found the answer to your problem dean5000v. and thank you agent_smith for helping us out. really appreciated it.