Displaying info from MySQL in Columns

[Aleroth]

Hi there,

 

Now i have a bunch rows in a SQL Database that i want to have displayed in a table but each rows info must be displayed in a column, for example the rows has data like this:

 

1, 2, 3, 4, 5, 6, 7, 8, 9, 10

 

so, lets say there is 3 rows it should display it like this :

 

1 1 1

2 2 2

3 3 3

4 4 4

5 5 5

6 6 6

7 7 7

8 8 8

9 9 9

10 10 10

 

this is the code i have so far:

 

$query  = "SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 FROM info ORDER BY id";
$result = mysql_query($query) or die('Error : ' . mysql_error());

$content = "<table>";

 //code goes here, i guess it will be some kind of foreach loop or something

$content .= "</table>";

echo $content;

 

I guess it will be a foreach loop i guess, i know how to work with the while loop but then it creates a column below each other and not next to each other. Help Please...Thank u

[LSW]

What is your output?

 

I would appear that you are placing the Data in HTML, if so it would be better to do the line breaks there.

 

Remember SQL is for gathering Data, not formatting it, so it should always be done with the output language when possible.

 

But try this link

[Aleroth]

Makes sense, but if i do that the info just displays like this :

 

1

2

3

4

1

2

3

4

1

2

3

4

 

and i want it to display it like this:

 

1 1 1 1

2 2 2 2

3 3 3 3

4 4 4 4

 

this is the code i added after your reply:

 

while($row = mysql_fetch_array($result, MYSQL_NUM))
{
	foreach($row as $value) 
	{
		$content .= "$value<br>";
	}
}

[falkencreative]

In order to create columns and rows, you would do something like this:

 

$content = '<table>';
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
     $content .= '<tr>'; // each time the while loop loops, it will create a new table row      
     foreach($row as $value) 
     {
           $content .= '<td>' . $value . '</td>';
     }
     $content .= '</tr>';
}
$content .= '</table>';

That will create something like this:

 

1 2 3 4

1 2 3 4

1 2 3 4

 

Creating a table that has a row for each record in the database is relatively easy. If you want each column to represent a row of data, like this:

 

1 1 1 1

2 2 2 2

3 3 3 3

 

That is a bit trickier. You would probably need to store all the variables you retrieve from the database in a 2D array (an array of arrays) and then sort out how to display those variables after. For example the array would look like this:

 

$data[0][0] = 1;

$data[0][1] = 2;

$data[0][2] = 3;

$data[0][3] = 4;

$data[1][0] = 1;

$data[1][1] = 2;

$data[1][2] = 3;

$data[1][3] = 4;

$data[2][0] = 1;

$data[2][1] = 2;

$data[2][2] = 3;

$data[2][3] = 4;

 

and the code would look something like this:

 

$data = array();
$i = 0; //this keeps track of the position within the array
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
     foreach($row as $value) 
     {
           $data[$i][] = $value; 
     }
}

// once all the data has been collected, then loop through it
$rows = count($data[0]);
$current_col = 0;
$content = '<table>';

for ($i = 0; $i < $rows; $i++) // create a row for each
{
$content .= '<tr>';

// now loop through each item in the data array, creating columns
// get all records for 'item1', then 'item2' and so on
// get $data[0]['item1'], then $data[1]['item1'], etc
foreach ($data as $d)
{
	$content .= '<td>' . $d[$current_col] . '</td>';
}

// track which item is currently being accessed, update as necessary
if ($current_col < 3)
	$current_col++;
else
	$current_col = 1;

$content .= '</tr>';	
}

$content .= '</table>';

echo $content;

 

The second way is definitely messier, and I'd suggest avoiding it if you can. (Note that this code hasn't been tested, but it should work).