Setting up SQL queries with multiple parameters

Question

I need to set up a SQL query with multiple parameters that are being pulled from the URL. So far I can only get it to work with the there is only one item in the URL.

My default query to pull in all the content

$sql = "SELECT "; 
$sql .= "* ";
$sql .= "FROM ";
$sql .= "cms_site_content ";
$sql .= "WHERE ";
$sql .= "1";

I then check if anything was passed through the URL and retrieve it.

if (isset($_GET["d"])) {
$d=$_GET["d"];

Inside the if statement, I break the values passed as "d" into separate items

$newD = explode(',',$d);
$countD = count($newD);


foreach($newD as $discipline) {

if ($countD == 1) {
            $sql .= " AND";
    $sql .= " discipline='".$discipline."'";
}

My problem is getting the SQL to work if there is more than one discipline value. It should read something like this:

SELECT * FROM cms_site_content WHERE 1 AND discipline="value"

however if there's more than one discipline value, it should read:

SELECT * FROM cms_site_content WHERE 1 AND discipline="value OR discipline="value2" OR discipline="value3"

Is there a more efficient way to write this? I can't figure out how to insert the OR into the foreach statement.

There are great libraries and frameworks out there that does all this for you including the security, why reinvent the wheel? — jtheman
– jtheman, Commented Feb 10, 2014 at 21:13
You should read how to use PDO with MySQL : php.net/manual/en/pdo.prepare.php — sdespont
– sdespont, Commented Feb 10, 2014 at 21:14
You can use round braces to make 'blocks' of checks (SELECT * FROM cms_site_content WHERE 1 AND (discipline = "value" OR discipline = "value2")). I suggest removing the 1 AND block to make it easier for yourself, or even use a framework/library that does the job for you. — Diamondo25
– Diamondo25, Commented Feb 10, 2014 at 21:16
Can you recommend any simple libraries to look into? I just started learning PHP and I'm not sure where to begin — user2989731
– user2989731, Commented Feb 10, 2014 at 21:25
Using a library for something like this is an overkill in my opinion. At the end of the day, SQL queries are simply strings. All you need is a bit of code to generate the string containing multiple conditions i.e. discipline = 'option1' OR discipline = 'option2' OR discipline = 'option3', etc. see answer below. — AnchovyLegend
– AnchovyLegend, Commented Feb 10, 2014 at 21:29

Community · Accepted Answer · 2023-11-17 20:20:27Z

2

Save all discipline values in an array;

$discipline_arr = array();
foreach($newD as $discipline) {

    $discipline_arr[] = $discipline; 
    // by the way, don't forget to escape for sql injection 
    // mysql_escape_string is the depracated one, u can use that if u have no 
    // other choice 
    
            
}

Then in your sql, add them as discipline in ('value1','value2', 'etc ...') condition (that is for strings, for numeric types it would be like discipline in (1,2,3,4, etc)

$sql = " SELECT * FROM cms_site_content WHERE 1 " . 
    (empty($discipline_arr) ? "" : "and 
         discipline in ('". implode("','" , $discipline_arr). "') ") ;

Link to escaping https://www.php.net/manual/en/function.mysql-escape-string.php

edited Nov 17, 2023 at 20:20

CommunityBot

11 silver badge

answered Feb 10, 2014 at 21:25

Nihat

3,1053 gold badges20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AnchovyLegend Over a year ago

+1, Good job, I like your response better. Didn't know you can use in() like that.

Nihat Over a year ago

Thanks. By the way, I saw this on your profile "10 kinds of people in the world, those who understand binary and those who don't." very clever

AnchovyLegend · Accepted Answer · 2014-02-10 21:46:52Z

1

Assuming the rest of your query is in tact. Simply store all of your discipline values in an array as follows, then feed the $discipline_string to your $sql query:

$discipline_ary = array('option1', 'option2', 'option3');
$discipline_string = "";

for($i=0; $i < count($discipline_ary); $i++){
    $discipline_string .= " discipline = '" . $discipline[$i] . "' ";

    if($i+1 == count($discipline_ary)){
       break;
    }else{
       $discipline_string .= " OR "
    }
}

edited Feb 10, 2014 at 21:46

answered Feb 10, 2014 at 21:18

AnchovyLegend

12.6k41 gold badges155 silver badges240 bronze badges

Collectives™ on Stack Overflow

Setting up SQL queries with multiple parameters

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related