[RESOLVED] multiple select query

[TheFanTree]

I'm trying to make a page where you can view all users or narrow them down by country.

The problem I'm having is selecting several countries. In other words intead of finding users from either country 1 or country 2 I want to be able to see users from countries 1,3,4,9,32.

So I have a multiple select list of countries:

<select name="country[]" size="2" multiple>
<option value="1" selected>United Kingdom</option>
<option value="2">USA</option>
and so on.
</select>

This is the code I have so far:

$countrylist=implode(',', $country);

//if the 'select by country box is checked'
if ($countryselection = "yes") {
$query = mysql_query("SELECT userid FROM user_table WHERE country IN '$countrylist'") or die (mysql_error());  

 }

I realise that this is probably completely wrong as I am very much a newbie with php. I would appreciate any help or advice.

[bpat1434]

Nope, that's correct. Although the parenthesis might be required for the IN statement:

SELECT userid FROM user_table WHERE country IN('$countrylist')

[TheFanTree]

Ahh, that explains it.

It's working perfectly now. Thanks a lot!😃

[TheFanTree]

For an encore, could anyone help me with the second part of this problem?

This is a bit more complicated. What if a user had more than 1 country selected. The countries have been saved as a string in the database table.

So in the user table it looks like this

userid country
user 1 1, 2
user 2 4

and so on.

I have no clue how to even begin with this so I would be grateful for any hints, tips, pointers.. anything really.

[halojoy]

Of course a user could be from several countries.
Living in one and born in another.

But question is if you need the user to select more than 1 country?

[TheFanTree]

Sorry, I was trying to keep it simple by using countries as an example.
The whole thing will hopefully allow you to narrow down users by country, interest, both or neither. The table looks like this;

userid | country | interests
user1 | 3 | 1, 3, 9, 6
user2 | 26 | 2, 3

It's the interests that are strings on numbers

$countrylist=implode(',', $country);
$interestlist=implode(',', $interests);

if ($countryselection = "yes") {
$query = mysql_query("SELECT userid FROM user_table WHERE country IN ($countrylist)") or die (mysql_error());  

}

elseif ($interestselection = "yes") {
$query = mysql_query("SELECT userid FROM user_table WHERE interests IN ($interestlist)") or die (mysql_error());  

}   

elseif ($bothselection = "yes") {
$query = mysql_query("SELECT userid FROM user_table WHERE country IN ($countrylist) AND interests IN ($interestlist)") or die (mysql_error());  

}   

else {
$query = mysql_query("SELECT userid FROM user_table") or die (mysql_error()); 
}

Obviously this is wrong but hopefully it gives a better idea of what I'm trying to do. Sorry again for the confusion.

[Sxooter]

You're not using a good relational model, which makes it harder to write queries.

If a user can have > 1 country, then you need to put the countries in one table, the users in another, and use an intermediate join table for those relationships.

users
userid | username

country
countryid | countryname

usertocountry
userid | countryid

then join them:

select users., country. from user u join usertocountry u2 on (u.userid=u2.userid) join country c on (u2.countryid=c.countryid)

[TheFanTree]

Ok, thanks. I hadn't thought of trying anything like that.
I'll give it a go and see what happens.

thanks again

[bpat1434]

I can say this from experience: it will make things a lot easier.

I was working on a project for an admin to be able to oversee multiple groups. So I initially did it as you with a CSV of group IDs. But then I ran into the issue of "What happens when..." and then actually dealing with the data became a hassle. So I created another table which mapped userIDs to groupIDs. Cut my code by 2/3 and it's easier to manage 😉 I still have a large function that manipulates the data to remove duplicates, deletes old entries, and adds new entries 😉

[Sxooter]

bpat1434 wrote:

I still have a large function that manipulates the data to remove duplicates, deletes old entries, and adds new entries 😉

You should at least be able to create some kind of unique index to prevent duplicates right?

OR is this a problem a foriegn key would handle and you're using myisam table handler?

[bpat1434]

Well, to elaborate.... What I mean by "duplicates" is those items that are selected in the form that are already in the database. So rather than send a query with the possibility of X already present items, I use PHP to just do a quick intersect of the current session hold (which is what is in the database) compared to the posted items. If there are duplicates, I then just loop through the posted item, and remove them. Then I compare them, and build a query to delete those no longer in the posted array, and add those that are in the posted array but not in the session.

I guess there could be a faster way, but this way works just as well. And yes, it's a MyISAM table.... they don't want to move to InnoDB because "we don't need it" yet.....

