Splitting up MySQL resultset?

Anon

Hey guys,

I am planning to build a site that will be powered by a MySQL database on the backend. I want to store a list of product categories in a MySQL table, and using a php page, output the contents of this category table.

When I do a "Select *" SQL statement, the entire resultset is pulled from the database. How can I split this resultset into "two parts" so that I can put one part into one table cell and the other part in another table cell?

This may be hard to explain, but for an example, check out:

http://www.onvia.ca/canada/products/index.cfm?task=ViewCategory&categoryID=30001

Onvia has all its product categories spread out into three columns. How do I do this with php?

Any help would be greatly appreciated!

Anon

I have this setup already.

My database is pretty much normalized; I'm just wondering how I can pull out the list of categories from the category table and split up the resultset into two parts, each part that can be put into a separate table cell. Right now I have one resultset that I can loop, but I can only put it into one cell using that method.

sepodati

I don't see where you're ending your table data anywhere </td>...

but, basically this has nothing to do with your select statement. You still select everything you need, then create your loop to display it correctly.

Something like Patrick said, but add an

echo "</td>";

before the

$NumberOfColumns--;

line...

Hope that helps...

---John Holmes...

Anon

Thank you very much; I'll give this code a try tomorrow. 🙂

[deleted]

"</TD>" is formality - the column should end with or without it. Most browsers compensate and end the column when they parse "<TD>".

Yeah, who needs a correct syntax? :-)

[deleted]

Indeed not! :-)

I was just pointing out that it is better to use the correct syntax than to rely on the browser fixing your mistakes.

Anon

Hey, thanks for the code! Unfortunately, it didn't work. But I spent about ten minutes debugging and shredding apart the code, and found a fix that works. Here's the correct code that I used, which was adapted from Patrick's:

$link = mysql_pconnect($DB_SERVER, $DB_LOGIN, $DB_PASSWORD);
$result = mysql_db_query($DB,$SQL);

$Total_Records = mysql_num_rows($result);
$NumberOfColumns = 3;
$Records_Per_Column = (int)($Total_Records/$NumberOfColumns);

while ($NumberOfColumns)
{
echo "<TD>";
for ($i = 0; $i < $Records_Per_Column; $i++)
{
$row = mysql_fetch_array($result);
echo $row["name"] . "<BR>";
}
echo "</td>";
$NumberOfColumns--;
}

Of course, substitute the values for your variables in the code above (SQL sever, password, query, etc etc.)

Thanks again, Patrick! =)

Anon

And for those that noticed the above code didnt shove the last entry in if the result was an odd number, below is a small fix that should sort it out and make the changes should the number be odd, or even..

Have fun!

<?
$link = mysql_connect("", "", "");
$DB = "";
$SQL = "SELECT * FROM table";
$result = mysql_db_query($DB,$SQL);
$Total_Records = mysql_num_rows($result);
if ($Total_Records % 2 == 1 ) {
$makechanges = 1;
} else {
$makechanges = 0;
}
$NumberOfColumns = 2;
$Records_Per_Column = (int)($Total_Records/$NumberOfColumns + $makechanges);
while ($NumberOfColumns){
echo "<TD valign='top'>";
for ($i = 0; $i < $Records_Per_Column; $i++){
$row = mysql_fetch_array($result);
if(!$row[rowname]){
}else{
echo "$row[maintopic]";
}
}
echo "</td>";
$NumberOfColumns--;
}
?>

Rob