Jump to content

Archived

This topic is now archived and is closed to further replies.

nick1

why is this query failing?

Recommended Posts

Greetings,

Can someone please tell me why the following query is failing?

[code] $qryupdate = "INSERT INTO Warranty (servicetag, description, provider, startdate, enddate, daysleft) " .
"VALUES ('$servicetag', '$description', '$provider', '$startdate', '$enddate', '$daysleft') " .
"WHERE (servicetag='$servicetag', description='$description', provider='$provider', " .
"startdate='$startdate', enddate='$enddate')";

$results = mysql_query($qryupdate);[/code]

Thanks,

*Nick*

Share this post


Link to post
Share on other sites
well I know for one that you can't put a php variable in single quotes and expect to use it's value

but instead of just changing it to double quotes like you'll probably do
try this
'{$yourvar}'

so for all those variables you're listing do like this
VALUES ('{$servicetag}', '{$description}', '{$provider}',....etc

makre sure you use curly braces


if that doesn't do it
you can get an actual error message by putting this on your $results line

$results = mysql_query($qryupdate) or die(mysql_error());

Share this post


Link to post
Share on other sites
You just can't have a WHERE clause and and INSERT statement... it doesn't make any sense.  What are you trying to do?

Share this post


Link to post
Share on other sites
I want to replace the old values with the new values.  UPDATE doesn't work because if the old values are the same as the new values it fails, which is fine, but the way the rest of my code is designed:

[code]if (mysql_affected_rows() == -1)
{

$qryinsert = "INSERT INTO Warranty (servicetag, description, provider, startdate, enddate, daysleft) " .
"VALUES ('$servicetag', '$description', '$provider', '$startdate', '$enddate', '$daysleft') ";

$results = mysql_query($qryinsert);

}[/code]

...so the information is just inserted into the table instead of being overwritten/updated.  Make sense?
INSERTing is fine if the information doesn't already exist in the table.  But if the information already does exist in the table, I want to update it because, more then likely, the information in $daysleft will change daily.

Thanks,

*Nick*

Share this post


Link to post
Share on other sites
[quote author=nick1 link=topic=107981.msg434261#msg434261 date=1158251388]
I want to replace the old values with the new values.  UPDATE doesn't work because if the old values are the same as the new values it fails
[/quote]

that doesn't make sense. an update statement will overwrite whatever is in the table no matter if it's the same as what you're trying to update the data [b]to[/b] or not. sounds like what you're going to need is to check out whether or not your record exists [b]first[/b], then update it if it's there and insert if it's not. basically, just run a SELECT query with your WHERE clause, and if you have a record returned, run your update, otherwise, run your insert.

Share this post


Link to post
Share on other sites
[quote author=zanus link=topic=107981.msg433929#msg433929 date=1158209606]
well I know for one that you can't put a php variable in single quotes and expect to use it's value
[/quote]

actually, zanus, since he's inside the double quotes, he's fine with his syntax. as fenway pointed out, you can't try to have a WHERE clause on an insert statement. so, for a query, any of the following would be accurate:
[code]
<?php
mysql_query("INSERT INTO Warranty (servicetag) VALUES ('$servicetag')");
mysql_query("INSERT INTO Warranty (servicetag) VALUES ('{$servicetag}')");
mysql_query("INSERT INTO Warranty (servicetag) VALUES ('" . $servicetag . "')");
mysql_query('INSERT INTO Warranty (servicetag) VALUES (\'' . $servicetag . '\')');
?>
[/code]

Share this post


Link to post
Share on other sites
oh..I guessed I'm just used to the curly brackets
it was burned into my head by either czambran or Barand..can't remember

Share this post


Link to post
Share on other sites
Alternatively, if you have a useful unique index, you might be able to get away with INSERT...ON DUPLICATE KEY UPDATE.

Share this post


Link to post
Share on other sites
[quote author=fenway link=topic=107981.msg434276#msg434276 date=1158253643]
Alternatively, if you have a useful unique index, you might be able to get away with INSERT...ON DUPLICATE KEY UPDATE.
[/quote]

good call, fenway... hadn't thought of that

Share this post


Link to post
Share on other sites
Thank you to everyone who replied.

Just an FYI:
"When using UPDATE, MySQL will not update columns where the new value is the same as the old value."  (source:  http://us2.php.net/mysql_affected_rows).    ...ggrrr!

I think what I might try is something like:

1.)  try a DELETE statement, even if the record doesn't exist.
    a.)  if the record does exist, perform an INSERT (since the old record was already deleted by step #1)
2.)  if DELETE fails (meaning the record doesn't exist), perform an INSERT.

This logic will require additional queries to the database but, on the flip-side, it safeguards me against the fact that the information in $daysleft may or may not change (depending on whether or not the warranty has expired).  Since the PHP script will only have to run once a day, I'm satisfied with such logic - unless I can be convinced otherwise.

Thanks,

*Nick*

Share this post


Link to post
Share on other sites
Two things:

1) Why should UPDATE update if there's nothing to change? And it shouldn't ever matter, since it won't "fail".

2) Like I said earlier, you can skip the extra delete or extra select if you have an appropriate unique key.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.