CODEWITHSUNDEEP
phpmysql
George
George

Reputation: 27

Change Array into Database Query in PHP

I have an array as follows:

 Array ( [item-name] => SM58 
         [make] => Shure 
         [model] => SM58 
         [qty] => 5 
         [status] => Available 
       )

And I want to change it into a database query such as:

INSERT INTO `Inventory`(`item-name`, `make`, `model`, `qty`, `status`) VALUES ('SM58','Shure','SM58','5',[Available])

I just can't quite work out how I achieve it.

Upvotes: 1

Views: 74

Answers (2)

Liam Sorsby
Liam Sorsby

Reputation: 2982

Try this

   foreach($array as $index => $value){
    $fields .= "`".$index."`".",";
    $values .= "'".mysql_real_escape_string($value)."'".","; //This escapes for mysql
    }
    $fields = rtrim($fields,",");
    $values = rtrim($values,',');
    $sql = "INSERT INTO `Inventory`($fields) VALUES ($values)";

However as the mysql_* function is depreciated you would be better using the above post with PDO and prepared statements or you could use the mysqli_* function along with prepared Statements. Below are the links to the two resources.

PDO: http://www.php.net/manual/en/book.pdo.php MySQLI: https://www.php.net/mysqli

Upvotes: -1

t1gor
t1gor

Reputation: 1292

You can use PDO:

$stmt = $PDOinstance->prepare("
  INSERT INTO `Inventory` (`item-name`, `make`, `model`, `qty`, `status`) 
  VALUES (:item-name, :make, :model, :qty, :status)
");
$stmt->execute($yourArray);

Upvotes: 3

Related Questions