Connect to MySQL with PDO

This code connects to the DB and closes it:

 

/*** mysql hostname ***/
$hostname = ‘localhost’;

/*** mysql username ***/
$username = ‘username’;

/*** mysql password ***/
$password = ‘password’;

try {
$dbh = new PDO(“mysql:host=$hostname;dbname=mysql”, $username, $password);
/*** echo a message saying we have connected ***/
echo ‘Connected to database’;

/*** close the database connection ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}

 

Insert into database, put before close the DB


/*** INSERT data ***/
$count = $dbh->exec("INSERT INTO animals(animal_type, animal_name) VALUES ('kiwi', 'troy')");

/*** echo the number of affected rows ***/
echo $count;

Select from the DB:


/*** The SQL SELECT statement ***/
$sql = "SELECT * FROM animals";
foreach ($dbh->query($sql) as $row)
{
print $row['animal_type'] .' - '. $row['animal_name'] . '
';
}

Insert into DB:


/*** INSERT data ***/
$count = $dbh->exec("UPDATE animals SET animal_name='bruce' WHERE animal_name='troy'");

/*** echo the number of affected rows ***/
echo $count;

 

Fetch Code


/*** The SQL SELECT statement ***/
$sql = "SELECT * FROM animals";

/*** fetch into an PDOStatement object ***/
$stmt = $dbh->query($sql);

/*** echo number of columns ***/
$result = $stmt->fetch(PDO::FETCH_ASSOC);

/*** loop over the object directly ***/
foreach($result as $key=>$val)
{
echo $key.’ – ‘.$val.’
‘;
}

Fetch object


/*** The SQL SELECT statement ***/
$sql = "SELECT * FROM animals";

/*** fetch into an PDOStatement object ***/
$stmt = $dbh->query($sql);

/*** echo number of columns ***/
$obj = $stmt->fetch(PDO::FETCH_OBJ);

/*** loop over the object directly ***/
echo $obj->animal_id.’
‘;
echo $obj->animal_type.’
‘;
echo $obj->animal_name;