PHP and mySQL Dynamic Query

Question

Im trying to create a dynamic query based upon any or all selections from option values selected by a user. Eg if they select a Project ID a query will return with the project ID, size and a lesson stored in a second table, or If they select a size and department another query would execute. displaying all projects that are of the chosen size with the lessons against it.

Heres what ive got so far. I really could do with some help.

<?php 
$pid = $_POST['project_id'] ;
$psize = $_POST['projectSize'] ;
$pdepts = $_POST['depts'] ;
$lstage = $_POST['stage'] ;
$ltype = $_POST['type'] ;
$impacted = $_POST['impacted'] ;


$sqlString = null;
echo "Total Number Of Captured Post Variables is:";
echo count($_POST);

echo '<br />';

 $number = 0;

foreach ($_POST as $param_name => $param_val ) {

if ($param_val ==""){


   }else{

     $number++;

   }

echo "Param: $param_name = $param_val<br />\n";
}
if($number ==1) {

}else{

}

?>

Seder · Accepted Answer · 2013-05-14 13:28:30Z

2

I hope this can help a little , I Also added array check and you need to check the security and injection :)

<?php 
$pid = $_POST['project_id'] ;
$psize = $_POST['projectSize'] ;
$pdepts = $_POST['depts'] ;
$lstage = $_POST['stage'] ;
$ltype = $_POST['type'] ;
$impacted = $_POST['impacted'] ;
//Your columns in the DB 
$columns = array('project_id'=>'project_id','project_size'=>'project_size','depts'=>'depts','stage'=>'stage'); 

$sqlString = null;
echo "Total Number Of Captured Post Variables is:";
echo count($_POST);

echo '<br />';

 $number = 0;
$queryStr = ""; 
$preStr = array(); 
foreach ($_POST as $key => $val ) {

if (!empty($_POST[$key])){
       if(!is_array($_POST[$key]))
           $currentStr = $columns[$key]." = ".$val; 
       else
           $currentStr = $columns[$key]." IN (".implode(',',$_POST[$key]).")"; 

       $preStr[] = $currentStr; 
   }
 }

$queryStr = "SELECT * FROM tableName WHERE ".implode(' AND ',$preStr);
echo $queryStr; 

if($number ==1) {

}else{

}

?>

answered May 14, 2013 at 13:28

Seder

2,7632 gold badges24 silver badges43 bronze badges

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

2 Comments

Tazzy Over a year ago

Thanks ill check this out just now, im working on a php project and dont have the best experience so trying to cath up quickly :)

Tazzy Over a year ago

Thanks Sedz, ive added that in (i updated field names and the table names:) This now writes the query correctly. So if i dont select a project_id the uery will say WHERE size = instead which is cool. My next question is how to i present the query to display the data back out?

CuriousMind · Accepted Answer · 2013-05-14 13:30:26Z

0

Its quite easy, just run a conditional for each query you want to run:

if(isset($_POST['project_id'])){
    //they selected a Project ID.
    //write the query will return with the project ID, size and a lesson stored in a second table
} elseif (isset($_POST['projectSize']) && isset($_POST['depts'])){
   //they selected a size and department
   // write a query displaying all projects that are of the chosen size with the lessons against it.
} elseif (some other condition ){
   //you can keep adding conditions 
}

lastly, you might want to sanitize your inputs using mysql_real_escape_string

answered May 14, 2013 at 13:30

CuriousMind

34.3k29 gold badges102 silver badges142 bronze badges

Comments

Tazzy · Accepted Answer · 2013-05-15 07:43:01Z

0

Got it working to the point where: when i echo $queryStr it outputs as"SELECT FROM table WHERE" etc..

How do I then display the output of that query into a table of results?

answered May 15, 2013 at 7:43

Tazzy

176 bronze badges

1 Comment

Tazzy Over a year ago

i take it should be something along the lines of $result = mysql_query($query) WHILE($row = mysql_fetch_assoc($result)) echo '.....

Collectives™ on Stack Overflow

PHP and mySQL Dynamic Query

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related