$_SESSION[' '][] array to comma delimited...

[Alexhoward]

Hi Everybody!

 

i've got a shopping basket, and each item added is saved into a session :

 

$_SESSION['a'][$product_id]

 

where 'a' saves the quantity and $product_id is the product id

 

i'd like to be able to save the basket into mysql on one row, so i can have one unique order id, along with the customer id

 

the only way i've found to do it is to use foreach, however this will obviously pull the sessions and insert them seperatly.

 

i'd like to insert them as :

 

| order_id | cust_id | date | products (as id, so 1,2,3) | quantity (e.g. 4,5,3) | price (e.g. 2.99,1.99,4.99) | 

 

so i can them pull the all out exploded again when customers want to see their order history..

 

could anyone please help me?

 

thanks

[F1Fan]

To do what you want, you'll have to do one of three things:

 

1: insert the data for products, quantity, and price as text that you format in a specific way so that you can extract then and translate as arrays

2: for those fields, use an array data type, but I don't think MySQL supports that

3: (this is your best option), have one table with order_id, cust_id, and date, and have a second table that has order_id, product, quantity, and price. In that second table, you can have one or more rows that all reference the same single row in the first table.

[Alexhoward]

Hello again F1Fan!

 

that's a good idea, but how do i find the order id when inserting into both tables as it's created by an auto number...?

 

what unique value would i have to ensure i select the correct one...?

[F1Fan]

Use the data that you just inserted to do a SELECT to get the order ID. So, like this:

<?php
// insert stuff here...
$q = "SELECT order_id FROM sometable WHERE cust_id = $cust_id AND date = '$date' ORDER BY order_id DESC LIMIT 1";
// this should be the order_id you're looking for
?>

[Alexhoward]

OK

 

Thanks again i'll give it a go, because i'm using a timestamp thou, it may be a second off...

 

sure there maust be somewway to tie them together,

 

thanks for the push, great idea!

[Alexhoward]

hi,

 

i tried this,

 

the problem with doing this is that when i select it again to get the order id it only pulls one value and doesn't loop through all the sessions...

 

any sugestions?

 

thanks again

[DarkWater]

Use the data that you just inserted to do a SELECT to get the order ID. So, like this:

<?php
// insert stuff here...
$q = "SELECT order_id FROM sometable WHERE cust_id = $cust_id AND date = '$date' ORDER BY order_id DESC LIMIT 1";
// this should be the order_id you're looking for
?>

 

No no no.  Use the mysql_insert_id() function.

[F1Fan]

You only want ONE order_id. You use that order_id to insert into all the second table:

<?php
// assuming you used the other code and $order_id is your order_id and $prices is an array with all your product pricing
$query = "";
foreach ($_SESSION['a'] as $pid=>$qty){
  $query .= "INSERT INTO table2 (order_id, product, qty, price) VALUES ($order_id, $pid, $qty, '{$prices[$pid]}'); ";
}
mysql_query($query) or die...
?>

[Alexhoward]

thanks for your help...

 

i'm getting in a right muddle...

 

here's what i've done.....

 

looking at it i know it's messed up...

 

<?php

session_start();

include("config.php");

//connect to the mysql server
$link = mysql_connect($host, $db, $pass)
or die ("Could not connect to mysql because ".mysql_error());

//select the database	
mysql_select_db($db)
or die ("Could not select database because ".mysql_error());



foreach($_SESSION['a'] as $product_id => $quantity) {

$query = "SELECT * FROM products WHERE id = '".$product_id."' " ;

$result = mysql_query($query);

while ($array= mysql_fetch_array($result)) {

$prod = $array['name'] ;
$price = number_format($array['price'],2) ;
$line_total = number_format($quantity * $price,2) ;

if(!isset($total)) { $total = 0 ; }

$total = number_format($line_total + $total,2) ;

if(!isset($_SESSION['customer'])) { $_SESSION['customer'] = "guest" ; }

$num = "SELECT * FROM Customers WHERE username = '".$_SESSION['customer']."' " ;

$cust = mysql_query($num);

while ($array1= mysql_fetch_array($cust)) {

$custid = $array1['id'] ;

mysql_query("INSERT INTO ordernum VALUES('Null', '".$custid."', '".$total."', CURRENT_TIMESTAMP)") ;

$order = "SELECT * FROM ordernum WHERE id = '".$custid."' AND amount = '".$total."' " ;

$ordernum = mysql_query($order);

while ($array2= mysql_fetch_array($ordernum)) {

$orderid = $array2['id'] ;

mysql_query("INSERT INTO orders VALUES ('Null', '".$orderid."', '".$custid."', CURRENT_TIMESTAMP, '".$prod."', '".$quantity."', '".$total."', '1')") ;


}
}
}
}

