Output two column <li> list from MySQL

[rmelino]

Hello,

 

I'm hoping someone can help.  I'm trying to output a two column list of cities from my database.  Right now I can only figure out how to output it as one column.  Here is my code so far:

 

		<td style="height: 500px">
                        <div style="width: 50%; height: 100%; overflow: auto;">
                        <ul>

<?php


$connection=mysql_connect ($dbname, $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}


$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}

// Select from cities
$query = "SELECT * FROM cities WHERE state='$state' ORDER BY city LIMIT 0 , 5000 ";

$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}


$arr = array("_" => " ", "-" => "'");

while ($row = @mysql_fetch_assoc($result)){
echo '<li><a href="page.html">' . strtr($row['city'],$arr) . '</a></li>',"\n";
}

?>
</ul>
</div>
</td>

[ignace]

Right now I can only figure out how to output it as one column

 

You are using the wrong html element. If you want to work with columns you need a table.

[aggrav8d]

echo "<div class='float:left;width:50%'><ul>";

use $x=mysql_num_results() to find out how many cities you got back.

loop $x/2 times to put the first list.

then echo "</ul></div><div class='float:left;width:50%'><ul>" and repeat the loop for the second half.

then echo "</ul></div>";

 

[ignace]

echo "<div class='float:left;width:50%'><ul>";

use $x=mysql_num_results() to find out how many cities you got back.

loop $x/2 times to put the first list.

then echo "</ul></div><div class='float:left;width:50%'><ul>" and repeat the loop for the second half.

then echo "</ul></div>";

 

Use a table if it's tabular data.

[rmelino]

I'm not sure i follow what you are saying regarding tabular data.  I have it within a table but it is a scrolling div within a table row.  The output looks like this when it's in a single column table:

 

<table style="width: 630px">
<tbody>
<tr>
<td style="height: 500px">
<div style="width: 50%; height: 100%; overflow: auto;">
<ul>
<li><a href="/VT/Adamant.html">Adamant, VT</a></li>
<li><a href="/VT/Albany.html">Albany, VT</a></li>
<li><a href="/VT/Alburg.html">Alburg, VT</a></li>
<li><a href="/VT/Arlington.html">Arlington, VT</a></li>
<li><a href="/VT/Ascutney.html">Ascutney, VT</a></li>
</ul>
</div>
</td>
</tr>
</tbody>
</table>

 

 

[ignace]

<table style="width: 630px">
<tbody>
<tr>
<td style="height: 500px">
<div style="width: 50%; height: 100%; overflow: auto;">
<ul>
<li><a href="/VT/Adamant.html">Adamant, VT</a></li>
<li><a href="/VT/Albany.html">Albany, VT</a></li>
<li><a href="/VT/Alburg.html">Alburg, VT</a></li>
<li><a href="/VT/Arlington.html">Arlington, VT</a></li>
<li><a href="/VT/Ascutney.html">Ascutney, VT</a></li>
</ul>
</div>
</td>
</tr>
<!-- second column -->
<tr>
<td style="height: 500px">
<div style="width: 50%; height: 100%; overflow: auto;">
<ul>
<li><a href="/VT/Adamant.html">Adamant, VT</a></li>
<li><a href="/VT/Albany.html">Albany, VT</a></li>
<li><a href="/VT/Alburg.html">Alburg, VT</a></li>
<li><a href="/VT/Arlington.html">Arlington, VT</a></li>
<li><a href="/VT/Ascutney.html">Ascutney, VT</a></li>
</ul>
</div>
</td>
</tr>
</tbody>
</table>

[rmelino]

Ok i follow what you are saying now but i'm still uncertain what the php code would be to output the data in two columns as you have it

[ignace]

Sorry I made a mistake. My suggested solution was a second row instead of a second column. This code now creates a second column.

 

<table style="width: 630px">
<tbody>
<tr>
<td style="height: 500px">
<div style="width: 50%; height: 100%; overflow: auto;">
<ul>
<?php
foreach ($datacol1 as $value) {
    print "<li>$value</li>";
}
?>
</ul>
</div>
</td>
<td>
<div style="width: 50%; height: 100%; overflow: auto;">
<ul>
<?php
foreach ($datacol2 as $value) {
    print "<li>$value</li>";
}
?>
</ul>
</div>
</td>
</tr>
</table>

[rmelino]

I keep getting errors when i try to incorporate the php code you gave my in my original code.  How would i insert your 'foreach ($datacol...' section into my current code?  My current code again is:

 

<?php


$connection=mysql_connect ($dbname, $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}


$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}

// Select from cities
$query = "SELECT * FROM cities WHERE state='$state' ORDER BY city LIMIT 0 , 5000 ";

$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}


$arr = array("_" => " ", "-" => "'");

