dynamic sql query php with AND

Question

I have some sql code:

    $id = "2000";
    $model = $_GET["model"]

    $result = mysql_query("SELECT
    Left(Right(Base.inputs.Data_inf,4),3) As 'Datas',
    count(Base.Data.Data_ID) As `U_Count`
    FROM
        Base.Data
    WHERE
        Product = '$id'
        AND model in ('a','b','c')
    GROUP BY ");

I would like to make AND part of query to be dynamic. Something like this:

$m= $model;
switch ($m)
{
    case "basic":
        $m = "AND model in ('a','b','c')";
        break;

    case "upgrade":
        $m = "AND model in ('d','e','f')";
        break;

    default:
        $m = "AND model in ('a','b','c')";
}

I put $m in the query and it doesn't work:

WHERE
      Product = '$id'
      '$m'

Any suggestion will be appreciated.

score 2 · Accepted Answer · 2013-10-29 12:57:50Z

2

You should use OR operator instead of AND, or better to use IN statement. Because I guess model can't at the same time be equal to a and b, it's impossible So part of your code should look like this:

$m = " AND model in ('a','b','c')";

EDIT: You can simplify your code something like this:

$id = "2000";
$model = $_GET["model"]



$m= $model;
switch ($m)
{
    case "upgrade":
    $mIN = "'d','e','c'";
        break;

    case "basic":
    default:
    $mIN = "'a','b','c'";
        break;
}


$result = mysql_query("SELECT
Left(Right(Base.inputs.Data_inf,4),3) As 'Datas',
count(Base.Data.Data_ID) As `U_Count`
FROM
    Base.Data
WHERE
    Product = '$id'
    AND model in ($mIn)
GROUP BY ");

edited Oct 29, 2013 at 12:57

answered Oct 29, 2013 at 12:52

user2768948

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

Comments

Ashalynd · Accepted Answer · 2013-10-29 13:13:10Z

1

Remove the quotes around $m. Also, add spaces and replace AND with the right condition:

$m= $model;
switch ($m)
{
    case "basic":
        $m = " AND model in ('a','b','c') ";
        break;

    case "upgrade":
        $m = " AND model in ('d','e','f') ";
        break;

    default:
        $m = " AND model in ('a','b','c') ";
}

And then do:

$result = mysql_query("SELECT
Left(Right(Base.inputs.Data_inf,4),3) As 'Datas',
count(Base.Data.Data_ID) As `U_Count`
FROM
    Base.Data
WHERE
    Product = '$id'
    $m ");

edited Oct 29, 2013 at 13:13

answered Oct 29, 2013 at 12:47

Ashalynd

12.6k2 gold badges36 silver badges38 bronze badges

1 Comment

Ashalynd Over a year ago

you're welcome :) But you should indeed replace AND with the right condition to select any of these variants, otherwise it won't work, because one field can't be equal to all of these values at the same time. Going to edit my answer to reflect that.

Adrian Sullivan · Accepted Answer · 2013-10-29 12:58:34Z

You can also create your SQL as a string before passing it through. If also makes it easier to debug your query prior to running it.

    $querytop = "SELECT
        Left(Right(Base.inputs.Data_inf,4),3) As 'Datas',
        count(Base.Data.Data_ID) As `U_Count`
        FROM
            Base.Data
        WHERE
            Product = '$id'";

    $m= $model;
    switch ($m)
    {
        case "basic":
            $m = " AND model = 'a'
        AND model = 'b'
        AND model = 'c' ";
            break;

        case "upgrade":
            $m = " AND model = 'd'
        AND model = 'e'
        AND model = 'f' ";
            break;

        default:
            $m = " AND model = 'a'
        AND model = 'b'
        AND model = 'c' ";
    }        
    $sqlquery = $querytop . $m;
    $result = mysql_query("$sqlquery");

Collectives™ on Stack Overflow

dynamic sql query php with AND

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related