Jun
7
2013
7
2013
Updated MySQLi Database Class
An article by admin
Since I posted my mysqli database class originally on February 18th, I’ve gotten some great feedback, and A LOT of downloads, which is amazing!
As a side note, I recommend always grabbing this class from the github repo at https://github.com/bennettstone/simple-mysqli
I’ve also been using my own class on a large number of websites, and through the course of normal usage, and user feedback, I have made some changes…
- The error reporting has been enhanced to provide developers better, and more thorough error report emails
- The entire class has been fully OOP-ed for consistency
- Better empty array handling for update and delete functions to prevent the possibility of errors
Let me know what you think, and anything that could be improved further!
[dm]10[/dm]
Did you enjoy this article? Share it!
Related Posts
37 Comments + Add Comment
Leave a comment
Recent Snippets
- htaccess : Only allow access to specific wordpress upload types if logged in
- MySQL : Query WordPress Database for Invalid Media Filenames
- PHP : Get Actual IP Address with PHP
- JavaScript : Allow Tab in Textarea
- PHP : Clean sanitized database contents on output
- htaccess : Force www. prefix in URIs
- PHP : Force File Download with Correct Content Type
- Wordpress : Disable upgrade notification
Your logic in this is kinda flawed, is it not?
Your function “insert_safe” filters the field, not the value, and your comment states that it should be known to be secure….But your unsafe insert function doesn’t have anything of the kind…?
And for some reason, you used the unsafe Query instead of bind_parm;execute; which also sanitizes the data within themselves…? Is there a reason for this? I thought that this class might be useful, but after seeing the failure to use your own “Filter” function when inserting “Safe” information, i doubt it’ll be worse the risk.
As noted in the function comments, insert_safe is to insert data that IS already sanitized, it’s purpose is specific to that and not to sanitize it for you. I added it as a workaround function to allow easier usage of MySQL statements such as now() or AES_ENCRYPT statements as they would otherwise be encapsulated and inserted as string values rather than MySQL functions.
Sorry im a bit lost. Where in the code does it send all data through the filters by default first?
$sql = “INSERT INTO “. $table;
$fields = array();
$values = array();
foreach( $variables as $field => $value )
{
$fields[] = $field;
$values[] = “‘”.$value.”‘”;
}
$fields = ‘ (‘ . implode(‘, ‘, $fields) . ‘)’;
$values = ‘(‘. implode(‘, ‘, $values) .’)’;
$sql .= $fields .’ VALUES ‘. $values;
$query = $this->link->query( $sql );
Seems it just pushes the function field and values without going through filter?
Sorry beginner at OOP, etc, etc
Correct. Autofiltering was removed early on as it caused problems using native mysql functions (among other value type insertions)
What´s the right sentence to execute an INSERT escaping the strings?
$db->insert(“MyTable”, array(‘id’ => $id,
‘name’ => $name));
This one fails…
Can you provide the values being passed as $id and/or $name? What errors is it throwing?
Sure, these are the values (received from $_POST):
$id = 24
$name = “Year’00”
Ah. Looks like some nifty single/double quotes there and your data isn’t being escaped.
Use:
$data = array(
‘id’ => $id,
‘name’ => $name
);
$data = $db->filter( $data );
$db->insert( ‘MyTable’, $data );
Or use $id = $db->filter( $id ) style sanitization
The value ‘ in the database i stored as '
Any ideas??
Thank you very much!
In the last message I meant:
In the database the value ‘ is stored as ” & # 0 3 9 ; “
I really would like to store it as ‘
Hello Bennett, any idea of how to fix the quotes (‘) issue?
Thank you very much!
Ah yes, see my snippet here for a straightforward function that will take care of all of the use cases as stored by the db class!
[…] PHP Class: Simply MySQLi […]
[…] PHP Class: Simply MySQLi […]
iam a new in php programming
but i like use this class
n my question, can u make sample login class from u mysqli class??
because iam confused how to make login class from u mysqli class
thanks b4
That’s not exactly a small request, so I’ll point you to the almighty google search: https://www.google.com/search?q=php+login+class&oq=php+login+class
[…] PHP Class: Simply MySQLi […]
Nice ideea to upload it to github.
Only criticism is, the example file doesn’t make use of a function, to see how to pass the DB object from function to function.
I suspect I would still need to use a global?
Correct. You’d need to initialize it ($db = DB::getInstance();), then within your subsequent functions declare “global $db;”
Insert query not working
$values = array(
‘username’ => $_SESSION[‘username’],
‘send_date’ => ‘now()’,
‘from_num’ => $from_num,
‘to_num’ => $to,
‘sms_message’ => $sms_msg
);
$res=$db->insert( ‘sms_log’, $values );
Error : in date field (send_date) using now() , but inserting value is “0000-00-00 00:00:00”
That’s because the class doesn’t automatically handle MySQL functions, meaning the insertion statement is trying to insert the string “now()” instead of a timestamp. You’d need to use something like…
'send_date' => date( 'Y-m-d H:i:s' )thank you…
Your class is a great job of work, thank you. There are a couple of things that I need/would like to do which from my understanding it does not do.
1. I need to be able to work with two databases simultaneously. would it be a simple matter of changing the construct to accept parameters?
2. Something which I’m sure would be of use to many is an insert if not exists/update if it does function.
Thanking you in anticipation.
Thanks!
1) You would be able to connect to multiple databases, however, you’d need to initiate the class separately for each instance and hack the class __construct() a bit. For example:
In class.db.php __construct(), change it to accept the connection details instead of using defined constants:
public function __construct( $host, $user, $pass, $name ){
mb_internal_encoding( 'UTF-8' );
mb_regex_encoding( 'UTF-8' );
mysqli_report( MYSQLI_REPORT_STRICT );
try {
$this->link = new mysqli( $host, $user, $pass, $name );
$this->link->set_charset( "utf8" );
} catch ( Exception $e ) {
die( 'Unable to connect to database' );
}
}
$db1 = new DB( ‘localhost’, ‘username’, ‘password’, ‘database1’ );
$db2 = new DB( ‘externalhost’, ‘otherusername’, ‘otherpass’, ‘database2’ );
Regarding #2, since the “insert” function is meant to provide a bit more structure, I’d likely leave that and use the $db->query() function to just write out the INSERT IF NOT EXISTS statement.
Great class, I have a question the destruct function within the class I am assuming deals with closing the mysqli connection. But when a new connection is made using the construct function there does not seem to be any reference or links to do destruct function. So my question is how does the class know when to disconnect from mysql? my worry is if it is not closing the connections then might cause issues down the line.
__construct and __destruct methods are automatically called
Another question. Using the get_results I can execute SELECT statements like this:
$database->query($query)
However, If I try and run an UPDATE, DELETE or INSERT statement using this it doesnt work and I understand why. I understand that there are built in UPDATE, DELETE and INSERT functions that can be used using $database->update for example.
However, there seems to be a flaw with the WHERE clause for all these functions, I need to created an array for the where clause like this: array( ‘user_id’ => 10, ’email’ => ‘[email protected]’ )
Problem with this is that when it constructs the actual WHERE clause of the above example it would be equivalent to WHERE user_id=10 AND email=’[email protected]’ but sometimes the WHERE clause may need to be something more complicated, for example where one would want to use WHERE NOT IN function or possibly use something like a combination of where clauses e.g. WHERE id=24 OR id=25
I think for majority of tasks the built-in UPDATE, DELETE and INSERT will be fine, but for those cases do we have the ability to just run our own SQL syntax no matter if it is insert, update or delete? I think from initially looking at it, it does seem like I can do this using the $database->query function. Can you confirm if this is what the $database->query function is for?
Thanks
You are correct, that is what the “query()” function is for as I had similar issues with using other database classes!
what about – mysqli_free_result($result). Any function?
It isn’t currently in this class, as the result sets are freed at the end of script execution, however, you could certainly add it if you’re having performance issues: http://fr2.php.net/mysql_free_result
Thanks, but this /mysqli_free_result/ does not work:
$query = “SELECT * FROM table”;
$results = $database->get_results( $query );
foreach( $results as $row )
{
extract ($row);
echo $somerow;
}
mysql_free_result($results);
script finishing execution
Is it necessary to explicitly disconnect from DB server in my script ending.?
$database = new DB();
…[snip]…
$database->disconnect(); //Is it necessary ?
It is not, great question. Scripts will automatically terminate at the end of the execution (applies to anything you have written normally anyway)
Is this class take care of SQL injection too ??
Yes, the UPDATED one uses bound params.
In simple mysqli wrapper class, How can I connect multiple databases