[RESOLVED] Multiple Column Output

overshard

I am trying to make a script that will display information from a MySQL table in multiple columns and not all in one one long column as it currently does.

Ex.

This starts from the left column goes down then starts on the next column and goes down spliting the whole thing as much in half as possible. My current script only displays in one long column using the while command.

<?php

/****************************************************
 * Asterisk Database Listings PHP Script
 * By:          Isaac Bythewood
 *
 * Discription: Automatically retrives the extension
 *              listings from the MySQL database and
 *              provies a sortable display for them.
 **********/

#
# Connect to the Asterisk database in which to get the information.
#
$dbhost = "localhost"; //server that contains the database more then likely localhost
$dbuser = "user"; //user with access to that database
$dbpasswd = "password"; //password for that user

mysql_connect("$dbhost","$dbuser","$dbpasswd");

mysql_select_db("asterisk");

$default = mysql_query('SELECT * FROM `users` WHERE extension like \'___\' ORDER BY `name` ASC');
$extasc = mysql_query('SELECT * FROM `users` WHERE extension like \'___\' ORDER BY `extension` ASC');
$extdesc = mysql_query('SELECT * FROM `users` WHERE extension like \'___\' ORDER BY `extension` DESC');
$nameasc = mysql_query('SELECT * FROM `users` WHERE extension like \'___\' ORDER BY `name` ASC');
$namedesc = mysql_query('SELECT * FROM `users` WHERE extension like \'___\' ORDER BY `name` DESC');

echo "<div align=\"center\"><strong><font size=20>Names and Extensions</font></strong>";
echo "<table padding=1>";
echo "<tr bgcolor=9bb0c9><td><a href=\"listings.php?order=1\">Extensions Ascending</a></td><td><a href=\"listings.php?order=2\">Extensions Descending</a></td><td><a href=\"listings.php?order=3\">Names Ascending</a></td><td><a href=\"listings.php?order=4\">Names Descending</a></td></tr>";
echo "<tr bgcolor=9bb0c9><td align=center><em>Extension</em></td><td align=center colspan=2><em>Name</em></td><td align=center><em>Department</em></td></tr>";
switch ($order) {
    case 0:
        //default display
        while($r=mysql_fetch_array($default))
        {
           $extension=$r["extension"];
           $name=$r["name"];
           $department=$r["department"];
           echo "<tr bgcolor=748eae><td align=center>$extension</td><td align=center colspan=2>$name</td><td align=center>$department</td></tr>";
        }
    break;
    case 1:
        //extensions in asc order display
        while($r=mysql_fetch_array($extasc))
        {
           $extension=$r["extension"];
           $name=$r["name"];
           $department=$r["department"];
           echo "<tr bgcolor=748eae><td align=center>$extension</td><td align=center colspan=2>$name</td><td align=center>$department</td></tr>";
        }
    break;
    case 2:
        //extensions in desc order display
        while($r=mysql_fetch_array($extdesc))
        {
           $extension=$r["extension"];
           $name=$r["name"];
           $department=$r["department"];
           echo "<tr bgcolor=748eae><td align=center>$extension</td><td align=center colspan=2>$name</td><td align=center>$department</td></tr>";
        }
    break;
    case 3:
        //names in asc order display
        while($r=mysql_fetch_array($nameasc))
        {
           $extension=$r["extension"];
           $name=$r["name"];
           $department=$r["department"];
           echo "<tr bgcolor=748eae><td align=center>$extension</td><td align=center colspan=2>$name</td><td align=center>$department</td></tr>";
        }
    break;
    case 4:
        //names in desc order display
        while($r=mysql_fetch_array($namedesc))
        {
           $extension=$r["extension"];
           $name=$r["name"];
           $department=$r["department"];
           echo "<tr bgcolor=748eae><td align=center>$extension</td><td align=center colspan=2>$name</td><td align=center>$department</td></tr>";
        }
    break;
}

echo "</table>";
echo "</div>";

?>

Thus giving me the table I want all in one long column...

Phone Extension | Name

100 | Some Name
101 | Some name
102 | Some namE

bogu

And the problem is?

U know, u execut 5 query's when u could execute only one ...

overshard

I'm a mysql/php noob so if my script could be fixed please do tell me how too...

And like I stated the problem is that i need 2 columns instead of just 1 big column.

bogu

overshard wrote:
And like I stated the problem is that i need 2 columns instead of just 1 big column.

In your first example u have 4 columns not 1 column, or what do u meen by 1 column?

cahva

And your code needs some serious clean up. Too much repetition and waste of resources.
Its too much to explain but see this code:

<?php

