Create mysql select - multiple parameters (some are unnecessary)

Question

Let's asume I have following object in PHP:

class param{
 public $home; //set by another function
 public $user; //set by another function
 public function createRequest(){
//in this function I want to create mysql string with $home and $user
  $sql = "select * FROM table WHERE home =".$this->home." AND user=".$this->user;
  return $sql;
}

Problem is, that $home (or $user) could be empty string and in this case I want to include all homes (or users), not just columns, where home="" (or user="");

Do you have any suggestion how to do that? Or is this idea wrong? (I'm just beginner with PHP)

By the way you are missing your speech marks for mysql_

Zevi Sternlicht
– Zevi Sternlicht

2013-07-01 20:57:55 +00:00
Commented Jul 1, 2013 at 20:57 — Zevi Sternlicht
– Zevi Sternlicht, Commented Jul 1, 2013 at 20:57

Rob W · Accepted Answer · 2013-07-01 20:58:28Z

1

This is not the most elegant, and we should be using PDO prepared statements... but for sake of example:

class param{
  public $home; //set by another function
  public $user; //set by another function
  public function createRequest(){
    //in this function I want to create mysql string with $home and $user
    $sql = "select * FROM table";
    if(strlen($this->home) || strlen($this->user)) {
      $sql .= " WHERE ";
      $and = array();
      if(strlen($this->home))
        $and[] = " home='".$this->home."' ";
      if(strlen($this->user))
        $and[] = " user='".$this->user."' "; 
      $sql .= implode(" AND ", $and);
    }
    return $sql;
  }
}

Example test output:

$p = new param;
echo $p->createRequest();
echo "<br>";

$p->home = "foo";
echo $p->createRequest();
echo "<br>";

$p->user = "bar";
echo $p->createRequest();
echo "<br>";

$p->home = "";
echo $p->createRequest();

Will yield:

select * FROM table
select * FROM table WHERE home='foo' 
select * FROM table WHERE home='foo' AND user='bar' 
select * FROM table WHERE user='bar'

answered Jul 1, 2013 at 20:58

Rob W

9,1621 gold badge32 silver badges50 bronze badges

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

9 Comments

Barmar Over a year ago

Prepared statements don't make this any easier.

Rob W Over a year ago

Coupled with sprintf, I've seen better solutions!

jcsanyi Over a year ago

I think you mean to say he should be properly escaping the values - and prepared statements are one way to do that. But you can escape them without preparent statements (and you probably should in this answer)

Rob W Over a year ago

But I don't know his driver or how he's connecting to the database :o

Barmar Over a year ago

Either way, you have to build the WHERE clause dynamically, either with interpolated values or ?.

|

Kickstart · Accepted Answer · 2013-07-01 21:07:55Z

0

class param{
 public $home; //set by another function
 public $user; //set by another function
 public function createRequest(){
//in this function I want to create mysql string with $home and $user
    $ClauseArray = array(' 1 = 1 ');
    if ($this->home != '') $ClauseArray[] = " home = '".$this->home."' ";
    if ($this->user != '') $ClauseArray[] = " user = '".$this->user."' ";
    $sql = "select * FROM table WHERE ".implode('AND', $ClauseArray);
    return $sql;
}

answered Jul 1, 2013 at 21:07

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Collectives™ on Stack Overflow

Create mysql select - multiple parameters (some are unnecessary)

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related