foreach() while() for() or new mysql query?

nemesis · 06-10-2003, 05:30 AM

I have two tables in a mysql databse 'topics' and 'horizons' for a newsletter . Topics contains 'issue' 'list' 'topic' 'link' and 'category' columns. Horizons has 'issue' 'month' 'pdf_name' 'file_size' and 'year' columns. What I'm trying to do is to have the Issue number and date with a link to both html and pdf versions, but also have the topics from each newsletter alongside. At the moment I have everything working can't find a way of looping through the topics and placing them alongside the associated issue without looping multiple versions of the issue, or just repeating the same topic. Do I need to use another nested array for the topic, or create another mysql query, or can I use something like while(), foreach() or for() to do this with the query I have? I would

PHP Code:


			
<?php

            

$db = mysql_pconnect("localhost", "username","password");

  if (!$db)

  {

     echo "Error: Could not connect to database.  Please try again later.";

     exit;

  }



// get results from horizon and topic tables 

 

    mysql_select_db("aamsurveys_com_au");



    $query = "select * from  topics, horizons

                where topics.issue = horizons.issue

                order by topics.issue desc";

    $result = mysql_query($query) or die(mysql_error());

    $num_results = mysql_num_rows($result);

    $row = mysql_fetch_array($result);





        $issue=$row[0];

        $topic=$row[2];

        $link=$row[3];

        $category=$row[4];

        $month=$row[6];

        $pdfname=$row[7];

        $filesize=$row[8];

        $year=$row[9];

        

        

// add topics and links etc



echo "

<tr align='left' valign='left' > 

 <td height='26' colspan='5' >&nbsp;</td>

</tr>

            

<tr align='left' valign='left' >

<td width='200'align='left' >

<font face='arial' size='2' color='#000099'>

Issue ".$issue."&nbsp;&nbsp;&nbsp;$month&nbsp;$filesize</font>

<p><a href='issue".$issue."/index.html'>

<font face='arial' size='1' color='#660000'>View<b>HTML </b></font></a>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

</p>

<p>

<a href='issue".$issue."/".$link."'>

<font face='arial' size='1' color='#660000'>

View <b> PDF ".$filesize."k </b></font></a>



&nbsp;&nbsp;

</p>

</font>

</td>

               

<tr>

<td colspan='2' valign='top'>

<div align='middle'>

<a href='".$link."'>

<font face='arial' size='2' color='#660000'><b>".$topic." </b></font></a>

</div></td>

<td >&nbsp;</td>

</tr>               

       <tr> 

          <td height='22'>&nbsp;</td>

          <td >&nbsp;</td>

          <td >&nbsp;</td>

          <td >&nbsp;</td>

       </tr>

 ";             

// build headings and tables    

echo"

<tr>

<td colspan='5'>

 <div align='right'>

 <a href='#top'>

<img src='images/totop.gif' width='100' height='23' border='0' class='totop'></a>

 </div>

</td>

</tr>

<tr  bgcolor='#aea981'>

<td colspan='5'>

<div align='left'>

<a href='horizonstopic.htm'>

<font face='arial' size='2' color='#000099'>

View by Topic</font></a>

</div>

</td>

</tr>

</table>

";

?>

--
edited in hopes to make it a little more readable - too much horizontal scrolling...

-bdl

sde · 06-10-2003, 07:47 AM

Code:

while($row=mysql_fetch_row($result))
{ do something }

i think that is what you are looking for .. check this out:

PHP Code:


			
<?php

            

$db = mysql_pconnect("localhost", "username","password");

  if (!$db)

  {

     echo "Error: Could not connect to database.  Please try again later.";

     exit;

  }



// get results from horizon and topic tables 

 

    mysql_select_db("aamsurveys_com_au");



    $query = "select * from  topics, horizons

                where topics.issue = horizons.issue

                order by topics.issue desc";

    $result = mysql_query($query) or die(mysql_error());

    $num_results = mysql_num_rows($result);

    

    //*************************************

    //

    // here's the loop you're looking for

    //

    //*************************************

     while($row = mysql_fetch_row($result))

     {

        $issue=$row[0];

        $topic=$row[2];

        $link=$row[3];

        $category=$row[4];

        $month=$row[6];

        $pdfname=$row[7];

        $filesize=$row[8];

        $year=$row[9];

        

        

// add topics and links etc



echo "

<tr align='left' valign='left' > 

 <td height='26' colspan='5' >&nbsp;</td>

</tr>

            

<tr align='left' valign='left' >

<td width='200'align='left' >

<font face='arial' size='2' color='#000099'>

Issue ".$issue."&nbsp;&nbsp;&nbsp;$month&nbsp;$filesize</font>

<p><a href='issue".$issue."/index.html'>

<font face='arial' size='1' color='#660000'>View<b>HTML </b></font></a>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

</p>

<p>

<a href='issue".$issue."/".$link."'>

<font face='arial' size='1' color='#660000'>

View <b> PDF ".$filesize."k </b></font></a>



&nbsp;&nbsp;

</p>

</font>

</td>

               

<tr>

<td colspan='2' valign='top'>

<div align='middle'>

<a href='".$link."'>

<font face='arial' size='2' color='#660000'><b>".$topic." </b></font></a>

</div></td>

<td >&nbsp;</td>

</tr>               

       <tr> 

          <td height='22'>&nbsp;</td>

          <td >&nbsp;</td>

          <td >&nbsp;</td>

          <td >&nbsp;</td>

       </tr>

 ";



    //*******************************************

    //

    // i think this is where you want to end loop .. not sure

    //

    //*******************************************

    }



     

