General term in a column

jonki

Hi - I am not sure if this title is descriptive of what I am trying to achieve but here's what I am trying to do;

I have an activity data base which lists availabe sports and activities in an area which has, among others, a field which specifies the age group.
This has 4 variables; All,Infants, Juniors and Seniors.
The person entering the activity can select for which group it is open for.

On the searching side of the website - I have no problems if my user wants to search all age groups (..WHERE age_g LIKE '%".$age."%' AND...)

What I have problems with is the other way round - if someone says that their activity is open to all age groups, and my user searches only "seniors" then how do I get it to appear?

I tried (in the "insert" part)
<select name="age">
<option value="all">All</option>
<option value="Infant all">Infantil</option>
<option value="Junior all">junior</option>
<option value="senior all">Senior</option>
</select>
and then do a search that included the word "all" (..WHERE age_G LIKE '%".$age."%' AND...)

But this doesn't seem to work and it also smells to me of bad programming.

Any suggestions - thanks a lot in advance to anyone who can help.

Jon

tekky

simple....

if ($age_g) == 'All') {
  $query = 'SELECT * FROM activities';
}
else {
   $query = 'SELECT * FROM activities WHERE age_g LIKE %' .$age_g. '%';
}

jonki

Lightning speed and the answer is so simple - I was so engrosed in the whole "Select" statement that I forgot everything outside it.

Thanks very much. I owe you a beer.

Jon

tekky

bah, whys it always a beer... :p

hehe enjoy, just gotta remember to use all the resources you got going on to solve any given problem 😉

broc7

Maybe I'm being dense, but- that works? I don't see it.

tekky

Originally posted by broc7
Maybe I'm being dense, but- that works? I don't see it.

hehe, you're being dense....

basically... if his value from the form is ALL it SELECTS ALL the values from the table (no preference on the age group....)

if the value from the form is NOT all, then it selects the values from the table WHERE age_g LIKE %value%

effectively grabbing all with %toddler% or %senior% etc etc...

instead of him trying to solve the problem using mysql and WHERE clauses... i used PHP's control structures to do it for him....

broc7

I follow you right up until you say %senior% effectively grabs all. If the search param is senior, he want to find activities listed as senior or all, right? '%senior% won't find 'all'.

tekky

No, %senior% w ont find all... thats why you CHECK for if $age_g (the PHP var) is ALL.. if it is... then you QUERY...

SELECT * FROM activeities

//without a WHERE clause to distinguish between age_g values....

helz

hey Tekky,
You got a quick explanation of the wildcards used in mysql queries?

jonki

I think broc7 has a point.....

I'm not quite sure how this works - I like the idea of doing it in php script but I am not sure if it can do what I want.

(Now I'm trying to actually implement it I realise it isn't as easy as I thought)

Here is another example along the same lines:

Someone offers tennis lessons for example.
The person offering the activity says that it is available all over the uk. From a pull down menu
<select>
<option>uk</option>
<option>Scotland</option>
<option>Edinburg</option>
.......
<option>England</option>
<option>London</option>
....etc

he selects "uk".

Fine - a value (eg "uk", or "all") is placed in the data base.

Now - Little Jimmy comes along and wants to play tennis in Edinburgh. He doesn not search uk, but he clicks on only "Edinburgh".

Now - how does the php pull out all entries that are in "Scotland, the Uk AND Edinburgh to display them?

Here is some very rough coding:

if(area==edingburgh) SELECT * from activities where area=edinburgh OR area = scotland OR area = uk AND sport = tennis;

This is all very well - I think it would work - but I have 3 zones and 36 counties - plus all the other characteristics that limit the seearch results.

Is there a better more efficient way?

Thanks guys.

Jon

tekky

% is wildcard like dos *
_ is single char like dos ?

and your previous attemps wanted ALL or one specific... if you click a specific area then you should get the specific area.. how I would do this is by modding your table to include a REGIONAL code...

then assign all areas that would be grouped in the same region with the same regional code....

however its illogical to return all of scotland and the UK for someone only wishing to see results in Edinborough....

you are right, what i said was no efficient for your previous post, but for your age group one, it is very

broc7

You can get a specific age group and 'all' on a search with this:

$query = "SELECT * FROM activities WHERE age_g = '".$age_g."' OR age_g = 'all'";

Another way of doing it is set values in the select to i, j, and s for the age groups, and set the all value to ijs. Then when searching, use LIKE '%$age_g%'. LIKE %s% will bring up senior and all (s and ijs); LIKE %ijs% will only bring up all.

For locations, I think you'd want specifics to come up when searching for generals, but not vice versa. Edinburg should come up for UK, but UK shouldn't come up for Edinburg.

If you have the activities records set up with country, region, city as separate fields, and all required, users could then choose to search on country, region, and city as they see fit. If you have some folks putting UK as location, and others entering Edinburg in the same field, that's going to be one hard-to-search db.

You could come up with some way of 'teaching' the db that Edinburg's in Scotland, but I don't think it will be worth it to you. You'd have to entire the entire hierarchy of locations into tables.

broc7

jonky: I reread your latest post and see that you do want generals to come up on specific searches. Nevermind that part of my previous post, then.

One way you can do it is with another table, as Tekky says.

But if you make Jimmy choose country and region along with Edinburg, you could match him with someone offering tennis throughout the country. Might be too much for the users though.

jonki

That is great guys - more or less along my line of thinking.
Some great ideas there that will certianly help me.

Broc7 - I was thinking about that and I originally wanted a way of allowing to specify the whole country but now I've figured that even though something might be open to the whole country they must have a base somewhere and I figured it would be easier just to allow them to specify one place. An all england championship, for example, must be held somewhere.

tekky - great help. Thanks.

Very much appreciated.

tekky

yup, no problem, glad it helped... somewhat 🙂