php - SQL Update/Insert with loop for $_GET

890

I'm using ajax scripts for most of my site's functionality. In order to update or insert a row in particular table i send the request with $_GET, then retrieve each value with

foreach ($_GET as $item => $value){
        $$item =addslashes($value);
    }

and then i have this SQL Statement for the query

if($_GET['action']=='update'){
     $sql =$conn->query("UPDATE `newDrivers` SET `idCompany`='$idCompany', ...
     WHERE `id`='$id'");
}
else{
     $sql =$conn->query("
    INSERT INTO `newDrivers`(`idCompany`, ...) 
    VALUES ('$idCompany', ...)");
} 

I see inefficiency, naming each column and creating new file for each table i have. Is there any way i can update/insert without naming each column individually so the script would be more of a universal one?

142

Answer

Solution:

It's depends from your database adapter. In your case you may write construction like this

$columnsForUpdate = array();
//collect array with all pair `field` = 'value'
foreach ($_GET as $item => $value){
  $value = addslashes($value);
  $columnsForUpdate[] = "`{$item}` = '{$value}'";
 }
 if($_GET['action']=='update'){
 //concat it to string
 $columns = implode(', ', $columnsForUpdate);
 $sql =$conn->query("UPDATE `newDrivers` SET {$columns} WHERE id = {$id}")
 }
else{
     $columns = implode(', ', $columnsForUpdate);
    //insert have SET syntax too
     $sql =$conn->query("INSERT INTO `newDrivers` SET {$columns}");
} 

People are also looking for solutions to the problem: sql - PHP query for a month but gives me last two years

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

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.