[SOLVED] Struggling with php logic to display results of mySQL query.

[gray8110]

I've got a page that will be based upon the results of a mySQL query. There should be 5 separate lists on the page - each will display content based according to the value of a matching SQL field. I could create 5 different queries, but it seems simpler to have some PHP logic display the appropriate results based on that value.

 

Here's the relevant code - Query:

<?php
//DB Query
$result = mysql_query("SELECT * FROM sv_rides", $connection);
if (!result) {
die("Database query failed: " . mysql_error());
}

//Use returned data
$row = mysql_fetch_array($result);
$common = $row["common"];
$hilly = $row["hilly"];
$centuries = $row["centuries"];
$dirt = $row["dirt"];
$cali = $row["cali"];
$ridename = $row["ridename"];
$distance = $row["distance"];
$elevation = $row["elevation"];
$description = $row["description"];
$shortDesc = shortenText($description);
?>

 

This is an example of the output from the query.

<?php
if ($common == '1') {
echo '
	<li>
		  <p style="font-size:14px; margin:0;"><a href="woodrat.php">' . $ridename . '</a></p>
		  <img src="images/rides/' . $mapgif . '" width="188" height="188" border="0" alt="Woodrat" />
		  <p class="rideHead"><strong>Distance:</strong> ' . $distance . ' miles</p>
		  <p class="rideHead"><strong>Elevation Gain:</strong> ' . $elevation . '&#39;</p>
		  <p class="rideHead"><strong>Description:</strong><br /><br />' . $shortDesc . ' ...</p>    
	<li>';
}?>

 

The if ($common == '1') statement is where I'm struggling. That particular statement is only returning the first result. If I use while ($common == '1') I get an infinite loop. This seems like a place where foreach would come in handy, but I'm struggling with how to do that in the context of the query.

 

Any ideas? (Sorry for beeing a php noob)

 

[chronister]

In order to get all results, you will have to loop through them with a while.

 

<?php

while($row = mysql_fetch_array($result))
{
  // set the items to  arrays here
  // once they are set in arrays, you can loop through those 
//arrays to display them where you want them.
}
?>

 

That is the basic concept of it. Since you want to display them in separate lists, then you need to create arrays out of them first with the while loop and then loop through them to display them in the lists you want.

 

Nate

[jhartog]

You should do something like the following:

 

while($row = mysql_fetch_array($result)){
    if($row['common'] == 1){
         // output results...
         
    }
}

mysql_free_result($result);

 

this loops to fetch a row one at a time, and only outputs the row if $row['common'] equals 1.

[gray8110]

Thanks for the help guys - I'd actually gone this route first, but something else was causing problems and I didn't recognize the source.

 

To that point. Each set of results needs to be wrapped in it's own UL. Can I do that with a single query (and a single while loop?)

 

<?php

$result = mysql_query("SELECT * FROM sv_rides", $connection);
if (!result) {
die("Database query failed: " . mysql_error());
}

//Use returned data
while ($row = mysql_fetch_array($result)) {
$common = $row["common"];
$hilly = $row["hilly"];
$centuries = $row["centuries"];
$dirt = $row["dirt"];
$cali = $row["cali"];
$ridename = $row["ridename"];
$distance = $row["distance"];
$elevation = $row["elevation"];
$description = $row["description"];
$shortDesc = shortenText($description);

$mapgif = $row["mapgif"];

//COMMON RIDES
if ($row['common'] == '1') {
echo '
	<li>
		  <p style="font-size:14px; margin:0;"><a href="woodrat.php">' . $ridename . '</a></p>
		  <img src="images/rides/' . $mapgif . '" width="188" height="188" border="0" alt="Woodrat" />
		  <p class="rideHead"><strong>Distance:</strong> ' . $distance . ' miles</p>
		  <p class="rideHead"><strong>Elevation Gain:</strong> ' . $elevation . ''</p>
		  <p class="rideHead"><strong>Description:</strong><br /><br />' . $shortDesc . ' ...</p>    
	</li>';
}

?>

 

Essentially, I'd like to have those results wrapped in a UL, then start a new UL, and get a second set of results based on another if block. if ($row['common'] == '1') for example

[chronister]

If you want the lists to appear horizontally (side-by-side) then it will be a little more challenging as some css will be needed to accomplish that. If you want them to appear in a vertical format (one right after another) then you could do that without much issue.

 

 

You will simply need to use some if/else type statements to determine what you want to trigger the end of a UL and then start a new one.

 

Nate