while loop only returns 1st record?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
nicademus
Forum Newbie
Posts: 14
Joined: Tue Apr 14, 2009 8:36 am

while loop only returns 1st record?

Post by nicademus »

Hello All,
I am using a while loop to display all the records of a db but I cannot figure out why it only returns the first record. I have used the same basic format of this while loop in other applications and it works fine. NOt sure what I have missed or done differently.

Code: Select all

 
    mysql_connect($hostname,$username,$password);
 @mysql_select_db($database) or die( "Unable to select database");
 //query to get info according to co_name in co_info tbl
 //and if they are classified in this category in categories tbl 
$query = "SELECT $info.*, $categories.co_name, $categories.everyday_wear
          FROM $info, $categories
          WHERE $info.co_name=$categories.co_name AND $categories.everyday_wear='y'
          ORDER BY $info.co_name ASC LIMIT 0,10";//limit 10 per page
          
  $result = mysql_query($query) or die (mysql_error());
  //place query in array to be echoed and looped
  while ( $row = mysql_fetch_array($result))
    {
     $co_name = $row[0];
     $city = $row[1];
     $city = trim($city);
     $country = $row[2];
     $logo_small = $row[3];
     
?>
 
<div id="category_list_co">
<img src="img/embellishment_img.png" class="co_top_img" />  
<img src="<?php echo $logo_small ?>" />     
<ul>
   <li>Location:<br />
       <!--echo city and country from co_info tbl -->
       <?php echo $city ?>,&nbsp;<?php echo $country ?></li>
   <li>Company Type:&nbsp;<?php
       $query = "SELECT designers FROM $categories WHERE co_name=$info.co_name";//Determine if Copmany is a designer or retailer and display accordingly
       $result = mysql_query($query);
        if ($result='y')
        {
          echo 'Designer';
        }
        else
        {
          echo 'Retailer';
        }
        ?></li>
   <li><a href="#">Full Page/another php script will get the url for the company page</a></li>  
</ul>
</div><!-- end category list co div -->
<?php 
     }//end while loop for category listing
    ?>
 
If this is actually a SQL issue please let me know and I will post this in another forum. :)
Thanx!

edit:
I get this error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\DSlayout2\category_practice2.php on line 33
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: while loop only returns 1st record?

Post by requinix »

Code: Select all

while ( $row = mysql_fetch_array($result))

Code: Select all

   $result = mysql_query($query);
You reset $result inside the loop. You lost the results from the first query.
nicademus
Forum Newbie
Posts: 14
Joined: Tue Apr 14, 2009 8:36 am

Re: while loop only returns 1st record?

Post by nicademus »

I think I understand but I use the same syntax in another loop and it works fine.
like this:

Code: Select all

 
mysql_connect($hostname,$username,$password);
 @mysql_select_db($database) or die( "Unable to select database");
 $query="SELECT link, text FROM $links WHERE co_name='$co_name'";
 $result=mysql_query($query);
 
  while ($row = mysql_fetch_array($result)) 
  
   { 
    $link = $row[0];
    $text = $row[1];
    $link = stripslashes($link);
    $text = stripslashes($text);
     ?> 
     
     <li><a href="<?php echo $link ?>"><?php echo $text ?></a></li> 
   
   <?php
   } 
   ?>
 
Why does it work with the above script but not the one I posted first? They look they same to me....

How do I go about getting the result from the select query into the while loop? - from my first post. Since it worked in the above example I am a bit confused.

Do I need to place the query inside the while loop?
Thanx
****************************
edit:
*****************************************

Fixed the problem!

Code: Select all

 
$Result=$result!!!!
while ($row = mysql_fetch_array($Result)).....
 
 
 
Post Reply