MySQL/PHP UPDATE Syntax error

[dalefish]

Ok, I've had enough of looking at the same piece of code now. Someone please enlighten me as to what simple bloody thing it is I'm missing:

$pp2 = $_REQUEST["pp"];
$io2 = $_REQUEST["io"];
$sn2 = $_REQUEST["sn"];
$re2 = $_REQUEST["re"];
$sd2 = $_REQUEST["sd"];
$ed2 = $_REQUEST["ed"];
$dp2 = $_REQUEST["dp"];
$rn2 = $_REQUEST["rn"];

$pp = "Product Price";
$io = "Model No";
$sn = "Serial No";
$re = "Ref";
$sd = "Start Date";
$ed = "End Date";
$dp = "Date Paid";
$rn = "Reciept No";

$query = "UPDATE $table SET '$io='$io2', $pp='$pp2', $sn='$sn2', $re='$re2', $sd='$sd2', $ed='$ed2', $dp='$dp2', $rn='$rn2' WHERE ID='$id'";
echo $query."<br><br>";
mysql_select_db($db, $link) or die ($db . " Database not found. " . $user);
mysql_db_query ($db, $query) or die (mysql_error());

The echo $query line is just in there to reassure me I'm not going insane. It's producing:

UPDATE loandata SET Model No='AP22', Product Price='39.0000', Serial No='220801', Ref='TLS00700', Start Date='2008-01-25', End Date='2008-02-25', Date Paid='2008-02-13', Reciept No='987954' WHERE ID='561'

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 ''Model No='AP22', Product Price='39.0000', Serial No='220801', Ref='TLS00700', S' at line 1

It's practically an indentical UPDATE statement as one I use inside a different IF clause (just to identify which variables have been sent by the form) which works as expected.

Someone please save my impending suicide and show me up as the amatuer I've turned out to be with an answer.

Cheers,

Dalefish

[nav33n]

$query = "UPDATE $table SET '$io='$io2', $pp='$pp2', $sn='$sn2', $re='$re2', $sd='$sd2', $ed='$ed2', $dp='$dp2', $rn='$rn2' WHERE ID='$id'";

Look near SET '$io. An extra ' . :) Remove it!

[dalefish]

$query = "UPDATE $table SET $io='$io2', $pp='$pp2', $sn='$sn2', $re='$re2', $sd='$sd2', $ed='$ed2', $dp='$dp2', $rn='$rn2' WHERE ID='$id'";
echo $query."<br><br>";
mysql_select_db($db, $link) or die ($db . " Database not found. " . $user);
mysql_db_query ($db, $query) or die (mysql_error());

Not sure where that extra one came from, it's not in my editor. Anywho, checked it (as above), still no joy.

As as side note, where did that extra one come from? If you look at the error message I first posted, it doesn't appear! Gremlins?

[dalefish]

Sorry, meant to say if you look at the echo $query output.

Any other ideas?

[nav33n]

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 ''Model No='AP22', Product Price='39.0000', Serial No='220801', Ref='TLS00700', S' at line 1

That's what the error said. It said there is an extra ' near Model No. But anyway, I dont see anything wrong with your query. Execute it in phpmyadmin/mysql and see if it returns any value. What is the error you are getting now ?

[dalefish]

I've attached an screen capture of the error I'm getting. I'm pasting it across as well so you can see the difference. Different capitalisation on various lines; could this difference be problem?

SQL query:

UPDATE loandata SET Model No = 'AP22',
Product Price = '39.0000',
SerialNo = '220801',
Ref = 'TLS00700',
START Date = '2008-01-25',
END Date = '2008-02-25',
DatePaid = '2008-02-13',
Reciept No = '987954' WHERE ID = '561'

MySQL said: Documentation
#1064 - 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 'No='AP22', Product Price='39.0000', Serial No='220801', Ref='TLS00700', Start Da' at line 1

Cheers,

Dalefish

[nav33n]

start and end are keywords. Try changing your column names to something else.

[dalefish]

First of all, thanks for the continued help on this. I've changed the query to:

UPDATE loandata SET Model No='AP22', Product Price='39.0000', SNo='220801', Ref='TLS00700', SDate='2008-01-25', EDate='2008-02-25', DPaid='2008-02-13', Reciept No='987954' WHERE ID='561'

and all the corresponding column names (thought DATE and SERIAL might have been confusing things), yet still no joy:

SQL query:

UPDATE loandata SET Model No = 'AP22',
Product Price = '39.0000',
SNo = '220801',
Ref = 'TLS00700',
SDate = '2008-01-25',
EDate = '2008-02-25',
DPaid = '2008-02-13',
Reciept No = '987954' WHERE ID = '561'

MySQL said: Documentation
#1064 - 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 'No='AP22', Product Price='39.0000', SNo='220801', Ref='TLS00700', SDate='2008-01' at line 1

Cheers again,

Dalefish

[nav33n]

Damn.. ok.. Try this.. Update only a part of the query. For example, update only 1 field first. If it doesn't give you any error, try 2 fields. Keep doing it until you get the error. Then you will know what is causing the error.

[dalefish]

So, it seems the UPDATE statement can't deal with spaces like the SELECT statement can. Odd. Had to remove all the spaces from column names, which means changing all my SELECT queries as well. Anywho, up and crawling now (running will be a while at this rate).

Cheers for the help.

Dalefish

P.S. If you think of a work around other than removing all spaces, do let me know, Cheers.

[nav33n]

:) Its not a good option to have spaces in your column names btw.

[dalefish]

Yeah, cheers.......Importing 3 of someone elses rather old, rapidly failing excel databases into one php/mysql driven system and adding a few tweaks of my own (making it colleague proof, or is that idiot proof?), oh the joys. Cheers for the help mate.

[nav33n]

:icon_smile: Cheers man !