/****************************************************
* Asterisk Database Listings PHP Script
* By:          Isaac Bythewood
*
* Discription: Automatically retrives the extension
*              listings from the MySQL database and
*              provies a sortable display for them.
**********/

#
# Connect to the Asterisk database in which to get the information.
#
$dbhost = "localhost"; //server that contains the database more then likely localhost
$dbuser = "user"; //user with access to that database
$dbpasswd = "password"; //password for that user

mysql_connect($dbhost,$dbuser,$dbpasswd);
mysql_select_db("asterisk");

echo "<div align=\"center\"><strong><font size=20>Names and Extensions</font></strong>";
echo "<table padding=1>";
echo "<tr bgcolor=9bb0c9><td><a href=\"listings.php?order=1\">Extensions Ascending</a></td><td><a href=\"listings.php?order=2\">Extensions Descending</a></td><td><a href=\"listings.php?order=3\">Names Ascending</a></td><td><a href=\"listings.php?order=4\">Names Descending</a></td></tr>";
echo "<tr bgcolor=9bb0c9><td align=center><em>Extension</em></td><td align=center colspan=2><em>Name</em></td><td align=center><em>Department</em></td></tr>";

$order = $_GET['order'];
switch ($order) {
	1:
	$orderquery = '`extension` ASC';
	break;

2:
$orderquery = '`extension` DESC';
break;


3:
$orderquery = '`name` DESC';
break;

4:
default:
$orderquery = '`name` ASC';
break;
}

$result = mysql_query("SELECT * FROM `users` WHERE extension like '___' ORDER BY ".$orderquery);
while($r=mysql_fetch_array($default)) {
	$extension=$r["extension"];
	$name=$r["name"];
	$department=$r["department"];
	echo "<tr bgcolor=748eae><td align=center>$extension</td><td align=center colspan=2>$name</td><td align=center>$department</td></tr>";
}

echo "</table>";
echo "</div>";

?>

overshard

Thanks for the code cahva, but what I need is a 2 column output as in...

Right now it is
Extension | Name
23 | Isaac
85 | Scott

bogu

$i = 0;
while($r=mysql_fetch_array($default)) { 
    if ( ($i%2) == 0 ) echo "<tr bgcolor=748eae>";
    echo "<td align=\"center\">{$r["extension"]}</td>";
    echo "<td align=\"center\">{$r["name"]}</td>"; 
    if ( ($i%2) == 1 ) echo "</tr>";
    $i++;
}

is not tested, but u should get the idea ...

overshard

Ok thats ALMOST there... it gets 2 columns exactly like I wanted but It goes from left to right then down the nleft to right i want to go down the up to the top again and down again.

Thanks for the help.

liquorvicar

Try something like this:

$num_rows = mysql_num_rows($default);
$halfway = ceil($num_rows/2);
 $i = 0;
echo "<tr bgcolor=748eae>";
echo "<td align=\"center\">";
while($r=mysql_fetch_array($default)) {
    echo $r["extension"]}.' : '.{$r["name"]}.'<br />';
    $i++;
    if ($i>=$halfway) {
       echo "</td>";
       echo "<td align=\"center\">";
    }
} 
echo "</td></tr>";

overshard

liquor, that didn'twork at all... even with some tweaking to get it to stop giving me errors it just gave me a very messed up page and i'm not sure what you are trying to do to really fix it being the php noob that I am.

liquorvicar

My bad. Try this:

$num_rows = mysql_num_rows($default);
$halfway = ceil($num_rows/2);
 $i = 0;
echo "<tr bgcolor=748eae>";
echo "<td align=\"center\">";
while($r=mysql_fetch_array($default)) {
    echo $r["extension"]}.' : '.{$r["name"]}.'<br />';
    $i++;
    if ($i==$halfway) {
       echo "</td>";
       echo "<td align=\"center\">";
    }
} 
echo "</td></tr>";

Note the change from '>=' to '==' on the if clause.

overshard

Ok cool, that did what I wanted now just to get the formatting right where I have the extensions and names separated in a table and not by just a : so that it looks cleaner. All my puny attempts at adding something into your script liquor have failed.

liquorvicar

I'm not going to write the code for you as that's mainly an HTML issue but a few pointers:
- in my code, there are two columns, each with a list of entries on a new line,
- to achieve what you want you probably want to put a new table inside each cell (column) in the original table,
[technically, the outer table should probably be styled css]
- the if clause ends the first column and starts the second.
Pseudo-code:

start outer table
start inner table
start loop
   print row in inner table
   if we're at halfway
       end inner table
       start inner table
end loop
end inner table
end outer table

overshard

Ok i got something similar to that that works thanks for the help to everyone who replied.