CODEWITHSUNDEEP
phpmysqlsqlselect
bearfriend
bearfriend

Reputation: 10421

SQL: Select based on related_id and related_table. ID's stored in array named for table

I have a table called activities that looks like this

id | related_id | related_table | ...

And 3 arrays, each named after a table, containing ID's:

$clubs[1,2,3]; $vendors[4,5,6]; $users[7,8,9];

How do I select the activities that belong to the corresponding clubs, vendors, and users in the arrays? I only know very simple SQL, but am sure there is a way to do this.

I am looking to do this in 1 select statement, if possible. Otherwise I know how to do "where in" to do it in 3.

ANSWER:

Here is how I accomplished getting all the activites in 1 select:

'SELECT * from activities where (related_table = \'clubs\' and related_id in ('.$clubs_string.')) or  (related_table = \'vendors\' and related_id in ('.$vendors_string.')) or (related_table = \'users\' and related_id in ('.$users_string.'));';

See Morgan Wilde's answer about implode()-ing the arrays to strings.

Upvotes: 2

Views: 158

Answers (1)

Morgan Wilde
Morgan Wilde

Reputation: 17323

Well if I've got your question right, the way to find all activities that relate to club id's saved in an array should be...

$clubs_string = implode(",", $clubs); // convert your array into a comma separated string

Once you've got that sorted, send a query like

SELECT `id` FROM `activities` WHERE `related_id` IN ({$clubs_string}) AND `related_table` = 'club_table_name'

You can repeat the same logic for all other table types that you have.

Here you can find some neat MySQL WHERE IN examples - http://www.tutorialspoint.com/mysql/mysql-in-clause.htm

Hope that helps!

Upvotes: 1

Related Questions