why is this query failing?

[nick1]

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*

[Zane]

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());

[fenway]

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

[nick1]

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*

[obsidian]

[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.

[obsidian]

[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]

[Zane]

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

[fenway]

Alternatively, if you have a useful unique index, you might be able to get away with INSERT...ON DUPLICATE KEY UPDATE.

[obsidian]

[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

[nick1]

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*

[fenway]

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.