homepage Welcome to WebmasterWorld Guest from 54.205.144.54
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Help With MySQL Query
bgordon




msg:4051172
 3:19 am on Dec 30, 2009 (gmt 0)

I have a couple of tables where I am trying to conditionally show an advertisement in the webpage based on a brand value... cannot quite figure out the query to make this happen:

I have a table called "ads" and a table called "brands" and an intermediary table called ads_brands.

Ads looks like: id, url, title
Brands looks like: id, type, brand_name
Ads_Brands looks like: id, fk_brand_id, fk_ad_id, status (which can be include or exclude)

The thought process is that a coke ad should never show up when a pepsi brand is presented in the query, but any ads marked as being for 'All' or specifically for the brand in questions should be in the found set.

Ads:
1 ¦ coke_ad_one.jpg ¦ This is an ad
2 ¦ pepsi_ad_one.jpg ¦ This is another ad
3 ¦ house_ad_one.jpg ¦ This is yet another ad

Brands:
1 ¦ Drink ¦ Coke ¦ Coca Cola
2 ¦ Drink ¦ Pepsi ¦ Pepsi Cola
3 ¦ Admin ¦ All ¦ This is a dummy brand used to link an ad to ALL brands

Ads_Brands:
ID¦ FK_BRAND_ID ¦ FK_AD_ID ¦ STATUS
1 ¦ 3 (all) ¦ 1(coke ad) ¦ Include <--- shows the coke ad on all requests
2 ¦ 2(pepsi) ¦ 1(coke ad) ¦ Exclude <--- trumps the show ad on all when pepsi brand presented
3 ¦ 3(all) ¦ 2(pepsi ad)¦ Include <--- shows the pepsi ad on all requests
4 ¦ 1(coke) ¦ 2(pepsi ad)¦ Exclude <--- trumps the coke ad when coke presented

So... I need to fetch a single random ads.url where the fk_brand_id = 3 (good for all) and is not exluded from the current brand id (if there is an exclusion for that brand... not an issue for all ads)

I am passing the current brand id in the url to the script...

Cannot seem to wrap my mind around this one...

Any takers? I am sure it will all be simple enough to one of you folks...

 

bgordon




msg:4051175
 3:23 am on Dec 30, 2009 (gmt 0)

Forgot to add the following as an example of expected results:

If I pass brand 1 to the script, then I would expect to get ads.id 1 or 3 back (not 2)
if I pass brand 2 then I would expect to get 2 or 3 back
If I pass brand 52 then I would expect to get 1,2 and 3 back as possible ads (although I will rand() and limit 1)

I would never pass brand 3 since it is only a placeholder to all me to make a single ad available to all my brands and not have to keep a monster list in includes...

ENetArch




msg:4051191
 3:48 am on Dec 30, 2009 (gmt 0)

Gordon,

Can you provide me with an object oriented layout of what this model would look like? Once you provide me with that, I can provide you with a join model that will give you what you are looking for.

E Net Arch

bgordon




msg:4051215
 5:10 am on Dec 30, 2009 (gmt 0)

Sorry to sound stupid but what does the OOM look like?

If you mean the relationships between the tables they look like below:

ADS (ID) <-> ADS_BRANDS(FK_AD_ID)
ADS_BRANDS(FK_BRAND_ID) <-> BRANDS(ID)

Here is what I am doing wrong...

SELECT ads.url
FROM ads
join ads_brands on ads_brands.fk_brand_id in
(select fk_brand_id from ads_brands where fk_brand_id in (1,3) and
status != 'Exclude')

This is meant to pass the brand 'coke' and I want to get the urls of the ads that are marked as all AND not excluded specifically from the 'coke' brand (by id)

I would expect to see ad id 1 and 3 from my model above... but alas... I get all the ads...

Hopefully this helps you help me :)

ENetArch




msg:4051876
 4:31 am on Dec 31, 2009 (gmt 0)

Gordon,

An Object Map usually has a root - a place where you start, and then you drill down from there.

The map for your data might look something like:

Root
.¦-Ads
.¦..¦-Ad(s)
.¦..¦ ¦-Brand

.¦-Brands
.¦..¦-Brand(s)
.¦..¦..¦-Excludes
.¦..¦..¦..¦-Exclusion(s)

Now comes the rules you use traverse the tree.

At random select brands 1 to N brands.
If 1 or more Brands exclude each other,
.. loop through exclusions
.. until no brand exclusions are in the list

At random select Ads for Brands

How does this look? If this map and the rules are correct, then the problem you are facing is that your dataset is to small. It may need to be expanded to get the results you are looking for. and you might have 2 select statements. One to select the brands, and another to select the ads.

Your thoughts?

ENetArch

ENetArch




msg:4051881
 4:34 am on Dec 31, 2009 (gmt 0)

