Sorting mySQL results into 3 columns...

[7thdigital]

Hi everyone,

I've been trying to do this but I can't get my head around this...

I have a select statement that returns an x amount of information.

SELECT int_ID, str_Name FROM myTable;

I have to divide the results into three columns,
but it has to be sorted as follows:

1==7==13
2==8==14
3==9==15
4==10==16
5==11==17
6==12

The numbers represent the data (int_ID) in the order they need to be sorted.

Any one knows how to do this? I know it has to be done with a loop but I don't know how.

oh, and since this is a table, I can't have empty cells, they all must be filled in at least by a non breaking space.  

Thanks a lot.

PR

[TheDefender]

Here is an example using a predefined array. You'd have to tweak it to fit your SQL in there, but I think you should be able to get it from my example... Let me know, and post some of your code, if you can't figure it out from here, and we'll see what we can do to wrap your code around this.

$testarray = range(1,17); 
$countarray = count($testarray);
$itemspercolumn = ceil($countarray/3);

echo "<table border=\"1\">";
for($i = 0; $i < $itemspercolumn; $i++){
	$col1 = $i;
	$col2 = $i+$itemspercolumn;
	$col3 = $col2+$itemspercolumn;
	echo "<tr>";
	if(array_key_exists("$col1", $testarray)) { echo "<td>".$testarray[$col1]."</td>";} else { echo "<td>&nbsp;</td>";}
	if(array_key_exists("$col2", $testarray)) { echo "<td>".$testarray[$col2]."</td>";} else { echo "<td>&nbsp;</td>";}
	if(array_key_exists("$col3", $testarray)) { echo "<td>".$testarray[$col3]."</td>";} else { echo "<td>&nbsp;</td>";}
	echo "</tr>";
}
echo "</table>";

[7thdigital]

Thanks for you code "TheDefender",

It worked great with your array.

This is probably so easy but I still don't know how to make this work properly.

This is what I need:

//mySQL select statement:
$sql = @('SELECT str_PName, str_SiteURL FROM partner WHERE int_Show = 1 ORDER BY dat_DateEntered DESC');

// This should be an array right?
$row = mysql_fetch_array($sql);

The results need to be sorted like this:

<tr>
<td><a href="str_URL">str_PName</a>
</td>
<td><a href="str_URL">str_PName</a>
</td>
<td><a href="str_URL">str_PName</a>
</td>
</tr>

and so forth.

In this order (until it runs out of rows and empty cells need to be filled in with non-braking spaces.

str_PName1==str_PName7==str_PName13
str_PName2==str_PName8==str_PName14
str_PName3==str_PName9==str_PName15
str_PName4==str_PName10==str_PName16
str_PName5==str_PName11==str_PName17
str_PName6==str_PName12==nbsp

Thanks for your patience and very helpful assistance.

[TheDefender]

OK, when I get back from church this afternoon, I will wrap the code around your code to make it work for you.

[TheDefender]

There is probably a different way to do this, but this is how I would do it personally (unless someone else can show me a different/better way.) If not, than this is the one for you.

<?php
// Connect String
$connect = mysql_pconnect("yourhostname", "username", "password") or die(mysql_error());
mysql_select_db("databasename", $connect) or die(mysql_error());

// Run the MySQL statement (Change to meet our needs)
$sql = @mysql_query("SELECT str_PName, str_SiteURL FROM partner WHERE int_Show = 1 ORDER BY dat_DateEntered DESC"); 

// Count the number of results and the number of items per column
$countofurls = mysql_num_rows($sql);
$itemspercolumn = ceil($countofurls/3); 

// Transfer the results to an array
$array = array();
while($row = mysql_fetch_array($sql)){
    $array[] = array($row['str_PName'] => $row['str_SiteURL']);
}

// This print_r() function is just here so you can verify the array's contents.  You can remove it later once you have made sure it works how you like.
print_r($array);

// Output the results into our table
echo "<table border=\"1\">"; 
for($i = 0; $i < $itemspercolumn; $i++){ 
    $col1 = $i; 
    $col2 = $i+$itemspercolumn; 
    $col3 = $col2+$itemspercolumn; 
    echo "<tr>"; 
    if(array_key_exists("$col1", $array)) { foreach($array[$col1] AS $k => $v){ echo "<td><a href=\"".$v."\">".$k."</a></td>";}} else { echo "<td>&nbsp;</td>";} 
    if(array_key_exists("$col2", $array)) { foreach($array[$col2] AS $k => $v){ echo "<td><a href=\"".$v."\">".$k."</a></td>";}} else { echo "<td>&nbsp;</td>";} 
    if(array_key_exists("$col3", $array)) { foreach($array[$col3] AS $k => $v){ echo "<td><a href=\"".$v."\">".$k."</a></td>";}} else { echo "<td>&nbsp;</td>";} 
    echo "</tr>"; 
} 
echo "</table>";
?>

[LordShryku]

This thread has another idea for this as well.

[TheDefender]

Hey Lord, NOW you pop in here... LOL. Anyhow, the post you gave looks like it generates things by row like so:

Item1 Item2 Item3
Item4 Item5 Item6
Item7 Item8 Item9

When what 7thDigital needed was one that dumped them out by column like so:

Item1 Item4 Item7
Item2 Item5 Item8
Item3 Item6 Item9

I didn't look at that code too hard, but that's what it looked like. I will look into the code in your post a bit harder later. I am going out back to the pool for a couple of hours with the kids, and grilling some Bratwurst later... Last "Summer Fling" before the kids go back to school tomorrow... MuHaHa!!!!!!

[LordShryku]

Very true, very true, but I don't think it would take much (maybe just some CSS tweaks) to do that. Your code looks like it does the trick, just wanted to get another idea in the mix.

[TheDefender]

Yeah, if you come up with something easier than I gave, I would be more than happy to take a look into it... It seems silly to dump the fetched array into an array like I did, just to output, but that's what I came up with, so that's what I am using... LOL 🙂

[7thdigital]

Thank You so much "TheDefender",

It workes like a charm!

Thank God for people like you.

And thanks to "LordShryku" as well.

The code is for this page: for RTV (Rock The Vote).

http://www.rockthevote.com/rtv_partners_test.php

So you can see it in action.

[TheDefender]

I was a bit concerned about it dumping the results into an array first, because I thought it might slow things down, but it loaded up quick, fast and in a hurry. Looks good.

Also, nice to see the script in action on a vital website... Looks good, and good job!