Forms, numeric data, and update

[PaulaRun]

I am doing a page that will allow the user to update the database. One of the fields is price. When I update the db, I'm getting an error and I think it could be because I'm trying to update the numeric field in the db with data coming from a text box. Any suggestions?

Thanks!

[Kudose]

You should post your code, and the layout of your table

[PaulaRun]

table:

while($row = mysql_fetch_assoc($result)) {
	   	echo "<br><font size=\"2\">";
    	echo mysql_affected_rows()." record found.";

	echo '<form action="" method="POST" onSubmit="return Validator(this)"><p><font size="2">';
	echo "<input type=\"hidden\" name=\"prodno\" value=\"".$row["prod_no"]."\">";	
	echo "<input type=\"text\" name=\"name1\" size=\"20\" maxsize=\"30\" value=\"".$row["prod_name"]."\"><font size=\"2\">&nbsp; Product Name&nbsp; </font><br>";
	echo "<input type=\"text\" name=\"price1\" size=\"5\" maxsize=\"5\" value=\"".$row["price1"]."\"><font size=\"2\">&nbsp; Price 1<br></font>";
    echo '</form>';
}

update:

if($updt == "Update") {

include "conndb.php";

$query = "update products set prod_name = $name1, price1= $price1, where prod_no = $prodno" or die(mysql_error());

$result = mysql_query($query);

if (!$result) {
    echo "Could not successfully run query from DB: " . mysql_error();
    exit;
} else {

echo "Record updated.";
    exit;
}
}

Thanks.

[rheroux]

Well, I always split my queries up like so:

<?php

if($updt == "Update") {

include "conndb.php";

$query = "update products set prod_name = '".$_POST['name1']."', price1 = ".$_POST['price1'].", where prod_no = '".$_POST['prodno']."'" or die(mysql_error());

$result = mysql_query($query);

if (!$result) {
    echo "Could not successfully run query from DB: " . mysql_error();
    exit;
} else {

echo "Record updated.";
    exit;
}
}
?>

It's usually best to use the $_POST array as well, since if you ever come across another variable that uses the same name (such as a session variable), then things can get messed up.

Also, note that there are no single quotes around the numeric update. If it's just a straight number that's being updated/inserted, there's no need for single quotes around the variable.

[Kudose]

<?
while($row = mysql_fetch_assoc($result)) {
           echo "<br><font size=\"2\">";
        echo mysql_affected_rows()." record found.";

    echo '<form action='update.php' method='POST'><p><font size='2'>';
    echo "<input type='hidden' name='prodno' value='.$row['prod_no'].'>";    
    echo "<input type='text' name='name1' size='20' maxsize='30' value='.$row['prod_name'].'><font size='2'>&nbsp; Product Name&nbsp; </font><br>";
    echo "<input type='text' name='price1' size='5' maxsize='5' value='.$row['price1'].'><font size='2'>&nbsp; Price 1<br></font>";
    echo '</form>';
}
?>

<?
    include "conndb.php";

$query = "UPDATE products SET prod_name = '$name1', price1= '$price1', WHERE prod_no = '$prodno'" or die(mysql_error());

$result = mysql_query($query);

if (!$result) {
    echo "Could not successfully run query from DB: " . mysql_error();

} else {

echo "Record updated.";

}

?>

Try this.

[PaulaRun]

I've tried both ways to no avail. I keep getting the following error: Could not successfully run query from DB: You have an error in your SQL syntax near 'where prod_no = '1'' at line 1

kudose - I tried your way but was unable to use action = update.php because that form is for the inquiry. So I included the action.php in the if($updt == "Update") part.

I have a (stupid) question. Why use php_self for action when I can use action = ""? To use $_POST, do I have to use php_self?

I'm so confused!

Thanks!

[Kudose]

You would probably want to check into using $PHP_SELF

and you need to change

prod_no = '$prodno'" or die(mysql_error());

to

prodno = '$prod_no'" or die(mysql_error());

becuase it is this way in your form

[cgraz]

change your query to this. notice the added single quotes and i deleted the comma right before the where.

But I think the error is because you shouldn't be using mysql_error() here because all this is is a string. mysql_error() goes when you execute the string using mysql_query

$query = "update products set prod_name='$name1', price1='$price1' where prodno='$prodno'";
$result = mysql_query($query) or die(mysql_error());

And if that outputs an error, then print the string $query and take a closer look at it. If it's a numeric field, and someone tries to insert "$20", i don't think it gives you an actual error, it just doesn't update that field becuase a dollar sign isn't numeric. You may also want to strip dollarsigns before inserting into db.

Cgraz

[PaulaRun]

Thanks for the information. The comma was the culprit for the query error. And I didn't know that I wasn't supposed to use mysql_error() with the string. It does work though.

I really appreciate your time!