tags:

views:

84

answers:

1
Q: 

Odd problem with loop

I have the following loop, which works fine if i<1 but gives me the the following error if i<2. I cannot for the life of me figure out what it is.

I get this error: Query failed: 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 'WHERE 1244790000 - datecreated >= 432000' at line 1 Actual query:

And here is the loop: ( I apologize for the mess...Only been programming for a few months and have not adopted OOP yet :[ ... )

     for($i=0;$i<2;$i++) {
 $row=mysql_fetch_assoc($displaylistings);

 // Check to what store this listing belongs to
 $nordstromtest=mysql_query("SELECT * FROM nordstromlisting WHERE ShopperID = '$ShopperID'");
 $nordstromstore=mysql_fetch_assoc($nordstromtest);
 if ($row == $nordstromstore) {
  $storename = "Nordstrom Listing: ";
  $storetbl = "nordstromlisting";
 }

 $saksfifthavenuetest=mysql_query("SELECT * FROM saksfifthavenuelisting WHERE ShopperID = '$ShopperID'");
 $saksfifthavenuestore=mysql_fetch_assoc($saksfifthavenuetest);
 if ($row == $saksfifthavenuestore) {
  $storename = "Sak's Fifth Avenue Listing: ";
  $storetbl = "saksfifthavenuelisting";
 }

 $neimanmarcustest=mysql_query("SELECT * FROM neimanmarcuslisting WHERE ShopperID = '$ShopperID'");
 $neimanmarcusstore=mysql_fetch_assoc($neimanmarcustest);
 if ($row == $neimanmarcusstore) {
  $storename = "Neiman Marcus Listing: ";
  $storetbl = "neimanmarcuslisting";
 }

 $guesstest=mysql_query("SELECT * FROM guesslisting WHERE ShopperID = '$ShopperID'");
 $guessstore=mysql_fetch_assoc($guesstest);
 if ($row == $guessstore) {
  $storename = "GUESS Listing: ";
  $storetbl = "guesslisting";
 }

 $americaneagletest=mysql_query("SELECT * FROM americaneaglelisting WHERE ShopperID = '$ShopperID'");
 $americaneaglestore=mysql_fetch_assoc($americaneagletest);
 if ($row == $americaneaglestore) {
  $storename = "American Eagle Listing: ";
  $storetbl = "americaneaglelisting";
 }

 $gnctest=mysql_query("SELECT * FROM gnclisting WHERE ShopperID = '$ShopperID'");
 $gncstore=mysql_fetch_assoc($gnctest);
 if ($row == $gncstore) {
  $storename = "GNC Listing: ";
  $storetbl = "gnclisting";
 }

 $footlockertest=mysql_query("SELECT * FROM footlockerlisting WHERE ShopperID = '$ShopperID'");
 $footlockerstore=mysql_fetch_assoc($footlockertest);
 if ($row == $footlockerstore) {
  $storename = "Foot Locker Listing: ";
  $storetbl = "footlockerlisting";
 }

 $bananarepublictest=mysql_query("SELECT * FROM bananarepubliclisting WHERE ShopperID = '$ShopperID'");
 $bananarepublicstore=mysql_fetch_assoc($bananarepublictest);
 if ($row == $bananarepublicstore) {
  $storename = "Banana Republic Listing: ";
  $storetbl = "bananarepubliclisting";
 }

 $urbanoutfitterstest=mysql_query("SELECT * FROM urbanoutfitterslisting WHERE ShopperID = '$ShopperID'");
 $urbanoutfittersstore=mysql_fetch_assoc($urbanoutfitterstest);
 if ($row == $urbanoutfittersstore) {
  $storename = "Urban Outfitters Listing: ";
  $storetbl = "urbanoutfitterslisting";
 }

 //Delete old listings (test with minutes...)

 $timenow = date("Y-m-d");
 $time = strtotime($timenow);
 $storehistory = str_ireplace("listing","history","$storetbl");
 $movetohistory = mysql_query("INSERT INTO $storehistory SELECT * FROM $storetbl WHERE $time - datecreated >= 432000");
 $deltefromlisting = mysql_query("DELETE FROM $storetbl WHERE $time - datecreated >= 432000") or die ("Query failed: " . mysql_error() . " Actual query: " . $query);

 //Item link modifactions
 if ($row['link'] != null) {
  $shortlink = substr($row['link'],0,30);
  $shortlink .= '...';
 }


 if ($row['itemsize'] != null ) {
  $mensshirt = $row['itemsize'];
 }

 if ($row['waistsize'] != 0) {
  $waistsize = $row['waistsize'];
  $w = 'Waist';
 }

 if ($row['lengthsize'] != 0) {
  $lengthsize = $row['lengthsize'];
  $l = 'Length';
 }

 if ($row['ladystopsize'] != null) {
  $ladystopsize = $row['ladystopsize'];
 }

 if ($row['ladysdresssize'] != 0) {
  $ladysdresssize = $row['ladysdresssize'];
 }

 if ($row['ladysbottomsize'] != 0) {
  $ladysbottomsize = $row['ladysbottomsize'];
 }

 if ($row['shoesize'] != 0) {
  $shoesize = $row['shoesize'];
 }

 echo "<div id='listings2'><div id='storenametab'><b><span class='storename'>$storename</span></b></div><br /><br />";
 echo "<span id='orderdetails'>Item Number : $row[itemnum] <br />";
 echo "Discount Price: $$row[DiscountedPrice] <br />";
 echo "Item Type : $row[itemtype]<br />";
 echo "Item Size :
 $mensshirt 
 $waistsize $w 
 $lengthsize $l 
 $ladystopsize 
 $ladysdresssize 
 $ladysbottomsize 
 $shoesize
 <br />";
 echo "Item Color: $row[itemcolor] <br />";

 if ($row['link'] != null) {
 echo "Item URL: <a href='$row[link]' target='_blank'>$shortlink</a><br />";
 }

//STATUS

 if ($row['VillainID'] == 0)
  echo "Status: Unclaimed <br />";

 else {

  $VillainPaypalEmail = mysql_query("SELECT PaypalEmail FROM villains WHERE VillainID = '$row[VillainID]'");
  $fetchVillain = mysql_fetch_assoc($VillainPaypalEmail);
  $VillainPaypal = $fetchVillain['PaypalEmail'];

  $itemname = "Item Number: $row[itemnum] -- $row[itemtype] -- $mensshirt $waistsize $w $lengthsize $l $ladystopsize $ladysdresssize $ladysbottomsize $shoesize -- $row[itemcolor]";
  $discprice = $row['DiscountedPrice'];
  $state = mysql_query("SELECT state from users WHERE Username = '$username'");
  $fetchstate = mysql_fetch_assoc($state);
  $state = $fetchstate['state'];

  switch ($storetbl) {
      case "nordstromlisting":
          $shipping = 8;
    if ($state == 'AZ' || 'CA' || 'CO' || 'CT' || 'FL' || 'GA' || 'HI' || 'IA' || 'ID' || 'IL' || 'IN' || 'KS' || 'MA' || 'MD' || 'MN' || 'MO' || 'NC' || 'NJ' || 'NV' || 'NY' || 'OH' || 'PA' || 'RI' || 'SD' || 'TX' || 'UT' || 'VA' || 'WA') {
     $tax = $discprice * .09;
    }
    else {
     $tax = $discprice * .02;
    }
          break;
      case "saksfifthavenuelisting":
    switch ($discprice) {
     case ($discprice <= 25):
      $shipping = 5;
      break;
     case ($discprice > 25 && $discprice <= 50):
      $shipping = 8;
      break;
     case ($discprice > 50 && $discprice <= 100):
      $shipping = 10;
      break;
     case ($discprice > 100 && $discprice <= 200):
      $shipping = 13;
      break;
     case ($discprice > 200 && $discprice <= 300):
      $shipping = 15;
      break;
     case ($discprice > 300 && $discprice <= 500):
      $shippig = 17;
      break;
     case ($discprice > 500):
      $shipping = 20;
      break;
    }
    if ($state == 'AL' || 'AZ' || 'CA' || 'CO' || 'CT' || 'FL' || 'GA' || 'IL' || 'IN' || 'LA' || 'MA' || 'MD' || 'MN' || 'MO' || 'NC' || 'NJ' || 'NV' || 'NY' || 'OH' || 'OK' || 'PA' || 'SC' || 'TX' || 'VA' || 'DC') {
     $tax = $discprice * .09;
    }
    else {
     $tax = $discprice * .02;
    }
          break;
      case "neimanmarcuslisting":
    switch ($discprice) {
     case ($discprice <= 25):
      $shipping = 6;
      break;
     case ($discprice > 25 && $discprice <= 50):
      $shipping = 9;
      break;
     case ($discprice > 50 && $discprice <= 100):
      $shipping = 11.50;
      break;
     case ($discprice > 100 && $discprice <= 200):
      $shipping = 16.50;
      break;
     case ($discprice > 200 && $discprice <= 300):
      $shipping = 18.50;
      break;
     case ($discprice > 300 && $discprice <= 500):
      $shippig = 21.50;
      break;
     case ($discprice > 500 && $discprice <= 700):
      $shipping = 25.50;
      break;
     case ($discprice > 700 && $discprice <= 1000):
      $shipping = 29.00;
      break;
     case ($discprice > 1000):
      $shipping = 32.00;
      break;
    }
    if ($state == 'AZ' || 'CA' || 'CO' || 'FL' || 'GA' || 'HI' || 'IL' || 'MA' || 'MD' || 'MI' || 'MN' || 'MO' || 'NC' || 'NJ' || 'NV' || 'NY' || 'PA' || 'TX' || 'VA' || 'WA' || 'DC') {
     $tax = $discprice * .09;
    }
    else {
     $tax = $discprice * .02;
    } 
       break;
   case "urbanoutfitterslisting":
    switch ($discprice) {
     case ($discprice <= 50):
      $shipping = 6;
      break;
     case ($discprice > 25 && $discprice < 50):
      $shipping = 8;
      break;
     case ($discprice >= 50 && $discprice < 100):
      $shipping = 10;
      break;
     case ($discprice >= 100 && $discprice < 150):
      $shipping = 12;
      break;
     case ($discprice > 150):
      $shipping = 0;
      break;
    }
    if ($state == 'AL' || 'AZ' || 'CA' || 'CO' || 'CT' || 'FL' || 'GA' || 'IL' || 'IN' || 'KS' || 'LA' || 'MA' || 'MD' || 'MI' || 'MN' || 'MO' || 'NB' || 'NM' || 'NC' || 'OH' || 'OR' || 'NV' || 'NY' || 'PA' || 'RI' || 'SC' || 'TN' || 'TX' || 'UT' || 'VA' || 'VT' || 'WA' || 'WI' || 'DC') {
     $tax = $discprice * .09;
    }
    else {
     $tax = $discprice * .02;
    }
    break;
   case "gnclisting":
    $shipping = 6;
    break;
   case "bananarepubliclisting";
    $shipping = 7;
    //call Banana Republic for list of taxable states
    break;
   case "guesslisting":
    $shipping = 7.95;
    if ($state == 'AZ' || 'CA' || 'CO' || 'CT' || 'FL' || 'GA' || 'HI' || 'IL' || 'IN' || 'KS' || 'LA' || 'MA' || 'MD' || 'MI' || 'MN' || 'MO' || 'NJ' || 'NV' || 'NY' || 'NC' || 'OH' || 'PA' || 'RI' || 'SC' || 'TN' || 'TX' || 'VA' || 'WA') {
     $tax = $discprice * .09;
    }
    else {
     $tax = $discprice * .02;
    }
    break;
   case "footlockerlisting";
    $shipping = 6.99;
    if ($state == 'AK' || 'DE'|| 'MT' || 'NH' || 'OR') {
     $tax = $discprice * .02;
    }
    else {
     $tax = $discprice * .09;
    }
    break;
   case "americaneaglelisting":
    switch($discprice) {
     case ($discprice < 100):
     $shipping = 7;
     break;
     case ($discprice > 100):
     $shipping = 0;
     break;
    }
     $tax = $discprice * .09;
    break;
  }

  echo "Status: Claimed!"?>
  <form action='https://www.paypal.com/cgi-bin/webscr' target='blank' method='post'> 
  <input type='hidden' name='business' value='<?php echo $VillainPaypal ?>'> 
  <input type='hidden' name='cmd' value='_xclick'> 
  <input type='hidden' name='item_name' value="<?php echo $itemname ?>"> 
  <input type='hidden' name='shipping' value="<?php echo $shipping ?>"> 
  <input type='hidden' name='tax' value="<?php echo $tax ?>"> 
  <input type='hidden' name='amount' value='<?php echo $row['DiscountedPrice'] ?>'> 
  <input type='hidden' name='currency_code' value='USD'> 
  <input type='image' name='submit' border='0' 
  src='https://www.paypal.com/en_US/i/btn/btn_buynow_LG.gif' 
  alt='PayPal - The safer, easier way to pay online'> 
  <img alt='blahblah' border='0' width='1' height='1' 
  src='https://www.paypal.com/en_US/i/scr/pixel.gif' > 
  </form>
  <br />
  <?php ;

//STATUS } ?> "> "> 

 <?php

 echo "</span></div>";

 $mensshirt = "";
 $waistsize = "";
 $lengthsize = "";
 $ladystopsize = "";
 $ladysdresssize = "";
 $ladysbottomsize = "";
 $shoesize = "";
 $storename = "";
 $storetbl = "";

}

+1  A: 

My guess is the second time through the loop,

$storetbl is either being set to nothing at all, or a tablename that doesn't exist in the database.

"DELETE FROM $storetbl WHERE $time - datecreated >= 432000" then evaluates to something like

DELETE FROM WHERE $time - datecreated >= 432000

try some debugging statements or a debugger to determine what $storetbl is evaluated to the second time through the loop.

tschaible  2009-06-12 10:03:38
That's right. If none of your conditions are true, then $storetbl will be blank and the SQL query will fail. You might like to put if($storetbl == "") continue; before your query.
Adam Pierce  2009-06-12 10:14:47
you might want to also initialise $storetbl/$storename at the beginning of each pass of the loop, otherwise the values from the previous pass will persist
Tom Haigh  2009-06-12 10:48:31
he actually does that at the end of each loop iteration, though it's though to tell from the formatting.
tschaible  2009-06-12 10:55:24

related questions

IDE suggestions: Eclipse IDE vs. Zend Studio ( confused )
MySQL/Apache Error in PHP MySQL query
Lightweight IDE for Linux
What PHP framework would you choose for a new application and why?
Why is my ternary expression not working?
How can I get at the matches when using preg_replace in PHP?
Mechanisms for tracking DB schema changes
Wordpress theme development offline tools
Using object property as default for method property
How can I get the authenticated user name under Apache using plain HTTP authentication and PHP?
Make XAMPP/Apache serve file outside of htdocs
How do you debug PHP scripts?
PHP Variables passed by value or by reference?
Best way to implement unit testing in PHP
Connect PHP to an AS/400
Best way to access Exchange using PHP?
PHP Session Security
How do I access a remote form in php?
What's the best way to generate a tag cloud from an array? (using h1 through h6 for sizing)
Apache/PHP: error_log per Virtual Host?
How do I track file downloads with apache/PHP
How would you access Object properties from within an object method?
Flat File Databases in PHP
Best way to allow plugins for a PHP application
Latest information on PHP upcoming releases