Categories
Popular Posts
RSS Feeds
ArchivesPHP MySQL Database Class
April 19th, 2005A class for very basic MySQL database connectivity. Written to reduce redundant code in my own projects aswell as aid in debugging and error reporting during the developement phase. Currently connects to a database, execute external files containing SQL commands, insert and update from an array of key => value pairs, insert and update with sql command, query (one result), query (multiple rows), and dump a select query to a table. The zip file contains the class aswell as a demonstration script.
Download Version 1.0.4: db-1.0.4.zip
Do you like this code? Rate This Script at Hot Scripts.
 |
Posted on
Tuesday, April 19th, 2005 at 10:08 am
Categories
Web Development PHP
Related Posts
Technorati Tags
November 27th, 2007 at 10:55 am
how can i make a class to insert data to database in php
November 27th, 2007 at 11:26 am
RE: serkan
The sample file included with the class shows how this is done.
December 1st, 2007 at 11:37 am
Hi,
Your script is working for me but I can't find which database was used to create the table users in ! I'm new to MySQL. Could you help me out with this?
January 14th, 2008 at 1:02 pm
Hi,
The script works perfectly fine on my PC, but online I get a "no database selected" error. I think it has something to do with the MySQL version of the server being quite old (3.23).
Could you tell me what I should change within the script?
January 15th, 2008 at 7:22 am
Are you sure you typed the database name correctly? Are you sure there is a database setup online?
January 16th, 2008 at 6:42 pm
This database class is a precious piece of php code.
May 2nd, 2008 at 7:03 am
Hi Micah, sorry for bother you but i get some rare error..
Parse error: syntax error, unexpected $end in C:\wamp\www\currency\include\db.class.php on line 458
Just including the file I get this.. any ideas?
Best regards and thanks for this..
May 22nd, 2008 at 2:31 pm
[...] the maze of duplicate entries I encountered yesterday through StumbleUpon, I came across this page, which reads: A class for very basic MySQL database connectivity. Written to reduce redundant code [...]
June 3rd, 2008 at 11:04 am
Hey, Micah,
Thanks for the class... It will be very usefull to me...
Thank you again!
June 19th, 2008 at 1:09 am
Hi Micah,
Thanks for your MySql Class.
One question. Is it only working on PHP 5 or also on lower versions ?
Raoul
June 28th, 2008 at 2:07 am
Thanks for a very clear tutorial. This is very helpful since I am making my code to OO design.
more power.
June 29th, 2008 at 10:33 am
Hi,
Is this still being used ?
Thanks
July 20th, 2008 at 3:05 pm
Many thanks - this will come in handy. Using it in a new play project I am creating....
-N@
September 4th, 2008 at 7:31 pm
Hi,
thank's your class, and please answer my question...
how to make query DELETE..
I was tried :
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/html/classmysql/db.class.php on line 137
Many thank's
October 9th, 2008 at 7:34 am
[...] PHP MySQL Database Class Esta es una clase muy b
October 9th, 2008 at 10:18 am
Good job, a nice handy class, i will use it on my scripts.
I found kind of bug, at select, when query fails, row_count remains the same, i think it`s better to set it to 0 ;
if (!$r) {
$this->last_error = mysql_error();
$this->row_count = 0;
return false;
}
October 10th, 2008 at 11:46 pm
hi
many many thanks
October 22nd, 2008 at 8:38 pm
Thanks mate
November 12th, 2008 at 4:12 pm
Thanks a lot. Very useful.
November 16th, 2008 at 11:17 pm
Thanks, You done a Good job.
April 3rd, 2009 at 7:33 am
its very useefull for me.
April 23rd, 2009 at 1:21 pm
excelente muy buena
June 9th, 2009 at 9:41 am
I had the same error as Diego: "Parse error: syntax error, unexpected $end in C:\wamp\www\currency\include\db.class.php on line 458"
The fix on my system was to replace any instance of
June 9th, 2009 at 3:11 pm
Very nice class.
I would suggest that you consider developing another flavour that uses MySqli.
As for this one, there is just one missing thing in it, ESCAPING strings. You can add a public function that can be used to escape values before being put into a query.
June 9th, 2009 at 11:32 pm
The same error as Diego had:
C:\Documents and Settings\harmat\Desktop\PHP5>php-5\php.exe DB_esimerkki_luokat
_testi.php
PHP Parse error: parse error, unexpected $end in C:\Documents and Settings\harm
at\Desktop\PHP5\db.class.php on line 457
Parse error: parse error, unexpected $end in C:\Documents and Settings\harmat\De
sktop\PHP5\db.class.php on line 457
C:\Documents and Settings\harmat\Desktop\PHP5>pause
Press any key to continue . . .
June 10th, 2009 at 8:31 pm
of the 3 classes that i have so far needed to start what i want to do, you have built 2 of them!
Thanks google, and thanks to you for making them.
cheers.
June 10th, 2009 at 11:48 pm
$data = array(
'id' => '',
'timestamp' => '',
'txn_id' => 'ok',
'verify_sign' => 'verified',
);
$id = $db->insert_array('ipns', $data);
if (!$user_id) $db->print_last_error(false);
$db->print_last_query();
$db->dump_query("SELECT * FROM ipns WHERE id=$id");
produces the insert SQL of:
INSERT INTO ipns (id,timestamp,txn_id,verify_sign) VALUES (,,'ok','verified')
wheras it needs to be:
INSERT INTO ipns (id,timestamp,txn_id,verify_sign) VALUES ('','','ok','verified')
for the cases where the variable is set, but has no value.
June 20th, 2009 at 6:07 am
Thanks for sharing, keep up the good work!
June 25th, 2009 at 4:11 am
thank for that script bro.. :-)
August 5th, 2009 at 5:33 am
Thanks for the class, I have been using it for a while. Recently when I started working on a project involving SQLite I modified your class to create a SQLite class.
Details on my website, download here: http://www.zipzapzorum.com/uploads/sqliteclass-1.0.zip
August 11th, 2009 at 9:35 pm
Thanks for this lib, it's handy.
One problem: It lacks something to delete data, and none of the functions is a good fit for that. If you use the "select" one, what gets returned to it is not a valid return type, so it errors out. If you use the "update_sql" one, it always returns true if it did something, which in the case of a delete is not good, if i deleted 0 rows i need to know, but since it returns true (which is equal to 1) whether it deletes no rows or just one you never know. And you can't use the insert ones for this.
This lib needs a nice delete() function. I think that just copying the insert_sql function, and returning the actual number of affected rows would do the trick, gotta check.
August 26th, 2009 at 3:35 pm
Thank you very much for this easy db class.
I am using this class in an app that needs to make 50,000+ inserts every time it runs and was running into a speed issue with insert_array().
I timed my script before and after each of my changes to this class. My test involved inserting 5,000 rows with 33 columns.
Base time was 00:28:33 (not good!)
I narrowed the problem down to the get_column_type() method. The query in that method "SELECT $column FROM $table" builds a result resource of every row in the table then discards it. This is an unnecessary waste of resources. Without changing the way that the method functioned I simply updated the query to read "SELECT $column FROM $table LIMIT 1". The results were dramatic!
Time after first change 00:01:43 (much better!)
Now it looks like we could speed things up by only checking the column type once per column.
Now I modified the method to store the table name and column type in an array. This removes the need to make multiple calls to the get_column_type() method when using insert_array() in an iterator.
Time after second change 00:00:03 (that's what we wanted to see!)
As you can see
If you want to update this class for faster inserts from an array just follow the directions below.
Add a new attribute to the class:
var $col_type_cache;
Inside the constructor, db_class() set the new attribute = array:
$this->col_type_cache = array();
Now just replace the original insert_array method with the new one found below:
Below is the revised method:
function insert_array($table, $data) {
// Inserts a row into the database from key->value pairs in an array. The
// array passed in $data must have keys for the table's columns. You can
// not use any MySQL functions with string and date types with this
// function. You must use insert_sql for that purpose.
// Returns the id of the insert or true if there is not auto_increment
// column in the table. Returns false if there is an error.
if (empty($data)) {
$this->last_error = "You must pass an array to the insert_array() function.";
return false;
}
$cols = '(';
$values = '(';
foreach ($data as $key=>$value) { // iterate values to input
$cols .= "$key,";
// Create array of column types to speed up multiple insertions - Jeff Simmonds
if(array_key_exists($table, $this->col_type_cache)){
if(array_key_exists($key, $this->col_type_cache[$table])){
$col_type = $this->col_type_cache[$table][$key]; // get column type from array
}else{
$col_type = $this->get_column_type($table, $key); // get column type from db
$this->col_type_cache[$table][$key] = $col_type; // insert column type into array
}
}else{
$col_type = $this->get_column_type($table, $key); // get column type from db
$this->col_type_cache[$table] = array($key => $col_type); // insert table name and column type into array
}
if (!$col_type) return false; // error!
// determine if we need to encase the value in single quotes
if (is_null($value)) {
$values .= "NULL,";
}
elseif (substr_count(MYSQL_TYPES_NUMERIC, "$col_type ")) {
$values .= "$value,";
}
elseif (substr_count(MYSQL_TYPES_DATE, "$col_type ")) {
$value = $this->sql_date_format($value, $col_type); // format date
$values .= "'$value',";
}
elseif (substr_count(MYSQL_TYPES_STRING, "$col_type ")) {
if ($this->auto_slashes) $value = addslashes($value);
$values .= "'$value',";
}
}
$cols = rtrim($cols, ',').')';
$values = rtrim($values, ',').')';
// insert values
$sql = "INSERT INTO $table $cols VALUES $values";
return $this->insert_sql($sql);
}
September 16th, 2009 at 4:17 am
Hello!
I have a problem with the method insert_array, when i want insert a value in col type "text". I've got always the message: db.class.php Error:
Column count doesn't match value count at row 1
In my array are 5 keys. The values are everywhere empty. When i change the col type in varchar, then works. But with type text doesnt work it and i dont know why.
December 10th, 2009 at 3:10 am
Those of you who are getting a "Parse error: syntax error, unexpected $end in..." error.
You may need to turn on short tags in your php.ini file: short_opentag=On
http://php.net/manual/en/ini.core.php