Ad:

PHP Discussion Thread
Marked Solved
Views: 7418

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Jul 12th, 2007

Generate sql string to query MySQL DB ?

Expand Post »

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.

MitkOK

Reputation Points: 59

Solved Threads: 12

Junior Poster

Offline

142 posts
since Jul 2007

Permalink

Jul 12th, 2007

Re: Generate sql string to query MySQL DB ?

Do you just mean the sql statement?

Something like this...

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

Cerberus

Reputation Points: 27

Solved Threads: 14

Junior Poster

Offline

162 posts
since Sep 2006

Permalink

Jul 12th, 2007

Re: Generate sql string to query MySQL DB ?

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

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.

MitkOK

Reputation Points: 59

Solved Threads: 12

Junior Poster

Offline

142 posts
since Jul 2007

Permalink

Jul 12th, 2007

Re: Generate sql string to query MySQL DB ?

The previous post is wrong, I'm sorry.

I cannot edit it.

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

MitkOK

Reputation Points: 59

Solved Threads: 12

Junior Poster

Offline

142 posts
since Jul 2007

Permalink

Jul 12th, 2007

Re: Generate sql string to query MySQL DB ?

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

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 . ";";

Cerberus

Reputation Points: 27

Solved Threads: 14

Junior Poster

Offline

162 posts
since Sep 2006

Permalink

Jul 12th, 2007

Re: Generate sql string to query MySQL DB ?

I want elegant way to check and generate.

Quote ...

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.

MitkOK

Reputation Points: 59

Solved Threads: 12

Junior Poster

Offline

142 posts
since Jul 2007

Permalink

Jul 12th, 2007

Re: Generate sql string to query MySQL DB ?

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

Cerberus

Reputation Points: 27

Solved Threads: 14

Junior Poster

Offline

162 posts
since Sep 2006

Permalink

Jul 12th, 2007

Re: Generate sql string to query MySQL DB ?

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

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;
		
}

Last edited by MitkOK; Jul 12th, 2007 at 12:13 pm.

MitkOK

Reputation Points: 59

Solved Threads: 12

Junior Poster

Offline

142 posts
since Jul 2007

Permalink

Jul 12th, 2007

Re: Generate sql string to query MySQL DB ?

Good solution.

Cerberus

Reputation Points: 27

Solved Threads: 14

Junior Poster

Offline

162 posts
since Sep 2006

Permalink

Jul 12th, 2007

Re: Generate sql string to query MySQL DB ?

Hi.

I don't think it's good, but at least works form me.

- Mitko Kostov

MitkOK

Reputation Points: 59

Solved Threads: 12

Junior Poster

Offline

142 posts
since Jul 2007

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: File Uploads

Next Thread in PHP Forum Timeline: If URL Exists

DaniWeb Message

Cancel Changes

User Name
Password