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
}
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
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.
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);
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’”
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.
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
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;}
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:
I can do this right “m.*”?
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;
}
