1

I want to get values in column (item_name) and put it to another column(items_ordered) in another table

i tried the basic codes but it doesn't work (the usual insert sql)

tablename:mycart

+------+----------------+
| item_name |  Client   |
+------+----------------+
|  item1    |    wev    |
|  item2    |    wev    |
|  item3    |    wev    |
+------+----------------+

tablename:orders

+------+--------------------+
| items_ordered |  Client   |
+------+--------------------+
|               |           |
+------+--------------------+

what I want it to be is like this:

tablename:orders

+------+--------------------------+
| items_ordered       |  Client   |
+------+--------------------------+
| item1,item2,item3   |   wev     |
+------+--------------------------+

Code i used:

$sqlb = "select * from mycart where client='" . $_SESSION['fname'] . "'"; 

$resultb = $conn->query($sqlb); 

while($rowb = $resultb->fetch_assoc()){ 
    $rowb['item_name'] . ', ' ; 
    $client=$rowb['client']; 
    $sql_insertInto_myCart = "INSERT INTO orders(items,client) VALUES('$rowb','$client')"; 
    $query_sql_insertInto_myCart = $conn->query($sql_insertInto_myCart); 
    if(isset($query_sql_insertInto_myCart)) { 

    } 
}
Alive to die - Anant
  • 70,531
  • 10
  • 51
  • 98
bae
  • 125
  • 5
  • try to use GROUP_CONCAT() – M.Hemant Apr 30 '19 at 04:30
  • 1
    but I think the " while($rowb = $resultb->fetch_assoc()){ $rowb['item_name'] . ', ' ; " part is wrong – bae Apr 30 '19 at 04:31
  • 1
    @m. hernant I'm sorry, I'm new to this, how do I use it with my current code? – bae Apr 30 '19 at 04:32
  • Add code to your question not as comment – Jens Apr 30 '19 at 04:32
  • Why you want that output like your above result. @bae – Mr doubt Apr 30 '19 at 04:33
  • i want it like that so I can display it together in a single card just by using echo – bae Apr 30 '19 at 04:34
  • 1
    First of all you should not store values separated by comma, change your DB design. For example you should probably have orders with `client, order_id` fields and then a new table with `order_id, items` fields. There is an answer for [joining comma delimited data](https://stackoverflow.com/questions/16507239/join-comma-delimited-data-column) that could help you in getting a cleaner database. – Cray Apr 30 '19 at 04:35
  • Learn about prepared statements to prevent sql injection – Jens Apr 30 '19 at 04:42

2 Answers2

1

You need to design your database schema according to normalization principles.

Then you would get something like this:

enter image description here

Ken Evans
  • 362
  • 1
  • 11
0

You need to use GROUP_CONCAT

select Client, GROUP_CONCAT(`item_name` separator ',') as `item_name` from mycart where client='" . $_SESSION['fname'] . "'";

Important Note:-

Saving , separated data is against NORMALIZATION, so usually don't do that. Because it's become very hard to do CRUD operation on those records [as well as search too]

You need to use prepared statement to prevent your query from SQL INJECTION.

mysqli::prepare

PDO::prepare

Alive to die - Anant
  • 70,531
  • 10
  • 51
  • 98