dsgm5631
2010-02-16 14:03
浏览 42
已采纳

帮助SQL语法错误消息

<?php

switch($_GET["action"])
{
case "add_item":
{
AddItem($_GET["idc"], $_GET["qty"]);
ShowCart();
break;
}
case "update_item":
{
UpdateItem($_GET["idc"], $_GET["qty"]);
ShowCart();
break;
}
case "remove_item":
{
RemoveItem($_GET["idc"], $_GET["id"]);
ShowCart();
break;
}
default:
{
ShowCart();
}
}
function AddItem($itemId, $qty){


$result = mysql_query("SELECT COUNT(*) FROM cart WHERE cookieId = '" . GetCartId() . "' AND id = $itemId");

$row = mysql_fetch_row($result);
$numRows = $row[0];

if($numRows == 0)
{
// This item doesn't exist in the users cart,
// we will add it with an insert query

mysql_query("INSERT INTO cart(cookieId, id, qty) values('" . GetCartId() . "', $itemId, $qty)");
//printf ("Inserted records: %d
", mysql_affected_rows());

}
else
{
// This item already exists in the users cart,
// we will update it instead
 mysql_query("UPDATE cart SET qty = $qty WHERE cookieId = '" . GetCartId() . "' AND id = $itemId");

}

}

function UpdateItem($itemId, $qty)
{

mysql_query("UPDATE cart SET qty = $qty WHERE cookieId = '" . GetCartId() . "' AND id = $itemId");
//printf ("Updated records: %d
", mysql_affected_rows());

}

function RemoveItem($itemId) 
{

mysql_query("DELETE FROM cart WHERE cookieId = '" . GetCartId() . "' AND id = $itemId");
}

?>

<?php 
function ShowCart()

