need help with PHP/MySQL query syntax

[Earl Parker II]

Here's the line of code I'm having trouble with:

$update_data_query = "UPDATE prices SET (part_no=".$part_no_update.",part_description=".$part_description_update.",price_1_9=".$price_1_9_update.",price_10_24=".$price_10_24_update.",price_25_49=".$price_25_49_update.",price_50_99=".$price_50_99_update.",price_100=".$price_100_update." WHERE part_no=".$part_no.")";

The query works on the command line (with different syntax) and the variables contain values. I don't get a syntax error, the query simply won't run.

I would appreciate any advice or help that anyone has- thanks!

[raf]

Welcome here.

You didn't give us a lot to go on. Maybe you simply don't run a mysql_query or so. Or maybe the $part_no is empty so the condition is not validated true for any record, or there is no record with that part_no in the table..
Maybe print the $update_data_query to the screen to see what query you've got (and which variebelvalues are inserted)
like
echo $update_data_query;
You could then copy that statement and past it in the commandline to see the difference.

[duniyadnd]

try this, when you include the variables, instead of having it like so:

".$variable."

put it like so:

'$variable'

The value of the variable will come out, but mysql_query needs to read the values within the single quotes.

[Earl Parker II]

You're right, there were some serious omissions in the post. To correct matters...

I did run mysql_query in the next line as:
$update_data_result = mysql_query($update_data_query);

I've checked to make sure that $part_no contains a value, which it does, and that there is a record with that part number in the table.

I echoed $update_data_query, with the following result:
UPDATE prices SET (part_no=newtest1,part_description=newtest2,price_1_9=11,price_10_24=22,price_25_49=33,price_50_99=4 4,price_100=55 WHERE part_no=test1)

Copying the above to the command line yielded syntax errors but the following did work at the command line:
UPDATE prices SET part_no="newtest1",part_description="newtest2" WHERE part_no="test1";

I had to truncate the statement and I used literal values instead of variables but it worked perfectly. I tried using literal values in the PHP code, to no avail.

When I echo $update_data_query, I get the above but when I echo $update_data_result I get nothing, which seems to suggest that the query is never being executed.

Hope that fills in some needed details.

[duniyadnd]

Going by your echo.. you need single quotes around your values. Just do what I said above.. it should work... :thumbsup:

[Spookster]

Originally posted by duniyadnd
Going by your echo.. you need single quotes around your values. Just do what I said above.. it should work... :thumbsup:

Not if they are number datatypes in which case you do not want to treat them as strings.

[Earl Parker II]

Did what you suggested and, unbelievably, it still didn't work. I've tried the following around my variables to no avail:

'".$variable."'
'"$variable"'
".$variable."
"$variable"
'$variable'

I've even tried no quotes at all, with the same result. I even found and corrected a logical error- didn't help. To reiterate, here's the code again:

$update_data_query = "UPDATE prices SET (part_no='$part_no_update',part_description='$part_description_update',price_1_9='$price_1_9_update' ,price_10_24='$price_10_24_update',price_25_49='$price_25_49_update',price_50_99='$price_50_99_updat e',price_100='$price_100_update' WHERE part_no='$part_no_to_update')";
$update_data_result = mysql_query($update_data_query);
echo $update_data_query;

It just seems as though the query is never executing.

[duniyadnd]

Not if they are number datatypes in which case you do not want to treat them as strings.

Ooops, you got me... forgot about that one. :rolleyes:

[Earl Parker II]

I just tried single-quotes around string variables and no quotes around numeric varaiables- unfortunately, it still doesn't work.

[duniyadnd]

Change this line $update_data_result = mysql_query($update_data_query);


to this

$update_data_result = mysql_query($update_data_query) or die(mysql_error());


and is there any output?

[Earl Parker II]

Very good idea. With the following lines of code:

$update_data_query = "UPDATE prices SET (part_no='$part_no_update',part_description='$part_description_update',price_1_9='$price_1_9_update' ,price_10_24='$price_10_24_update',price_25_49='$price_25_49_update',price_50_99='$price_50_99_updat e',price_100='$price_100_update' WHERE part_no='$part_no_to_update')";