[Sxooter]

bpat1434 wrote:

Well, to elaborate.... What I mean by "duplicates" is those items that are selected in the form that are already in the database. So rather than send a query with the possibility of X already present items, I use PHP to just do a quick intersect of the current session hold (which is what is in the database) compared to the posted items. If there are duplicates, I then just loop through the posted item, and remove them. Then I compare them, and build a query to delete those no longer in the posted array, and add those that are in the posted array but not in the session.

But isn't that method open to race conditions?

I guess there could be a faster way, but this way works just as well. And yes, it's a MyISAM table.... they don't want to move to InnoDB because "we don't need it" yet.....

It's hard to appreciate what you're missing until you have and / or need it. Things like foreign keys and transactions are a luxury for some apps, but an absolute necessity for others.

[bpat1434]

But isn't that method open to race conditions?

Like what?

It's hard to appreciate what you're missing until you have and / or need it. Things like foreign keys and transactions are a luxury for some apps, but an absolute necessity for others.

A part of the project now is using transactions finally. I personally wanted all the tables switched to InnoDB to be able to harness the feature-set, but I don't get to make project decisions.

[Sxooter]

bpat1434 wrote:

Like what?

Like if two people do the same thing at the same time, could you wind up with duplicates?

And I understand about not getting to make those decisions. Been there, done that. Got the TShirt

[bpat1434]

Umm, no I don't have to worry about that (as much anyway). Essentially there's an global administrator that can do just about anything with the site, and then there are "admins" that can do things for their groups. So there really is only 1 person adding multiple group admins. So for my project right now, no, it shouldn't happen; however, later on, I'll probably look at how to counteract this.

[TheFanTree]

Hello, sorry to bother everyone again.

Things have been going both well and badly.
The second table/join statement that Sxooter recommended worked brilliantly.
And the original multiple country select works just fine. It's when I try to use both that I'm getting problems.

If I don't select a country I get this error
'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1'

And if I do select a country I get the country results.

$countrylist=implode(',', $country);
$interestlist=implode(',', $interests);

//get list of users by country
if ($countryselection = "yes") {
$query = mysql_query("SELECT userid FROM user_table WHERE and country IN ($countrylist)") or die (mysql_error());  

}

//get list of users by interest
elseif ($interestselection = "yes") {
$query = mysql_query("SELECT user_table.userid FROM user_table
INNER JOIN user_to_interests ON user_table.userid = user_to_interests.userid
WHERE user_to_interests.interestid IN ($interestlist)
GROUP BY user_table.userid") or die (mysql_error());
}  

//get list of everyone
else {
$query = mysql_query("SELECT userid FROM user_table") or die (mysql_error()); 
}   

?>

<input name="countryselection" type="checkbox" value="yes">Select a country
<input name="interestselection" type="checkbox" value="yes">Select an interest

<select name="country[]" size="2" multiple>
<option value="1" selected>United Kingdom</option>
<option value="2">USA</option>
and so on..
</select>

<select name="interests[]" size="14" multiple>
<option value="1" selected>A</option>
<option value="2">B</option>
<option value="3">C</option>
and so on..     

</select> 

<input name="submit" type="submit" class="submit-button" value="Send">

Please help?

[bpat1434]

= sets a value
== evaluates

So which would we need for if statements? ==
What's your code have?

if ($countryselection = "yes") { 

//.... cut for berevity

elseif ($interestselection = "yes") {

[TheFanTree]

Thanks bpat1434.
That was it, it's all working now. 😃

Thanks again

[PHPsites]

I tried to put some of this to work in my own code but seem to have an issue. Can anyone see anything wrong with this or provide a suggestion? Obviously I am relatively new to the php/mysql world....

Want to show the name & country for 5 random users. Have their user ids stored in an array $fiveUsers.

$result = mysql_query("SELECT usertable.user_id, usertable.user_name, countrytable.country_name FROM usertable JOIN countrytable ON usertable.country_id = countrytable.country_id WHERE usertable.user_id IN ('$fiveUsers')") or die (mysql_error());

$count = 0;
while ($resultarray=mysql_fetch_array($result)){
	$count++;
	echo $count.'  '.$resultarray['user_name'];
            echo '&nbsp;&nbsp;'.$resultarray['country_name'].'<br>';
}

When I execute, nothing is returned. I am not even entering the while loop so I obviously have messed something up. Any ideas?

Thanks.

[bpat1434]

Where is $fiveusers defined?