Werner der Champ Werner der Champ - 4 months ago 8
MySQL Question

Struggling with PDO

I've written a sql query in php, using mysquli. When I got some strange errors I quickly recognized that this was bad coding, since backslashes screwed the query (and sql injection would also have been a big problem)

Someone here said I should use PDO, but this is way too complex and the documentation on php.net didn't help me either.

Does someone have a link to a page, where it's expained for newbies or can someone send me suitable code?

What I need to do is basicly just inserting values from an array into a table (currently locally with XAMPP)

Answer

There's quite a bit to PDO, from connecting to generating queries and getting errors. There's also a lot of information out there, with varying degrees of usefulness, but that's there Stack Overflow Documentation is trying to help. Here's a link to the PHP/PDO page

There's pages off there for:
Basic connection and retrieval
Data storage and access
SQL injection prevention

And other topics in that area too.

From the relevant areas of the documentation, stripped to only use MySQL:

// First, create the database handle

//Using MySQL (connection via local socket):
$dsn = "mysql:host=localhost;dbname=testdb;charset=utf8";

$username = "user";
$password = "pass";
$db = new PDO($dsn, $username, $password);

Once connected, you'll be able to insert or update data. Here's an example of how to do an insert:

// Insert the metadata of the order into the database
$preparedStatement = $db->prepare(
    'INSERT INTO `orders` (`name`, `address`, `telephone`, `created_at`)
     VALUES (:name, :address, :telephone, :created_at)'
);

$preparedStatement->execute([
    ':name' => $name,
    ':address' => $address,
    ':telephone' => $telephone,
    ':created_at' => time(),
]);

// Get the generated `order_id`
$orderId = $db->lastInsertId();

And selecting data can be done in a similar manner:

$query = "SELECT * FROM orders WHERE order_id = ?";
$statement = $db->prepare($query);

// Create some parameters to fill the placeholders, and execute the statement
$parameters = [ $orderId ];
$statement->execute($parameters);

// Now, loop through each record as an associative array
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    do_stuff($row);
}
Comments