PHP Function to Create Dynamic SQL Queries

Question

I have a lot of repetitive code that inserts and selects data from my MySQL database. Out of curiosity, is it possible to create a PHP function that can take the table, columns, and rows as parameters and generate the SQL statement. A completely dynamic SQL statement generator, if you will.

For example:

function PushData($table, $columns, $values)
{
    // insert data
}

Furthermore, is the opposite possible. A function that can select and return data.

function PullData($table, $columns, $rowID)
{
    // grab data
    return data;
}

Is something like this possible? If so, would this be something worth doing?

You can do this in Zend. But I imagine that's extreme overkill for what your looking for. — Trevor
– Trevor, Commented Jul 24, 2017 at 13:06
Yes it is possible, i work with such functions every day. But be carefull about the sql injection! — Melody
– Melody, Commented Jul 24, 2017 at 13:16

Michael GEDION · Accepted Answer · 2017-07-25 11:44:38Z

2

Try something like that i'm not sure that it will work.

function PushData($table, $columns, $values)
{
    $question_mark = count($columns)-1;
    $question_mark_array = array_fill(0, $question_mark, '?');

    $valueEscapedTrimed = array();

    foreach($values as $value)
    {
        $value=trim($value);
        $valueEscapedTrimed[] = htmlentities($value, ENT_QUOTES | ENT_HTML5, $encoding = 'UTF-8' );
    }

    $query = 'INSERT INTO '.$table. '(' .implode(' ',$columns). ') VALUES ('.implode(',',$question_mark_array).') \' ' ;

    foreach($valueEscapedTrimed as $value)
    {
        $stmt = $conn->prepare($query);
        $stmt->bindValue('?', $value);
        $stmt->execute();
        $stmt->CloseCursor();
        $stmt=null;
    }
}

edited Jul 25, 2017 at 11:44

answered Jul 24, 2017 at 15:12

Michael GEDION

95910 silver badges17 bronze badges

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

2 Comments

Lars Peterson Over a year ago

Correct me if I am wrong, but wouldn't the $valueEscapedTrimed be out of scope and therefore unaccessible to the second foreach loop?

Michael GEDION Over a year ago

I think i need to initialize the array . See the edit.

Lars Peterson · Accepted Answer · 2017-07-25 12:09:47Z

Just in case anyone was curious like I was, I made a working example of both functions. Special thanks to Michael's answer for putting me in the right direction.

Insert Data Function

function PushData($conn, $table, $columns, $values)
{
  $column_count = count($columns);
  $overwriteArr = array_fill(0, $column_count, '?');

  for ($i = 0; $i < sizeof($columns); $i++)
  {
    $columns[$i] = trim($columns[$i]);
    $columns[$i] = '`' . $columns[$i] . '`';
  }

  $query = "INSERT INTO {$table} (" . implode(',', $columns) . ") VALUES (" . implode(',', $overwriteArr) . ")";

  foreach ($values as $value)
  {
    $value = trim($value);
    $value = mysqli_real_escape_string($conn, $value);
    $value = htmlentities($value, ENT_QUOTES | ENT_HTML5, $encoding = 'UTF-8');
    $value = '"' . $value . '"';
    $query = preg_replace('/\?/', $value, $query, 1);
  }

  $result = mysqli_query($conn, $query);
  return $result;
}

Select Data Function

function PullData($conn, $table, $columns, $operand_column, $operand_value)
{
  $columns = implode(',', $columns);
  $sql = "SELECT {$columns} FROM {$table} WHERE {$operand_column} = {$operand_value}";
  $result = mysqli_query($conn, $sql);
  return $result;
}

Collectives™ on Stack Overflow

PHP Function to Create Dynamic SQL Queries

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related