$update_data_result = mysql_query($update_data_query) or die(mysql_error());

I'm getting:
You have an error in your SQL syntax near '(part_no='test1',part_description='test2',price_1_9='11',price_10_24='22',price_' at line 1

Update: have removed single-quotes from numeric variables with the same result.

Will be back in just a bit, have to do a couple of things. Again, thanks for all the help.

[duniyadnd]

Okay... we going to try to narrow it down:

for your code, write it as such:

$query = "UPDATE prices SET ( "
."part_no='$part_no_update', "
."part_description='$part_description_update', "
."price_1_9='$price_1_9_update', "
."price_10_24='$price_10_24_update', "
."price_25_49='$price_25_49_update', "
."price_50_99='$price_50_99_update', "
."price_100='$price_100_update' "
."WHERE part_no='$part_no_to_update')";


This will help you get the error in a more exact spot.

[Earl Parker II]

Ran the query, result was:
You have an error in your SQL syntax near '( part_no='test1', part_description='test2', price_1_9='11', price_10_24='22', p' at line 1

Out of curiosity, is the first " after SET ( needed?

[duniyadnd]

UGH!!! Can't believe I didn't notice it.

Remove the ( and ).

[Earl Parker II]

Sir, thanks to you, it now works.

Another question- do the periods and extra double-quotes serve as separators in the last block of code you posted?

[duniyadnd]

No problem..

Okay.. as for the question.. notice how each line ends with a quote, but doesn't end with a semicolon. that means the string for that line has ended, but the statement hasn't (ie. the value of the string is incomplete).

The next line starts with a period, which means, it is a continuation of the above string, since its a string, it has to be followed with a "

The whole thing terminates at the semi-colon.

Made sense?

If mysql_error has to post an error, they usually are more helpful when we break the query up, though they usually start from the error itself.

[Earl Parker II]

Definitely makes sense and it's much easier to read and comprehend when presented in that manner, so that's how I'm going to do it from now on.

I appreciate the help very much- you've solved a big problem! Maybe someday I'll know enough to contribute.

This is my first time on this board and I'm impressed. As the man said, I'll be back.

[Spookster]

Originally posted by duniyadnd
No problem..

Okay.. as for the question.. notice how each line ends with a quote, but doesn't end with a semicolon. that means the string for that line has ended, but the statement hasn't (ie. the value of the string is incomplete).

The next line starts with a period, which means, it is a continuation of the above string, since its a string, it has to be followed with a "

The whole thing terminates at the semi-colon.

Made sense?

If mysql_error has to post an error, they usually are more helpful when we break the query up, though they usually start from the error itself.

Actually you don't need to do that. PHP is not line dependent. If you start a string it will end as soon as it comes across the closing quote and semicolon whether or not those exist on the same line or not.



$query = "UPDATE prices
SET (
part_no='$part_no_update',
part_description='$part_description_update',
price_1_9='$price_1_9_update',
price_10_24='$price_10_24_update',
price_25_49='$price_25_49_update',
price_50_99='$price_50_99_update',
price_100='$price_100_update'
)
WHERE
part_no='$part_no_to_update'
";



This would be a correct way to write the query. BTW your parenthesis was in the wrong spot. The where clause should be outside the parenthesis. I fixed it above.

[Earl Parker II]

Thanks for the clarification and the code sample.

[duniyadnd]

This would be a correct way to write the query. also..

I personally like the way I do it, cause I have a habit of naming my variables with stupid names, so I put a comment at the end of each line to tell me what it is.. :p

And yeah, didn't realize you could have paranthesis for Update, learn something new everyday.. wish I could say the same for my courses.

[Earl Parker II]

Indirectly, you've also helped me work out a problem with an insert query, so thanks for that, too.

I take it that you're a student?

[duniyadnd]

Since you're new to the forum... check your PM :cool:
On the "User Profile" up top.