// build headings and tables    

echo"

<tr>

<td colspan='5'>

 <div align='right'>

 <a href='#top'>

<img src='images/totop.gif' width='100' height='23' border='0' class='totop'></a>

 </div>

</td>

</tr>

<tr  bgcolor='#aea981'>

<td colspan='5'>

<div align='left'>

<a href='horizonstopic.htm'>

<font face='arial' size='2' color='#000099'>

View by Topic</font></a>

</div>

</td>

</tr>

</table>

";

?>

--
edited in hopes to make it a little more readable - too much horizontal scrolling...

-bdl [/b][/quote]

nemesis · 06-10-2003, 03:18 PM

I think It's close, but I instead of an issue with links and a list of topics from that issue, I have say 'issue 20" with a topic, then this is repeated with the next topic in the array as many times as there are topics... somehow I need to loop the issues and display them just once with all their associated topics. It seems simple but there must be something basic I'm missing.... The first issue in the array is also missed out. I'm in the process of testing different places to try the loop. I also tried something like the code below , but that just created a list of all the topics below.....

PHP Code:


			
for($i=0; $i<$num_results; $i++) {

$topic

}

sde · 06-10-2003, 03:44 PM

ahh .. you have multiple topics per issue? i think i understand now. here's a quick snippet that nests a loop inside another loop.

PHP Code:


			
<?

// query "a"

$resulta = mysql_query("select * from  topics, horizons");



// row count "a"

$num_resultsa = mysql_num_rows($resulta);



// loop "a"

for($a=0;$i<$num_resultsa;$a++)

{

  $issue_id=mysql_result($resulta,$a,"issue_id");



  echo "this is issue_id:" . $issue_id . "<br>\n";



  // query "b"

  $resultb=mysql_query("select * from topics where issue_id='$issue_id'");



  // row count "b"

  $num_resultsb=mysql_results($resultb);



  // loop "b"

  for($b=0;$b<$num_resultsb;$b++)

  {

    $topic_id=mysql_result($resultb,$b,"topic_id");



    echo "this is topic id: " . $topic_id . "<br>\n";

  }

}

?>

good luck!

nemesis · 06-10-2003, 06:08 PM

I've altered the code a little - here is a little of the output :-

this is issue:19
this is topic: An Interview with a Flood Study
this is topic: Regular Section - ALS will visit...
this is topic: Regular Section - In Brief
this is topic: Surveyors Surveyed: Results of Readers Questionnaire
this is topic: When is a Helicopter or Fixed Wing the More Appropriate
this is topic: Forrestry LiDAR Discussion List
this is topic: The Truth, the whole truth and nothing but the ground truth.
this is topic: Q&A from Readers' Questionnaire
this is topic: ALS Research Finally Underway
this is topic: Happy New Year for the Year of the Goat
this is topic: Regular Section - Scan Art
this is issue:19
this is topic: An Interview with a Flood Study

which repeats with different newsletter issues - I now get this error:-

Warning: Unable to jump to row 54 on MySQL result index 2 in /home/httpd/testurl on line 87
this is issue:
You have an error in your SQL syntax near '' at line 1

I altered the code a little to get to this stage as there were other problems, here it is :-

[php]<?php

@ $db = mysql_pconnect("localhost", "aam","8ychmqp");
if (!$db)
{
echo "Error: Could not connect to database. Please try again later.";
exit;
}

// get results from horizon and topic tables

mysql_select_db("dtabase");

$query = "select * from topics, horizons ";

// query "a"

$resulta = mysql_query($query) or die(mysql_error());

// row count "a"

$num_resultsa = mysql_num_rows($resulta);

// loop "a"

for($a=0;$i<$num_resultsa;$a++)

{

$issue_id=mysql_result($resulta,$a,"issue");

echo "this is issue:" . $issue_id . "<br>\n";

// query "b"

$query2="select * from topics where issue =$issue_id";

$resultb=mysql_query($query2) or die(mysql_error());

// row count "b"

$num_resultsb=mysql_num_rows($resultb);

// loop "b"

for($b=0;$b<$num_resultsb;$b++)

{

$topic_id=mysql_result($resultb,$b,"topic");

echo "this is topic: " . $topic_id . "<br>\n";

}

}

and I thought was going to be easy!

sde · 06-10-2003, 06:48 PM

well i can help ya out here and there, .. but yer gonna learn to debug your own code .. look at the errors .. it looks like you have a bad query .. probably the second query .. double check your syntax.

nemesis · 06-10-2003, 06:57 PM

thanks sde