[djnaf]

hello;

i have this table (restcards_tbl) ,
both are primary keys
+--------+--------+
| restid | cardid |
+--------+--------+
| 1 | 1 |
| 1 | 2 |
| 1 | 4 |
| 1 | 6 |
| 15 | 1 |
| 15 | 2 |
| 15 | 3 |
| 15 | 4 |
| 15 | 12 |
+--------+--------+

and this table (cards_tbl)

+--------+------------------+
| CardID | CardName |
+--------+------------------+
| 1 | Visa |
| 2 | Master Card |
| 3 | American Express |
| 4 | GO Card |
| 5 | Golden Card |
| 6 | Green Card |
| 7 | Silver Card |
| 12 | Bronze Card |
+--------+------------------+

every restaurant have an id RESTID.
and CARDID means , the id of the cards accepted in the restaurants.

i want a coide that the user can search for the restaurants that accepts for example
Visa AND MasterCard AND American Express

and it displayes the id for those restaurants!

i tried

PHP Code:

select restid from restcards_tbl where cardid=1 and cardid=2 and cardid=3 

but no results
if any1 has a php code to help me display results i'd be grateful

Thank you

[wiremind]

You should be using OR instead of AND.

Code:

select restid from restcards_tbl where cardid=1 OR cardid=2 OR cardid=3

Or you could do it like this

Code:

select restid from restcards_tbl where cardid IN (1, 2, 3)

[djnaf]

OR won't work , i want all 3 together

and IN will display all restaurants that has 1 alone and 2 and 3
not the restaurant that has 1,2,3 together!

[stormshadow]

Resturants table or w.e.

PHP Code:

CREATE TABLE `resturants` (

`restid` INT( 255 ) NOT NULL AUTO_INCREMENT ,

`Visa` CHAR( 1 ) DEFAULT 'N' NOT NULL ,

`Master Card` CHAR( 1 ) DEFAULT 'N' NOT NULL ,

`American Express` CHAR( 1 ) DEFAULT 'N' NOT NULL ,

`GO Card` CHAR( 1 ) DEFAULT 'N' NOT NULL ,

`Golden Card` CHAR( 1 ) DEFAULT 'N' NOT NULL ,

`Green Card` CHAR( 1 ) DEFAULT 'N' NOT NULL ,

`Silver Card` CHAR( 1 ) DEFAULT 'N' NOT NULL ,

`Bronze Card` CHAR( 1 ) DEFAULT 'N' NOT NULL ,

PRIMARY KEY ( `restid` )

) TYPE = MYISAM ; 

Then when you insert the restraunt change the cc type from N to Y

to find do...

mysql_query("SELECT * FROM resturants WHERE `Silver Card`='Y' && `American Express`='Y'");

That would take all the resturants that accept Silver cards and american express....

I didnt read your whole ? so i hope that is what you are looking for...

[mab]

This query will do it -

$query = 'SELECT restid, COUNT(*) AS cnt FROM restcards_tbl WHERE cardid=1 OR cardid=2 OR cardid=3 GROUP BY restid HAVING cnt = 3';

So what does this do:

1) This part - SELECT restid FROM restcards_tbl WHERE cardid=1 OR cardid=2 OR cardid=3 - gets all of restid rows where cardid is 1, 2, or 3. Note, using AND does not work as any row only has one value for cardid and the AND would require a row to have values of 1 AND 2 AND 3 at the same time, which they cannot.

2) This part - COUNT(*) AS cnt GROUP BY restid - groups the rows by restid and produces a count of the rows in each group called cnt.

3) This part - HAVING cnt = 3 - limits the results to those where cnt = 3 (there were three rows in the result, one for each cardid=) Having a value of 2 would mean only two rows matched, having a value of 1 would mean only one row matched, and zero would mean that there were no rows that matched.

The results produced by this will be a restid followed by the cnt (which will be 3)

some_restid 3
someother_restid 3
another_restid 3
.
.
.
P.S. The above can also use the cardid IN (1, 2, 3) syntax instead of the cardid=1 OR cardid=2 OR cardid=3.

[djnaf]

MAB your amazing , and always helpful
the logic ur talking about is great!

Thanks alot for ur help