Change Array into Database Query in PHP

Question

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.

To make it easier, you can also do INSERT INTO `Inventory` SET `item-name`='SM58', .... — gen_Eric
– gen_Eric, Commented Jan 17, 2014 at 19:48

t1gor · Accepted Answer · 2014-01-17 19:51:18Z

3

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);

answered Jan 17, 2014 at 19:51

t1gor

1,28212 silver badges25 bronze badges

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

2 Comments

George Over a year ago

Can you explain the parts of this to me so I can understand it for the future? PDO means...? ->execute sends it to the database?

t1gor Over a year ago

@user3207974, PDO stands for PHP Data Objects. Basically, this is a safe and pretty easy way to access your database. Please see the documentation for more info: php.net/manual/en/class.pdo.php

Community · Accepted Answer · 2023-11-17 20:24:57Z

-1

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

edited Nov 17, 2023 at 20:24

CommunityBot

11 silver badge

answered Jan 17, 2014 at 19:51

Liam Sorsby

3,0023 gold badges30 silver badges52 bronze badges

4 Comments

tadman Over a year ago

Please, do not post answers without SQL escaping. This creates enormous liabilities in any applications that use this code.

Liam Sorsby Over a year ago

@tadman the answer wasn't asking about escaping they was asking about a solution. This is entirely there choice to escape the string before they create an array. Also I am unable to specify an escape unless we know whether they are using PDO, MySQL or mysqli functions which they do not specify

tadman Over a year ago

The solution always involves escaping. When in doubt, give an example in PDO if you're trying to promote the use of that. Look at t1gor's example here which does it correctly. Yours, unfortunately, perpetuates the worst habits of PHP developers.

Liam Sorsby Over a year ago

@tadman i have noted how to escape for mysql_* function but as it is depreciated i have noted that PDO with prepared statements would be better to use.

Collectives™ on Stack Overflow

Change Array into Database Query in PHP

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related