Jump to content


Photo

Order query results by columns


  • Please log in to reply
1 reply to this topic

#1 paingul

paingul
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 06 January 2003 - 03:52 PM

Hello,

I\'m trying to order a mysql query by column, not by rows.
Let\'s say I have 6 results, so I want to output on screen smth. like:

1 | 4
2 | 5
3 | 6

and NOT like:

1 | 2
3 | 4
5 | 6



The code I currently use is [results are ordered in 2 columns]:





<TABLE width="85%" ALIGN="CENTER">

    <tr><td class="barre"> <font class="lien"><a href="../">Linkuri.ro</a> <? echo $a[0]; ?></font> </td></tr>

    <tr><td  class="barreclair">< </td></tr>

    <tr><td><?	





	if ($ad==1) $query="SELECT * FROM $tablecatannu WHERE attach=\'$num\' ORDER BY $tri_cat ASC";

  else $query="SELECT * FROM $tablecatannu WHERE attach=\'$num\' AND actif=\'1\' ORDER BY $tri_cat ASC";

	$result=mysql_query($query); if (!$result) { echo "<BR><FONT class=\'texte\'>Error!</FONT><BR>"; exit(); }

	$n=mysql_numrows($result);

	echo "<TABLE align=\'center\' width=\'85%\'>";

	for ($i=0; $i<$n-1; $i=$i+2)

  { //left column

  $nom=mysql_result($result,$i,"nom");

  $num2=mysql_result($result,$i,"id");

  $description=mysql_result($result,$i,"description");

  $im=mysql_result($result,$i,"im");

  $actif=mysql_result($result,$i,"actif");	

  $nb_tot=mysql_result($result,$i,"nb_tot");

  $sous_cat=mysql_result($result,$i,"sous_cat");

  echo "<TR><TD width=\'40%\' align=\'left\'>";

  affcat($num2,$nom,$actif,$ad,$nb_tot,$im);

  if ($sous_cat!="") echo "<FONT class=\'lienp\'>$sous_cat</FONT>";

  echo "</TD>";

  //right column

  $nom=mysql_result($result,$i+1,"nom");

  $num2=mysql_result($result,$i+1,"id");

  $description=mysql_result($result,$i+1,"description");

  $im=mysql_result($result,$i+1,"im");

  $actif=mysql_result($result,$i+1,"actif");

  $nb_tot=mysql_result($result,$i+1,"nb_tot");

  $sous_cat=mysql_result($result,$i+1,"sous_cat");

  echo "<TD width=\'40%\' align=\'left\'>";

  affcat($num2,$nom,$actif,$ad,$nb_tot,$im);

  if ($sous_cat!="") echo "<FONT class=\'lienp\'>$sous_cat</FONT>";

  echo "</TD></TR>";

  }

	if ($n%2==1) 

  {

  $nom=mysql_result($result,$n-1,"nom");

  $num2=mysql_result($result,$n-1,"id");

  $description=mysql_result($result,$n-1,"description");

  $im=mysql_result($result,$n-1,"im");

  $actif=mysql_result($result,$n-1,"actif");

  $sous_cat=mysql_result($result,$n-1,"sous_cat");

  $nb_tot=mysql_result($result,$n-1,"nb_tot");

  $size=100-strlen($nom);

  echo "<TR><TD width=\'$size%\' colspan=\'2\'>";

  affcat($num2,$nom,$actif,$ad,$nb_tot,$im);

  if ($sous_cat!="") echo "<FONT class=\'lienp\'>$sous_cat</FONT>";

  echo "</TD></TR>";

  }

  

	echo "</TABLE>";






Any ideas?

Thanks

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 06 January 2003 - 04:33 PM

You code currently gives you

i | i+1
i+2 | i+3
...
...
n-1 | n

or something like that... Right ?

You want:

i | k + 1
i+1 | k +2
i+2 | k + 3
k

So if you have a total of 53 rows, k should be 27, resulting in 27 rows in the table, the last row consisting of 1 output only.

Calculate $n as you do. Make $k = ceiling($n/2)
Now you may use

Pseudocode:
[php:1:f262e15009]
$n = number of rows in query result;
$k = number of rows/2 rounded up //ceiling will be the last result in left coloumn.
$i = 0; // counter for output row
While $i < $k; $i++;{
//Left column:
$nom=mysql_result($result,$i,\"nom\");
//Right coloumn
$nom=mysql_result($result,($i+$k),\"nom\");
}
[/php:1:f262e15009]
This should work. You\'ll have to add some checking whether the number of rows is even or not (in the sql result) and adjust the output accordingly.

I hope this helps,
P., denmark
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users