mysql_query within while loop

Hi guys,
I have two tables. The 1st (countriesToSee), the 2nd (mainweb). I’m trying to choose all users according to the country each would like to see (countriesToSee), and then display all information of each user (from Mainweb).
But the problem is that I’m running a mysql_query within a while loop, which I was told I should avoid.

Can anyone tell me if it’s possible to do this without running that query within the while loop.

Thank you.


if($_POST['submit'])
$country_form = $_POST['country'];


$users = mysql_query("SELECT * FROM countriesToSee WHERE country ='$country_form'");
while ($rows = mysql_fetch_assoc($users)){
$member_id = $rows['member_id'];
$get = mysql_query("SELECT * FROM mainweb WHERE member_id ='$member_id'");
$row = mysql_fetch_assoc($get);


$username= $row['username'];
$firstname= strip_tags(stripslashes($row['firstname']));
$lastname= strip_tags(stripslashes($row['lastname']));
echo $firstname;
echo $lastname;//and so on with all the user's information
}

See how this works for you

if (isset($_POST['submit'])) {
    // Set the posted country value
    $country_form = isset($_POST['country']) ? $_POST['country'] : NULL;
    
    // Create the MySQL query
    $users = mysql_query("SELECT m.username, m.firstname, m.lastname FROM countriesToSee c LEFT JOIN mainweb AS m ON m.member_id = c.member_id WHERE country = '$country_form'");
    
    // Loop through the results
    while (list($username, $firstname, $lastname) = mysql_fetch_assoc($users)) {
        echo $firstname . ' ' . $lastname . '<br />';
    }
}

Thank SgtLegend, but all the info of every user is on the Mainweb table. The CountriesToSee only has the country and the memeber_id.
So I can’t “SELECT m.lastname FROM countriesToSee” as you suggested.

Here are my tables:
CountriesToSee -> member_id, country
Mainweb -> member_id, firstname, lastname, email, username, phone, blablabla

Have a read of the query a little more, m refers to the mainweb table through the LEFT JOIN statement while c refers to the CountriesToSee table.

You might be misunderstanding how the SQL query works.


SELECT m.username, m.firstname, m.lastname
FROM countriesToSee c
    LEFT JOIN mainweb AS m
        ON m.member_id = c.member_id
WHERE country = 'New Zealand'

What the database does is to take both the countriesToSee and mainweb tables, and creates a completely new temporary table from which to select your desired info.

In this case, the temporary table will only consist of rows that have the required country, and those rows will be joined together with rows from the mainweb table that have a matching member_id

From that combined joined table will be selected the parts that you want.

For further details on how joins work, there are resources such as this left outer join example from IBM’s [url=“http://www.ibm.com/developerworks/data/library/techarticle/purcell/0112purcell.html”]comparison of simple outer join constructs

Ok, I see, now I got what the query is doing.

But it still doesnt work. When I try to run it the following comes up:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /public_html/civiliandatabase.php on line 175

Line 175 would be the “while(list…” line


 if($_POST['submit'])
   $country_form = $_POST['country'];
// Create the MySQL query    
$users = mysql_query("SELECT m.username, m.firstname, m.lastname 
FROM countriesToSee c LEFT JOIN mainweb AS m ON m.member_id = c.member_id WHERE country = '$country_form'");

// Loop through the results    
while (list($username, $firstname, $lastname) = mysql_fetch_assoc($users)) {	
echo $firstname . ' ' . $lastname . '<br />';    }

Split the SQL from the query, and view the value of the SQL statement.


$sql = "SELECT m.username, m.firstname, m.lastname FROM countriesToSee c LEFT JOIN mainweb AS m ON m.member_id = c.member_id WHERE country = '$country_form'";
var_dump($sql);
$users = mysql_query($sql);

What does it show?

bool(false)

That’s very odd don’t you think, because $sql is supposed to be a string.

Opps, I redid it correctly, my bad. I chose to look up Guatemala and I got the following:

string(145) “SELECT m.username, m.firstname, m.lastname FROM countriesToSee c LEFT JOIN mainweb AS m ON m.member_id = c.member_id WHERE country = ‘Guatemala’”

But I still don’t understand the problem

Nothing seems to be wrong with that query.

Do you have a database manager such as phpMyAdmin where you can directly run sql commands on your database? They typically give error messages that are easier to understand.

Yes I do, but I’m new at this, so I don’t know how to run the sql command?
should I just copypaste this:

“SELECT m.username, m.firstname, m.lastname FROM countriesToSee c LEFT JOIN mainweb AS m ON m.member_id = c.member_id WHERE country = Guatemala”
???

Yes, without the double quotes at the start and end.

Hey wait!!

Do you see it?

Look at the end of the query. There are no quotes around the country name now.

Is that a copy/pasta error, or is that what you actually have there?

I have no idea where the quotes dissapeared, but I tried running the query directly on the phpmyadmin database twice (with and without the single quotes) and got this both times:

#1052 - Column ‘country’ in where clause is ambiguous

Thank you. This is the first time that we have been made aware that both tables share the same name for that column.

Use c.country in the SQL statement so that it will check against the countriesToSee table and not the mainweb table.

It seems to be doing fine now, becuase it’s not showing any errors, But it’s not echoing anything.
I must be missing something.


$users = mysql_query("SELECT m.username, m.firstname, m.lastname 
		FROM countriesToSee c LEFT JOIN mainweb AS m ON m.member_id = c.member_id WHERE c.country = '$country_form'");        
// Loop through the results    
	while (list($username, $firstname, $lastname) = mysql_fetch_assoc($users)) {	
	echo $firstname;
	echo $lastname;}

I changed “mysql_fetch_assoc” to “mysql_fetch_array” and now it shows me the names as a string. Is there a way I can devide them up?

One sec Paul, I got it. Give me a minute to see if everything is working. But yes, I had to change “mysql_fetch_assoc” to “mysql_fetch_array”.

Thank you all, especially Paul_wilkins. If it were not for you I would still have a mysql_query within a while loop. And now I understand how to create a new temporary table.

One last question. If I’d like to chose all columns from mainweb:

  1. I can do this right “m.*”?
  2. But what would I put within the while(list(…
    I tried using the same list but obviously it didn’t work.
 
$users = mysql_query("SELECT m.username, m.firstname, m.lastname          
FROM countriesToSee c LEFT JOIN mainweb AS m ON m.member_id = c.member_id 
WHERE c.country = '$country_form'");        
 // Loop through the results         
while (list($username, $firstname, $lastname) = mysql_fetch_assoc($users)) {        
 echo $firstname;     echo $lastname;
} 

Then you can just use something like this, right?


SELECT * FROM mainweb

Or are you wanting to achieve something different here?