Loopy Loop

[snappy]

Hi ALL

Could someone please advise me where to put the brakes on the while loop in the following code :

<?
include "db.php";

$sql = "SELECT 
users.userid, 
users.first_name, 
users.last_name, 
users.email_address, 
users.username, 
users.password, 
users.user_level, 
users.signup_date, 
users.last_login, 
users.activated, 
sites.site_id, 
sites.title, 
sites.url, 
sites.description, 
sites.category_id, 
sites.PR, 
choose.site_id, 
choose.user_id, 
category.category_id, 
category.category
FROM users, sites, choose, category
WHERE users.userid = choose.user_id";


$result = mysql_query($sql) or die(mysql_error());

if ($result) {
  if (mysql_num_rows($result) > 0) {
      while ($row = mysql_fetch_row($result)) {

$userid = $row[0];
$first_name = $row[1];
$last_name = $row[2];
$email_address = $row[3];
$username = $row[4];
$user_level = $row[5];
$signup_date = $row[6];
$last_login = $row[7];
$activated = $row[8];

$siteid = $row[9];
$title = $row[10];
$url = $row[11];
$description = $row[12];
$category_id = $row[13];    

$PR = $row[14];  

$user_id = $row[15];
$site_id = $row[16];

$cat_id = $row[17];
$category = $row[18];

/*echo "Users userid : $userid - 
        Users first name : $first_name - 
        Users last name : $last_name - 
        Users email address : $email_address - 
        Users username : $username - 
        Users user level : $user_level
        <br>";*/

echo "Sites siteid : $siteid - 
        Sites title : $title - 
        Sites url : $url - 
        Sites description : $description - 
        Sites categoryid : $category_id - 
        <br>";

/*echo "Choose userid : $user_id - 
        Choose siteid : $site_id
        <br>";*/

/*echo "Category catid : $cat_id - 
        Category : $category
        <br>";*/


  }
  }
}

?>

It doesn't matter which one of the 3 echos I use the loop just keep going

THX

[Roger_Ramjet]

Not following you? You tell it to select records from the db and then print them all out, so that is what happens. If you don't want them all then either select less from the db, or don't use a while to print them all.

[snappy]

The while loop doen't end, I think it is because the query is from multiple tables or something ?

[snappy]

It displays :

Sites siteid : $siteid - Sites title : $title - Sites url : $url - Sites description : $description - Sites categoryid : $category_id

over and over and ....

PS. I left out the values of the variables

[Roger_Ramjet]

Well, now we are talking about 2 different things.

debugging tutorial :

step 1. print out the query sent to the db before you send it

step 2. print out the number of rows returned by the query

step 3. if the result in step 2 is not what you expected then there is something wrong with your query.

[justsomeone]

Your where clause is short a few joins.

assuming a one-to-one relationship between users.userid and choose.user_id, then those two tables are joined fine for the query. But you haven't told the query how the sites and category tables are to be joined in. In the absence of this knowledge, mysql will pair every individual row returned from users/choose, with every individual row in sites and pair every row in this combination with every row in category.

So, if you had 10 rows in users, 10 rows in choose, 5 rows in sites and 8 rows in category, your returned row set would contain 10 5 8 = 400 rows. Which is unlikely to be waht you intended.

What you need to do depends on what you want to achieve, but basically you need to restrict the number of combinations returned, by specifying how the different tables are to be joined... eg something like:

WHERE users.userid = choose.user_id
     AND sites.site_id = choose.site_id 
    AND category.category_id = sites.category_id

[Roger_Ramjet]

Which would all have been made clear if you read and learnt the Join Syntax . While you are at it, learn to code joins explicitly (fewer errors of ommision that way) and move all join conditions from the WHERE clause to the right place: after the join clause in an ON() or USING() clause.

[snappy]

Still haveing a few problems but I will do as you suggested Roger Ramjet

Thanks everyone