[SOLVED] problem updating info into table

[jakebur01]

ok heres my problem, I am trying to update new information on a row in my mysql database.

 

This works fine:

$query = "UPDATE `orders` SET `c_phone` = '$c_phone', `c_email` = '$c_email', `card_type` = '$card_type', `card_number` = '$card_number', `card_month` = '$card_month', `card_year` = '$card_year', `card_name` = '$card_name', `three_number` = '$amex_code', `total_amount` = '$c_total', `total_cost` = '$t_price', `total_shipping` = '$c_ship' WHERE `orders` .`ship_name` = '$name'";

 

And this way does not:

$query = "UPDATE `orders` SET `c_phone` = '$c_phone', `c_email` = '$c_email', `card_type` = '$card_type', `card_number` = '$card_number', `card_month` = '$card_month', `card_year` = '$card_year', `card_name` = '$card_name', `three_number` = '$amex_code', `total_amount` = '$c_total', `total_cost` = '$t_price', `total_shipping` = '$c_ship' WHERE `orders` .`ship_name` = '$name' and `orders` .`amount` ='$t_price'";

 

I get my problem when I added the  {and `orders` .`amount` ='$t_price'} part on the end. I need to match the new information with the row by using two values " by name and total".

 

I have tried printing the querys out in all different ways. When I paste it into the sql, it accepts it but it has 0 affected rows even when amount does equal $t_price.

[Caesar]

<?php

$query = "UPDATE `orders` SET `c_phone` = '$c_phone' WHERE `ship_name` = '$name' &&  `amount` = '$t_price'";
//Shortened query to make it more visible
?>

[jakebur01]

I am still getting Affected rows: 0

 

It has something to do with using two values. It works fine with one, but when I add an additional one no matter what it is it said 0 affected rows.

[bbaker]

could it be that the value in the 'amount' field does not match the value of $t_price?

[Caesar]

That is what I'm thinking. Print out the value in your browser prior to the update query....see if it matches. Are there any whitespaces being added? How did you format the `amount` column/field in the database?

[jakebur01]

Could be.  I could use just one value if I could get the auto-incremented number off of my last insertion.

 

How would I do this? If on the previous page I inserted data into a new row and has it auto-increment a number under the column `orderid` ? I want to find out what number it created when it inserted the data and store that number into a session.

[bbaker]

if you're updating an order, you should be able to retrieve the orderid pretty easily, then use that in your WHERE statement.

[jakebur01]

you were right about the number deal bbaker. Any column I take that has a number value, when i go to sql it I get 0 affected rows whether it's the orderid, amount, or whatever. Do I need to use some type of number format function when sqling?

[bbaker]

when checking against the database the field type shouldn't really matter, the value should.  So if you're orderid in the database is "1234" and the value you're checking with is "1234", it should work.

 

I'm assuming you're using a form to update the other info.  When you pull the other info from the database, pull the orderid as well.  Then submit it (maybe as a hidden input field) with the other info.

 

Then do your validation and run your query.

<?php
//short version
$query = "UPDATE `orders` SET `c_phone` = '$c_phone' WHERE `orderid` = ' " . $_POST['orderid'] . " ' ";
?>

[redarrow]

This is bad programming habit?

 

never update without minuim addslashes and never use the POST in the query ok.

<?php
//short version
$query = "UPDATE `orders` SET `c_phone` = '$c_phone' WHERE `orderid` = ' " . $_POST['orderid'] . " ' ";
?>

 

corrected

 

<?php
//correct version

// database connection.

$c_phone=addslashes($_POST['c_phone']);
$orderid=addslashes($_POST['orderid']);

$query = "UPDATE `orders` SET `c_phone` = '$c_phone' WHERE `orderid` = ' $orderid ' ";
$result=mysql_query($query)or die("mysql_error()");

if(mysql_affected_rows($result)){

echo" database updated";

}else{

echo"database not updated";
}
?>

 

[redarrow]

Your concept is wrong first select the criteria the = what ever then  update and only use a where cluse with no and/&& ok.

 

 

 

example

 

<?php
//correct version

// database connection.


$c_phone=addslashes($_POST['c_phone']);
$orderid=addslashes($_POST['orderid']);

$query="SELECT * FROM `payments` WHERE `c_phone`='442423442423' and `orderid`='3293494'";

$result=mysql_query($query)or die("mysql_error"); 

while($x=mysql_fetch_assoc($result)){

$c_phone=$x['c_phone'];
$orderid=$x['order_id'];

$query1 = "UPDATE `orders` SET `c_phone` = '$c_phone' WHERE `orderid` = ' $orderid ' ";
$result1=mysql_query($query1)or die("mysql_error()");

if(mysql_affected_rows($result1)){

echo" database updated";

}else{

echo"database not updated";
}
}
?>

[jakebur01]

Ha ha, your right! I don't know what I was thinking.

[redarrow]

so i solved your issue press solved ok.

 

 

good luck.

 

here a link to remind you off mysql commands ok add to fav ok

 

http://www.sql-tutorial.net/SQL-tutorial.asp