Any reason NOT to have a mysql_query executed in a while loop?

[suttercain]

A co-worker said she think's this is a bad idea, but didn't say why. Does anyone know if it is a bad practice to do this?

 

Example:

 

<?php
require('../includes/get_connected.php'); // include database

//GROUP BY//
$sql = "SELECT * FROM actionFigures GROUP BY seriesTitle, series";
$result = mysql_query($sql) or die(mysql_error());
if ($result) {
while ($row = mysql_fetch_array($result)) {
$sql1 = "SELECT title, image FROM actionFigures WHERE seriesTitle='".$row['seriesTitle']."' AND series ='".$row['series']."' ORDER BY title";
$result1 = mysql_query($sql1) or die(mysql_error());

echo $row['seriesTitle']. " " .$row['series'];
echo "<br />";
while ($row1 = mysql_fetch_array($result1)) {
echo "<a href='http://www.supermandatabase.com/images/actionFigures/".$row1['image']."'>";
echo $row1['title'];
echo "<a>";
echo "<br />";
}
echo "<br />";
}
}
?>

[premiso]

Probably because it does not make sense to pull the same exact data twice?

 

You are pulling from the same table, same fields. That code is redundant and pointless as this would accomplish what you want:

 

<?php
require('../includes/get_connected.php'); // include database

//GROUP BY//
$sql = "SELECT * FROM actionFigures GROUP BY seriesTitle, series";
$result = mysql_query($sql) or die(mysql_error());
if ($result) {
while ($row = mysql_fetch_array($result)) {

echo $row['seriesTitle']. " " .$row['series'];
echo "<br />";
echo "<a href='http://www.supermandatabase.com/images/actionFigures/".$row['image']."'>";
echo $row['title'];
echo "</a>";
echo "<br />";
}
}
?>

 

Should pull the same data. If you want it to be organized a certain way, the use order by, but since there is a group by it is already organized.

 

What type of data are you trying to pull out? Why would you need that inner mysql loop?

[suttercain]

Because I need to loop through the results. Like so:

 

I have a series of action figure. I need to have only the series outputted once... but I then need it to loop through each action figure within that series.

 

My code does it, but I am not sure if this is the best or only way to do it.

 

Check out your code and my code results:

http://www.supermandatabase.com/actionFigures/

[premiso]

Understandable.

 

Here is a quick solution to your problem without creating extra load on the mysql server.

 

<?php
require('../includes/get_connected.php'); // include database

//GROUP BY//
$sql = "SELECT * FROM actionFigures GROUP BY seriesTitle, series ORDER BY title";
$result = mysql_query($sql) or die(mysql_error());
if ($result) {
  $prev_title = ""
while ($row = mysql_fetch_array($result)) {
if ($prev_title != $row['seriesTitle']) {
    echo $row['seriesTitle']. " " .$row['series'];
    echo "<br />";
    $prev_title = $row['seriesTitle'];
}

echo "<a href='http://www.supermandatabase.com/images/actionFigures/".$row['image']."'>";
echo $row['title'];
echo "</a>";
echo "<br />";
}
}
?>

 

Probably a better way to do it, but that will/should work.

[suttercain]

Hi premiso,

 

I thank you for your response and time. I tried the code you posted above, unfoutanetley I don't the result was the one desired.

 

I have the results posted at the same link above or

 

My Orginal Code reuslts:

DC Super Heroes 2

Bizarro

Doomsday

Supergirl

Superman

 

DC Super Heroes 4

Brainiac

Darkseid

Lex Luthor

Superman

Superman

 

Elseworlds 4

Batman

Blue Beetle

Evil Superman

Flash

Good Superman

Wonder Woman

 

Justice League of America 1

Black Canary

Black Lightning

Red Arrow

Superman

Vixen

 

Reactivated 3

Batman

Flash

Superman

Wonder Woman

 

Superman/Batman 4

Batman Beyond

Batwoman

Batzarro

Bizarro

Kryptonite Batman

Superwoman

 

Your results from the last code:

DC Super Heroes 2

Bizarro

Lex Luthor

Elseworlds 4

Blue Beetle

Justice League of America 1

Red Arrow

Reactivated 3

Superman

Superman/Batman 4

Batman Beyond

[premiso]

<?php
require('../includes/get_connected.php'); // include database

//GROUP BY//
$sql = "SELECT seriesTitle, series, title, image FROM actionFigures ORDER BY seriesTitle, series, title";
$result = mysql_query($sql) or die(mysql_error());
if ($result) {
  $prev_title = ""
while ($row = mysql_fetch_array($result)) {
if ($prev_title != $row['seriesTitle']) {
    echo $row['seriesTitle']. " " .$row['series'];
    echo "<br />";
    $prev_title = $row['seriesTitle'];
}

echo "<a href='http://www.supermandatabase.com/images/actionFigures/".$row['image']."'>";
echo $row['title'];
echo "</a>";
echo "<br />";
}
}
?>

 

Try it without the group by and use order by. Also defining columns in the mysql query is better than using * if you know what columns you need to use.

[suttercain]

Hi Premiso,

 

I just got around to giving the above code a shot, and so far so good. Thanks for helping me out on this.

 

SC

[suttercain]

Uh oh, I spoke too soon (or wrote in this case).

 

The code appeared to be working, but upon close observation of the output I noticed the titles were being 'grouped' together.

 

I used the code placed in replay #5 by Premiso. The only thing I changes was adding a ; to the end of a line.

 

You can view both my original result and the Premiso output results here:

http://www.supermandatabase.com/actionFigures/

 

You'll notice there are two Action Figure sets named 'DC Super Heroes' but one is series 2 and the other is series 4.

[suttercain]

So if I cannot do it the way Premiso was nice enough to help me with... is there a downside to doing it the original way I had it? Looping through a query?

 

Thanks.