[SOLVED] what am i doing wrong?

[mikefrederick]

Two tables are important here, regionz and countriez. In countriez, rid=the id of the region that the country is in. I have an html table that echoes the region names (rname) and the countries listed in each region. I am trying to split the list of countries into 3 <td>'s. To do this, I have divided the number of rows in countriez where the rid=the current region's id. I round this number upward using ceil. In the first td, I echo where $x<=numrows/3, second td I echo where $x>numrows/3 && $x<=numrows/3*2 and in the third I echo where $x>numrows/3*2.....or at least that is what I am trying to do. You can see the page at http://vacation.truegraphic.com.  As you can see, things are not being split properly. I also need help putting things in alphabetical order. Because I have to select from a table where rid=regionid, I can't use order by cname and so things are not in order. Any suggestions for that as well? Here is the code, with a few comments in red (by the way, I realize that the script contains a few unnecessarily repeated variables):

 

 

<?php

  $qr="select * from regionz order by rname";

  $rs=mysql_query($qr);

  while($rec=mysql_fetch_array($rs))

  {

  $regid=$rec['id']; =THE ID OF THE REGION

  ?>

THIS ROW ECHOES THE NAME OF THE REGION<tr><td align="left" class="regionsone" width="100%" colspan="3"><strong>

<a class="regions" href="properties_for_rent.php?rid=<?=$rec["id"]?>&rname=<?=$rec["rname"]?>"><u><?php echo $rec["rname"];?></u></a>

              </strong></td></tr><tr align="center" valign="top" bgcolor="#000066">

                <?php

 

  THESE ARE IMPORTANT, THEY ARE WHERE I AM GETTING THE TOTAL NUMBER OF ROWS FROM

$qraa="select * from countriez where rid='$regid'";

  $rsaa=mysql_query($qraa);

  $qrbb="select * from countriez where rid='$regid'";

  $rsbb=mysql_query($qrbb);

  $qrcc="select * from countriez where rid='$regid'";

  $rscc=mysql_query($qrcc);

 

 

  $qr2="select * from countriez where rid='$regid'";

  $rs2=mysql_query($qr2);

  $tester=mysql_num_rows($rs2);

 

  if($tester==0)

  { ?>

IF THERE ARE NO COUNTRIES IN THE REGION:   <td  width="100%" height="23" align="center" class="regionsone">

                <div align="left">No Vacation Rentals Found

                </div>

                <hr width="100%" /> </td>

  <? }

  else

  {

 

  ?>

 

 

 

THIS TD SHOULD ECHO THE FIRST THIRD OF THE TOTAL NUMBER OF COUNTRIES IN THE REGION<td align="left" width="33%" class="regionsone">

 

<?php

 

  $tot=mysql_num_rows($rsaa);

  $baserec=ceil($tot/3);

  $newbase=baserec*2;

  $x=0;

 

;

 

  while( $rec1=mysql_fetch_array($rsaa))

{

  $rid=$rec1['rid'];

?>  <?

if($rid==$regid && $x<=$baserec) {

?>

<a class="regionsone" href="properties_for_rent.php?cid=<?php echo $rec1["id"];?>&cname=<?=$rec1["cname"]?>">

<?php

echo $rec1["cname"];

?>

</a><br />

 

  <?php

} ?>

<?

++$x;

}

?> </td>

                        THIS TD SHOULD ECHO THE SECOND THIRD OF THE TOTAL NUMBER OF COUNTRIES IN THE REGION<td align="left" width="33%" class="regionsone">

 

<?php

 

 

$tot1=mysql_num_rows($rsbb);

  $basereca=ceil($tot1/3);

  $newbasea=$basereca*2;

  $y=0;

 

;

 

 

 

  while( $rec2=mysql_fetch_array($rsbb))

{

  $aid=$rec2['rid'];

?>  <?

if($aid==$regid && $y>$basereca && $y<=$newbasea) {

?>

<a class="regionsone" href="properties_for_rent.php?cid=<?php echo $rec2["id"];?>&cname=<?=$rec2["cname"]?>">

<?php

echo $rec2["cname"];

?>

</a><br />

 

  <?php

} ?>

<?

++$y;

}

?> </td>

                        THIS TD SHOULD ECHO THE THIRD THIRD OF THE TOTAL NUMBER OF COUNTRIES IN THE REGION<td align="left" width="33%" class="regionsone">

 

<?php

 

 

$tot2=mysql_num_rows($rscc);

$baserecb=ceil($tot2/3);

  $newbaseb=$baserecb*2;

  $z=0;

 

;

 

 

 

  while( $rec3=mysql_fetch_array($rscc))

{

  $gid=$rec3['rid'];

?>  <?

if($gid==$regid && $z>$newbaseb) {

?>

<a class="regionsone" href="properties_for_rent.php?cid=<?php echo $rec3["id"];?>&cname=<?=$rec3["cname"]?>">

<?php

echo $rec3["cname"];

?>

</a><br />

 

  <?php

} ?>

<?

++$z;

}

?> </td>

 

                   

              <? } ?>

              </tr>

              <?php

  }

  ?>

 

[mikefrederick]

Sorry guys, I figured it out right after I posted it....But I still need help putting things in order by Country Name, any suggestions?

[GingerRobot]

Your code looks pretty confusing. You seem to be making 4 identical queries near the top of your script. I can only assume that, at present, you have a query for each column (not sure what the 4th one is for), which would explain why you cant sort the data?

 

Perhaps reading this FAQ here will show you a better method for putting your results into multiple columns, using just one query.

 

Also, please remember to place your code inside

tags when you post.

[nikefido]

"select * from countriez where rid='$regid' ORDER BY countriez ASC";

 

 

Why do you have the same SQL statement created multiple times?

[mikefrederick]

Yeah I know that I didn't need to use all of those, that was actually related to something else and I was trying to keep things separate for each column. But I can't use order by because I am selecting the countries using where so that I can echo the appropriate countries for each region.

[mikefrederick]

Yeah, problem solved, thanks...I am an idiot, I was using order by and then where instead of where and then order by.