creating a sql query dynamically

Question

I want to create sql queries dynamically depending upon the data I receive from the user.

Code:

$test = $_POST['clientData']; //It can be an array of values
count($test); //This can be 2 or 3 or any number depending upon the user input at the client

$query = "select * from testTable where testData = ".$test[0]." and testData = ".$test[1]." and . . .[This would vary depending upon the user input]"

Is it possible to achieve the above scenario. I am relatively new in this area.Your guidance would be helpful.

Yes.I am firing sql queries via PHP.Just added PHP tag.Thanks for that. — Ravikanth
– Ravikanth, Commented Oct 23, 2013 at 12:41

Squirrel5853 · Accepted Answer · 2013-10-23 12:52:08Z

1

Use:

<?php

$test=$_POST['clientData'];//It can be an array of values

$query = "select *from testtable where 1 ";
foreach($test as $value) {
    $query .= " AND testData='" . $value . "'";
}

echo $query;

?>

edited Oct 23, 2013 at 12:52

Squirrel5853

2,4061 gold badge19 silver badges35 bronze badges

answered Oct 23, 2013 at 12:46

MD SHAHIDUL ISLAM

14.5k6 gold badges84 silver badges93 bronze badges

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

Comments

Savv · Accepted Answer · 2013-10-23 12:45:38Z

1

Use prepared statements:

$query = $dbh->prepare("SELECT * FROM testtable WHERE testData=:test0 and testData=:test1");
$query ->bindParam(':test0', $test0);
$query ->bindParam(':test1', $test0);

$test0 = $test[0];
$test1 = $test[1];

$query->execute();

answered Oct 23, 2013 at 12:45

Savv

4312 silver badges7 bronze badges

Comments

Alessio Nobile · Accepted Answer · 2013-10-23 12:48:42Z

1

Rishi that's a very long chapter.

If you want to search into a single field then you can try to do:

<?php
$test = $_POST[ 'clientData' ];
if( is_array( $test ) ){
    $select = implode( ",", $test );
} else { 
    $select = $test;

}

$query=select *from testtable where testData IN ( $select );
?>

This is valid only for searches into a specific field. If you want to create searches on multiple fields then you need to do a lot of more work, having an associative mapping which can create a relation variable name -> field_to_search

answered Oct 23, 2013 at 12:48

Alessio Nobile

412 bronze badges

Comments

Morteza Sepehri Niya · Accepted Answer · 2013-10-23 12:50:08Z

0

$data = $_POST['data'];

$query = "SELECT";

if ( is_set($data['columns']) )
   $query .= " ".implode(',',$data['columns']);
else
   $query .= "*";

if ( is_set($data['table']) )
$query .= " ".$data['table'];

and ...

answered Oct 23, 2013 at 12:50

Morteza Sepehri Niya

4147 silver badges13 bronze badges

Comments

Squirrel5853 · Accepted Answer · 2013-10-23 12:50:10Z

0

This is very much pseudo code as I don't really know PHP, but could you not do something like this

$query = "select * from testable";

$count = count($test);

if($count  > 0)
{ 
    $query .= " where ";

    for ($x=0; $x<=$count; $x++)
    {
        if($x > 0)
        {
              $query .= " and ";
        }

        $query .= " testData='" . $test[x] . "'";
    } 
}

edited Oct 23, 2013 at 12:50

answered Oct 23, 2013 at 12:44

Squirrel5853

2,4061 gold badge19 silver badges35 bronze badges

Comments

Vijay Verma · Accepted Answer · 2013-10-23 12:55:07Z

0

$test=$_POST['clientData'];
$query="select * from testtable where testData='".$test[0]."' and testData='".$test[1]."' and . . .[This would vary depending upon the user input]";
$result = mysql_query($query);

edited Oct 23, 2013 at 12:55

answered Oct 23, 2013 at 12:46

Vijay Verma

3,7082 gold badges21 silver badges27 bronze badges

Comments

Leo Bali · Accepted Answer · 2013-10-23 12:55:22Z

0

$test=$_POST['clientData'];//It can be an array of values
    $dValuesCount = count($test);//This can be 2 or 3 or any number depending upon the user input at the client

    $query="select *from testtable ";  


    if ($dValuesCount > 0 ){
        $query .= " WHERE ";

        for ($dCounter = 0; $dCounter <= $dValuesCount ; $dCounter++){

            $query .= "testData=" . $test[$dCounter];
            if ($dCounter != ($dValuesCount - 1)){

            $query .= " AND ";
            }

        }


    }

answered Oct 23, 2013 at 12:55

Leo Bali

3111 silver badge8 bronze badges

Comments

Manjunath Hegde · Accepted Answer · 2013-10-23 13:00:14Z

0

$q="select *from table where ";
    $a=count($test)-1;
    $b=0;
    while($element = current($test)) {
        $key=key($array);
        if($b!=$a){
            $q.=$key."=".$test[$key]." and ";
        }
        else {
            $q.=$key."=".$test[$key];
        }
        next($array);
        $b=$b+1;
    }

for this your array must contain columnname as key for example

$test['name'],$test['lastname']

then it will return

$q="select * from table where name=testnamevalue and lastname=testlastnamevalue";

hope it works

answered Oct 23, 2013 at 13:00

Manjunath Hegde

4144 silver badges24 bronze badges

Collectives™ on Stack Overflow

creating a sql query dynamically

8 Answers 8

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related