Jump to content

Query to pull data from one field into many fields


scalhoun2005

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.