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

帮助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
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能