CODEWITHSUNDEEP
phpmysql
Different55
Different55

Reputation: 597

PHP infinitely repeats first MySQL result

I have this code that I've been working on and when I run it I get the first result 10 times (it used to repeat infinitely until I added the if statement). It's supposed to return the first 10 results. I've looked at a couple other similar questions but none of the solutions on those worked here. Can anyone see what I'm missing?

<?php
$link = mysql_connect('XXXXXXXXX.com', 'XXXXXX', 'XXXXX');
$db = mysql_select_db('a1007515_troop1', $link);
$i = 1;
while($row = mysql_fetch_array(mysql_query('SELECT * FROM `posts` ORDER BY `pid` DESC LIMIT 10;')))
{
$authinfo = mysql_fetch_array(mysql_query('SELECT * FROM `users` WHERE `id` = '.$row['aid'].' LIMIT 1'));
echo '
<div class="content">
<a href="post.007?pid='.$row['pid'].'"><h1 class="title">'.$row['title'].'</h1></a>
<span class="authinfo">This post was made by '.$authinfo['name'].'<i>('.$authinfo['username'].')</i> on '.$row['date'].'.</span>
<p class="txt">'.$row['content'].'</p>
</div>';
if ($i == 10) { break; }
++ $i;
}
mysql_close($link);
?>

Upvotes: 0

Views: 360

Answers (2)

Justin Swanhart
Justin Swanhart

Reputation: 1856

The problem is that you execute the SQL statement again each time you test the while() condition:

while($row = mysql_fetch_array(mysql_query('SELECT * FROM `posts` ORDER BY `pid` DESC LIMIT     10;')))
{
...
}

The above code will execute this function every time the while() is evaluated:

mysql_query('SELECT * FROM `posts` ORDER BY `pid` DESC LIMIT     10;')

Instead use:

$stmt = mysql_query(...) or die(mysql_error());
while($row = mysql_fetch_array($stmt)) {
...
}

Next you should use a join. You are following a pattern that "over queries" the database.

You only need the following SQL and a single loop:

SELECT u.* 
  FROM `posts` p 
  JOIN `users` u 
    ON u.id = p.aid  
 ORDER BY `pid` DESC 
 LIMIT 0,10;

Upvotes: 3

John C
John C

Reputation: 8415

Maybe try simplifying things, try and stick to doing one thing per line, rather than trying to compress it.

Calling mysql_query in the while will repeat that query every time the while loop is run, rather than fetch the next result. Running another mysql query while iterating through the results of another one can also cause problems.

$link = mysql_connect('mysqlwebhost', 'user', 'pass');
$db = mysql_select_db('dbname', $link);

$result = mysql_query('SELECT * FROM `posts` p, `users` u WHERE u.id = p.aid  ORDER BY `pid` DESC LIMIT 0,10;');

while ($row = mysql_fetch_assoc($result)) {
    echo '
    <div class="content">
    <a href="post.007?pid='.$row['pid'].'"><h1 class="title">'.$row['title'].'</h1></a>
    <span class="authinfo">This post was made by '.$row['name'].'<i>('.$row['username'].')</i> on '.$row['date'].'.</span>
    <p class="txt">'.$row['content'].'</p>
    </div>';
}

mysql_close($link);

You may also wish to avoid using mysql_* functions as they have been deprecated. Have a look at either mysqli or PDO.

Upvotes: 3

Related Questions