?>

[Alexhoward]

OK guys,

 

so i've got it working like this :

 

<?php

session_start();

include("config.php");

//connect to the mysql server
$link = mysql_connect($host, $db, $pass)
or die ("Could not connect to mysql because ".mysql_error());

//select the database	
mysql_select_db($db)
or die ("Could not select database because ".mysql_error());


if(!isset($_SESSION['customer'])) { $_SESSION['customer'] = "guest" ; }


$num = "SELECT * FROM Customers WHERE username = '".$_SESSION['customer']."' " ;

$cust = mysql_query($num);

while ($array= mysql_fetch_array($cust)) {

$custid = $array['id'] ;

mysql_query("INSERT INTO ordernum VALUES('Null', '".$custid."', 'Null', CURRENT_TIMESTAMP)") ;

}

$ordnum = "SELECT * FROM ordernum WHERE date = CURRENT_TIMESTAMP " ;

$number = mysql_query($ordnum);

while ($array1= mysql_fetch_array($number)) {

$ordernumber = $array1['id'] ;

}


foreach($_SESSION['a'] as $product_id => $quantity) {

$query = "SELECT * FROM products WHERE id = '".$product_id."' " ;

$result = mysql_query($query);

while ($array= mysql_fetch_array($result)) {

$prod = $array['name'] ;
$price = number_format($array['price'],2) ;
$line_total = number_format($quantity * $price,2) ;

if(!isset($total)) { $total = 0 ; }

$total = number_format($line_total + $total,2) ;


mysql_query("INSERT INTO orders VALUES ('Null', '".$ordernumber."', '".$custid."', CURRENT_TIMESTAMP, '".$prod."', '".$quantity."', '".$total."', '1')") ;


}
}

?>

 

a marked improvement....

 

however, what happens if the timestamps don't match...

 

is it possible to put a tollerance on the time? or to possibly timeout if it takes longer than a second.....

[F1Fan]

OK, first, you don't have to select all columns in that table, and you don't want more than one result. You also don't want to loop through the results. Second, rather than trying to equal the timestamp, try this:

<?php

session_start();

include("config.php");

//connect to the mysql server
$link = mysql_connect($host, $db, $pass)
or die ("Could not connect to mysql because ".mysql_error());

//select the database   
mysql_select_db($db)
or die ("Could not select database because ".mysql_error());


if(!isset($_SESSION['customer'])) { $_SESSION['customer'] = "guest" ; }


$num = "SELECT id FROM Customers WHERE username = '".$_SESSION['customer']."' " ;

$cust = mysql_query($num);

$array= mysql_fetch_assoc($cust));

$custid = $array['id'] ;

mysql_query("INSERT INTO ordernum VALUES('Null', '".$custid."', 'Null', CURRENT_TIMESTAMP)") ;

// select only what you need in your SELECTS; don't always SELECT *...
// also, you need more in your WHERE statement here, like the customer ID (I don't know your table column names)
$ordnum = "SELECT id FROM ordernum WHERE cust_id = $custid ORDER BY date DESC LIMIT 1" ;

$number = mysql_query($ordnum);

$array1= mysql_fetch_assoc($number));

$ordernumber = $array1['id'] ;

foreach($_SESSION['a'] as $product_id => $quantity) {

$query = "SELECT * FROM products WHERE id = '".$product_id."' " ;

$result = mysql_query($query);

while ($array= mysql_fetch_assoc($result)) {

$prod = $array['name'] ;
$price = number_format($array['price'],2) ;
$line_total = number_format($quantity * $price,2) ;

if(!isset($total)) { $total = 0 ; }

$total = number_format($line_total + $total,2) ;

mysql_query("INSERT INTO orders VALUES ('Null', '".$ordernumber."', '".$custid."', CURRENT_TIMESTAMP, '".$prod."', '".$quantity."', '".$total."', '1')");


}
}

?>

Finally, you really should add or die(mysql_error()); after each mysql_query. If you don't, you won't have any idea if one of your queries fails.