A little challenge with php and mysql

How to make a sub-query without using sub-query

[whizzy]

Not sure if this is the right place but here we go.

I got a database with user data.

Like this:

User-table:
User1 ¦ Pizza ¦ Beer ¦ New York
User2 ¦ Hot-dog ¦ Soda ¦ Florida

Each user can then select what listing they want to get.
Totally 12 different choices and each choice can have more than one option. So I made 12 tables with username and criteria in. (Think that's smart?)

Food-table
user3 want to list out people with pizza
user3 want to list out people with hotdog

Drink-table
user3 want to list out people with beer
user3 want to list out people with Coffee

city-table
user1 lives in Florida
user2 lives in New York

Get the picture?

So when I want to list them all out, how can I do this without subqueries...since mysql does not support subqueries. (?)

Do I have to make a BIG loop that loops through it all? That would be SO slow I guess...

Confused :(

[ukgimp]

If I have this right you would be best creating more tables. It does make a difference if the user should be allowed to pick more than one food. It sounds like they can?

So here is how I would see one aspect of what you wish to do

"Users"
(pk) user_id
username

"Categories"
(pk) cat_id
name (eg florida, soda, pizza)
(fk) cat_id

"Type"
(pk )type_id
type name (eg food, location, drink etc)

This next table is the one that will allow you to get all the information from the other tables

"User_category_link"
(fk) user_id
(fk) cat_id

So when user1 who info was pizza ¦ beer ¦ new york there are three separate entries in the user_cat_link table for that user id with id’s for pizza, beer and new york. The same happens for each user. This way you can add another category at will, ie you wont have to create a whole new table

You might want to consider have a other table for the location just to keep is separate.

I how the above makes sense, it takes lot to get your schema right. Put it on a whiteboard and get a friend to explain it too. He will find errors and come up with, you need to do this so that wont work. Get it right from the off and you will save yourself potential woes later on.

Cheers

[whizzy]

Thanks for the answer but there is more....

I got the tables like you say, but here's the thing:

Each user can choose from the same options as when he made his own profile, to make a "matchmaker". He can select both hot-dog and pizza as food products, but only one city and then he will get a list of everyone who eats pizza and hot-dog in New-York for example.

Hmmm...horrible to explain I think.

If I now try to say like

SELECT username, food_id, drink_id, state_id, country_id
FROM user, food, drink, state, country,
pref_food, pref_drink, pref_state, pref_country
WHERE food.food_id = pref_food.food_id
AND drink.drink_id = pref_drink.drink_id
AND state.state_id = pref_state.state_id
AND country.country_id = pref_country.country.id

I will get like 10 posts for each user, because he has more than one option on food and the other...

Does this make sense? I think I got more confused now!

[Sander]

XML is your friend for this kind of information.

OR - save all the stuff in one big array and write that array to the database one time per page load, and read it from the database one time per page load as well.

Use serialize($array) to write your array to the database and unserialize($array) (I think) to read it from it again.

Arrays are much faster than SQL

[ukgimp]

You have a complex one ahead of you. You still don’t need a new table for each category. Here is a rough idea of what you need to do The code is not correct just ideas of the processes.

Using the tables I have above also add one for the country which ads a foreign key in the users table.

<php

Get all user choices into an array (apart from country)

//Start to build your sql

SQL = "SELECT user_id FROM User_category_link "

//Build a new line of sql for each user choice
For each input[1-n] {

SQL .= "WHERE user_cat_link.cat_id = $input[] and"

}

SQL .= " country_id = $countryChoice"

This will give you a set of user ids that match the query. You can then use those ID to select the actual information from the users table.

?>

There are going to be many ways to do this. You need to get a decent handle of relational database design. Without you will cause yourself headaches.

This is all asuming I have what you have in mind, in my mind :)