Gordon,

A slight modification of the tree ...

Root
.¦-Brands
.¦..¦-Brand(s)
.¦..¦..¦-Excludes
.¦..¦..¦..¦-Exclusion(s)
.¦..¦..¦-Ads
.¦..¦..¦..¦-Ad(s)

ENetArch

bgordon




msg:4051889
 5:27 am on Dec 31, 2009 (gmt 0)

Kewl... never thought of it like a tree... To make life simpler, since I am not sure if I am explaining this right, here are mysql create statements and some dummy data sets. It might be more meaningful if you don't mind. You seem to have a firm grasp on this and I am sure you will know the answer once you see the data...

CREATE TABLE `videoads` (
`id` int(11) NOT NULL auto_increment,
`url` varchar(254) default NULL,
`title` varchar(254) default NULL,
`live` varchar(1) default NULL,
`startdate` date default NULL,
`enddate` date default NULL,
`views` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE `videoads_2_brands` (
`id` int(11) NOT NULL auto_increment,
`status` varchar(7) NOT NULL,
`fk_vad_id` int(11) default NULL,
`fk_brand_id` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

The brand table is not significant to my problem, but suffice to say that coke=1, pepsi=2 and all=3 (all brand simply lets me make videos available to all brands unless excluded)

INSERT INTO videoads_2_brands(id, status, fk_vad_id, fk_brand_id) VALUES (1, 'Include', 1, 1); <-- makes the video ad available to all brands (saving me from linking to each brand)
INSERT INTO videoads_2_brands(id, status, fk_vad_id, fk_brand_id) VALUES (2, 'Include', 2, 2); <-- makes the video ad available to all brands (saving me from linking to each brand)
INSERT INTO videoads_2_brands(id, status, fk_vad_id, fk_brand_id) VALUES (3, 'Include', 3, 3);<-- makes the video ad available to all brands (saving me from linking to each brand)
INSERT INTO videoads_2_brands(id, status, fk_vad_id, fk_brand_id) VALUES (4, 'Exclude', 1, 2); <-- Makes sure a coke ad does not show for a pepsi brand when pepsi brand id is passed
INSERT INTO videoads_2_brands(id, status, fk_vad_id, fk_brand_id) VALUES (5, 'Exclude', 2, 1); <-- Makes sure a pepsi ad does not show for a coke brand when coke brand id is passed

INSERT INTO videoads(id, fk_adv_id, url, title, live, startdate, enddate, views) VALUES (1, null, 'coke.flv', 'Coke Promo', '1', null, null, 0);
INSERT INTO videoads(id, fk_adv_id, url, title, live, startdate, enddate, views) VALUES (2, null, 'pepsi.flv', 'Pepsi Promo', '1', null, null, 0);
INSERT INTO videoads(id, fk_adv_id, url, title, live, startdate, enddate, views) VALUES (3, null, 'generic.flv', 'Generic Promo', '1', 'null', null, 0);

So… in my PHP script I want to pass the brand ID and have a list of URLs show up that are:
1. Assigned to the brand in question
or Assigned to all brands
BUT not if they are excluded from the brand in question

Ahhhh! This query has been driving me nuts. Maybe my tables and data are the problem, but this should be easier than I am finding it!

ENetArch




msg:4052122
 4:12 pm on Dec 31, 2009 (gmt 0)

Gordon,

I think we are the same page with the business logic.

I'm finding a problem with the business logic as built into your tables. You have brands 1, 2, and 3 .. but 3 means all brands, not a specific brand. I think 3 is causing the confusion here. 3 is not brand it's a flag saying, select 1 and 2. A better way to do this is to create a list of brands from which to select from. So ... either choose, 1, 2, or all which would look like:

SELECT ads.ID
FROM ads
WHERE
ads.Brand_ID IN ( ... list of IDs ... )

This requires that you know the ID's previous if an ALL FLAG is thrown, or that you know a specific brand of interest. In either case, this query will work for both.

Now to exclude competing brands:

I think you need a better exclusion list, from what the object model shows. You have a 1 to many relationship between brands and excludes. So, I would suggest creating a 3rd table for Exclusions that you can run a query:

SELECT ads.ID
FROM ads, excludes
WHERE
ads.Brand_ID IN ( ... list of brands ... ) AND
exclude.Brand_ID NOT IN
(
... SELECT excludes.Brand_ID
... FROM excludes
... WHERE
... exclude.Brand IN ( ... list of brands ... )
)

There is a problem though in this query .. in that both brands will exclude themselves. So, it's probably better to run a query to randomly select brands that don't exclude each other.

Also,
If I were to use OSQL to describe this .. it would look like:

SELECT INSTANCES
USING CLASS "ad"
FROM "brands"
WHERE
"brands" CONTAINS "ads" AND
"ads" CONTAINS "ad" AND
"brand".ID IN ( ... List of IDs ... ) AND
"brand"."Exclusions"."Excluded" NOT IN ("ad".parent."Exclusions"."Excluded")

Does this help?

ENetArch

bgordon




msg:4052154
 4:46 pm on Dec 31, 2009 (gmt 0)

Ok... so I have a table called brands from which I use the brands.id in my content. I pass that brand id to the script that shows a video ad. The intent of the videoads_2_brands is to simply define which videoad has been targeted to a specific brand and also which videoads should not appear with that brand.

That being said, should I be rewriting the way I identify which ads should or should not appear based on the brand ID? If so, how do you suggest I re-do it? I am in early development so I am not committed to the current structure.

The thought behind the brand ID 3 ( it actually is a brand in my brand table, called 'all') is so I don't have to maintain a huge list of brands 2 videos on a 1-1 basis for each videoad... it seemed to make sense to make a catchall for 'all' brands unless I specifically assign it to a brand or exclude it from a brand...

Essentially, if a videoad is assigned to a specific brand and excluded from others, there would not be an 'all' assignment. The 'all' linking only happens to videoads where there is no specific brand assigned and/or there are exclusions...

That being said, how would you do this if you were doing it from scratch?

Thanks for you perseverance!

ENetArch




msg:4052205
 6:02 pm on Dec 31, 2009 (gmt 0)

Gordon,

Want's aside, let's start by reviewing the requirements. Then, let's build A solution that meets the requirements. It doesn't have to be the BEST solution, just A solution. Once you have that, you can work on a BETTER solution. =)

So, requirements:

Select 1 or more brands to display on a page
Brand A cannot be displayed with Brand B where ...
... Brand A or Brand B excludes each other

Are there more requirements? And to be clear, I understand that Brand (3) is your catch all. Basically where all the ads can be found. Including those that can exclude each other. However, I suggest that you avoid putting brands from two competitors into Brand (3) that can exclude each other. But instead create additional brands (4, 5 6, ...) where they are distinctly identified.

And don't worry about how many values you have in your relationships. Once you understand the business logic on how the data is managed, you'll see how to condense the solution. =)

