DaniWeb Forum Index > Web Development > PHP

Generate sql string to query MySQL DB ?

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Jul 2007

Posts: 142

Reputation: MitkOK is an unknown quantity at this point

Solved Threads: 12

MitkOK

Offline

Junior Poster

Generate sql string to query MySQL DB ?

Jul 12th, 2007

Hi folks.

I Have a question :

I'm interested how you generate query string to search DB with more than 1 form with LIKE.

For example :

We have $_POST['name'], $_POST['title'], $_POST['phone'], $_POST['email']. How do you generate sql query to search with two criteria ( name and phone ) ?

Thanks.

- Mitko Kostov.

Last edited by MitkOK; Jul 12th, 2007 at 7:47 am.

•

Join Date: Sep 2006

Posts: 162

Reputation: Cerberus is an unknown quantity at this point

Solved Threads: 14

Cerberus

Offline

Junior Poster

Re: Generate sql string to query MySQL DB ?

Jul 12th, 2007

Do you just mean the sql statement?

Something like this...

$sql="select * from table where name = ' " . $_POST[name] . " ' ";
$sql = $sql . " and phone = ' " . $_POST[phone] . " ';";

•

Join Date: Jul 2007

Posts: 142

Reputation: MitkOK is an unknown quantity at this point

Solved Threads: 12

MitkOK

Offline

Junior Poster

Re: Generate sql string to query MySQL DB ?

Jul 12th, 2007

Yes, that's right. But what if $_POST['name'] is not set ? And what if there are more fields to check ?

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
if (isset($_POST['name'])) { $query.="WHERE name LIKE '%$_POST'name']'"; } 
 else { $query.="AND name LIKE '%$_POST['name']'"; } 
 
if (isset($_POST['title'])) { $query.="WHERE title LIKE '%$_POST['title']'"; } 
 else { $query.="AND title LIKE '%$_POST['title']'"; } 
 
if (isset($_POST['email'])) { $query.="WHERE email LIKE '%$_POST['email']'"; } 
 else { $query.="AND email LIKE '%$_POST['email']'"; }
if (isset($_POST['name'])) { $query.="WHERE name LIKE '%$_POST'name']'"; } 
 else { $query.="AND name LIKE '%$_POST['name']'"; } 

if (isset($_POST['title'])) { $query.="WHERE title LIKE '%$_POST['title']'"; } 
 else { $query.="AND title LIKE '%$_POST['title']'"; } 

if (isset($_POST['email'])) { $query.="WHERE email LIKE '%$_POST['email']'"; } 
 else { $query.="AND email LIKE '%$_POST['email']'"; }

Now think that we have 15 fields to check. Is there an easy way to check ( loop maybe ) ?

Last edited by MitkOK; Jul 12th, 2007 at 8:40 am.

•

Join Date: Jul 2007

Posts: 142

Reputation: MitkOK is an unknown quantity at this point

Solved Threads: 12

MitkOK

Offline

Junior Poster

Re: Generate sql string to query MySQL DB ?

Jul 12th, 2007

The previous post is wrong, I'm sorry.

I cannot edit it.

Last edited by MitkOK; Jul 12th, 2007 at 9:41 am.

•

Join Date: Sep 2006

Posts: 162

Reputation: Cerberus is an unknown quantity at this point

Solved Threads: 14

Cerberus

Offline

Junior Poster

Re: Generate sql string to query MySQL DB ?

Jul 12th, 2007

this is really rough but i think you mean something along these lines

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$sql = "select * from table name where ";
$start;
 
if(isset $_POST['name'])
{
    $sql = $sql . $_POST['name'];
    $start = 1;
}
 
if(isset $_POST['phone'])
{
    if($start ==1)
    {
        $sql = $sql . "and '". $_POST['name'] ."'";
    }
    else
    {
        $sql = $sql . $_POST['name'];
    }
}
 
$sql = $sql . ";";
$sql = "select * from table name where ";
$start;

if(isset $_POST['name'])
{
    $sql = $sql . $_POST['name'];
    $start = 1;
}

if(isset $_POST['phone'])
{
    if($start ==1)
    {
        $sql = $sql . "and '". $_POST['name'] ."'";
    }
    else
    {
        $sql = $sql . $_POST['name'];
    }
}

$sql = $sql . ";";

•

Join Date: Jul 2007

Posts: 142

Reputation: MitkOK is an unknown quantity at this point

Solved Threads: 12

MitkOK

Offline

Junior Poster

Re: Generate sql string to query MySQL DB ?

Jul 12th, 2007

I want elegant way to check and generate.

•

Now think that we have 15 fields to check. Is there an easy way to check ( loop maybe ) ?

Last edited by MitkOK; Jul 12th, 2007 at 9:40 am.

•

Join Date: Sep 2006

Posts: 162

Reputation: Cerberus is an unknown quantity at this point

Solved Threads: 14

Cerberus

Offline

Junior Poster

Re: Generate sql string to query MySQL DB ?

Jul 12th, 2007

I don't know, i'll have to have a think about it. Perhaps using an associative array.

•

Join Date: Jul 2007

Posts: 142

Reputation: MitkOK is an unknown quantity at this point

Solved Threads: 12

MitkOK

Offline

Junior Poster

Re: Generate sql string to query MySQL DB ?

Jul 12th, 2007

This I wrote a function for my purpose, it simple but works for me :

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
function genQuery() {
 
  $row_names = array("name","email","phone","title");
 
  $post_values = array($_POST['name'], $_POST['email'], $_POST['phone'], $_POST['title']);
 
  $num = count($post_values);
 
  $sqlString = "SELECT * FROM t WHERE";
 
for ($i=0;$i<$num;$i++) {
 
  if (empty($post_values[$i]))  {   $sqlString.=""; } 
 
   if (($post_values[$i]) && ($i==0))  {   $sqlString.=" $row_names[$i] LIKE '%$post_values[$i]%'";   }
 
   if (($post_values[$i]) && ($i!=0))  {  $sqlString.=" $row_names[$i] LIKE '%$post_values[$i]%'";   }
 
 }
 
  $sqlString = preg_replace("/%'/", "%' AND ", $sqlString);
  $len = strlen($sqlString);
  $len=$len-4;
  $sqlString = substr($sqlString, 0 , $len);     
 
  return $sqlString;
 
}
function genQuery() {

  $row_names = array("name","email","phone","title");

  $post_values = array($_POST['name'], $_POST['email'], $_POST['phone'], $_POST['title']);

  $num = count($post_values);

  $sqlString = "SELECT * FROM t WHERE";

for ($i=0;$i<$num;$i++) {

  if (empty($post_values[$i]))  {   $sqlString.=""; } 
  
   if (($post_values[$i]) && ($i==0))  {   $sqlString.=" $row_names[$i] LIKE '%$post_values[$i]%'";   }
  
   if (($post_values[$i]) && ($i!=0))  {  $sqlString.=" $row_names[$i] LIKE '%$post_values[$i]%'";   }
     
 }
	 
  $sqlString = preg_replace("/%'/", "%' AND ", $sqlString);
  $len = strlen($sqlString);
  $len=$len-4;
  $sqlString = substr($sqlString, 0 , $len);     
	
  return $sqlString;
		
}