Re: [PHP-DB] SQL statement - PHP/mySQL - brain fart
Gurhan: snip I was wondering if you'd wanna use temporary tables to accomplish it.. Actually, another person on this list came up with the following: SELECT s1.site_id FROM site_category AS s1 JOIN site_category AS s2 WHERE s1.category_id=10 AND s2.category_id=12 AND s1.site_id=s2.site_id It worked like a charm! Thanks to all who replied! Steve You may wanna do: CREATE TEMPORARY TABLE tmp1 SELECT * FROM site_category WHERE category_id=$category_id_1; ok this will give us the records that matches $category_id_1 in category_id field put them into a temporary table called tmp1 Then do: CREATE TEMPORARY TABLE tmp2 SELECT * FROM site_category WHERE category_id=$category_id_2; and this will give us the records matching $category_id_2 in category_id field and put them into a temporary table called tmp2.. Now you have 2 temporary tables, tmp1 and tmp2 as shown below: tmp1: +--+--+--+ | sci | si | ci | +--+--+--+ |1 |2 | 10 | |4 |4 | 10 | +--+--+--+ tmp2: +--+--+--+ | sci | si | ci | +--+--+--+ |3 |4 | 12 | |5 |5 | 12 | +--+--+--+ Now you can use use join syntax to find the si value thats common to the both tables... select * from tmp1, tmp2 where tmp1.si=tmp2.si; Does this work for you?? Gurhan -Original Message- From: Summit [mailto:[EMAIL PROTECTED]] Sent: Monday, March 18, 2002 9:22 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] SQL statement - PHP/mySQL - brain fart For some reason I keep thinking that this should be simple - but I just can't seem to figure it out. Help??? Please??? [I've been working on it for two days now.] Overview: What I'm trying to do is query one table by passing it two different variables - and return only the results that are COMMON to both variables. [PHP 4.1.2/mySQL 3.23.44/FreeBSD] Assume I have a site_category table: --- site_category --- site_category_id site_id category_id --- Perhaps a dump of this looks something like this: --- --- site_category_idsite_id category_id --- --- 1 2 10 2 3 11 3 4 12 4 4 10 5 5 12 6 5 14 --- --- Using values for the varibles I'm passing to the query (see below) of ... $category_id_1 = 10 $category_id_2 = 12 ... the result I'm looking for is: site_id = 4 ... as this is the only site_id which is common to both ... category_id = 10 category_id = 12 I've tried a bazillion variations on the following query: SELECT sc.* FROMsite_category sc WHERE (sc.category_id = $category_id_1 OR sc.category_id = $category_id_2) Breaking out the parts ... So, if category_id_1 = 10, I'm returned: site_id = 2 site_id = 4 So, if category_id_2 = 12, I'm returned: site_id = 4 site_id = 5 How can I get that 4 which you can clearly see is common to both of the parts above? But just about no matter how I write my queries, I keep getting: site_id = 2 site_id = 4 site_id = 4 site_id = 5 Or if use SELECT DISTINCT we get: site_id = 2 site_id = 4 site_id = 5 [I want that extra 4 that the DISTINCT threw out!!!] I keep thinking that I can do this in a single query - but I don't know for sure. I've tried sub-selects with no luck [E.g. IN()]. Do I need to do something with arrays and array_intersect? [I've even tried messing with the PHP3 hacks for array_unique - trying to reverse them 'n stuff - but still no luck.] Does anyone have a simple solution? [I'll even take a hard solution - but I keep thinking that I'm just looking at the the wrong way.] TIA, Summit There is no such thing as a stupid person - there are only those who choose not to learn! Summit - [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- -- Peak to Peak Trail and Wilderness Links Steve Fry - LinkKeeper - [EMAIL PROTECTED] http://www.peaktopeak.net
[PHP-DB] SQL statement - PHP/mySQL - brain fart
For some reason I keep thinking that this should be simple - but I just can't seem to figure it out. Help??? Please??? [I've been working on it for two days now.] Overview: What I'm trying to do is query one table by passing it two different variables - and return only the results that are COMMON to both variables. [PHP 4.1.2/mySQL 3.23.44/FreeBSD] Assume I have a site_category table: --- site_category --- site_category_id site_id category_id --- Perhaps a dump of this looks something like this: --- --- site_category_idsite_id category_id --- --- 1 2 10 2 3 11 3 4 12 4 4 10 5 5 12 6 5 14 --- --- Using values for the varibles I'm passing to the query (see below) of ... $category_id_1 = 10 $category_id_2 = 12 ... the result I'm looking for is: site_id = 4 ... as this is the only site_id which is common to both ... category_id = 10 category_id = 12 I've tried a bazillion variations on the following query: SELECT sc.* FROMsite_category sc WHERE (sc.category_id = $category_id_1 OR sc.category_id = $category_id_2) Breaking out the parts ... So, if category_id_1 = 10, I'm returned: site_id = 2 site_id = 4 So, if category_id_2 = 12, I'm returned: site_id = 4 site_id = 5 How can I get that 4 which you can clearly see is common to both of the parts above? But just about no matter how I write my queries, I keep getting: site_id = 2 site_id = 4 site_id = 4 site_id = 5 Or if use SELECT DISTINCT we get: site_id = 2 site_id = 4 site_id = 5 [I want that extra 4 that the DISTINCT threw out!!!] I keep thinking that I can do this in a single query - but I don't know for sure. I've tried sub-selects with no luck [E.g. IN()]. Do I need to do something with arrays and array_intersect? [I've even tried messing with the PHP3 hacks for array_unique - trying to reverse them 'n stuff - but still no luck.] Does anyone have a simple solution? [I'll even take a hard solution - but I keep thinking that I'm just looking at the the wrong way.] TIA, Summit There is no such thing as a stupid person - there are only those who choose not to learn! Summit - [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL statement - PHP/mySQL - brain fart
On Tuesday 19 March 2002 10:22, Summit wrote: For some reason I keep thinking that this should be simple - but I just can't seem to figure it out. Help??? Please??? [I've been working on it for two days now.] Overview: What I'm trying to do is query one table by passing it two different variables - and return only the results that are COMMON to both variables. [PHP 4.1.2/mySQL 3.23.44/FreeBSD] Assume I have a site_category table: --- site_category --- site_category_id site_id category_id --- Perhaps a dump of this looks something like this: --- --- site_category_idsite_id category_id --- --- 1 2 10 2 3 11 3 4 12 4 4 10 5 5 12 6 5 14 --- --- Using values for the varibles I'm passing to the query (see below) of ... $category_id_1 = 10 $category_id_2 = 12 ... the result I'm looking for is: site_id = 4 ... as this is the only site_id which is common to both ... category_id = 10 category_id = 12 I've tried a bazillion variations on the following query: SELECT sc.* FROMsite_category sc WHERE (sc.category_id = $category_id_1 OR sc.category_id = $category_id_2) Breaking out the parts ... So, if category_id_1 = 10, I'm returned: site_id = 2 site_id = 4 So, if category_id_2 = 12, I'm returned: site_id = 4 site_id = 5 How can I get that 4 which you can clearly see is common to both of the parts above? But just about no matter how I write my queries, I keep getting: site_id = 2 site_id = 4 site_id = 4 site_id = 5 Or if use SELECT DISTINCT we get: site_id = 2 site_id = 4 site_id = 5 [I want that extra 4 that the DISTINCT threw out!!!] I keep thinking that I can do this in a single query - but I don't know for sure. I've tried sub-selects with no luck [E.g. IN()]. Do I need to do something with arrays and array_intersect? [I've even tried messing with the PHP3 hacks for array_unique - trying to reverse them 'n stuff - but still no luck.] Does anyone have a simple solution? [I'll even take a hard solution - but I keep thinking that I'm just looking at the the wrong way.] SELECT sc.* FROM site_category sc WHERE sc.category_id = $category_id_1 AND sc.category_id = $category_id_2 Or am I missing something? -- Jason Wong - Gremlins Associates - www.gremlins.com.hk /* Let's just be friends and make no special effort to ever see each other again. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL statement - PHP/mySQL - brain fart
ok it is 1:37 am here and i have been up since 6:00 am (the day before) and have to be awake ths mornign again at 6:00 am so if I make any mistakes trying to answer your question, please take it easy:) I was wondering if you'd wanna use temporary tables to accomplish it.. You may wanna do: CREATE TEMPORARY TABLE tmp1 SELECT * FROM site_category WHERE category_id=$category_id_1; ok this will give us the records that matches $category_id_1 in category_id field put them into a temporary table called tmp1 Then do: CREATE TEMPORARY TABLE tmp2 SELECT * FROM site_category WHERE category_id=$category_id_2; and this will give us the records matching $category_id_2 in category_id field and put them into a temporary table called tmp2.. Now you have 2 temporary tables, tmp1 and tmp2 as shown below: tmp1: +--+--+--+ | sci | si | ci | +--+--+--+ |1 |2 | 10 | |4 |4 | 10 | +--+--+--+ tmp2: +--+--+--+ | sci | si | ci | +--+--+--+ |3 |4 | 12 | |5 |5 | 12 | +--+--+--+ Now you can use use join syntax to find the si value thats common to the both tables... select * from tmp1, tmp2 where tmp1.si=tmp2.si; Does this work for you?? Gurhan -Original Message- From: Summit [mailto:[EMAIL PROTECTED]] Sent: Monday, March 18, 2002 9:22 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] SQL statement - PHP/mySQL - brain fart For some reason I keep thinking that this should be simple - but I just can't seem to figure it out. Help??? Please??? [I've been working on it for two days now.] Overview: What I'm trying to do is query one table by passing it two different variables - and return only the results that are COMMON to both variables. [PHP 4.1.2/mySQL 3.23.44/FreeBSD] Assume I have a site_category table: --- site_category --- site_category_id site_id category_id --- Perhaps a dump of this looks something like this: --- --- site_category_idsite_id category_id --- --- 1 2 10 2 3 11 3 4 12 4 4 10 5 5 12 6 5 14 --- --- Using values for the varibles I'm passing to the query (see below) of ... $category_id_1 = 10 $category_id_2 = 12 ... the result I'm looking for is: site_id = 4 ... as this is the only site_id which is common to both ... category_id = 10 category_id = 12 I've tried a bazillion variations on the following query: SELECT sc.* FROMsite_category sc WHERE (sc.category_id = $category_id_1 OR sc.category_id = $category_id_2) Breaking out the parts ... So, if category_id_1 = 10, I'm returned: site_id = 2 site_id = 4 So, if category_id_2 = 12, I'm returned: site_id = 4 site_id = 5 How can I get that 4 which you can clearly see is common to both of the parts above? But just about no matter how I write my queries, I keep getting: site_id = 2 site_id = 4 site_id = 4 site_id = 5 Or if use SELECT DISTINCT we get: site_id = 2 site_id = 4 site_id = 5 [I want that extra 4 that the DISTINCT threw out!!!] I keep thinking that I can do this in a single query - but I don't know for sure. I've tried sub-selects with no luck [E.g. IN()]. Do I need to do something with arrays and array_intersect? [I've even tried messing with the PHP3 hacks for array_unique - trying to reverse them 'n stuff - but still no luck.] Does anyone have a simple solution? [I'll even take a hard solution - but I keep thinking that I'm just looking at the the wrong way.] TIA, Summit There is no such thing as a stupid person - there are only those who choose not to learn! Summit - [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL statement - PHP/mySQL - brain fart
Well it's sorta' simple once you get your mind around this - you need two tables but you've only got one. And there's no OR, you need both to be true for a site_id ... The query would be easy if you actually had two tables so we'll search off of the same table twice giving it different names each time (sc1 and sc2). SELECT sc1.* FROMsite_category sc1, site_category sc2 WHERE sc1.category_id = $category_id_1 AND sc2.category_id = $category_id_2 AND sc1.site_id = sc2.site_id DISTINCT and such are not necessary unless it's possible you'll have duplicate rows (and you'd mind getting them back multiple times). I know this will work in all the big ones (Oracle, MS SQL, IBM) but I'm less sure about MySQL as I don't use it all that much. This is standard SQL. Good Luck, Frank On 3/18/02 10:53 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: From: Summit [EMAIL PROTECTED] Date: Mon, 18 Mar 2002 19:22:22 -0700 To: [EMAIL PROTECTED] Subject: SQL statement - PHP/mySQL - brain fart For some reason I keep thinking that this should be simple - but I just can't seem to figure it out. Help??? Please??? [I've been working on it for two days now.] Overview: What I'm trying to do is query one table by passing it two different variables - and return only the results that are COMMON to both variables. [PHP 4.1.2/mySQL 3.23.44/FreeBSD] Assume I have a site_category table: --- site_category --- site_category_id site_id category_id --- Perhaps a dump of this looks something like this: --- --- site_category_idsite_id category_id --- --- 1 2 10 2 3 11 3 4 12 4 4 10 5 5 12 6 5 14 --- --- Using values for the varibles I'm passing to the query (see below) of ... $category_id_1 = 10 $category_id_2 = 12 ... the result I'm looking for is: site_id = 4 ... as this is the only site_id which is common to both ... category_id = 10 category_id = 12 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