ENetArch

bgordon




msg:4052237
 6:52 pm on Dec 31, 2009 (gmt 0)

Nope that should do it...

ENetArch




msg:4052241
 6:56 pm on Dec 31, 2009 (gmt 0)

Gordon,

so now the business logic portion ...

Let's say the page has 5 ads to display ... so
step 1 - select 5 different brands to display.
step 2 - confirm that the brands don't exclude each other
step 2.5 - add another brand if needed
step 3 - select ads from the 5 brands
step 4 - display the ads

Does this look right for the business logic portion, based on the requirements? or is something missing? either in the logic or in the requirements?

ENetArch

bgordon




msg:4052251
 7:03 pm on Dec 31, 2009 (gmt 0)

Yes... but the technically, there is only one videoad, randomly chosen from the found set, which runs before the intended media...

bgordon




msg:4052252
 7:03 pm on Dec 31, 2009 (gmt 0)

Yes... but the technically, there is only one videoad, randomly chosen from the found set, which runs before the intended media...

ENetArch




msg:4052254
 7:07 pm on Dec 31, 2009 (gmt 0)

Gordon,

Sorry I'm misunderstanding something in your last set .. how is a video ad different from the intended media?

bgordon




msg:4052275
 7:30 pm on Dec 31, 2009 (gmt 0)

Originally I was using a generic example but when I posted the MySQL code, I switched to my actual tables... I am showing a video sponsored by coke. I need to precede that video with a coke ad or a generic ad (ie: not pepsi). When I show a pepsi sponsored ad, I need it be precede by a pepsi ad or a generic ad. When I show a GM sponsored ad, I can show coke, pepsi or the generic...

This, ultimately is what I am trying to achieve. I cannot show a pepsi video ad before a coke-sponsored video.

ENetArch




msg:4052291
 7:54 pm on Dec 31, 2009 (gmt 0)

Gordon,

Ok, so the tree that I have above is not complete, and neither are the requirements. I'm a bit lost on the add the video requirement .. does the user click on the ad and it switches to a video, or is the ad replaced by the video after X seconds?

Here's the new object model.

Root
.¦-Brands
.¦..¦-Brand(s)
.¦..¦..¦-Excludes
.¦..¦..¦..¦-Exclusion(s)
.¦..¦..¦-Ads
.¦..¦..¦..¦-Ad(s)
.¦..¦..¦-Videos
.¦..¦..¦..¦-Video(s)

Requirements:
Select 1 or more brands to display on a page
Brand A cannot be displayed with Brand B where ...
... Brand A or Brand B excludes each other
Select 5 Ads from the various brands
Select 5 videos from the various brands

