Jump to content


Photo

why is this query failing?


  • Please log in to reply
10 replies to this topic

#1 nick1

nick1
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 14 September 2006 - 04:08 AM

Greetings,

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

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

Thanks,

*Nick*

#2 Zane

Zane
  • Administrators
  • Advanced Member
  • 4,134 posts

Posted 14 September 2006 - 04:53 AM

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

btn_donate_SM.gif Want to thank me? Contribute to my PayPal piggy-bank
 

172938.png

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 September 2006 - 02:51 PM

You just can't have a WHERE clause and and INSERT statement... it doesn't make any sense.  What are you trying to do?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 nick1

nick1
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 14 September 2006 - 04:29 PM

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:

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

}

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

#5 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 14 September 2006 - 04:45 PM

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


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 to or not. sounds like what you're going to need is to check out whether or not your record exists first, 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.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#6 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 14 September 2006 - 04:48 PM

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


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:
<?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 . '\')');
?>

You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#7 Zane

Zane
  • Administrators
  • Advanced Member
  • 4,134 posts

Posted 14 September 2006 - 05:00 PM

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

btn_donate_SM.gif Want to thank me? Contribute to my PayPal piggy-bank
 

172938.png

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 September 2006 - 05:07 PM

Alternatively, if you have a useful unique index, you might be able to get away with INSERT...ON DUPLICATE KEY UPDATE.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 14 September 2006 - 05:47 PM

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


good call, fenway... hadn't thought of that
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#10 nick1

nick1
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 14 September 2006 - 06:48 PM

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*

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 September 2006 - 07:03 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users