使用相同ID PHP编译MySQL行

I have a table "orders" which saves all the orders made on a website. It saves the data in the following way:

我有一个表“订单”,它保存了网站上的所有订单。它以下列方式保存数据:

ID | Session_id | image | item | extra | customer_name

Sample date

 12 | sdgfafjhsf | image1.jpg | coffee | milk | roger
 13 | sdgfafjhsf | image1.jpg | muffin | jam  | roger
 14 | fjgjgsdfjg | image3.jpg | coffee | none | John

Currently I have the PHP accessing the database and spitting out all of the listings one by one.

目前我有PHP访问数据库并逐个吐出所有列表。

 mysql_connect("localhost", "root", "") or die(mysql_error()) ; 
 mysql_select_db("store") or die(mysql_error()) ; 

 //Retrieves data from MySQL 
 $data = mysql_query("SELECT * FROM orders WHERE status ='ordered'") or die(mysql_error());  //Puts it into an array 
 while($info = mysql_fetch_array( $data )) 
 { 

 //Outputs the image and other data

 Echo "$info[customer_name] <img src='cameras/$info[image]'/>  : $info[item] with $info[extras] <br />"; 


 }

I am ideally wanting the data to group by the session ID. So it prints out the name of the customer and the image once and then all of the items associated with it.

理想情况下,我希望数据按会话ID分组。因此,它会打印出客户名称和图像,然后打印出与之关联的所有项目。

eg. Roger , coffee, milk, muffin, jam

例如。罗杰,咖啡,牛奶,松饼,果酱

Any ideas? Thanks!

有任何想法吗?谢谢!

4 个解决方案

#1

A simple way would be to order the SQL so that you get all entries from each session following each other, and just remember the last session id you fetched to tell if you should output the name and picture or not; here's some pseudo code to show what I mean;

一种简单的方法是对SQL进行排序,以便从每个会话中获取所有条目,并记住您提取的最后一个会话ID,以告知您是否应该输出名称和图片;这里有一些伪代码来表明我的意思;

$data = 
  mysql_query("SELECT * FROM orders WHERE status ='ordered' ORDER BY session_id")
                or die(mysql_error());

$last_session_id = "**DUMMY**";  // Set a dummy value to not match the first row

while($info = mysql_fetch_array( $data )) 
{ 
  if($info['session_id'] != $last_session_id) 
  {
    //Outputs the image and other data if a new session_id has been found
    echo "$info[customer_name] <img src='cameras/$info[image]'/>  : $info[item] with $info[extras] <br />"; 
    $last_session_id = $info['session_id'];
  } else {
    // Same session_id as last row, skip name and picture
    echo "$info[item] with $info[extras] <br />"; 
  }
}

As a side note, the mysql_* database API is deprecated, you should look into using mysqli or pdo instead.

作为旁注,不推荐使用mysql_ *数据库API,您应该考虑使用mysqli或pdo。

#2

well try this..

试试吧..

 SELECT Session_id,image,item,extra,customer_name FROM orders WHERE status='ordered' group by Session_id,image,item,extra,customer_name

#3

Here you have to run two separate query. In first query you have to find all distinct customer name, better if you use customer id rather than customer name because id cannot be duplicate. Then after getting all customer who have ordered item, iterate them in loop and inside loop run another query to retrieve all orders of that customer by again customer id or by customer name.

在这里你必须运行两个单独的查询。在第一个查询中,您必须找到所有不同的客户名称,如果您使用客户ID而不是客户名称,则更好,因为ID不能重复。然后在获得已订购商品的所有客户后,在循环中迭代它们并在内部循环中运行另一个查询以再次通过客户ID或客户名称检索该客户的所有订单。

#4

In you while($info = mysql_fetch_array( $data ))-loop, where you print the output, you could add it to a standard array where the keys are the Session_ids.

在你的同时($ info = mysql_fetch_array($ data)) - 循环,你打印输出,你可以将它添加到标准数组,其中键是Session_ids。

Then you call echo $foo['sdgfafjhsf'] to get an array with db-entries which you can enumerate through print accordingly.

然后你调用echo $ foo ['sdgfafjhsf']来获取一个带有db-entries的数组,你可以通过print相应地枚举它。

#1

$data = 
  mysql_query("SELECT * FROM orders WHERE status ='ordered' ORDER BY session_id")
                or die(mysql_error());

$last_session_id = "**DUMMY**";  // Set a dummy value to not match the first row

while($info = mysql_fetch_array( $data )) 
{ 
  if($info['session_id'] != $last_session_id) 
  {
    //Outputs the image and other data if a new session_id has been found
    echo "$info[customer_name] <img src='cameras/$info[image]'/>  : $info[item] with $info[extras] <br />"; 
    $last_session_id = $info['session_id'];
  } else {
    // Same session_id as last row, skip name and picture
    echo "$info[item] with $info[extras] <br />"; 
  }
}