PHP Query Function

[jason360]

Hey guys I am stuck on how to best create a php function to do the following.

 

I need to echo out 4 restaurants.  I have a database of numerous restaurant categories: breakfast, lunch, dinner, and coffee.  In this database some categories have more results than others.

 

Basically I want to randomly choose results from each category query, Breakfast, Lunch, and dinner to create 4 echoed results. (I understand that one category will have an extra result as there are 3 categories and 4 echoed results.)  I do not want one category of restaurant to bump out another category because it has more results.  Also, if there are not enough or no results from one category, I want extra results from the other queries to make up for it so that there are always 4 echoed items.

 

Finally, if there are not enough results from breakfast, lunch, dinner to make 4 echoed items, I would like Coffee to fill the 4 required results.

 

Randomly echo 4 results from the below primary queries:

Primary Query:  Breakfast Restraunts Query

Primary Query:  Lunch Restraunts Query

Primary Query:  Dinner Restraunts Query

 

Get results from secondary query if Primary queries can’t echo 4 results:

Secondary Query:  Coffee Shops Query

 

I hope this makes sense.  I understand how to do the mysql queries, I just don't know how to do the php function to echo the results as required.  Any help would be appreciated!

Edited January 9, 2015 by jason360

[Psycho]

Sounds like a homework problem. Why don't you at least make an attempt and then ask for questions on where you are stumped. Or, at least provide an explanation of how you think it might be accomplished. Most people here don't mind "helping" people with homework, but we like to see the person at least try.

 

I'll give you a little push in the right direction:

 

1) You show that there should be three primary queries from which you want 1 record each. You say you know how to create queries, but do you know how to, correctly, get 1 random record? Hint: don't use ORDER BY RAND()

 

2. You then need a secondary query to get the remaining record(s). This will be very similar to the first three queries - except you don't want to filter on the category. But, you do not want to include the records you got from the first three queries.

 

So, start with #1. If you get it working move on to #2. If not, post back with what you've done and the problems you are experiencing.

[jason360]

I have started a function with various if statements and a counter.  The individual queries are quite complicated.  I have been using individual queries with a counter and if statements, however when a category has 4 or more results it just takes over the whole function and the other categories are not included.  

 

I figured explaining it this way would be simpler. 

Edited January 10, 2015 by jason360

[mac_gyver]

since we don't have any details about your selection conditions and what condition(s) in them you relax when there isn't enough matching data found, the only thing i can think of, to avoid running repeated sets of queries, is to select a large enough set of results in the first place, using the most relaxed set of conditions you are willing to use, then order the results by how closely they match the more strict conditions. this will give you a larger than needed pool data with an order/ranking assigned to each piece of data that you can use to find a result set that meets your needs.

[jason360]

Thanks @mac_gyver

 

I was able to make a large pool of results using the UNION function in my query.

 

Here is a link if anyone else comes across this situation in the future:

 

http://www.w3schools.com/sql/sql_union.asp