How can I create this sql query?

[thara]

hi.. everyone..

 

This is a sql query problem that I have uncounted and have been in the same script for hours. But Still I couldn't get it to work.

 

I need to get a image from database and its type should be one of a value from my $designation array. The query should check $designation array's value with database and if its match with one of a value in database then query can retrieve one row.

 

This is my designation array. its value I use for image type in my db.

 

	
$designation = array ( 'Managing Director', 'Manager', 'Director', 'The Principal', 'Deputy Principal', 'Proprietor', 'Assistant Manager', 'Head Master');

 

My problem is how can I check these value exit in my table and how can I make a query for this. In query where condition is confusing to me...

 

any comments are greatly appreciated.

Thank you..

 

 

[Barand]

SELECT .... FROM mytablename
WHERE somecolumn IN ( 'Managing Director', 'Manager', 'Director', 'The Principal', 'Deputy Principal', 'Proprietor', 'Assistant Manager', 'Head Master')

[Maq]

Going off Barand's example, with implode and a bit of string manipulation you can do:

$designation = array ( 'Managing Director', 'Manager', 'Director', 'The Principal', 'Deputy Principal', 'Proprietor', 'Assistant Manager', 'Head Master');
$sql = "SELECT .... FROM mytablename WHERE somecolumn IN('" . str_replace(',', '\', \'', implode(',', $designation)) . "')";
echo $sql; 

 

Output:

SELECT .... FROM mytablename WHERE somecolumn IN('Managing Director', 'Manager', 'Director', 'The Principal', 'Deputy Principal', 'Proprietor', 'Assistant Manager', 'Head Master')

 

 

EDIT: This is probably easier to read (replaced single quotes in function with double quotes):

$sql = "SELECT .... FROM mytablename WHERE somecolumn IN('" . str_replace(",", "', '", implode(",", $designation)) . "')";

[Psycho]

I'm curious. If you have a defined list of image_types then would you have records in your image table that do not have one of those defined types? Or, are the types in the array a subset of all the types (that would make sense)?

 

Also, having a hard-coded array of types will make any changes much harder to implement and it will have a greater potential for bugs since a single typo in one type can go unnoticed unless you did testing for each and every type. A better solution, IMHO, is to have a separate table for the types and then use the id for those types in your current table.

 

Assuming the list above is only a subset of all the types that you want to use for that particular query, the types table might need a field to identify that subset. Here's an example

id | type             | admin
1   Managing Director  1
2   Manager            1
3   Director           1
4   The Principal      1
5   Teacher            0
6   Custodian          0

 

Then using the same type of logic Barand provided you can simplify things and, more importantly, you can add/delete types without any changes needed to the core code

SELECT .... FROM mytablename
WHERE type IN (SELECT id FROM types WHERE admin=1)

[thara]

Psycho's idea was very imported for me do my job. In common, Thanks for all 3 replies. Its greatly appreciated.