So honestly, this is the first time I am working with PDO and Error Exceptions. I have gone thru manuals as well as different Q/As resolved here in past and came up with code that I am pretty satisfied with. But I really really need your opinion on this. I have built few functions that I normally use often in my projects.
Please note that right now I am doing a test project just intended to learn these 2 new things.
First of all, I am not a fan of OOP and have always preferred procedural programming type.
function _database_row($_table, $_id = 0, $_key = "id") {
global $Database;
if(is_object($Database)) {
$Query = $Database->prepare("SELECT * FROM {$_table} WHERE {$_key} = :id");
if($Query->execute(Array(":id" => $_id))) {
$Query_Data = $Query->fetchAll(PDO::FETCH_ASSOC);
if(count($Query_Data) >= 1) {
if(count($Query_Data) == 1) {
return $Query_Data[0];
}
return $Query_Data;
}
} else {
throw new Exception("Database Query Failure: ".$Query->errorInfo()[2]);
}
}
return false;
}
the above function is intended to fetch a row from $_table
table with $_id
(not necessarily an integer value).
Please note that
$_id
may (sometimes) be the only thing (for this function) that is fetched from$_REQUEST
. By simply preparing the statement, am I totally secure from any SQL injection threat?I couldn't find an alternative to mysql_num_rows() (I indeed found few PDO methods to use fetchColumn while using COUNT() in the query. But I didn't prefer it that way, so I want to know if I did it right?
also before people ask, let me explain that in above function I designed it so it returns the row directly whenever I am looking for a single one (it will always be the case when I use "id" as $_key
because its PRIMARY auto_increment in database), while in rare cases I will also need multiple results :) this seems to be working fine, just need your opinions.
example of use:
_database_row("user", 14); // fetch me the user having id # 14
_database_row("products", 2); // fetch me the user having id # 14
_database_row("products", "enabled", "status"); // fetch me all products with status enabled
...sometimes during procedural programming, I wouldn't like those nasty "uncaught exception" errors, instead I will simply prefer a bool(false). So I did it like this:
function __database_row($_table, $_id = 0, $_key = "id") {
try {
return _database_row($_table, $_id, $_key);
} catch (Exception $e) {
return false;
}
}
(don't miss the use of another leading "_"). This also seems to be working perfectly fine, so what's your opinion here?
IMPORTANT:
- what is the use of "PDOStatement::closeCursor" exactly? I did read the manual but I am quite confused as I can call my functions as many times as I want and still get the desired/expected results but never "closed the cursor"
now... ENOUGH WITH SELECTS
AND FETCHINGS
:) lets talk about INSERTS
so I made this function to add multiple products in a single script execution and quickly.
function _add_product($_name, $_price = 0) {
global $Database;
if(is_object($Database)) {
$Query = $Database->prepare("INSERT INTO products (name, price) VALUES (:name, :price)");
$Query->execute(Array(":name" => $_name, ":price" => $_price));
if($Query->rowCount() >= 1) {
return $Database->lastInsertId();
} else {
throw new Exception("Database Query Failure: ".$Query->errorInfo()[2]);
}
}
return false;
}
This also seems to be working perfectly fine, but can I really rely on the method I used to get the ID of latest insert?
Thank you all!