Mysql query

[swedguy]

My mind is completely blank right now, so please help me out here

Table:

sponsor | keyword | banner
sponsor1 | horizontal | banner1.jpg
sponsor1 | vertical | banner2.jpg
sponsor1 | button | banner3.jpg
sponsor2 | horizontal | banner4.jpg
sponsor2 | vertical | banner5.jpg
sponsor3 | horizontal | banner6.jpg
sponsor3 | vertical | banner7.jpg
sponsor3 | small | banner8.jpg

Sponsor1 has: horizontal, vertical and button banners
Sponsor2 has: horizontal, vertical
Sponsor3 has: horizontal, vertical and small

Now I want to make a query that gets me this info:
" Find a sponsor that has horizontal and vertical and (button or small) "

I made a quick and dirty to find sponsor that has horizontal and vertical and ..... banners.

SELECT COUNT(DISTINCT keyword) from table WHERE keyword LIKE 'horizontal' OR keyword LIKE 'vertical'

If I was looking for 2 kinds of banners and the query returned a 2, BINGO!

I'm at a loss how to do it differently. So if anyone got a different way and a solution to the first query, I'm all ears

[swedguy]

I think I might've solved it.

UNION !

Why have I never used that before?

[ronnie]

SELECT count
FROM table
WHERE keyword = 'horizontal' AND keyword = 'vertical' AND (keyword = 'small' OR keyword = 'button')

That is to find only sponsors that have horz and vert and butt/small baners. It must match all three conditions to be a match. Is that what you are looking for? Not sure why your using UNION, that is usually for selecting from different tables, if I remember right. Also LIKE is use more for a wildcard type select with % as the wild card.

ronnie

[swedguy]

Originally Posted by ronnie

SELECT count
FROM table
WHERE keyword = 'horizontal' AND keyword = 'vertical' AND (keyword = 'small' OR keyword = 'button')

That is to find only sponsors that have horz and vert and butt/small baners. It must match all three conditions to be a match. Is that what you are looking for? Not sure why your using UNION, that is usually for selecting from different tables, if I remember right. Also LIKE is use more for a wildcard type select with % as the wild card.

ronnie

That query will only match on a row, not a whole table. So it will look for a row that has keyword set to horizontal, vertical and (small or button). Which doesn't really work, keyword can only be set to one value in a row
If I would have 3 keyword fields: keyword1, keyword2, keyword3 then I could do it that way.

And LIKE is just an old habit I have since the Sybase days. Mysql isn't as picky when it comes to =/LIKE, if you just put it in quotes it will treat it as a string. But for standard SQL, LIKE is for strings and = is for numerics.

[ronnie]

Thought you were trying to match rows, not tables, that would help.. If multiple tables, JOIN might be a solution. Unless you already have it figured out.

As for LIKE, guess I am old school, proper coding so I dont run in to trouble later.

ronnie

[swedguy]

Originally Posted by ronnie

As for LIKE, guess I am old school, proper coding so I dont run in to trouble later.

Exactly, that's why I stick to LIKE

I have it pretty much figured out, but it's in 2 queries.

#1. Get all sponsors
#2. Check if sponsor has the type of banners I'm looking for


SELECT 3 = COUNT(*) FROM (
SELECT DISTINCT 1 FROM banners WHERE sponsor LIKE 'sponsor1' AND keyword LIKE 'horizontal'
UNION ALL
SELECT DISTINCT 1 FROM banners WHERE sponsor LIKE 'sponsor1' AND keyword LIKE 'vertical'
UNION ALL
SELECT DISTINCT 1 FROM banners WHERE sponsor LIKE 'sponsor1' AND (keyword LIKE 'small' OR keyword LIKE 'button')
) AS tbl

3 is how many different banners I'm looking for. I just wish I could get rid of step #1 and just make it into one query.

[cd34]

remove the first query and read mysql_num_rows() ?

[ronnie]

Hey, if it works, thats all that counts. Kinda confusing, dont know you db layout, ect, not that I want to know.

I try to keep all my stuff in one table at a time, sure makes queries easier..

ronnie

[swedguy]

Originally Posted by ronnie

Hey, if it works, thats all that counts. Kinda confusing, dont know you db layout, ect, not that I want to know.

I try to keep all my stuff in one table at a time, sure makes queries easier..

ronnie

It works, but it bugs me that I have to do it in two steps. First get the sponsors, then go through each sponsor and check if they have the banners I'm looking for.

Which means: If I have 100 sponsors = 101 queries.

[swedguy]

Originally Posted by cd34

remove the first query and read mysql_num_rows() ?

Same result. But I still have to do the first query, the one to get the sponsors.

