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

Question

narekm 0 Light Poster

14 Years Ago

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

php

3 Contributors
9 Replies
284 Views
1 Day Discussion Span
Latest Post 14 Years Ago Latest Post by Member #120589

Dani AI

Generated 9 Months Ago

A pragmatic approach: separate responsibilities and avoid long positional argument lists. Keep a small PDO-based executor (prepare/execute/run), a thin query-builder (fluent or helper methods that accumulate clauses and bindings), and a few focused CRUD wrappers that call the builder. That prevents the brittle string-concatenation style shown by , while still giving the OOP practice sought. The suggestions from (study framework builders / method chaining) and (PDO wrapper plus a raw-run fallback) fit together well.

Minimal, safe delete pattern (assumes $this->pdo is a PDO instance; identifier names must be validated/whitelisted):

public function delete(string $table, array $where = [], ?int $limit = null): int
{
    if (empty($where)) {
        throw new RuntimeException('Refusing to delete without WHERE clause');
    }

    // Validate $table and column names against an allowed list before using them.
    $clauses = [];
    $params = [];
    foreach ($where as $col => $val) {
        $ph = ':w_' . preg_replace('/\W+/', '_', $col);
        $clauses[] = "`$col` = $ph";
        $params[$ph] = $val;
    }

    $sql = 'DELETE FROM `' . $table . '` WHERE ' . implode(' AND ', $clauses);
    if ($limit !== null) {
        $sql .= ' LIMIT ' . (int)$limit;
    }

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->rowCount();
}

Practical notes: never bind identifiers — always whitelist table/column names or map public names to safe identifiers. Refuse empty WHERE clauses (or require an explicit flag) to avoid accidental mass-deletes. Use transactions around multi-statement operations and return rowCount() (or throw on error) so callers can react. Keep a simple run($sql, $params) method as an escape hatch for complex queries and for tests. Implement logging in dev only.

Implementation roadmap: create a tiny PDO wrapper, add a reusable where-to-bind helper, expose chainable builder methods later if desired, and write unit/integration tests against a disposable DB. That delivers safer, testable OOP code without needing to copy a full framework.

All 9 Replies

blocblue 238 Posting Pro in Training

14 Years Ago

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

blocblue 238 Posting Pro in Training

14 Years Ago

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.

blocblue 238 Posting Pro in Training

14 Years Ago

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'), ' = t2.t1_id')->where('' = 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.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

narekm 0 Light Poster · Answer 1 · 2011-11-19T01:16:00+00:00

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 ?

narekm 0 Light Poster · Answer 2 · 2011-11-19T12:19:44+00:00

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 ?

narekm 0 Light Poster · Answer 3 · 2011-11-19T17:05:52+00:00

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

Member #120589 · Answer 4 · 2011-11-19T17:30:20+00:00

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 0 Light Poster · Answer 5 · 2011-11-19T17:43:36+00:00

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.

Member #120589 · Answer 6 · 2011-11-19T18:02:12+00:00

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

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

Dani AI

Recommended Answers Collapse Answers

All 9 Replies

Recommended Answers