Priming read for PHP Query

Question

I am new to PHP and working on my own CMS for a project. Mostly to just give myself a project to learn the language. The CMS is working but I am just tracking down some bugs in it. So here it goes...

I am trying to list all of my published articles for each section. It does this correctly but I am having an issue in the code statement that follows around the line of

    echo '</br></br><br>'.ucfirst($row['section']).' Articles: '; //['section'] is the name

Now If I am showing all my sections its correct, because its querying in ascending order, but if I go with just one section it always just shows the section name of the very first section it never picks up the section that its currently showing articles for. Is there a way that I can have a first priming read to get the section name, then reset it back to 0 times through the loop for the while ($stories = $result->fetch_assoc()) loop so I dont miss an article?

I had reworked it to use the join so I could get the name of the section with the section id number itself. The joined sql statement I have does just what I need. But I am not initiating the call to the joined statement until after I have listed the section name and then the article count (the while ($stories = $result->fetch_assoc())).

//query for getting the sections to the list each ones articles
$querySection = 'select * from sections order by section asc';
  $resultSection = $handle->query($querySection);

if ($resultSection->num_rows) 
  {

//hard coded to only show the scection ONE time... 
//SQL repeats the rows the number of sections there are. So lets cheat.
if(isset($sectionHolder) && $sectionHolder > 0)
 $counterHolder = 9998;
else
 $counterHolder = 0;

while ($row = $resultSection->fetch_assoc()) 
{ 

 if(isset($sectionHolder) && $sectionHolder > 0) 
  //we are looking at one specific section
  $query = 
  'select articles.*, sections.section
  from articles 
  INNER JOIN sections
  ON art_sec=sections.id
  where articles.art_sec = \''.$sectionHolder.'\' and articles.published IS NOT NULL 
  ORDER BY sections.section, articles.headline asc
  ';
 else //just looping through everything we have...
  $query = 'select * from articles where art_sec = \''.$row['id'].'\' and published IS NOT NULL order by art_sec asc';

 $result = $handle->query($query);


 if($result->num_rows > 0 && $counterHolder != 9999)//we have a defined section to go into
 {


echo '</br></br><br>'.ucfirst($row['section']).' Articles: ';
echo $result->num_rows;
echo '</br></p>';

 if ($result->num_rows) 
 {
  echo '<table width="90%">';
  echo '<tr><th>Headline</th>';
  echo '<th>Modified</th></tr>';
  while ($stories = $result->fetch_assoc()) 
  {
   echo '<tr><td width="75%">';
   echo $stories['headline'];
   echo $stories['sections.section'];
   echo '</td><td>';
   echo date('m-j-y, h:i', $stories['modified']);
   echo '</td><td>';

   //add to array
   $array[] = $stories['id']; // add every ID on this page to the array.
  } //end while of stories

 echo '</table></br></br></br></br>';
 $counterHolder += 1; //adds one to only come in once if the section is selected or all times if its all sections.
  } // end if results num rows
 }//end if results num rows >0 and counter != 9999
}//end while row-fetch
}//end if ResultSection-numrows

Maybe someone could also try and help me figure out a way to take out the delimiter of counter. I messed around with other ways but that was the only logic I could come up with to solve my problem, and it just doesnt seem like a very effective way to code. The issue was that when I was looking at just one section and it had 5 articles, it would show the section Name then list its 5 articles, 5 times, vs only being done once. So I have a counter going to only go inside the loop one time, if a section is passed in.

This is my first post on here, If I have posted too much and need to break it down more just say so (i figured more is better than bits and pieces). I could also post the entire page of code if needed.

Thanks

Answer 1

Could you put a where clause in your initial SQL statement?

For example select * from sections where secionId = $sectionId order by section asc

I take it you click on a section and by doing so your app will take you to a page that shows the section you have selected and its articles?

Answer 2

I think your problem lies in that you iterate through your data for the first time while you're already printing your output. This makes for pretty inflexible code. Resource efficient, but not flexible.

Here's what you might do instead:

1. after you've finished querying your sections and articles, make an array that will hold your sections.
2. put an article array in each of those sections with related articles
3. loop though the section array with a foreach, and then through the articles in those with a foreach

To accomplish the first and second item, do the following:

$sections = array();
$result = $handle->query($query);
if($result)
 while ($storie = $result->fetch_assoc()) {
  $article = array();
  $article["stuff"] = $storie["stuff"];
  /* replace "stuff" with things you need from your query */
  $sections[$stories['section']][$stories['articleName'] = array('stuff'=>'some value');
 }

foreach($sections as $sectionName=>$articles)
 foreach($articles as $articleName=>$article)
  // print your stuff here
  // at this point you have all your data organised and you
  //   could've gone though it easily before the 2 foreaches
  //   to search for other stuff you needed.