[SOLVED] Probably a simple query but still a problem.

[karlblau]

Hey!

Its been a while since i had to deal with mysql and now i have a problem that i cant seem to sort out, it is probably werry basic stuff though =)

i have 2 tables both are a copy of each other the tables are called : main_table and play_table.

main_table contains allot of information and play_table is empty but with the same structure.

 

Tables look like this.

+--------+--------------+------+-----+---------+----------------+

| Field  | Type         | Null | Key | Default | Extra          |

+--------+--------------+------+-----+---------+----------------+

| id     | bigint(255)  |      | PRI | NULL    | auto_increment |

| artist | varchar(255) | YES  |     | NULL    |                |

| song   | varchar(255) | YES  |     | NULL    |                |

| link   | varchar(255) | YES  |     | NULL    |                |

| genre  | varchar(255) | YES  |     | NULL    |                |

| year   | varchar(64)  | YES  |     | NULL    |                |

| album  | varchar(255) | YES  |     | NULL    |                |

| prio   | int(64)      | YES  |     | NULL    |                |

+--------+--------------+------+-----+---------+----------------+

8 rows in set (0.00 sec)

 

So to the problem.

Basicly the user via a html form run a search query on the main_table that looks like this:

$query = "SELECT * FROM main_table where artist like '%$sartist%' and song like '%$ssong%' and genre like '%$sgenre%' and year like '%$syear%' limit 1000";

 

The user is presented with this information and is availible to choose which track(s) he/she(me) want to listen to

via a form. when user has choosen the track(s) the id(s) of the choosen track(s) are passed on to a new form that is supposed to take the incoming http get data(id) and do a search in the main db for those id(s).

so that later on we can insert them in to the play_table.

I have tryed every possible way i can think of but i just cant get this search query to work.

the query that i have tryed is

 

" SELECT * from main_table where id=ID1 and id=ID2 etc..

 

but that wont work.. what am i doing wrong??

 

 

 

 

[Illusion]

what u r getting 0 search results or any error.

[karlblau]

Yeah.. that just it...

Getting 0 results

[Illusion]

try this

 

$query=SELECT * FROM main_table where artist like '%".$sartist."%' and song like '%".$ssong."%' and genre like '%".$sgenre."%' and year like '%".$syear."%' limit 1000";

[karlblau]

Maybe i sould clarify this a bit more..

The first query i dont have any problems with.

It the second one, the one that actully wants to select certain $id from the database

 

[Illusion]

try this

 

SELECT * from main_table where id='$ID1' UNION SELECT * from main_table where id='$ID2'

 

ur query SELECT * from main_table where id=ID1 and id=ID2 id column is integer so u can't compare strings ID1 and ID2. if they are variables use above query.

[karlblau]

Still wont do the job.. i tried it before with both " and '

 

mysql> select * from main_db where id="144";

+-----+-------------------+----------------------------+---------------------------------------------------------------------------+-------+------+-----------------+------+

| id  | artist            | song                      | link                                                                      | genre | year | album          | prio |

+-----+-------------------+----------------------------+---------------------------------------------------------------------------+-------+------+-----------------+------+

| 144 | The Nightgroovers | Do it All Night (SP Vocal) | alltracks/the_nightgroovers-do_it_all_night_soul_providers_vocal_mix-.mp3 | House | 2002 | Do it All Night | NULL |

+-----+-------------------+----------------------------+---------------------------------------------------------------------------+-------+------+-----------------+------+

1 row in set (0.00 sec)

 

mysql> select * from main_db where id="143";

+-----+---------------------------+-------------------------------+----------------------------------------------------------------------------+-------+------+-------------------------------+------+

| id  | artist                    | song                          | link                                                                      | genre | year | album                        | prio |

+-----+---------------------------+-------------------------------+----------------------------------------------------------------------------+-------+------+-------------------------------+------+

| 143 | The Committee & Richard F | Scream & Shout (Vincenzos Clu | alltracks/the_committee_and_richard_f-scream_and_shout-vincenzos_club-.mp3 | House | 2002 | Scream & Shout & Cookie Dough | NULL |

+-----+---------------------------+-------------------------------+----------------------------------------------------------------------------+-------+------+-------------------------------+------+

1 row in set (0.00 sec)

 

mysql> select * from main_db where id="143" and id ="144";

Empty set (0.00 sec)

 

mysql> select * from main_db where id='143' and id ='144';

Empty set (0.00 sec)

 

mysql> select * from main_db where id=143 and id =144;

Empty set (0.00 sec)

 

mysql>

 

[radalin]

well I assume that id is the pk. So

 

SELECT * FROM foo WHERE id = 1 AND id = 2

 

will not return anything, because primary keys can only be unique values. And there is no way that id can be both 1 and 2

 

Try this one:

 

select * from main_db where id="143" OR id ="144"

 

This should do the trick.

 

But I recommend:

 

select * from main_db where id IN (143,144,145)

[karlblau]

Whooohoo.. Thanks!.. i remember now =)

Of course its where id IN ()...

Thanks werry much!!

 

mysql> select * from main_db where id IN (143,144,145);

+-----+---------------------------+-------------------------------+----------------------------------------------------------------------------+-------+------+-------------------------------+------+

| id  | artist                    | song                          | link                                                                      | genre | year | album                        | prio |

+-----+---------------------------+-------------------------------+----------------------------------------------------------------------------+-------+------+-------------------------------+------+

| 143 | The Committee & Richard F | Scream & Shout (Vincenzos Clu | alltracks/the_committee_and_richard_f-scream_and_shout-vincenzos_club-.mp3 | House | 2002 | Scream & Shout & Cookie Dough | NULL |

| 144 | The Nightgroovers        | Do it All Night (SP Vocal)    | alltracks/the_nightgroovers-do_it_all_night_soul_providers_vocal_mix-.mp3  | House | 2002 | Do it All Night              | NULL |

+-----+---------------------------+-------------------------------+----------------------------------------------------------------------------+-------+------+-------------------------------+------+

2 rows in set (0.24 sec)

 

mysql>