Tag: automation

PHP PDO MySQL

Needed to make a simple php script with mysql in it, so I decided to use PDO because… well peer pressure 😐 so heres a basic PHP PDO MYsql script.

The idea is I have a table called table with a field called field in a database called somedatabase. We have 1000 rows of data in said table and we need to pick a random result from the 1000 rows. This is just 1 way of doing it, it isnt the most optimized, neither is it the cleanest. but this will give you a basic idea of how to connect to the DB and get a result. you can expand this of course to do very tricky queries.

// Add a user variable for PDO, mysql user
$user='root';
// Add a password for PDO, mysql password
$password='password';
// Setup the mysql connection info
$dsn = 'mysql:dbname=somedatabase;host=127.0.0.1';

// Wrap the actual connection to mysql in try so if it doesn't work you can catch the exception and echo the message.
try {
$dbh = new PDO($dsn, $user, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''));
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

// Pick a number from 1 - 1000 and assign to $rc
$rc = mt_rand(1, 1000);
// Assign the SQL query selecting the data from our DB / field to variable $sql
$sql = "SELECT field FROM table LIMIT 1 OFFSET $rc";
// run the query and for each one assign to variable $field
foreach ($dbh->query($sql) as $row) {
$field = $row['field'];
}

// clear the variable $dbh and return used memory back to usable pool
$dbh = null;

I want to write more articles concerning the trio of PHP PDO Mysql but I don’t do much with PHP anymore so the chance that I have need of the three at this point is slim.