PHP MySQL Database Class

Hello,

I'm using this nice class to build the backend of a system and it's been very very handy so far.
By the way I might be missing something, but isn't there a way to run a straight sql instruction like 'DELETE FROM xxx WHERE id = 123' ?

I tried using the select() method and it works, but obviously the row_count variable receives a boolean which it isn't supposed to receive and throws a warning:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in E:\webservers\......importer\classes\db.class.php on line 147 ( $this->row_count = mysql_num_rows($r); )

hai, i need help in points calculation in php.

i did the points calculation already. but dont know to input it in the database? can anyone help me out?


$sql ="select * from tbl_predictions where user_id=".$user_id;
$result =$db->readValues($sql);
$prediction_points = 0;


for($k=0;$kreadValues($sql);


$week_points = 0;
if(count($predictions)>0){


for($p=0;$preadValues("select * from tbl_schedule where id_schedule=".$predictions[$p]['match_id']);
$scorer_team1 = explode('^^^',$predictions[$p]['scorer1']);
$scorer_team2 = explode('^^^',$predictions[$p]['scorer2']);

$sql = ("select result from tbl_schedule where period='$week' and id_schedule=".$predictions[$p]['match_id']);
$InsertedR = $db->readValues($sql);


$points = 0;

if($predictions[$p]['result']==$teams[0]['result']){

$points = $points+2;

}

if($predictions[$p]['scoreline1']==$teams[0]['team1_scoreline'] && $predictions[$p]['scoreline2']==$teams[0]['team2_scoreline']){


$points = $points+2;

}

if($predictions[$p]['goal1']==$teams[0]['team1_goals'] && $predictions[$p]['goal2']==$teams[0]['team2_goals']){

$points = $points+2;

}
if($predictions[$p]['redcard1']==$teams[0]['team1_cards'] && $predictions[$p]['redcard2']==$teams[0]['team2_cards']){

$points = $points+2;

}


$scorer1 = array();
$scorer2 = array();
$scorer3 = array();
$scorer4 = array();


$scorer1 = explode('^^^',$teams[0]['team1_scorer']);
$scorer2 = explode('^^^',$teams[0]['team2_scorer']);
$scorer3 = explode('^^^',$predictions[$p]['scorer1']);
$scorer4 = explode('^^^',$predictions[$p]['scorer2']);



$available = array_diff_assoc($scorer1, $scorer3);

#print_r($available);

$available1 = array_diff_assoc($scorer2, $scorer4);

#print_r($available1);

if(count($available)>0&&count($available1)>0){
$points = $points+2;
}



if ($InsertedR[0]['result'] == 0){

$points = 0;
}else{
$points = $points;


}




}

//echo $week.'==='.$week_points;
$week_points = $week_points+$points;


}


$prediction_points =$week_points+$prediction_points ;



// echo $total_points.'=====';


}

?>


How input the week_points into database now, since i have created column in mysql weekPoints. the data will be insert based on user_id per user. how to do it?

Jeff Simmonds, look up 'framework'.
Don't waste your time with what you are doing!!!!
Look up 'describe table' mysql query. You are doing a lot of work no nothing!!!!
triggers, ... alot of things can make ur life easier.
I dont know ur database design, but perhaps is a really bad one!

can you give me code for class schedule,for qoutes and how to create online databank?

Hello,

I am using your class file. Good work. But I am interested know why you did not use any connection close function. And I am getting another error sometimes:

User *** already has more than 'max_user_connections' active connections in

Could you please explain me why its hipping.

You my friend are a genius and a squire! I have been searching the web for the last few days constantly for a class to connect to a db, query it and actually display the results!
Its amazing how many scripts lack all those, thank you for such a simple and easy class & easy to follow script to access it!!

Paluzek:
Change the line:
'define('MYSQL_TYPES_STRING', 'string blob ');'

to:
'define('MYSQL_TYPES_STRING', 'string blob text ');'

Don't forget the space character after 'text'.

For those running MySQL V5+

In the get_column_type function:
Change the line:
' $r = mysql_query("SELECT $column FROM $table");'

to
' $r = mysql_query("SELECT $column FROM $table LIMIT 1");'
as Jeff Simmonds says

or
' $r = mysql_query("SHOW FIELDS FROM $table");'

Thank you for the script ;)

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

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.

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);

}

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.

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

thank for that script bro.. :-)

Thanks for sharing, keep up the good work!

$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.

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.

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 . . .

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.

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

excelente muy buena

its very useefull for me.

Thanks, You done a Good job.

Thanks a lot. Very useful.

Thanks mate

hi

many many thanks

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;
}

[...] PHP MySQL Database Class Esta es una clase muy b

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

Many thanks - this will come in handy. Using it in a new play project I am creating....
-N@

Hi,

Is this still being used ?

Thanks

Thanks for a very clear tutorial. This is very helpful since I am making my code to OO design.

more power.

Hi Micah,

Thanks for your MySql Class.
One question. Is it only working on PHP 5 or also on lower versions ?

Raoul

Hey, Micah,

Thanks for the class... It will be very usefull to me...

Thank you again!

[...] 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 [...]

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..

This database class is a precious piece of php code.

Are you sure you typed the database name correctly? Are you sure there is a database setup online?

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?

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?

RE: serkan
The sample file included with the class shows how this is done.

how can i make a class to insert data to database in php