0

I'm trying to create an SQL SELECT statement on my PHP file which can select all rows that have an unknown number of different values in one of the column. To explain better I'll say the exact situation. I have three tables in my database; tests, categories, sections. Each test belongs to a category, there are around 50 categories, each category belongs to a section, there are 10 sections. However, in my tests table, it only has a column for the category it belongs to. I now want to be able to display all tests within a section.

So to make things clearer:

SELECT * FROM tests WHERE category = '$categoryId' ORDER BY id ASC

This lets me select all tests in a particular category, but I want to make a loop of some sort, where, depending on which section is selected, the category id's are put in this statement separated by OR. Thank you.

Omair Vaiyani
  • 552
  • 7
  • 28
  • Do that before. Create your OR statement before the actual query and assign the query string to the variable categoryIds then just do `SELECT * FROM tests '.$categoryIds.' ORDER BY id ASC` – user1048676 Apr 20 '13 at 20:01
  • And, please don't insert the categoryId variable into the SQL statement like that. Look at this question for why: http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work – Eric Jablow Apr 20 '13 at 20:18

3 Answers3

2
SELECT t.* 
FROM tests t
    JOIN category c on t.category = c.id AND c.section = ' . $section . '
ORDER BY t.id ASC

This is to show you the way, in your production environment you should sanitize the $section parameter.

The idea is to join tables together to take advantage of the powerful relational database features over programmatic loops.

Internally, the RDBMS is doing loops for you, calculating intermediate resulset of each JOINed table.

Sebas
  • 21,192
  • 9
  • 55
  • 109
0

If you want to select all tests in one section then you should not use the or statement on category id but should use join on these tables: something like:

SELECT tests.* FROM (tests JOIN categories ON tests.category = categories.id) WHERE categories.section = $section

where $section is the id of section you want to select tests. You can also specify columns to use instead of * to select only those you are interested in. $section should be escaped to be sure that it cannot be injected.

I do not know your table schema but when you have relations like these you should have foreign keys in these tables and the query should be fast and you need only one! This is how to perform queries on data model as relations are stored right in the DB.

kuncajs
  • 1,096
  • 1
  • 9
  • 20
0

use special character for Example pipe symbole | and do this :

   $str="|".$cat1."|".$cat2."|".$cat3+"|";

and use Like condition :

SELECT * FROM tests WHERE $str like '%|'+cast(category as varchar) +'|%' oRDER BY id ASC

mojtaba
  • 339
  • 1
  • 4
  • 17