PHP MySQL statement with multiple variable components

194

I am producing an API that takes in certain parameters, carries out an algorithm and returns the results. As part of this process I have several different clauses that can go into the SQL statement based on what is sent to the API. This has produced many sections of this format:

if(isset($_GET['val'])) {
    $sqljoin = " INNER JOIN b ON b.1=a.1 "
    $sqlwhere = " WHERE b.2 = " . $_GET['val'];
}
$sql = "SELECT * FROM a " . $sqljoin . $sqlwhere;

Which was fine initially, but now I have approximately 6 different clauses going into it, both with a JOIN and a WHERE clause. Is there a better way of structuring this?

357

Answer

Solution:

this may help you .

  $sql = "SELECT * FROM a "; 
  $where = " WHERE 1=1  ";
  if(isset($_GET['val'])) {
   $sqljoin = " INNER JOIN b ON b.1=a.1 "
   $sqlwhere = " AND  b.2 = " . $_GET['val'];
  }
   if(isset($_GET['val2'])) {
   $sqljoin2 = " INNER JOIN b2 ON b2.1=a.1 "
   $sqlwhere2 = " AND b2.2 = " . $_GET['val2'];
  }
$fullquery = $sql . $sqljoin . $sqljoin2 . $where .  $sqlwhere . $sqlwhere2 ;

Some notes:

Your code is under sql injection vulnerability. you should escape your variables by

$_GET['val'] = mysql_real_escape_string($_GET['val']);
you should switch to PDO or MYSQLI.

People are also looking for solutions to the problem: Issue with php/mysql prepared insert statement

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question