Give it a try.
- You should create only one PDO instance, e.g. connection. It should be passed as argument to each function that uses it.
- I wrote a function named
fetchLeagues() instead of impressoras. Implement function carrefour() in a similar way.
- I wrote a function
fetchleagueById() too, in order to show you how to bind values to the prepared sql statement.
- I implemented three other example functions (
insertUser(), updateUser() and deleteUser()) too, so that you can see the complete CRUD process.
Some recommendations:
- Try to move to OOP.
- Apply the so-called dependency injection instead of implementing globals and statics.
- Apply exception handling.
Resources:
Here is the code - four PHP pages.
Good luck!
index.php
<?php
require_once 'connection.php';
require_once 'functions.php';
try {
// Fetch leagues.
$leagues = fetchLeagues($connection);
var_dump($leagues); // For testing.
// Fetch league by id
// ------------------
// $leagueId = 1;
// $league = fetchLeagueById($connection, $leagueId);
// var_dump($league); // For testing.
//
// Insert user
// -----------
// $username = 'Sam';
// $lastInsertId = insertUser($connection, $username);
// var_dump($lastInsertId); // For testing.
//
// Update user
// -----------
// $userId = 6;
// $username = 'Mikaela';
// $userUpdated = updateUser($connection, $userId, $username);
// var_dump($userUpdated); // For testing.
//
// Delete user
// -----------
// $userId = 6;
// $userDeleted = deleteUser($connection, $userId);
// var_dump($userDeleted); // For testing.
} catch (PDOException $exc) {
// Announce the user about the fact of a raised error, or redirect him to a predefined display-error page.
echo 'We are sorry for the inconvenience. An error occurred during your request. Please try again or contact the administrator.';
// Log exception to a file.
// ...
exit();
} catch (Exception $exc) {
// Announce the user about the fact of a raised error, or redirect him to a predefined display-error page.
echo 'We are sorry for the inconvenience. An error occurred during your request. Please try again or contact the administrator.';
// Log exception to a file.
// ...
// Handle the exception correspondingly.
// ...
}
connection.php
<?php
require_once 'configs.php';
try {
// Create a db connection.
$connection = new PDO(
sprintf(
'mysql:host=%s;port=%s;dbname=%s;charset=%s'
, MYSQL_HOST
, MYSQL_PORT
, MYSQL_DATABASE
, MYSQL_CHARSET
)
, MYSQL_USERNAME
, MYSQL_PASSWORD
, array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => FALSE,
PDO::ATTR_PERSISTENT => TRUE,
)
);
} catch (PDOException $exc) {
// Announce the user about the fact of a raised error, or redirect him to a predefined display-error page.
echo 'We are sorry for the inconvenience. An error occurred during your request. Please try again or contact the administrator.';
// Log exception to a file.
// ...
exit();
}
configs.php
<?php
// Db connection parameters.
define('MYSQL_HOST', '...');
define('MYSQL_PORT', '3306');
define('MYSQL_DATABASE', '...');
define('MYSQL_CHARSET', 'utf8');
define('MYSQL_USERNAME', '...');
define('MYSQL_PASSWORD', '...');
functions.php
<?php
/**
* Fetch leagues.
*
* SELECT * FROM [table-name] WHERE [col1]=:[val1] [oper] [col2]=:[val2]
*
* @param PDO $connection Connection instance.
* @return array Leagues list.
*/
function fetchLeagues($connection) {
$sql = 'SELECT * FROM league';
$statement = $connection->prepare($sql);
$statement->execute();
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
/**
* Fetch league by id.
*
* SELECT * FROM [table-name] WHERE [col1]=:[val1] [oper] [col2]=:[val2]
*
* @param PDO $connection Connection instance.
* @param string $leagueId League ID.
* @return array League details.
*/
function fetchLeagueById($connection, $leagueId) {
$sql = 'SELECT * FROM league WHERE id = :id LIMIT 1';
$statement = $connection->prepare($sql);
$statement->bindValue(':id', $leagueId, PDO::PARAM_INT);
$statement->execute();
return $statement->fetch(PDO::FETCH_ASSOC);
}
/**
* Insert user.
*
* INSERT INTO [table-name] ([col1],[col2],[col3]) VALUES (:[col1],:[col2],:[col3])
*
* @param PDO $connection Connection instance.
* @param string $username User name.
* @return integer Last insert id.
*/
function insertUser($connection, $username) {
$sql = 'INSERT INTO users (
username
) VALUES (
:username
)';
$statement = $connection->prepare($sql);
$statement->bindValue(':username', $username, PDO::PARAM_STR);
$statement->execute();
return $connection->lastInsertId();
}
/**
* Update user.
*
* UPDATE [table-name] SET [col1]=:[col1],[col2]=:[col2] WHERE [PK-name]=:[PK-name]
*
* @param PDO $connection Connection instance.
* @param integer $userId User ID.
* @param string $username User name.
* @return bool TRUE if update successful, FALSE otherwise.
*/
function updateUser($connection, $userId, $username) {
$sql = 'UPDATE users
SET username = :username
WHERE id = :id';
$statement = $connection->prepare($sql);
$statement->bindValue(':id', $userId, PDO::PARAM_INT);
$statement->bindValue(':username', $username, PDO::PARAM_STR);
$statement->execute();
return $statement->rowCount() > 0;
}
/**
* Delete user.
*
* DELETE FROM [table-name] WHERE [PK-name]=:[PK-name]
*
* @param PDO $connection Connection instance.
* @param integer $userId User ID.
* @return bool TRUE if delete successful, FALSE otherwise.
*/
function deleteUser($connection, $userId) {
$sql = 'DELETE FROM users
WHERE id = :id';
$statement = $connection->prepare($sql);
$statement->bindValue(':id', $userId, PDO::PARAM_INT);
$statement->execute();
return $statement->rowCount() > 0;
}
UPDATE:
As you can see, my code contains code parts which are repeating themself. But I had a long discussion with @YourCommonSense and it had crystallized itself a better method of applying exception handling. @YourCommonSense has written its own tutorial - which I presented as the first resource/link in the above list. You can use his method instead. It has the BIG advantage of eliminating all code repetitions.