[swedguy]

Here's what the full query looks like:

PHP Code:

$uniq_banners = 3;
$query = 'SELECT '.$uniq_banners.' = COUNT(*) FROM ( 
SELECT DISTINCT 1 FROM banners WHERE sponsor LIKE "$sponsor" AND keyword LIKE "horizontal" 
UNION ALL 
SELECT DISTINCT 1 FROM banners WHERE sponsor LIKE "$sponsor" AND keyword LIKE "vertical" 
UNION ALL 
SELECT DISTINCT 1 FROM banners WHERE sponsor LIKE "$sponsor" AND (keyword LIKE "small" OR keyword LIKE "button") 
) AS tbl';

$result = mysql_query("SELECT DISTINCT sponsor FROM table");
$myrow = mysql_fetch_array($result);
if ($myrow and @mysql_num_rows($result)) {
  do {
    $sponsor = $myrow["sponsor"];
    eval("\$use_query = \"$query\";");
    $result2 = mysql_query($use_query);
    $res = mysql_result($result2, 0);
    if ($res == 1)
      // We got a sponsor that has the correct banners
      ;
  } while($myrow = mysql_fetch_array($result));
} 


[cd34]

mysql> create temporary table blah SELECT sponsor FROM banners group by sponsor,if(keyword='horizontal','H',if(keyword='vertical','V','O'));
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select sponsor,count(*) from blah group by sponsor;
+----------+----------+
| sponsor | count(*) |
+----------+----------+
| sponsor1 | 3 |
| sponsor2 | 2 |
+----------+----------+
2 rows in set (0.00 sec)

Is that what you want?

[swedguy]

Hmm, let me give that a try.

[swedguy]

F I N A L L Y !


SELECT sponsor, COUNT(keyword) AS cnt FROM (
SELECT sponsor, keyword FROM banners WHERE keyword LIKE 'horizontal'
UNION
SELECT sponsor, keyword FROM banners WHERE keyword LIKE 'vertical'
UNION
SELECT sponsor, keyword FROM banners WHERE (keyword LIKE 'small' OR keyword LIKE 'button')
) AS tbl
GROUP BY sponsor HAVING cnt = 3


1 query

Thanks Sparky, you got me on the right track there with the count(). Thanks

[raymor]

Wow yall went through some contortions
on a pretty straightforward query.
It's called a self join.
Oh, and BTW, like is for pattern matching.
You want to use = when looking for
exact matches.
The query is kind of long just because the question itself
is long, with several parts, but it's very simple.
To understand how it works, let's first just
see how to get sponsors with either horizontal or vertical:

SELECT  sponsors.sponsor 
FROM sponsors, sponsors AS sponsors_h 
WHERE sponsors.keyword='vertical' AND
sponsors_h.keyword='horizontal' AND
sponsors.sponsor=sponsors_h.sponsor

Throw a "distinct" in there if you have a silly RDMS
that doesn't assume distinct by default.
So we've seen how to do a self join, that is
how to join a table to itself, which is just
another case of relational multiplication (cartesian product) like any other.
Now we just need to multiply by a third relation
to get the "button or small" part:

SELECT  sponsors.sponsor 
FROM sponsors, sponsors AS sponsors_h,
sponsors_b
WHERE sponsors.keyword='vertical' AND
sponsors_h.keyword='horizontal' AND
(
   sponsors_b.keyword='small' OR
   sponsors_b.keyword='button'
) AND
sponsors.sponsor=sponsors_h.sponsor AND
sponsors_b.sponsor=sponsors.sponsor

As always, the above code is off the top of my head,
untested
and my contain typos or logical errors.
(Fully tested code requires that I test it on your system.)

[swedguy]

Hi Raymor,

It worked to get 1 type of banner, but when I tried with 2 it returned nothing. I'll play around with it a little bit.

Thanks

[raymor]

Rather than just returning it should have given you an error.
I used a non-existant table because I accidently
tried to select from just sponsors_b instead
of saying "sponsors AS sponsors_b".
Correct code is:

SELECT DISTINCT sponsors.sponsor 
FROM sponsors, sponsors AS sponsors_h,
sponsors AS sponsors_b
WHERE sponsors.keyword='vertical' AND
sponsors_h.keyword='horizontal' AND
(
   sponsors_b.keyword='small' OR
   sponsors_b.keyword='button'
) AND
sponsors.sponsor=sponsors_h.sponsor AND
sponsors_b.sponsor=sponsors.sponsor;

[swedguy]

Niiice. Now it's working, I'm not sure what I made wrong before, but it works now.

The query is heck of a lot faster than having a bunch of sub-queries. Thanks man