Jump to content


Photo

Inserting multiple entries into single ROW & FIELD for sales record


  • Please log in to reply
4 replies to this topic

#1 modigy

modigy
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationMadrid, Spain

Posted 20 October 2006 - 11:04 AM

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:

<?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>';
?>

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:

<?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 {
?>

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
Code now, you'll get all the sleep you need when your Dead!!

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 20 October 2006 - 12:15 PM

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

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 order detail 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.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 modigy

modigy
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationMadrid, Spain

Posted 02 November 2006 - 08:41 PM

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

idorderNumisbn
2562207
isbn1
isbn2
isbn3


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
Code now, you'll get all the sleep you need when your Dead!!

#4 modigy

modigy
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationMadrid, Spain

Posted 03 November 2006 - 09:00 AM

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


M
Code now, you'll get all the sleep you need when your Dead!!

#5 modigy

modigy
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationMadrid, Spain

Posted 06 November 2006 - 10:12 PM

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
Code now, you'll get all the sleep you need when your Dead!!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users