{
    $result = mysql_query("SELECT
                  cart.id         cart_id,
                  cart.id         cart_id,
                  cart.cartId     cartId,
                  cart.cookieId   cookie_Id,
                  cart.qty        qt_y,                     
                  cdkb.id         cdkb_id,
                  cdkb.name       name,
                  cdkb.image      image,
                  cdkb.price      price,
                  dkb.id          dkb_id,
                  dkb.name        name1, 
                  dkb.image       image1,
                  dkb.price       price2,
                  dbl.product_id  product_id,
              dbl.price       price3,
              dbl.variety     variety,
              dbl.description description                  
FROM
        cart


        LEFT OUTER JOIN cdkb
           ON cart.id = cdkb.id    

        LEFT OUTER JOIN dkb
           ON cart.id = dkb.id

        LEFT OUTER JOIN dbl
           ON dbl.id = dkb.id 


    WHERE
        cart.cookieId ='" . GetCartId() . "' ' ORDER BY cdkb.name AND dkb.name ASC");

    <div id="cart">
<div id="group">
<div id="quantity">Qty</div>
<div id="cartpic">Pic</div>
<div id="product">Product</div>
<div id="cartprice">Price</div>
<div id="remove">Remove</div>
</div>
<?php

$totalCost=0;
while($row = mysql_fetch_array($result))
{  
// Increment the total cost of all items
$totalCost += ($row["qt_y"] * $row["price1"]);

?>

<div id="cart1">
<select name="<?php echo $row["ckb_id"];?>" onChange="UpdateQty(this)">
<?php  print($row["ckb_id"]);?>
<?php

for($i = 1; $i <= 30; $i++)
{
echo "<option ";
if($row["qt_y"] == $i)
{
echo " SELECTED ";
}
echo ">" . $i . "</option>";
}
?>
</select>
</div>
<div id="cart2">
<img src="images/logopic.gif"<?php /*?><?php echo $row["image1"]; ?><?php */?> alt="we" width="60" height="50" />
</div>
<div id="cart3"><p><?php echo $row["dishname1"]; ?></p></div>



<div id="cart4"><p>
$<?php echo number_format($row["price3"], 2, ".", ","); ?></p></div>

<div id="cart5">
<p><?php
printf('<a href="cart.php?action=remove_item&id=%d&idc=%d&register=%s">Remove</a>', $_GET['id'], $row['ckb_id'], $_GET['register']);
?></p></div>

<hr size="1" color="red" >

<script language="JavaScript">

function UpdateQty(item)
{
itemId = item.name;
newQty = item.options[item.selectedIndex].text;

document.location.href = 'cart.php?action=update_item&id='+itemId+'&qty='+newQty;
}

</script>





<?php
}
?>

<font face="verdana" size="2" color="black" style="clear:right;">
<b>Total: $<?php echo number_format($totalCost, 2, ".", ","); ?></b></font></td>


<?php
}
?>

Above is the updated code and query of the cart.php. If you notice below the query there is a while loop. Right now the while loop is perfectly set up for dkb table which are items coming from page1.php. this loop is set up in a way that it will repeat save, display and manage ecah item qty, image, name, price and remove options, display them and repeat them as many items the user chooses. Well that fields set up and design doesn't quite work from items coming from page2.php which the information displayed in the cart gets pull up from the tables dkb and dbl. The problem right now is that when coming from page2.php to the cart the same item name repeats three times because in the dbl table the design is as follows.

INSERT INTO `dbl` (`id`, `price`, `variety`, `description`) VALUES
(1, 20.30, 'Small Tray', 'Serves 6 to 8 people' ),
(1, 25.90, 'Medium Tray', 'Serves 12 to 15 people'),
(1, 30.90, 'Large Tray', 'Serves 18 to 21 people'),
(136, 0.00, 'Small Tray', '', 'small'),
(136, 0.00, 'Medium Tray', '' ),
(136, 0.00, 'Large Tray', ''),
(2, 0.00, 'Small Tray', ''),
(2, 0.00, 'Medium Tray', ''),
(2, 0.00, 'Large Tray', '');

The insert above suppose to be three items of dkb see the dbl.id it joins on dkb.id in the query dbl.id=dkb.id Well dbl.id has three prices for one item in dkb table which in this case is three prices for item 1, three for item 136 and three prices for item 2. So it is three different variety, and descriptions for each one of those items in dkb. From now and on if you take a look at the fields assign in the while loop below the query some of them are of the dkb others from the cdkb and dbl. Rigth now as I said some where above the set up in the while loop is ok from information coming from the dkb table which only will have one price, and one name. Some what I am thinking is to arrange an if statement condition to detect where the information is coming from page1.php which is the one name and price or from page2.php which is going to be one name, from one to three prices and varieties depending on the small, medium or large tray the user chooses. Which each tray carry a price but it will be one name name 1, 136, or 2 which those are the id coming from dkb table. In the if statement inside the while loop to display the information from page2.php that require from one to three prices and variety will have a foreach loop or maybe a for loop don't know to count how many variety the user choose in page2.php to display it inside the while loop in cart with the help of an if statment that will detect weather it was choosen from page1.php or page2.php.

$result = mysql_query("SELECT * FROM .... LEFT OUTER JOIN .... WHERE") // This is the query we have discussed in this forum.
 while($row = mysql_fetch_array($result))
{  if($row['cdkb_id']){
echo "<div>qty</div>";
echo"<div>". $row['image'] . "</div>";
echo"<div>". $row['name'] . "</div>";
echo"<div>". $row['price'] . "</div>";
echo"<div>remove</div>";
}
else{
echo "<div>qty</div>";
echo"<div>". $row['image1'] . "</div>";
echo"<div>". $row['name1'] . "</div>";
foreach ($row['dkb_id'] as $variety) {
echo"<div>".$variety['variety']. $variety['price3'] . "</div>";
// the vriety and price3 field will display three times as in table dbl has three prices and three variety. It is correct to use a foreach loop for  in this case?}
echo"<div>remove</div>"; 
// end of foreach loop inside the else statement
}// end of if else statement
}// end of while loop
?>

So the final display and illustration to be search and present as a final product is as below, That's the desired look.

 while($row = mysql_fetch_array($result))
{  
if (table cdkb) { 
  [1]qyt         image      name          price        remove
     1            ---       marina        $18.90        remove?    
     }end of if statement
Else table dkb {
   1]qyt            Image         Name             Price                  Remove
     1               ---         marina         Small Tray  $18.90        remove? 
                                                Medium Tray $30.24
                                                Large Tray  $35.90
                  } // end of else statement

                  }//end of while loop

Give me a final hand on this.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dongyoufo5672 2010-02-16 14:06
    已采纳

    Your SELECT has a comma at the end of your last field. You should remove that comma.

    SELECT
    cart.id    cart_id,
    dkb.id     dkb_id,
    cdkb.id    cdkb_id,
    dbl.id     dbl_id, <-- comma here isn't needed
    
    已采纳该答案
    打赏 评论
  • dtla92562 2010-02-16 14:04

    It seems to me you have a comma too much after:

    dbl.id dbl_id
    

    Edit: Another error in the query is that you are referencing a field from table dbl:

    dbl.id dbl_id
    

    but you are not joining that table to the query so dbl.id is an unknown entity.

    打赏 评论
  • dongqing8765 2010-02-16 14:39

    Your query will also blow up if $_GET['is'] is not set. You're assigning the value null to $is and $ic, not the string "null". A null value in string context is a blank string (''), so you'll end up with ON ( cart.id = dkb.id and dkb.id = ) which is a syntax error.

    You'll have to change the initial assignment to something like this:

    $ic= isset($_GET['is']) ? '= ' . (int)$_GET['is'] : 'is null';
    
    打赏 评论

相关推荐 更多相似问题