Query to pull data from one field into many fields

[scalhoun2005]

I have a field we will call values, with and a id field.  As you can see it contains basic contact information with 9 rows data for each person  all in one huge database.  Now each person has a order_id that separates all the values from the rest.

 

order_id    id    values

5              1      John

5              2      Smith

5              3      456 Landy Lane

5              4      Bedford

5              5      PA

5              6      15544

5              7      email@email.com

5              8      555-555-5555

5              9      Single

 

I need some code to take all this data and display it in rows like i have illustrated below, after it does 9 fields it creates a new row with the next set of data.

 

First Name      Last Name      Address      City      State      Zip      email      phone      registration_type

 

Any help on how I can do this?

[effigy]

Are all 9 ids always there?

[scalhoun2005]

Are all 9 ids always there?

 

Yes all 9 are always there.  Order_id is always unique for each 9 as well.

[effigy]

If you use ORDER BY order_id, id in your SQL you should get the data in that order, which you can then process in a loop, outputting a new row when a different order_id is found and putting the rest into columns. You could also use GROUP_CONCAT (example) to separate each field by a delimiter, which you could then explode on.

[scalhoun2005]

If you use ORDER BY order_id, id in your SQL you should get the data in that order, which you can then process in a loop, outputting a new row when a different order_id is found and putting the rest into columns. You could also use GROUP_CONCAT (example) to separate each field by a delimiter, which you could then explode on.

 

If I took the loop approach.  How would I pursue taking the data to a row of fields?  Sorry I'm a little rusty, just jumped back into sql after a very long break.  Any input helps.

 

Thanks

 

Shane

[effigy]

When looping through the result set each row will be an associative array of the columns, which I've mimicked:

 

<pre>
<?php
$data = array(
	array(
		'order_id' => 5,
		'id' => 1,
		'values' => 'John'
	),
	array(
		'order_id' => 5,
		'id' => 2,
		'values' => 'Smith'
	),
	array(
		'order_id' => 6,
		'id' => 1,
		'values' => 'Bob'
	),
	array(
		'order_id' => 6,
		'id' => 2,
		'values' => 'Wallace'
	),
);
$order_id = -1;
$in_row = 0;
$row = 1;
$total_rows = count($data);
echo '<table border="1">';
foreach ($data as $array) {
	// If we need to start a new row...
	if ($order_id !== $array['order_id']) {
		// ...see if we're already in one...
		if ($in_row) {
			// ...if so, end it...
			echo '</tr>';
			$in_row = 0;
			// ...and start a new one if it isn't the last.
			if ($row !== $total_rows) {
				echo '<tr>';
				$in_row = 1;
			}
		} else {
			// ...if not, start one.
			echo '<tr>';
			$in_row = 1;
		}
	}
	// Output the data into a column.
	echo '<td>', $array['values'], '</td>';
	// If this is the last row...
	if ($row == $total_rows) {
		// ...end it.
		echo '</tr>';
	}
	// Save the last id and increment the row counter.
	$order_id = $array['order_id'];
	++$row;
}
echo '</table>';
?>
</pre>

[fenway]

I have a field we will call values, with and a id field.  As you can see it contains basic contact information with 9 rows data for each person  all in one huge database.  Now each person has a order_id that separates all the values from the rest.

 

order_id    id     values

5              1      John

5              2      Smith

5              3      456 Landy Lane

5              4      Bedford

5              5      PA

5              6      15544

5              7      email@email.com

5              8      555-555-5555

5              9      Single

 

I need some code to take all this data and display it in rows like i have illustrated below, after it does 9 fields it creates a new row with the next set of data.

 

First Name      Last Name      Address      City      State      Zip      email      phone      registration_type

 

Any help on how I can do this?

 

Please, please tell me why you don't just convert this to a proper table.