doubengshao8872
2016-02-01 12:35
浏览 58
已采纳

PHP / MySQL - 更新查询

I am trying to fix up some SQL queries in CS-Cart so uses their built in queries better, the following works and further in code loops through and displays products

$getOrder=db_get_fields("SELECT * FROM ?:orders WHERE total='".$orderSum."' AND email='".$email."' AND status IN('P','C','O') ");
$orderProducts = db_get_fields("SELECT product_id FROM cscart_order_details WHERE order_id='".$getOrder[0]."'");

But i have tried to write using:

http://docs.cs-cart.com/4.2.x/core/db/placeholders.html

However it only ever returns 1 product so im making a mistake somewhere and no sure what? anyone have any ideas? im rather new to PHP and MySQL

$getOrder = db_get_fields("SELECT * FROM ?:orders WHERE total = ?i AND email = ?s AND status IN ('P','C','O')", $orderSum, $email);
$orderProducts = db_get_fields("SELECT product_id FROM ?:order_details WHERE order_id = ?a", $getOrder[0]);

UPDATE with loop and check:

$acId = $_REQUEST['id'];

$productsGet = db_get_field("SELECT cart FROM ?:abandoned_cart WHERE user_id = ?s", $acId);
$products = unserialize($productsGet);

$cartProducts=array();
if (is_array($products) || is_object($products)){
  foreach($products as $pData){
    $cartProducts[]=$pData['product_id'];
  }
}
if(!array_diff($cartProducts,$orderProducts)){
  $products=array();
}

oh here is the output part but its the issue with the new queries

if (!empty($products)) {
  foreach ($products as $product) {
    $text .='
      <tr>
        <td><a  href="http://'.$_SERVER['SERVER_NAME'].'?dispatch=products.view&product_id='.$product['product_id'].'"> <img title="" height="120" width="120" alt="" src="'.$product['main_pair']['detailed']['image_path'].'"></a></td>
        <td><a href="#" style=" font-weight:bold; color:#333; font-size:13px; text-decoration:none;">'.$product['product'].'</a><a href="#">&nbsp;<i></i></a><div style=" font-weight:bold; color:#333; font-size:12px; margin-top:4px; text-decoration:none;"> CODE: <span>'.$product['product_code'].'<!--product_code_update_2512012004--></span> </div></td>
        <td style=" text-align:center;"><span style=" font-weight:bold; color:#333; font-size:12px; margin-top:4px; text-decoration:none;">$</span><span style=" font-weight:bold; color:#333; font-size:12px; margin-top:4px; text-decoration:none;">'.$product['price'].'</span> </td>
        <td><div style="display: inline-block;vertical-align: top;width: 56px;"><input type="text" disabled value="'.$product['amount'].'" size="3"  style="border:1px solid #c2c9d0; box-shadow:0 1px 3px rgba(0, 0, 0, 0.1) inset; border-radius:3px; float: left;height: 33px;text-align: center;width: 36px;"></div></td>
        <td style="font-size:14px;  font-weight:bold; color:#333; text-align:center; font-size:13px; text-decoration:none;"><span>$</span><span stye=" color:#000;">'.$product['price']*$product['amount'].'</span> </td>
      </tr>';
    $sum =$sum+$product['price']*$product['amount'];
  }
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doufan3408 2016-02-01 12:48
    已采纳

    Ok found the issue and was rather simple

    $orderProducts = db_get_fields("SELECT product_id FROM ?:order_details WHERE order_id = ?a", $getOrder[0]);
    

    to

    $orderProducts = db_get_fields("SELECT product_id FROM ?:order_details WHERE order_id = ?s", $getOrder[0]);
    

    ?a > ?s

    点赞 打赏 评论
  • dongni1892 2016-02-02 07:52
    $getOrder = db_get_fields("SELECT * FROM ?:orders WHERE total = ?i AND email = ?s AND status IN ('P','C','O')", $orderSum, $email);
    $orderProducts = db_get_fields("SELECT product_id FROM ?:order_details WHERE order_id = ?a", $getOrder[0]);
    

    In the first query, you are using ?i, which represents an integer, however, CS-Cart stores total as a float number. It is not the best solution.

    Your first query should be:

    $getOrder = db_get_row("SELECT * FROM ?:orders WHERE total = ?i AND email = ?s AND status IN ('P','C','O')", $orderSum, $email);
    

    And the second:

    $orderProducts = db_get_row("SELECT product_id FROM ?:order_details WHERE order_id = ?i", $getOrder['order_id']);
    

    Also, I suggest you to use the db_get_row() function, if you want to fetch only 1 row from the database, because CS-Cart will convert this into an associative array, where the keys are the field names. So your code will be much more readable.

    If you check the official documentation, you can use ?a if you want to find something in a set. Eg. db_query('SELECT * FROM ?:orders WHERE order_id IN (?a)', $order_id);

    点赞 打赏 评论

相关推荐 更多相似问题