Jump to content


Photo

Updating a table, using 2 filters


  • Please log in to reply
5 replies to this topic

#1 SephirGaine

SephirGaine
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 09 August 2006 - 02:15 AM

Whooptie. I'm back (unfortunately, again), however this time my question's a lot different. I'll start off by posting my code..

<?php
	$query1 = "UPDATE `WOR` SET Invoice=Yes where work_order_num='$ordernum' where Invoice=No";
	$query2 = "UPDATE `WOR` SET Invoice=No where work_order_num='$ordernum' where Invoice=Yes";
?>
<?php
	if (mysql_query($query1) or die(mysql_error())
		else {
				mysql_query($query2) or die(mysql_error();
			}
?>

What I want to do is to basically switch the "Invoice" section of an entry to Yes or No, much like a toggle. However, I'm running into a few problems when I try to run the script. Unfortunately I'm not sure where I've gone wrong. I can see it being either I can't use 2 "where" statements in a single query, or it's in my else statement (syntax error somewhere). Any help would be appreciated, and if there's a simpler way for me to do this, I'm all ears. This is just what I came up with off the top of my amateur head. Thanks!

#2 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 09 August 2006 - 02:54 AM

<?php
$query = "UPDATE `WOR` SET Invoice=Yes where work_order_num='$ordernum' AND Invoice=No;";
$query .= "UPDATE `WOR` SET Invoice=No where work_order_num='$ordernum' AND Invoice=Yes;";

$qry = mysql_query($query);
if 9mysql_affected_rows() == 0)
{
// output some error warninh
}

?>
follow me on twitter @PHPsycho

#3 SephirGaine

SephirGaine
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 09 August 2006 - 03:04 AM

Hm.. getting a syntax error when I try it. I get the idea though, so thanks! Changed what you gave me just a bit, came up with this..

<?php
   $query = "UPDATE `WOR` SET Invoice=Yes where work_order_num='$ordernum' AND Invoice=No";
   $query .= "UPDATE `WOR` SET Invoice=No where work_order_num='$ordernum' AND Invoice=Yes";
  ?>
  <?php
mysql_query($query) or die(mysql_error());
if (mysql_affected_rows() == 0)
{
 echo("An error has occured.");
}
?>


#4 SephirGaine

SephirGaine
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 09 August 2006 - 05:58 PM

Hate to be a pain, but bumpin' 'dis. Still having a bit of trouble.. see one post up.  ???

#5 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 09 August 2006 - 06:42 PM

"UPDATE `WOR` SET `Invoice`=(SELECT IF(`Invoice`='Yes','No',Yes')) where work_order_num='$ordernum'"

#6 SephirGaine

SephirGaine
  • Members
  • PipPipPip
  • Advanced Member
  • 44 posts

Posted 09 August 2006 - 10:08 PM

Most unfortunately..

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 'SELECT IF(Invoice='Yes','No','Yes')) where work_order_num='2''


This is what I've got..

<?php
   $query = "UPDATE `WOR` SET Invoice=(SELECT IF(Invoice='Yes','No','Yes')) where work_order_num='$ordernum'"
  ?>
  <?php
mysql_query($query) or die(mysql_error());
?>





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users