[SOLVED] help updating products

[nichanson]

MySQL version  5.0.51a-community

 

Sorry I'm new on this board and don't normally post so excuse my lack of knowledge. I and trying to update a product information table but it just doesn't update saying just Mysql error. I know for sure the variables are being passed onto the update page but it doesn't insert it into my database (I hope that makes sense) anyway below is my two files:

# my form opening is: <form action="test.php" method="post"> #

 

A: Prawns.php

<?php
$number=0;
$sql = "SELECT * FROM products WHERE catagory='prawn'";
$query = mysql_query($sql);
while($row = mysql_fetch_array($query)) {
$pro_id = $row['code'];
$_POST[$pro_id];
echo "<tr><th>".$number." ".$row['code'];
echo "<input type='hidden' value='".$row['code']."' name='code' width='10px'></th>";
echo "<th><input type='text' value='".$row['name']."' name='name'></th>";
echo "<th><input type='text' value='".$row['packaging']."' name='packaging'></th>";
echo "<th><input type='text' value='".$row['description']."' name='description'></th>";
echo "<th><a href='info.php?code=".$row['code']."'><img src='../images/plus.png'></a></th>";
echo "<th><input type='submit' value='submit' name='submit'><br></th></tr>";
$number++;
}

?>
</form>

 

b: update.php

<?php

$host="localhost"; // Host name
$username="#####"; // Mysql username
$password="####"; // Mysql password
$db_name="######"; // Database name
$tbl_name="#####"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// update
$sql="UPDATE $tbl_name SET 
name='".$_GET['name']."', 
long_name='".$_GET['long_name']."', 
section='".$_GET['section']."', 
photo='".$_GET['photo']."', 
description='".$_GET['description']."', 
short_desc='".$_GET['short_desc']."', 
long_desc='".$_GET['long_desc']."' 	
WHERE code='".$_GET['code']."'";

//check data
$result=mysql_query($sql);
if($result){
        echo "Updated sucessfully";
else{
echo "Mysql error";
}

//end

mysql_close();

?>

[Barand]

your form method is POST but you are using $_GET

[nichanson]

sorry for not saying but I did replace the $_get to a $_post but it still didn't input it. Maybe its just a bad bit of coding.

[Barand]

So the code you posted is NOT the same as the code that's giving a problem. I'm not going to waste my time.

[nichanson]

what I'm saying is I first used $_post but it didn't work so I tried $_get which I'm using at the moment (but as you said my method was to post so it wouldn't work anyway, which I realise now).

anyway I think I'll just start from scratch again. sorry for the confusion but I think I'll just have to go back to some good tutorials on the subject. back to basics for a newbie  :-\

[Barand]

$_post won't work. Has to be $_POST (variable names are case-sensitive in PHP)

[nichanson]

thx for you patience,

This is what my new updated version look like, but sadly it still says "Mysql error":

 

update.php

<?php
//get variables
$id=$_POST['id'];
$code=$_POST['code'];
$name=$_POST['name'];
$long_name=$_POST['long_name'];
$section=$_POST['section'];
$photo=$_POST['photo'];
$packaging=$_POST['packaging'];
$description=$_POST['description'];
$long_desc=$_POST['long_desc'];

//connect to database
$username="###";
$password="###";
$database="###";
mysql_connect(localhost,$username,$password);

//update database
$query="UPDATE products SET name=$name, long_name=$long_name, section=$section, photo=$photo, description=$description, long_desc=$long_desc WHERE code=$code";
$result = mysql_query($query);

//check results
if($result){
echo "Record Updated";}
else{
echo "Mysql error";}
mysql_close();
?>

 

[Barand]

you need '..' around string variables

 

SET name='$name', long_name='$long_name'...  etc

[nichanson]

thx thx thx for you help

it turns out I overlooked afew of things as I added a mysql_error() command at the end of my code and corrected all my mistakes plus implemented all your tips, heres my code if it may be of interest:

<?php
//get variables
$id=$_POST['id'];
$code=$_POST['code'];
$name=$_POST['name'];
$long_name=$_POST['long_name'];
$section=$_POST['section'];
$photo=$_POST['photo'];
$packaging=$_POST['packaging'];
$description=$_POST['description'];
$long_desc=$_POST['long_desc'];

//connect to database
$username="###";
$password="###";
$database="###";
mysql_connect(localhost,$username,$password);
mysql_select_db($database);

//update database
$query="UPDATE products SET name='$name', long_name='$long_name', catagory='$section', photo='$photo', description='$description', long_desc='$long_desc' WHERE code='$code'";
$result = mysql_query($query);

//check results
if($result){
echo "Record Updated";}
else{
echo "Mysql error because: " . mysql_error();}
mysql_close();
?>

[Barand]

Now you just need to guard against malicious sql injection and sanitize all user inputs (GET, POST or COOKIE)

 

<?php
function clean($data)
{
    $data = get_magic_quotes_gpc() ? stripslashes($data) : $data;
    return mysql_real_escape_string($data);
}

$code = clean($_POST['code']);  // etc for all items
?>

 

Or as your variables all have the same names as the fields

 

foreach ($_POST as $fld => $val)
{
    $$fld = clean($val);
}

[nichanson]

Thanks guys for all you help and I was also wondering about sql injections so your advice was much needed!

I just have one more question if anyone has time, how do I return to the same page I came from as there'll be multiple pages coming to this page (For example: prawns.php & shellfish.php & squid.php will come to this update.php page to be updated and users will just quickly return to their original page - if that makes sense) any suggestions more then welcome.

Nick