[SOLVED] PHP/MySQL problem

[SocomNegotiator]

Ok here is my `order` database table layout

 

id      |    user_id    |      item_id    |    quantity    | date

4      |    4              |      3            |    3              | 10/2/04

5      |    4              |      2            |    3              | 10/2/04

6      |    4              |      1            |    5              | 10/3/04

 

 

Ok now you notice that the items have a different id, but the same user_id. Now the item_id may be the same but not likely unless they submit the same thing twice. Then the quantity is how many they want. Well the date for the first two items are the same which means they were ordered together, and the last one is one day later which means ordered alone. Well I am trying to figure out how to make a page that will show the user each order they have done. So on their display page they should see their first order only having those first two items. Then the second order will show the third item by itself. I just need to use the date in a while statement, but I can't figure out how and where.

[SocomNegotiator]

The date is really a timestamp, but I just did it this way for an example.

[beboo002]

in this case user want to know that how much shoping he did betwwen two dates id yes then

 

first u use thuis query some thing like

 

select * from orders where user_id=4 and date between first date and last date;

 

now if you hav store date as unix timestamps format in your database then first u change  date to unix timestamp date using mktime(). then its insert it date to query.

 

[unkwntech]

<?php
$sql = "SELECT * FROM order WHERE user_id='123456789' ORDER asc date";
$result = mysql_query($sql, $link) or die(mysql_error());
$i=0;
while($i<mysql_num_rows($result))
{
if(!isset($orders[mysql_result($result, $i, 'date')]))
 {
	$orders[mysql_result($result, $i, 'date')]['date'] = mysql_result($result, $i, 'date');
	$orders[mysql_result($result, $i, 'date')]['items'] = array(array(mysql_result($result, $i, 'item_id'), mysql_result($result, $i, 'quantity')));
	$orders[mysql_result($result, $i, 'date')]['id'] = array(mysql_result($result, $i, 'id'));
 }
else
 {
	$orders[mysql_result($result, $i, 'date')]['items'] .= array(mysql_result($result, $i, 'item_id'), mysql_result($result, $i, 'quantity'));
	$orders[mysql_result($result, $i, 'date')]['id'] .= mysql_result($result, $i, 'id');
 }
$i++;
}

 

With this data set:

 

id | user_id | item_id | quantity | date

4 | 4 | 3 | 3 | 10/2/04

5 | 4 | 2 | 3 | 10/2/04

6 | 4 | 1 | 5 | 10/3/04

 

It should produce this:

$order['10/2/04']['items'] = array(array(3, 3), array(2, 3)); //array(3, 3) is the item id then the quantity for that order
$order['10/2/04']['id'] = array(4,5); //The ids, in the database for that order
$order['10/3/04']['items'] = array(array(1, 5));
$order['10/3/04']['id'] = array(6);

 

[SocomNegotiator]

Hmm...I tried it out and had to change a few errors, but when the query worked I got nothing displayed on the page...

 

 

<?php
$sql = "SELECT * FROM order WHERE user_id='123456789' ORDER asc date";
$result = mysql_query($sql, $link) or die(mysql_error());
$i=0;
while($i<mysql_num_rows($result))
{
if(!isset($orders[mysql_result($result, $i, 'date')]))
 {
	$orders[mysql_result($result, $i, 'date')]['date'] = mysql_result($result, $i, 'date');
	$orders[mysql_result($result, $i, 'date')]['items'] = array(array(mysql_result($result, $i, 'item_id'), mysql_result($result, $i, 'quantity')));
	$orders[mysql_result($result, $i, 'date')]['id'] = array(mysql_result($result, $i, 'id'));
 }
else
 {
	$orders[mysql_result($result, $i, 'date')]['items'] .= array(mysql_result($result, $i, 'item_id'), mysql_result($result, $i, 'quantity'));
	$orders[mysql_result($result, $i, 'date')]['id'] .= mysql_result($result, $i, 'id');
 }
$i++;
}

 

With this data set:

 

id      |    user_id    |      item_id    |    quantity    | date

4      |    4              |      3            |    3              | 10/2/04

5      |    4              |      2            |    3              | 10/2/04

6      |    4              |      1            |    5              | 10/3/04

 

It should produce this:

$order['10/2/04']['items'] = array(array(3, 3), array(2, 3)); //array(3, 3) is the item id then the quantity for that order
$order['10/2/04']['id'] = array(4,5); //The ids, in the database for that order
$order['10/3/04']['items'] = array(array(1, 5));
$order['10/3/04']['id'] = array(6);

 

[unkwntech]

Immediately after that add a print_r($orders);

And lets see if my code is building the array the way it should.

[SocomNegotiator]

Immediately after that add a print_r($orders);

And lets see if my code is building the array the way it should.

 

 

This is what it outputs now....

 

 	
Array ( [2008-07-17 17:05:58] => Array ( [date] => 2008-07-17 17:05:58 [items] => ArrayArray [id] => Array2 ) [2008-07-18 14:01:17] => Array ( [date] => 2008-07-18 14:01:17 [items] => Array ( [0] => Array ( [0] => 1 [1] => 1 ) ) [id] => Array ( [0] => 3 ) ) )

[PFMaBiSmAd]

Since someone could easily place multiple orders in a single day, your existing method is doomed to fail. You need to identify orders using an order number or an order id.

[SocomNegotiator]

Yeah but each item is separate from each other...and so I could not do an order id...unless....ok when the person submits the order I can change the query I have so that the items they submit have the same id rather than the order table auto incrementing. But how would I make it so that I never get that same id used again, but then again the items for order #1 would have the same id and then the order #2 would have a different id....so how would you be able to do that because an order could have multiple items??

 

Here is my query for when the order is actually submitted:

 

 

if(isset($_POST['submit']))
{

$userid = $_POST['user_id'];
$res = mysql_query("SELECT * FROM pre_order WHERE user_id = ".$userid) or die(mysql_error());
while($r = mysql_fetch_array($res)){
$db->query("INSERT INTO `order` (`pre_order_id` , `item_id` , `amount` , `user_id` )  VALUES (".$r['id'].", ".$r['item_id'].", ".$r['amount'].", ".$r['user_id'].")") or die (mysql_error());

// track the id's of the new inserts
$insert_ids[] = mysql_insert_id();
}
if($error){
echo $error;
// remove any rows that HAVE been inserted from the pre_order table
$ids = implode("', '", $insert_ids);
$remove = "DELETE FROM order WHERE id IN ('$ids')";
$dres = mysql_query($remove);
  if($dres){
  echo "Your items have been removed from your order";
  } else {
  echo "Could not remove items from order Error: ".mysql_error();
  }
} else {
$pre_del = "DELETE FROM pre_order WHERE user_id = ".$userid;
$pres = mysql_query($pre_del);
  if($pres){
  echo "Your items have been cleared and submitted over to your order history. ";
  } else {
  echo "Could not remove items Error: ".mysql_error();
  }
}


}

[SocomNegotiator]

Hmm if I can make run a query before the insert and use an order number...then I can check the database for the last order number. Then just add 1 to that number. That would actually work...but do you have any ideas on how I could do that and make sure an order number is only used once..?