Create SQL using variables

Question

I have a function that receives a an array from the $_POST function then uses the indexes and values contained in the indexes to create an SQL. My problem is that I can get the function to echo the SQL correctly but I'm unable to create a variable. My function is below

 function createcontactsArray($sql,Array $contactsArray){
         //array has already been cleaned from sql injections

        //delete null variables and the value of the submit button        
        foreach ($contactsArray as $key => $value) {

            if($value == ""||$value=="continue") {
                unset($contactsArray[$key]);
            }

        }

        echo "INSERT INTO users(";
        //create list of tables to use in the database
        foreach ($contactsArray as $key => $value) {

            if ($value == end($contactsArray))                {
                echo $key;
            } else                {
                echo $key.",";
            }

        }
        echo ') VALUES (';

        //create list of tables to use in the database
        //$newcontactsArray = array_values($contactsArray);
        foreach ($contactsArray as $key => $value) {

            if ($value == end($contactsArray))                {
                echo '"'.$value.'"';
            } else                {
               echo '"'.$value.'"'.",";
            }

        }

        echo ');';

}

If you run this script and pass it an associative array for example $contacts = array("name"=>"Peter griffin","town"=>"Quahogn");it will output the following INSERT INTO users (name,contacts) VALUES ("Peter griffin","Quahog"). However I want the function to create an sql like $sql = INSERT INTO users (name,contacts) VALUES ("Peter griffin","Quahog") so that to output I just say echo $sql; Thanks.

Paul · Accepted Answer · 2012-05-02 10:53:33Z

1

Just don't echo all the parts but collect them in a string variable. So, instead of:

echo 'Text';
echo $variable;

do something like

$output = 'Text';
$output .= $variable;

At the end of the function return that output with

return $output;

Note that .= concatenates the former value with the new one.

answered May 2, 2012 at 10:53

Paul

9,0523 gold badges30 silver badges48 bronze badges

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

Comments

Leri · Accepted Answer · 2012-05-02 10:55:39Z

 function createcontactsArray($sql,Array $contactsArray){
         //array has already been cleaned from sql injections

        //delete null variables and the value of the submit button        
        foreach ($contactsArray as $key => $value) {

            if($value == ""||$value=="continue") {
                unset($contactsArray[$key]);
            }

        }

        $sql = "INSERT INTO users(";
        //create list of tables to use in the database
        foreach ($contactsArray as $key => $value) {

            if ($value == end($contactsArray))                {
                $sql .= $key;
            } else                {
                $sql .= $key.",";
            }

        }
        $sql .= ') VALUES (';

        //create list of tables to use in the database
        //$newcontactsArray = array_values($contactsArray);
        foreach ($contactsArray as $key => $value) {

            if ($value == end($contactsArray))                {
                $sql .= '"'.$value.'"';
            } else                {
               $sql .= '"'.$value.'"'.",";
            }

        }

        $sql .= ');';

        return $sql;

bitoshi.n · Accepted Answer · 2012-05-02 10:57:08Z

0

function createcontactsArray($sql,Array $contactsArray){
         //array has already been cleaned from sql injections
         $sql = '';
        //delete null variables and the value of the submit button        
        foreach ($contactsArray as $key => $value) {

            if($value == ""||$value=="continue") {
                unset($contactsArray[$key]);
            }

        }

        $sql .= "INSERT INTO users(";
        //create list of tables to use in the database
        foreach ($contactsArray as $key => $value) {

            if ($value == end($contactsArray))                {
                $sql .= $key;
            } else                {
                $sql .= $key.",";
            }

        }
        $sql .= ') VALUES (';

        //create list of tables to use in the database
        //$newcontactsArray = array_values($contactsArray);
        foreach ($contactsArray as $key => $value) {

            if ($value == end($contactsArray))                {
                $sql .= '"'.$value.'"';
            } else                {
               $sql .= '"'.$value.'"'.",";
            }

        }

        $sql .= ');';

        echo $sql;

answered May 2, 2012 at 10:57

bitoshi.n

2,3381 gold badge16 silver badges16 bronze badges

2 Comments

sammyukavi Over a year ago

Don worry. I saved u with an upvote to take u to zero. Your code works

bitoshi.n Over a year ago

Thank you @Ukavi. Nice to share

Your Common Sense · Accepted Answer · 2012-05-02 11:27:36Z

0

here is the right way. safe and clean

function dbSet($fields,$source=array()) {
  global $mysqli;
  if (!$source) $source = &$_POST;
  $set='';
  foreach ($fields as $field) {
    if (isset($source[$field])) {
      $set.="`$field`='".mysqli_real_escape_string($mysqli,$source[$field])."', ";
    }
  }
  return substr($set, 0, -2); 
}

used like this

$query  = "UPDATE $table SET ".dbSet(array("name","contacts"));

note that you should always hardcode the allowed fieldnames, not get them from the $_POST, or site will be hacked in a matter of seconds.

with mysql this function can be used for either INSERT or UPDATE queries.

edited May 2, 2012 at 11:27

answered May 2, 2012 at 10:57

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

4 Comments

Your Common Sense Over a year ago

it is also perfectly SAFE. you should always hardcode the fieldnames, not get them from the POST, or site will be hacked in a matter of seconds. use $this->conn instead of $mysqli if it's a part of a class

sammyukavi Over a year ago

what exactly is returned by 'return substr($set, 0, -2);'

Your Common Sense Over a year ago

the string without 2 last characters. removing a comma

Baba Over a year ago

Now look at a bad code using global $mysqli; causes ugly scoping issues especially when it comes to database connection. This has been well discussed several times .. but no one down voted you yet you down voted be because of something that was specifically written in PHP documentation

Collectives™ on Stack Overflow

Create SQL using variables

4 Answers 4

Comments

Comments

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related