Gather certain rows as One Order?

[lobfredd]

Hello, i am wandering how to gather certain rows in mysql database as Orders.

 

The method i thought of was to sort them using TIMESTAMP or something.

A simply php code that generates a random number in a column, and that number is same in all the rows that has the same TIMESTAMP ?

 

Thanks

 

[Psycho]

I'm not sure I understand what you are asking. If you just want to order the results by a timestamp then do just that

SELECT *
FROM table
ORDER BY timestamp_field

 

If that's not what you want perhaps an example of some database records and the results you are trying to achieve

[lobfredd]

 

Here u have the table, I want the ones with the same TIMESTAMP to get a field with the same number in it aka. Order number. (i know i havnt created the ordernumber field yet)

 

Thanks

[DavidAM]

The data there already satisfies your requirement (well, except for the "random" part). If you call the PHP strtotime function against the "Time" value, all of the rows with the same Timestamp will return the same integer value.

 

At first I thought you wanted something like a sequence, but then I re-read the OP and you say you want a "random" number. Maybe if you explain a little about why you need it and/or how it will be used, we can offer a better answer.

[lobfredd]

Sorry, it does not need to be random, just unique

something like a field with Auto Increment or something

[Barand]

Create table for your order numbers eg

CREATE TABLE `ono` (
  `ono` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` datetime NOT NULL,
  PRIMARY KEY (`ono`)
);

 

Now write a record for each date to this table, thus creating an ono for each date

 

INSERT INTO ono (time)
SELECT DISTINCT time FROM mytable

 

You can then use this table to update the ono field in the records with the matching times in your original table.

[lobfredd]

Could you explain alittle more please?

 

I created the ono table as you said, then i wrote this php code:

<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mydatabase", $con);

mysql_query("INSERT INTO ono ('time')
SELECT DISTINCT time FROM ordre")or die(mysql_error());

mysql_close($con);
?>

 

However, i get a browser error when i try to excecute that code :/

 

Thanks!

[Barand]

As a test I created an "orders" table , 9 records with 3 dates. Note I added an empty ono column which I will populate later

+---------+---------------------+------+

| idorder | time                | ono  |

+---------+---------------------+------+

|      1 | 2012-05-05 22:46:42 | NULL |

|      2 | 2012-05-05 22:46:42 | NULL |

|      3 | 2012-05-05 22:46:42 | NULL |

|      4 | 2012-05-05 22:46:42 | NULL |

|      5 | 2012-05-09 22:46:42 | NULL |

|      6 | 2012-05-10 22:46:42 | NULL |

|      7 | 2012-05-10 22:46:42 | NULL |

|      8 | 2012-05-10 22:46:42 | NULL |

|      9 | 2012-05-10 22:46:42 | NULL |

+---------+---------------------+------+

 

I then inserted records for these 3 dates into the "ono" table with

 

mysql_query("INSERT INTO ono (time)SELECT DISTINCT time FROM orders")

 

The ono table now contains an auto_incrementd ono for each date:

+-----+---------------------+

| ono | time                |

+-----+---------------------+

|  1 | 2012-05-05 22:46:42 |

|  2 | 2012-05-09 22:46:42 |

|  3 | 2012-05-10 22:46:42 |

+-----+---------------------+

 

Now update the orders table ono column by matching the dates in the ono table.

 

UPDATE orders
INNER JOIN ono ON orders.time = ono.time
SET orders.ono = ono.ono

 

orders table now has the order numbers for each date:

 

+---------+---------------------+------+

| idorder | time                | ono  |

+---------+---------------------+------+

|      1 | 2012-05-05 22:46:42 |    1 |

|      2 | 2012-05-05 22:46:42 |    1 |

|      3 | 2012-05-05 22:46:42 |    1 |

|      4 | 2012-05-05 22:46:42 |    1 |

|      5 | 2012-05-09 22:46:42 |    2 |

|      6 | 2012-05-10 22:46:42 |    3 |

|      7 | 2012-05-10 22:46:42 |    3 |

|      8 | 2012-05-10 22:46:42 |    3 |

|      9 | 2012-05-10 22:46:42 |    3 |

+---------+---------------------+------+

 

Job done.

[lobfredd]

omg, Many thanks!!

 

Well now i have to figure out how to list em to the user.

 

I want something like:

Ordernumber:        XX

Product:                product 1

                              product 2

Total price:            XX

 

any suggestions?

 

The only way i have managed is this:

Ordernumber:        XX

Product:                product 1

Price:                      XX

 

Ordernumber:        XX

Product:                product 2

Price:                      XX

[lobfredd]

 mysql_select_db($database_lol, $lol);
$query_ono = "SELECT DISTINCT ordre.ono FROM ordre WHERE ordre.bruker='{$_SESSION['MM_Username']}'";
$ono = mysql_query($query_ono, $lol) or die(mysql_error());
$row_ono = mysql_fetch_assoc($ono);
$totalRows_ono = mysql_num_rows($ono);

mysql_select_db($database_lol, $lol);
$query_history = "SELECT ordre.vare FROM ordre WHERE ordre.ono={$ono}";
$history = mysql_query($query_history, $lol) or die(mysql_error());
$row_history = mysql_fetch_assoc($history);
$totalRows_history = mysql_num_rows($history);

 

WHERE ordre.ono={$ono}, seems that this is not right

What is wrong with this?

I get the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id #8' at line 1

 

Many thanks

[Barand]

My "orders" table

+---------+---------------------+------+-----------+--------+

| idorder | time                | ono  | product  | price  |

+---------+---------------------+------+-----------+--------+

|      1 | 2012-05-05 22:46:42 |    1 | Product 1 |  29.99 |

|      2 | 2012-05-05 22:46:42 |    1 | Product 3 |  10.49 |

|      3 | 2012-05-05 22:46:42 |    1 | Product 5 |  55.00 |

|      4 | 2012-05-05 22:46:42 |    1 | Product 7 |  0.99 |

|      5 | 2012-05-09 22:46:42 |    2 | Product 1 |  29.99 |

|      6 | 2012-05-10 22:46:42 |    3 | Product 2 | 149.99 |

|      7 | 2012-05-10 22:46:42 |    3 | Product 4 |  1.15 |

|      8 | 2012-05-10 22:46:42 |    3 | Product 6 |  21.25 |

|      9 | 2012-05-10 22:46:42 |    3 | Product 8 |  99.00 |

+---------+---------------------+------+-----------+--------+

 

<?php
    $res = mysql_query("
  SELECT ono
  , GROUP_CONCAT(product SEPARATOR '<br>') as products
  , SUM(price) as total
  FROM orders
  GROUP BY ono ");
echo '<pre>';
while (list($ono, $products, $total) = mysql_fetch_row($res)) {
    echo <<< TXT
    <table border=1 cellspacing=0 cellpadding=4>
    <tr>
        <td>Order number</td>
        <td>$ono</td>
    </tr>
    <tr>
        <td>Products</td>
        <td>$products</td>
    </tr>
    <tr>
        <td>Total price</td>
        <td>$total</td>
    </tr>
    </table><br /><br />
TXT;
}
?>