2. PHPRunner General questions
  3. Help with custom sql statement using ifs
This topic is locked

Help with custom sql statement using ifs

 		1/20/2011 2:05:23 PM
PHPRunner General questions
D
 Dale author

I am trying to change the sqlquery in before list Process SQL of my advertise table
Any pointers to get me started would be great.

I have three session variables.

$_SESSION["country_id"]

$_SESSION["region_id"]

$_SESSION["city_id"]
my advertise table has three fields in it that correspond to the session variables.

advertise_country_id required

advertise_region_id could be empty

advertise_city_id could be empty
3 users have ads set

1 - to show only in city value 22 (Calary) City

2 - to show in any city in the region value 2 (Alberta) Region

3 - to show in any city in the country value 1 (Canada). Country
So if a user, on the ad, fills every field, Country, Region, City, then ONLY show that ad in the city matching the $_SESSION["city_id"]

but also include any ad that has the country and region set and no city set

and include any ad that has only the country set and No region or No city set.
So if a small company sets thier ad's country and region and city fields, his ad will only show in that city.

If a bigger company wants to advertise in the region, then they will set there ad to Country, and Region and exclude city.

A huge company wants to advertise accross all regions and cities will set there ad to Country only.
I cant seem to get my head around this, I am hoping I can do this in an sql statement. I just need a little sample of how to approach this.
My goal, to grab 7 to 10 ads, and the last wrinkle would be at random, but matching the ad settings.
I need to pull advertisements that match $_SESSION["city_id"]
Sergey Kornilov admin 1/20/2011

As far as I understand you only have $_SESSION["city_id"] available at the beginning.
Step 1.

Run a SELECT to find what region $_SESSION["city_id"] belongs to. Save region in session variable $_SESSION["region_id"]
Step 3.

Run a SELECT to find what country $_SESSION["city_id"] belongs to. Save country in session variable $_SESSION["country_id"]
Step 1.

Construct SQL Query as follows:

select * from ads where city_id=$_SESSION["city_id"] or region_id=$_SESSION["region_id"] or country_id=$_SESSION["country_id"]

order by RAND() limit 10


That should do it.
D
 		Dale author 1/21/2011

Thanks Sergey,
That's the kind of query I started with, but, i think i need something like this
select from ads where city_id=$_SESSION["city_id"] or region_id=$_SESSION["region_id"] or country_id=$_SESSION["country_id"]

order by RAND() limit 10
select from ads where city_id=$_SESSION["city_id"] or

where ((city_id is null or city_id = '') and region_id=$_SESSION["region_id"] ) or

where (((city_id is null or city_id = '') AND (region_id is null or region_id = ''))) and country_id=$_SESSION["country_id"])
Not sure of the sql statement to build out of that.
Thanks for the tip on the RAND() feature.
Sergey Kornilov admin 1/21/2011

Not sure I understand the question. Did you try the suggested method?
D
 		Dale author 1/21/2011

Your right, I will give the suggestion a try. I might be trying to make to much out of it as usual.

I will give it a try.