PHP select,update,delete related functions in "Db" class

[narekm]

Hi.
I am developing an apllication(btw try) which requires to use methods for deleting,selecting the database rows.
If you write a code for example for delete some rows from the table how would you do it ?

delete($table_name,$where,$limit,$asc...)

how many argument do you usually use ? Can the task that I am explaining be acomplished only writing one method realted to deleteing or one function for delete(..$where) and second for delete( $orderby,$limit) (hope you got the idea) . How you developers usually write that methods in your Db class ?
Thanks in advance!!!

[blocblue]

Why don't you look at the database object from one of the many PHP libraries? E.g. Zend, Kohana, etc? Why re-inventing the wheel?

[narekm]

Why do we learn html and css if there is Dreamveawer ? It is not a process of reinventing something . i want to master my OOP and understand how that methods are mostly defined in Db class ?

[blocblue]

I think there is a big difference between a piece of software, like Dreamweaver, writing bad code for you and looking and learning from code written by some of the best PHP developers worldwide.

I would recommend looking at the Zend Db and Zend Db Select classes. I've found this to be a useful point of reference. One aspect of OOP that is heavily used in the Db class is method chaining. This would also help avoid code replication.

R.

[narekm]

Sorrybut I think you didint understand what I mean ? So let me edit my question this ?
Can you help me with the things I want without using any prepared frameworks ?

[blocblue]

I think you're misunderstanding me too. I appreciate that you don't want to use a class from an existing framework. Instead, I am now suggesting look at examples from frameworks to get an idea of what you could do.

As a starting point, have you considered what functions you're going to try and write? For example, the four most commonly used SQL queries could be considered to be: SELECT, INSERT, UPDATE and DELETE. So, I assume you'll want at least a function for each. I would also recommend that you still have a generic query function, as there are undoubtably going to be instances where your SQL query doesn't nicely fit into one of your tighter functions.

Each of the four queries above is made up of similar clauses: SELECT, INSERT, UPDATE, FROM, JOIN (INNER, LEFT, RIGHT, OUTER), WHERE (AND, OR), HAVING, ORDER, GROUP BY, LIMIT. Were you planning to support all these in your various functions?

My suggestion, borrowed from various framework database implementations is to split these various aspects out into separate methods so you can try and avoid code repetition.

So for a select queries:
SELECT * FROM `table1` WHERE `id` = 1 LIMIT 1;
$db->select('*')->from('table1')->where('id', 1)->limit(1)->execute();

SELECT * FROM `table1` `t1` INNER JOIN `table2` `t2` ON (`t1`.`id` = `t2`.`t1_id`) WHERE `t1`.`id` = 1;
$db->select('*')->from(array('t1' => 'table1'))->join_inner(array('t2' => 'table2'), 't1.id = t2.t1_id')->where('td.id' = 1)->execute();

For a delete query:
DELETE FROM `table1` WHERE `id` = 1 LIMIT 1;
$db->delete()->from('table1')->where('id', 1)->limit(1)->execute();

For insert and update queries, you have a decision to make between using the (cols) VALUES (vals) OR SET col = val approaches.

Does something like the above look workable?

R.

[narekm]

before reading you post i tied to write something like this

public function select($table_name=null,$column_set=null,$where=null,$order_by=null,$asc=null,$low_limit=null,$up_limit=null){
                if(!is_null($table_name) && is_null($column_set) && is_null($where) && is_null($order_by) && is_null($asc) && is_null($low_limit) && is_null($up_limit) ){
                $query="SELECT * FROM {$table_name}";
                //return $this->process($query);
                return $query;
                }else{
                    if(!is_null($table_name)){
                        $query="SELECT FROM {$table_name} ";
                            if(!is_null($column_set)){
                                $i=1;
                                $query="SELECT (";
                                foreach($column_set as $key=>$value){
                                    if($i!=count($column_set)){
                                        $query.="'".$value."'".",";
                                    }else{
                                        $query.="'".$value."'";
                                    }
                                    $i++;
                                }
                                $query.=")";
                                $query.=" FROM {$table_name} ";
                            } // End "if" statement if $column_set is not empty

                            if(!is_null($where)){
                                $query.=" WHERE ".$where;
                            }
                            if(!is_null($order_by)){
                                $query.=" ORDER BY ".$order_by;
                            }
                            if(!is_null($asc)){
                                $query.=" ".$asc;
                            }
                            if(!is_null($low_limit)){
                                $query.=" LIMIT".$low_limit.",";
                            }
                            if(!is_null($up_limit)){
                                $query.=$up_limit;
                            }
                        return $query;
                    }
                    //return $this->process($query);

                }//  End "else" statement
                
           } //END "select()" method

I am looking for this kind of functions for selecting updating ...
What kind of bad things do you see above can i use it as a Db select method ?
Do I omit smth ?
However thank you very much!!

This approach will allow simple queries, but as soon as you want something a bit more complicated like calculated fields, nested queries it looks as if it may become tiresome. I tend to use PDO with a simple wrapper class:

http://code.google.com/p/php-pdo-wrapper-class/

It has a cop-out 'run' function that allows me to write more complex sql. It's not great, but may give you an idea or two.

[narekm]

Ok. I will mark this thread solved and also understood that developers dont use their own home grown Db classes when it comes to that they are running for something alredy written.

I know this is solved narekm, but your last comment may not be strictly true. Creating a robust DB class is extremely difficult (IMO) - sure you can create simple 'templates' for Sel,upd,ins,del, but you'd probably need to be accomplished with the specific DB to make it safe. However, I would suggest that you carry on with this as a project as you will gain a lot of insight into the world of OOP and SQL syntax. :)