dongxiao1591
2014-01-20 23:52
浏览 89
已采纳

选择加入两个表PHP的查询

I have put a script together which prints all of the rows from the "sales_list" table but only those with the "users_sales_guild_id" which matches the logged in user. This works fine.

What I am trying to do is print all of the rows but retrieve the matching sales_id from the "accessories_orders" table, and put the "accessories_orders_total" and shipped status with the query, so the query below should look like this in the browser if the person logging in has a "user_sales_guild_id" value of "1234".

+--------+---------------+-------------------+----------+
| Model  | Customer Name | Accessories Total | Status   | 
+--------+---------------+-------------------+----------+
| Nissan | Malcom Smith  |                   | Add      |
| Ford   | Jane Smith    | 200.00            | Pending  | 
+------------------------+-------------------+----------+

So if there is a matching row in the "accessories_orders" table, then it will print the "shipped" and "accessories_orders_total" data. If there is no matching row for this, then it will display an "Add" link which leads to the add_accessories_sales.php.

I'm getting an error message "Undefined index: sales_model" and pretty much everything else within the first query, can anyone point out where I am going wrong?

"sales_list" Table

+--------------------------------------------------------------------------------------------------------------------------+
| sales_list                                                                                                               |
+------+--------------------------+--------------------------+------------------------+-------------+----------------------+
| sales_id | users_sales_guild_id | sales_customer_firstname | sales_customer_surname | sales_model |  sales_entry_date    |
+----------+----------------------+--------------------------+------------------------+-------------+----------------------+
| 1        | 1234                 | Jane                     | Smith                  | Ford        | 2013-12-02 12:00:00  |
| 2        | 5678                 | John                     | Chan                   | Mazda       | 2013-12-03 12:00:00  |
| 3        | 5678                 | Kevin                    | Chan                   | Fiat        | 2013-12-04 12:00:00  |
| 4        | 1234                 | Malcom                   | Smith                  | Nissan      | 2013-12-05 12:00:00  |
+----------+----------------------+--------------------------+------------------------+-------------+----------------------+

"accessories_orders" table

+-------------------------------------------------------------------------------------------------------------------------+
| accessories_orders                                                                                                      |
+-----------------------+----------------------+----------+--------------------------+-------------------------+----------+
| accessories_orders_id | users_sales_guild_id | sales_id | accessories_orders_total | accessories_orders_date | shipped  |  
+-----------------------+----------------------+----------+--------------------------+-------------------------+----------+
| 1                     | 1234                 | 1        | 200.00                   | 2013-12-02 12:00:00     | Pending  |
| 2                     | 5678                 | 2        | 350.00                   | 2013-12-03 12:00:00     | Pending  | 
| 3                     | 5678                 | 3        | 100.00                   | 2013-12-03 12:00:00     | Pending  |
+-----------------------+----------------------+----------+--------------------------+-------------------------+----------+

EDITED and UPDATED Code

<?php
require_once ('database.php'); // Connect to the database.

$query = " SELECT sl.sales_model, sl.sales_customer_firstname, sl.sales_customer_surname, ao.accessories_orders_total, ao.shipped, 
       COALESCE(ao.shipped)
       FROM sales_list sl
       LEFT JOIN accessories_orders ao ON(ao.sales_id = sl.sales_id)
       WHERE sl.users_sales_guild_id ='".$_SESSION['users_sales_guild_id']."' 
       ORDER BY 
     ".$order_by." LIMIT ".$start.", ".$display;

$result = @mysql_query ($query); // Run the query.

echo '<table width="610" cellspacing="1" cellpadding="5" style="font-size:11px;">
<tr>
<td align="center">Model </td>
<td align="center">Customer Name</td>
<td align="center">Accessories Total</td>
<td align="center">Status</td></tr>';

$bg = '#ffffff'; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

$status = $row['shipped'];

$bg = ($bg=='#e1e3e6' ? '#cdcdcf' : '#e1e3e6'); // Switch the background color.   
echo '<tr bgcolor="' . $bg . '">';
echo  '<td align="center">' . $row['sl.sales_model'] . '</td>';
echo  '<td align="center">' . $row['sl.sales_customer_firstname'] . ' ' . $row['sl.sales_customer_surname'] . '</td>';
echo  '<td align="center">$' . $row['acc.accessories_orders_total'] . '</td>';

$str = '<td align="center">';
if($status == 'Pending') {
 $str .=' Pending</td></tr>';
}
else {
 $str .='<strong><a href="add_accessories_sales.php?sid=' . $row['sl.sales_id'] . '">Add</a></strong></td></tr>'; 
}   
echo $str;
}

echo '</table>';
mysql_free_result ($result); // Free up the resources.
mysql_close();  //Close the database connection.
?>
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

2条回答 默认 最新

  • doulu3808 2014-01-21 01:09
    最佳回答

    Your query needs to be more like this:

    SELECT sl.sales_model, sl.sales_customer_firstname, sl.sales_customer_surname, ao.accessories_orders_total, COALESCE(ao.shipped, 'Add') status
    FROM sales_list sl
    LEFT JOIN accessories_orders ao ON(ao.sales_id = sl.sales_id)
    WHERE sl.users_sales_guild_id = 1234;
    

    The LEFT JOIN is the key here. It allows a row to be returned with the data from sales_list even if there is no corresponding entry in accessories_orders.

    See this fiddle for a working example: http://sqlfiddle.com/#!2/f7d3d/4

    As others have already stated, you should be using the mysqli function set as opposed to the mysql function set.

    评论
    解决 无用
    打赏 举报
查看更多回答(1条)

相关推荐 更多相似问题