while ($row = @mysql_fetch_assoc($result)){
echo '<li><a href="page.html">' . strtr($row['city'],$arr) . '</a></li>',"\n";
}

?>

[ignace]

I keep getting errors when i try to incorporate the php code you gave my in my original code.  How would i insert your 'foreach ($datacol...' section into my current code?

 

Depends on what you want in the first column and what you want in the second column. You may need to create a separate query for this to work.

[rmelino]

I would like to list 1000 city names on a single page.  I want to put those city names into two columns instead of one so that it is more visually appealing and user friendly.  If it's in two columns the user doesn't have to scroll down so far to find the city they are looking for.

 

The cities are in alphabetical order as shown by the query line:

 

$query = "SELECT * FROM cities WHERE state='$state' ORDER BY city LIMIT 0 , 5000 ";

 

If i did two queries (one for each column) they would display the same list of cities in each.  I don't know how to list all 1000 city names split within two columns alphabetically instead of one...

 

Thanks in advance for your help on this!

[Daniel0]

This is more of a CSS issue really.

 

Output them all in a single list, then add a style sort of like this:

#theList li {
float: left;
width: 50%;
}

 

If the container has a fixed width you can also just take half of that for the li width. Do note that things like border, padding and margin is part of the total width according to the box model, so you will have adjust for that.

 

When outputting HTML you should never be thinking presentation, but semantics. Presentation is something CSS is responsible for. HTML was never meant to convey any presentational information.

[ignace]

<?php
$i = 0;
$cities_per_column = 500;
print '<table><tr>';
while ($row = @mysql_fetch_assoc($result)){
    print '<td><a href="page.html">', strtr($row['city'],$arr) ,'</a></td>';
    ++$i; if (!($i % $cities_per_column)) {
        print '</tr><tr>';
    }
}
print '</tr></table>';
?>

 

Creates columns of each 500 cities.

[Daniel0]

Tables are not the tool for this task. Now what if you want it in 3 columns instead? Then you'll have to go back to edit your PHP source files. Again, the frontend presentational layer is CSS.

 

Separation of concerns is a very important aspect in computer science (thus also programming and web development). It has several benefits in web development. I won't bother going over it in this topic though.

[rmelino]

DanielO,

 

Thanks for the reply.  I tried your suggestion and I'm not having any luck.

 

I added the following on my style sheet:

#theList li {
   float: right;
   width: 50%;
}

 

 

My php code looks like this:

 

		<table style="width: 630px">
		<tbody>
		<tr>
			<td style="height: 500px">
                        <div id="theList">
                        <ul>

<?php

$connection=mysql_connect ($dbname, $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}


$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}

// Select from cities
$query = "SELECT * FROM cities WHERE state='$state' ORDER BY city LIMIT 0 , 5000 ";

$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}

$arr = array("_" => " ", "-" => "'");

while ($row = @mysql_fetch_assoc($result)){
echo '<li><a href="page.html">' . strtr($row['city'],$arr) . '</a></li>',"\n";
}
?>
</ul>
</div>
</td>
</tr>
</tbody>
</table>

 

 

Unfortunately when it runs i still only get the city list in one column.  How do i float half of the <li> results right while leaving the other half floated left?  I don't think I'm fully understanding how to make this happen via css.  Don't I still need to have different styles applied to different <li> values in order for it to appear in two columns?  Wouldn't the html output need to be something like this to achieve that:

 

<ul>
<li class="left"><a href="page.html">City in Left Column</a></li>
<li class="right"><a href="page.html">City in Right Column</a></li>
</ul>

 

Right now as it stands, the <li> tags will all have the same style which floats them to the left...

[Daniel0]

As I said (or rather implied), it's not just plug and play. That's not how programming works.

 

<style type="text/css">
ul {
width: 200px;
}

li {
width: 50%;
float: left;
}
</style>

<ul>
<?php
for ($i = 0; $i < 100; ++$i) echo '<li>Test ' . $i . '</li>';
?>
</ul>

 

See screenshot for output.

 

[attachment deleted by admin]

[Daniel0]

Okay, so here is something you might want to take a look at:

http://www.alistapart.com/articles/multicolumnlists

[rmelino]

Daniel0

 

Thanks for the info.  I realized it's an inheritance issue in my css file.  When pull from the database and style it like you have without using my style sheet it works fine.  However when I use my style sheet with this added:

 

#citylist {
   width: 400px;
}
#citylist ul {
   width: 400px;
}
#citylist li {
   float: left;
   width: 50%;
}

 

It doesn't put it into two columns because it is inheriting from table and td from my style sheet.  I can't figure out why it won't simply use the styling from the citylist div that i pasted above.  I'll keep plugging away and try to figure out why.  Thanks again for your help everyone on this...