I think something is missing in translation here =)

ENetArch

bgordon




msg:4052292
 7:57 pm on Dec 31, 2009 (gmt 0)

The ad IS a video. I show a video preceded by a 'sponsored' video ad...

ENetArch




msg:4052317
 8:17 pm on Dec 31, 2009 (gmt 0)

Gordon,

Oh, so it's something like Hulu .. this show is brought you to by Tide .. and they show commercials related to Tied =) And of course you wouldn't want a commercial from Arm and Hammer Laundry Detergent in there =) But other commercials for trucks and cloths, and house hold goods would be ok.

ok, so the first tree is correct, all ads are videos.

Root
.¦-Brands
.¦..¦-Brand(s)
.¦..¦..¦-Excludes
.¦..¦..¦..¦-Exclusion(s)
.¦..¦..¦-Ads
.¦..¦..¦..¦-Ad(s)

the requirements though needed to be updated:

Let's stick with one Ad preceeding 1 video for now. Unless you need multiple ads per page.

Requirements:
Select 1 brand to display on a page - (Brand A)
Select a 2nd brand to display on the page - (Brand B)
WHERE ... Brand A does NOT exclude Brand B

Select 1 Ad from the brand's videos
Select 1 Ad from the 2nd brand's videos

How does those requirements work?

ENetArch

The order in which the Ads are run, doesn't matter here.

So,

bgordon




msg:4052352
 9:41 pm on Dec 31, 2009 (gmt 0)

Yep... in this case the videoad is the content of the page. People have chosen to watch the video and I simply need to present a suitable pre-video-ad to show before their selected video begins.

So, the script would request a videoad for brand 52. That is the primary video url loaded. Now, the script selects a pre-roll video to show that is 1) targeted specifically to that brand (52) OR 2) available to any brand AND 3) not excluded from the targeted brand (52) specifically.

ENetArch




msg:4052618
 4:43 pm on Jan 1, 2010 (gmt 0)

Gordon,

Assuming that Brand A is fed into the SQL query ...

SELECT ad.*
FROM ad, exclusions
WHERE
exclusions.brand = "BrandA" AND
exclusions.excludes <> ad.brand

Mind you that that is based on the model that I've been building.

Your thoughts?

ENetArch

bgordon




msg:4052697
 9:36 pm on Jan 1, 2010 (gmt 0)

This makes sense. So the exclusions are are a table to themselves... should there be an inclusions table too then? I guess I was trying to do it both in one table with a value... I guess that is what was causing the havoc?

bgordon




msg:4052800
 4:26 am on Jan 2, 2010 (gmt 0)

I have built a query that seems to work...

Now for the crazy part. It works in PHPMyAdmin but in my php script I get a MYSQL error:

Sql Query Failed: 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

$query = "
SELECT `url` FROM videoads WHERE `id` IN (
SELECT `fk_vad_id` FROM videoads_2_brands
WHERE (`status` = 'Include' AND `fk_brand_id` = $brandID_from_script)
OR (`status` = 'Include' AND `fk_brand_id` = '1398')
AND `fk_vad_id` NOT IN (SELECT `fk_vad_id` FROM videoads_2_brands WHERE `status` = 'Exclude' AND `fk_brand_id` = $brandID_from_script))
GROUP BY `url` ORDER BY RAND() LIMIT 1";

$adresult = @mysql_query($query,$conn) or die("Sql Query Failed: " . "<br/>".mysql_error());

This is the biggest pain in the ass I have worked on yet. Any ideas why PHP->MYSQL would choke on this when it works fine in MYSQL?

ENetArch




msg:4052924
 4:25 pm on Jan 2, 2010 (gmt 0)

Gordon,

Woo Hoo ... you got an answer =)
Glad that I was able to be of service.

Concerning your mySQL script issue ... the problem I usually find that causes language translation issues .. php to Javascript to SQL, to HTMl .... you get the pic .. is QUOTES!

Are you familiar with HEREDOC strings ...

$string = <<<EOT
This is a long string containing quotes " " and single quotes ' '
and php won't care about them becuase it's using <<<eot and eot
to denote were the string begins and ends.

Note that there are rules on it's use .. while <<< can start anywhere,
the ending eot must be on a seperate line of it's own.

EOT;

btw .. I have a chm file for PHP ... [php.net...]

hope this helps.

ENetArch

bgordon




msg:4052937
 5:13 pm on Jan 2, 2010 (gmt 0)

Thanks... the script is ugly so I will try to clean it up with joins instead of the IN statements... I had never heard of EOT... will look into this! Thanks again.

ENetArch




msg:4052987
 6:46 pm on Jan 2, 2010 (gmt 0)

Gordon,

If you need reach me again, [enetarch.net...]

ENetArch

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved