How to display single column mysql data in multiple columns in php

PHP
1

I use the code below to display 50 US states from MySQL db in a single row separated by “|”. I need help displaying 50 states in 20 rows by 3 columns table in alphabetical order (1st column to have states 1-20, 2nd 21-40, 3rd 41-50)


$result=mysql_db_query($dbname,"select `state` from states order by `state`");
	while(list($name)=mysql_fetch_array($result)){
	echo "<a href=\\"?state=$name\\" >$name</a> | \
" ;
}

thanks,

2

See http://www.php.net/array_chunk

Look for the user comment for the array_chunk_vertical() variation.

3

crmalibu,

thanks for the pointer. I get the idea. But due to my lack of knowledge in arrays area, I am limping. here is what I have so far.


			$query = "SELECT id, state FROM states";
			$result = mysql_query($query) or die(mysql_error());
		    $user = mysql_fetch_array($result);
			
			while ($row = mysql_fetch_array($result)) {
 			   printf ("<br>ID: %s  State: %s", $row[0], $row[1]);
			}

<< something is missing here >>


$values = " " ; // arrays of id and state names should be here.

			$rows = array_chunk_vertical($values, 20, false, false);
			
			print "<table>\
";
			foreach ($rows as $row) {
				print "<tr>\
";
				foreach ($row as $value) {
					print "<td width='200'>" . $value . "</td>\
";
				}
				print "</tr>\
";
			}
			print "</table>\
";
			
			function array_chunk_vertical($input, $size, $preserve_keys = false, $size_is_horizontal = true)
			{
				$chunks = array();
			
				if ($size_is_horizontal) {
					$chunk_count = ceil(count($input) / $size);
				} else {
					$chunk_count = $size;
				}
			
				for ($chunk_index = 0; $chunk_index < $chunk_count; $chunk_index++) {
					$chunks[] = array();
				}
			
				$chunk_index = 0;
				foreach ($input as $key => $value)
				{
					if ($preserve_keys) {
						$chunks[$chunk_index][$key] = $value;
					} else {
						$chunks[$chunk_index][] = $value;
					}
				
					if (++$chunk_index == $chunk_count) {
						$chunk_index = 0;
					}
				}
			
				return $chunks;
			}

After properly displaying the data, I would like to hyperlink each state to its id. Further help is appreciated.

4 

$values = array();
while ($row = mysql_fetch_array($result)) {
    $values[] = $row;
}
5

If you are using the table simply to hold the display, the countries could be UL or P separated couldnt they?

If so, then just do this, create a simpler table.


$array = range(1,52); // this is pretending to your result from the db

$cols = 3;  // decide how many cells you want, wide

$rows = ceil( count($array) / $cols ); // works out how many rows

$cnt = 1; // set a counter for upcoming loop

echo '<table><tr valign=top><td>' . PHP_EOL; // start table

foreach ($array as $name){

if( $cnt >= $rows ) {  // start a new TD when you get to the number of rows

 echo '<p>' . $name . '</p></td><td>' .PHP_EOL ;
  $cnt = 0;  // reset the counter, and start a new column

 }else{ // other wise just echo the country name

  echo '<p>' . $name . '</p>' . PHP_EOL;

 }
$cnt++;
}
echo '</td></tr></table>' . PHP_EOL;

Makes a table with 3 td’s each holding 18 countries (52 / 3 and rounded up)

I commented it so you can hopefully see what is going on, and you should be able to spot where the A links go, or how to turn the Ps into UL / LI s.

6

Cups & Crmalibu,

THANK YOU!

Here is final working code:


function get_states() {

	//get stuff in the cart and return in indexed array
	$q1 = "SELECT id, state FROM states ";
	$incart_r = mysql_query($q1) or die(mysql_error());

	$contents = array();
		
	while($incart = mysql_fetch_array($incart_r)){
						
		//build array of info
		$item = array(
		$incart['id'],
		$incart['state']);
		array_push($contents,$item);
	}

	return $contents ;

}

$c = get_states();

$cols = 3;  // decide how many cells you want, wide

$rows = ceil( count($c) / $cols ); // works out how many rows

$cnt = 1; // set a counter for upcoming loop

echo '<table><tr valign=top><td>' . PHP_EOL; // start table

//foreach ($array as $name){

for($i=0; $i < count($c); $i++){

if( $cnt >= $rows ) {  // start a new TD when you get to the number of rows

 echo "<p><a href='?stateID=" . $c[$i][0] . "'>" . $c[$i][1] . "</a></p></td><td>" .PHP_EOL ;
  $cnt = 0;  // reset the counter, and start a new column

 }else{ // other wise just echo the country name

  echo "<p><a href='?stateID=" . $c[$i][0] . "'>" . $c[$i][1] . "</a></p>" . PHP_EOL;

 }
$cnt++;
}
echo '</td></tr></table>' . PHP_EOL;