Database Class :: Feedback Requested

[Waizujin]

I create a database class and I was hoping people could comment on it. Let me know if anything can be improved.

It's VERY simple.

[BlackScorp]

Well i wouldnt add mysql_connect and select db into constructor, i would add it in a static function. so you can call db::connect(); once in the bootstrap php and you can just execute the mysql query. otherwise you connect on every included page, send some querys, disconnect , then connect again and so on..

instead of Fetch or query methods, i would check what kind of Query you have and give back an result over parameter.
it had some advantages:
a) you dont have to remember when to use which method
b) with parameters what kind of result you want to have i could be more flexible(for example if i want only to get one row, or i want to get mysql_fetch_object)

then , vsprintf is nice but you dont escape your values.. or do i have it before calling the method?

and last but not least you dont throw any errors. if i use the class and i dont get the right result i dont know why, is it because your class has a bug? or because my query has a wrong syntax?

try change it:D

Greeting blackscorp

[ajacksified]

My initial reaction is that you should probably be using mysqli rather than mysql (reference: http://uk3.php.net/manual/en/mysqli.overview.php)

That said, what does your database class provide that the mysqli extension doesn't? You're still passing in raw sql to run. Is the automatic execution / return of objects / result rows what you're after?

(Just asking some questions / giving you feedback; don't take this as negative criticism.)

[BlackScorp]

instea of mysql and mysqli, i would implement PDO but thats another points

[Barrikor]

It's a good way to start the class, you'll want to add more functions to make the class do more later.

Like the others have pointed out:
- the mysqli set of functions is the newer+better way to use mysql now
- You could filter or escape the vars in your query() function


Personally I'd take connect and disconnect out of __construct() and __destruct() and give them their own functions, but that depends on your style and how you're gonna use the class.

[BlackScorp]

"the mysqli set of functions is the newer+better way to use mysql now"

why does everyone says that? MySQLi is a complete another Database, so before you use mysqli_* functions you have to install the MySQLi Database on your webspace. mysqli_* functions are the same like mysql_* the different between mysqli und mysqli is , that youre able to use mysqli as classes. youre able to use somethink like $sql = new mysqli($connectArray); and after it $sql->query("SELECT * Bla"); that would be the newer and better way, but if you use mysqli_connect or mysqli_select_db and such that would not be the better way.

at the end, the Best way, which is used by most Frameworks is to use PDO or ORM which use the PDO.. but mysqli_* is not better than mysql_*

[ajacksified]

"the mysqli set of functions is the newer+better way to use mysql now"

why does everyone says that? MySQLi is a complete another Database, so before you use mysqli_* functions you have to install the MySQLi Database on your webspace. mysqli_* functions are the same like mysql_* the different between mysqli und mysqli is , that youre able to use mysqli as classes. youre able to use somethink like $sql = new mysqli($connectArray); and after it $sql->query("SELECT * Bla"); that would be the newer and better way, but if you use mysqli_connect or mysqli_select_db and such that would not be the better way.

at the end, the Best way, which is used by most Frameworks is to use PDO or ORM which use the PDO.. but mysqli_* is not better than mysql_*

• MySQLi is not "another database"; it's a newer library for php to deal with MySql
• You can structure parameterized queries in a much more secure way instead of worrying so much about SQL injection attacks.
• It follows OOP paradigms
• Transaction support
• Multiple query support
• If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead. from the php docs

I assume that from your assumption that mysqli is another database that you aren't familiar with mysql connections with PHP. I'd look at the mysqli overview page here to get an idea: http://php.net/manual/en/mysqli.overview.php

Some people use PDO, sure. But your assumption that mysql is just as good is false.

[BlackScorp]

MySQLi is not "another database"; it's a newer library for php to deal with MySql

oh that is my Fault. last time i used MySQL with Xampp there was an Option for MySQL it called MySQL light. It was Basicly a Textfile Database. Since iam using only PDO iam not really up to date.

You can structure parameterized queries in a much more secure way instead of worrying so much about SQL injection attacks.

Only if you use Prepared Statments, else you have to Escape your Queries Anyways

It follows OOP paradigms

Only if you use OOP way of mysqli, if you use it Procedural so it is kinda same like MySQL

Transaction support

not everytime needed.

Multiple query support

because of Prepared Statements

If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead. from the php docs

Actually MySQL version is 5.5.16, if your server isnt up to date then i would agree.

But your assumption that mysql is just as good is false.

i told that MySQLi is only better if you using the OOP Way of MySQLi. if you use procedural way it is kinda same like MySQL.

i agree that MySQli has more features to MySQL but only to telling them to use mysqli instead of mysql, they would just replace their function calls in the code and that would not be the better way... that is what iam tryin to say:D (damn english)

Best regards BlackScorp

[hiigara]

• Transaction support

You don't need to use mysqli to use transactions.

What's wrong with doing:
mysql_query ("start transaction");   ?

[Chris]

Why is it said that MySQLi is better than MySQL? Anyone knows some details?
I have not noticed people rewriting their code to the "i", so I'm a bit sceptical...

[BlackScorp]

Why is it said that MySQLi is better than MySQL? Anyone knows some details?
I have not noticed people rewriting their code to the "i", so I'm a bit sceptical...

its because of the functions for Prepared Statements and Multiple Queries if you just just replace mysql_* to mysqli_* there is nothink better then.

[ajacksified]

You can structure parameterized queries in a much more secure way instead of worrying so much about SQL injection attacks.

Only if you use Prepared Statments, else you have to Escape your Queries Anyways

Why would you not use prepared statements?

If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use the mysqli extension instead. from the php docs

Actually MySQL version is 5.5.16, if your server isnt up to date then i would agree.

It says if you're using 4.1.3 or later, you should be using mysqli. So, yeah, with 5.5.16, you should be using mysqli, not mysql.

i agree that MySQli has more features to MySQL but only to telling them to use mysqli instead of mysql, they would just replace their function calls in the code and that would not be the better way... that is what iam tryin to say

It's not just about what has more features, it's also about what's currently being worked on, and what's generally accepted as good practice. (From the official php docs, that's mysqli.) It's about cleaner code and better security (read: prepared statements).

Unless you have a tangible reason to use mysql, you shouldn't- and chances are really high that you don't. I'm suggesting using modern practice rather than sticking with "well, that's what I did in 2005, so I guess it's alright."

[BlackScorp]

thats why i started to use PDO so i dont have to care about mysql or mysqli or maybe some days there will be a new lib mysql super or somethink like that:D PDO is up to date and iam happy with it:D

[codestryke]

Why would you not use prepared statements?

For every query you are going to the database twice (once to prepare the sql and the second to execute the query). Prepared statements are expensive to run because of the additional trip to the server.

[hiigara]

If you are a beginner learning PHP for the first time, then I would recommend learning mysqli instead of mysql.
But even in this case, most online tutorials use plain mysql.
People who already use plain mysql will not bother to migrate to mysqli, because if there is a bottleneck, it will not be solved by using mysqli. The gains are very small.

[BlackScorp]

Why would you not use prepared statements?

For every query you are going to the database twice (once to prepare the sql and the second to execute the query). Prepared statements are expensive to run because of the additional trip to the server.

i think if your page make about 5 quries each call you dont even need prepared statements, i think they are made for sites who make 1000 Quries:D for example if some one using like this

$sql1 = "SELECT post,user_id... FROM forum WHERE topic_id = 12345"

foreach($row = mysql_fetch_assoc($sql1)){
$sql2 = "SELECT username FROM users WHERE id = ".$row->user_id
}

then ppl prepare those statements and sending them instead of using joins

[Waizujin]

It's been a while since I posted this, but I wanted to let you guys know that I eventually decided to switch to using PDO and am very happy with it thus far.