CODEWITHSUNDEEP
phphtmlmysqli
sammyukavi
sammyukavi

Reputation: 1511

Create SQL using variables

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.

Upvotes: 0

Views: 114

Answers (4)

Your Common Sense
Your Common Sense

Reputation: 157919

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.

Upvotes: 0

bitoshi.n
bitoshi.n

Reputation: 2318

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;

Upvotes: 0

Leri
Leri

Reputation: 12525

 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;

Upvotes: 0

Paul
Paul

Reputation: 9012

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.

Upvotes: 1

Related Questions