update db rows from textbox

textbox update set

[indiguy]

Hi, this is part of my code, it queries ok, but will not update! ive tried heaps. push submit and then check the row, and nothing changes. Im lost, and someone help plz?

oh, this code is within "mod_php" module plugin in Joomla CMS.

--------------
// Select every record from the table or display error
$menu1 = mysql_query("select cp_teas.* from cp_teas") or die(mysql_error());
while ( $row = mysql_fetch_array($menu1) ) //return results line by line
{
// For the javascript function above: onkeyup='checkField(this);', see below.
echo "<tr>";
echo "<input type='hidden' name='id' value='".$row['tea_id']."'>";
echo "<td><input type='text' size = 10 name='choice' onkeyup='checkField(this);' value='".$row['tea_choice']."'></td>";
echo "<td>"."&nbsp;&nbsp"."<input type='text' size = 45 name='description' onkeyup='checkField(this);' value='".$row['tea_desc']."'>"."..."."</td>";
echo "<td>"."&nbsp;&nbsp\$"."<input type='text' size = 3 id='price' name='price' value='".$row['tea_price']."'></td>";
echo "<td><input type='submit' value='Update' name='button1' ></td>";
echo "<td><input type='submit' value='Delete' name='button2'action='javascript:confirm(<? echo '".$row['tea_id']."','".$row['tea_choice']."','".$row['tea_desc']."','".$row['tea_price']."'; ?>)' ></td>";
echo "</tr>";
// update button function
if(isset($_POST['button1']))
{
$choice = $_POST['choice'];
$description = $_POST['description'];
$price = $_POST['price'];
// remove spaces before and after input.

// Set new data
$update_row = mysql_query("update cp_teas SET tea_choice = '".$description."', tea_desc = '".$choice."', tea_price = '".$price."' where tea_id = '".$row['tea_id']."'") or die(mysql_error());
mysql_query($update_row);
}//if isset
// delete button function
----------
plz note: Ive tried '$choice' and '".$choice."' but makes no difference

[rocknbil]

I'm a little confused, but I'm going on the presumption that the first part of the sample code is the form, and the second is the code after that form is submitted. If it's all in the same task/process of the script, of course it's going to set the data to the same - it hasn't changed yet. (This is the source of my confusion.)

Look at this:

echo "<input type='hidden' name='id' value='".$row['tea_id']."'>";

Then you do this select:

$myquery= "update cp_teas SET tea_choice = '".$description."', tea_desc = '".$choice."', tea_price = '".$price."' where tea_id = '".$row['tea_id']."'";

(Note that storing your selects in variables will **always** make changes and debugging much easier. )
$update_row = mysql_query($myquery) or die(mysql_error());

If you added this right after compiling the select in $myquery,

echo "$myquery<br>\n";

You would probably see something like this:

"update cp_teas SET tea_choice = 'this is the description', tea_desc = 'this is the dchoice', tea_price = '22.95' where tea_id = '';

Where tea_id is blank or null. This is where quoting integer values*** can throw you a curve. If left unquoted, that would have given you an error, but as is, it slipped by you. What you want, judging by the first part of the code, is this (bolded:)

$choice = $_POST['choice'];
$description = $_POST['description'];
$price = $_POST['price'];
$rec_id = $_POST['id'];
$myquery= "update cp_teas SET tea_choice = '".$description."', tea_desc = '".$choice."', tea_price = '".$price."' where tea_id = '".$rec_id."'";

*** Many believe you "should not" quote integer values with mySQL because the database is "expecting a string" and will enter a null value. I have never seen this happen, and believe it's a PHP coders confusion of the way PHP works and mySQL works. You can quote integer values when reading or writing to mySQL, but if you attempt to enter a non-integer value it will error. If invalid data is quoted in a select statement on an integer field,

update table set integer_field='one two three four' where id=1234

It will make the field null or zero, if you have the default set for zero on that field. But if the data is valid numeric data, the following are equivalent.

update table set integer_field='1234' where id=1234
update table set integer_field=1234 where id=1234

[indiguy]

Hi,

Ive solved the problems large and wide.. but Im trying now to insert a new row.
I think its a phase problem or something.

Whats happening is that when i enter text into the textbox, the results after the screen is refreshed to show the new row, is that instead of what i entered, i only see one zero per field and not the text. I dont know what as in my update query, everything works !

The db is not empty, and its autoincrement.

My code:

echo "<td><input type=\"text\" size = 10 name=\"choice_$id\"></td>";
echo "<td>"." "."<input type=\"text\" size = 45 name=\"description_$id\" ></td>";
echo "<td>"." \$"."<input type=\"text\" size = 3 name=\"price_$id\" ></td>";
echo "<td><input type=\"submit\" value=\"Add New\" name=\"buttonA_$id\" ></td>";
echo "</tr>";
echo "</center></table></form>";
// insert new data.
if(isset($_POST["buttonA_$id"]))
{
$choice = $_POST["choice_$id"];
$description = $_POST["description_$id"];
$price = $_POST["price_$id"];
$rec_id = $_POST[""];
// Set new data
$new_row = "insert into cp_teas ( tea_choice, tea_desc, tea_price ) values( tea_choice = '".$choice."', tea_desc = '".$description."', tea_price = '".$price."')";
mysql_query($new_row) or die(mysql_error());
echo "Changes have been made!";
echo "$new_row<br>";
}

[indiguy]

SOLVED:

code:

new_row = "insert into cp_teas set tea_choice = '".$choice."', tea_desc = '".$description."', tea_price = '".$price."'";

note: Since its php and using textbox etc..
use SET, not the traditional sql - insert into cp_teas ( tea_choice, tea_desc, tea_price ) values( tea_choice....