Inserting multiple entries into single ROW & FIELD for sales record

[modigy]

Hey All!

I am working on a purchasing system.  All seems to work thusfar...except a customer may select more than one item for purchase.  Currently the MSQL DB only lists the one item in the sales table.  I would like each additional item (the book# ISBN) to be listed in the same row (the ISBN field).  How would I go about doing this?

Here is the code I have:

[code]<?php $database=mysql_pconnect('localhost','jandj_root');
mysql_select_db('books');
$total = '0';

if (count($selectedArray) > 0) { // If the shopping cart is not empty...
foreach ($selectedArray as $value) {      // For each book id in the shopping cart...
// Get the book information
$query="select * from jandj_jandj.books where id=".$value." and display='JJ' limit 1";
$result=mysql_query($query);
$column=mysql_fetch_array($result);
$image=$column[7];
$title=$column[1];
$priceAct=$column[13];
$pricePub=$column[12];
$author=$column[3];
$isbn=$column[2];
$id=$column[0];

$align = $left;

$total=$total+$priceAct;

// Display the information of the selected book
echo '<div id="book"> <p><font color="#7b8049" face="Geneva, Arial, Helvetica, sans-serif"><b>';
echo '<img src="../images_books/'.$image.'" width="47" height="69" align="'.$align.'">';
echo $title;
echo '<br></b></font>
<b><font color="#000000" size="1" face="Geneva, Arial, Helvetica, sans-serif">
by '.$author.'
  </font></b></p>
  <br>
<p><b><font color="#000000" size="2" face="Geneva, Arial, Helvetica, sans-serif">
J &amp; J price: '.$priceAct.' &#8364;
  </font></b>

</p></div><hr>';
?>[/code]

As you see above I have each book added to the shopping cart set to list for the customer's confirmation.  Addtionally the prices are added to create a 'Grand Total'.

Here is the INSERT for the purchase into the DB:

[code]<?php $status= '1'; // 1 - placing the order
$_SESSION['sessionOrderNum'] = $orderNum;  // Track the order number for when we return from Caixa Cataluyna

$submit="insert into jandj_jandj.sales values ('','".$customerID."',NOW(),'".$grandtotal."','".$paymentType."','".$status."','".$orderNum."','".$isbn."')";
$result=mysql_query($submit);
// echo 'insert $result = '.$result.'<br>$submit='.$submit;
if ($result)
{
// New sales record was created. Move along
}
else {
?>[/code]

As you see above I have the 'Insert' for the Order information.  However, it only inserts ONE 'ISBN' number.  Additionally, I want to maintain just one 'orderNumber' (1 orderNumber per row in DB), but I would like the ISBN column to list each 'ISBN' in the order.  The client will need to know what books were ordered!

Any and all help would be GREATLY appreciated!!!

M

[obsidian]

i'm not sure i follow. are you wanting all ISBN's to be listed in the [b]one field[/b], or are you willing to consider setting up an associative table? if it's the former, you'll need to do something like:
[code]
<?php
$isbn = array('#1', '#2', '#3';
$sql = mysql_query("INSERT INTO table (isbn) VALUES ('" . implode(',', $isbn) . "')");
?>
[/code]

that way, you're creating one string to be inserted with several ISBN's in it.

if you're willing to consider a table change, i would recommend having an order table where each row contains one order number, but have another table for [b]order detail[/b] where you can store as many records as you want (ISBN's), and each row would then reference an order number. so, you could have 15 entries in the order detail table that all reference the same order. make sense?

hope this helps some.

[modigy]

obsidian,

Unfortunately, perhaps because I wasn't clear...I'm not so sure I understand what your suggestion is  :-\

Here's what I would like to happen:

1. Customer places 3 books in the shopping cart.
2. Customer checks out
3. PHP inserts a new row and creates an 'orderNum' 1 higher than the highest currently in the database
4. The 'isbn' field receives 'isbn' numbers for each book in the order

[table][tr][td]id[/td][td]orderNum[/td][td]isbn[/td][/tr][tr][td]256[/td][td]2207[/td][td][table][tr][td]isbn1[table][tr][td]isbn2[/td][/tr][tr][td]isbn3[/td][/tr][/table][/td][/tr][/table][/td][/tr][/table]

Does this help?  I'm not sure how else I might explain it..except my client will need to query the DB based on 'orderNum' and view each book purchased under that 'orderNum'.

Please let me know your thoughts.

Thanks,


M

[modigy]

Any Ideas?  Any and All help will be greatly appreciated!


M

[modigy]

Obsidian,

I'm not sure how I would use the code snippet you gave me.

I have an 'if/foreach' loop that pulls the DB information for the customer to view while checking out.  Then the price and shipping, etc. are put together for the transaction with the bank. 

This same information is used to create the sales record.  HOWEVER, the sales table as it currently stands on receives ONE new row and that row only contains 'I believe' the last 'ISBN' if it was the last in the loop.

How can I put together a list of variables that are retreved and then enter them in to one row in the DB (please see my above grafic)??

Please, help...I'm getting a bit crazy....

Thanks,

M