HELP: Parse a SQL query, do some change, and compse a new query

@cysinNov 21.2008

Hello everyone, is there any way to parse a SQL query, and do some change, adding some where conditions for example, and then compose a new SQL query?
The first thing that came into my mind is using ORM, because they can add any condition or do whatever you want with a SQL query. But I don’t know how to convert a SQL query into ORM object (Doctrine, or Propel), and if they can output the SQL query that I want.

I don’t know if my idea is feasible, and anyone can help?

Any help is appreciated and thanks in advance~

to post a comment

PHP

7 Comments(s) _↴

@GeatNov 21.2008 — #Hy Cysin

Perhaps if you pop in some pseudo code here, kind of a step-by-step example of what you're trying to achieve, it may give people something more to work with.

@cysinauthorNov 22.2008 — #Hy Cysin

Perhaps if you pop in some pseudo code here, kind of a step-by-step example of what you're trying to achieve, it may give people something more to work with.[/QUOTE]

Well, I am trying to explain what I want to do.

For example, there are lots of SQL queries, like the following:

SELECT * FROM members WHERE memberID=34

UPDATE articles SET author='john', time=NOW() WHERE articelID=67

...

...

Now, I need to do something with the queries, add a where condition "siteID=2" to each of the queries, so the above queries will be:

SELECT * FROM members WHERE siteID=2 AND memberID=34

UPDATE articles SET author='john', time=NOW() WHERE siteID=2 AND articelID=67

So here comes the problem, it is simply a manipulation of strings, but how do I add the "siteID=2" to each of the SQL queries? Using regular expression, or something else like a SQL parser?

Maybe I can write a scripts to deal with simple SQL like SELECT * FROM table WHERE, but how to do with the complex queries, like join, union etc, I am not sure how to achieve that.

I really need help~~

@skywalker2208Nov 22.2008 — #Add it to a variable and then add the variable to the query string. Something like below.

[code=php]
 $add = '';
 if ($something != '') {
 $add = " AND siteID=2 ";
 }
 
 $query = "SELECT * FROM table_name WHERE articleId=38 $add";
 [/code]

@cysinauthorNov 22.2008 — #That's the problem. How do I add the where condition correctly? There are too many things to consider about if you just manipulate the query string:

SELECT

[ALL | DISTINCT | DISTINCTROW ]

[HIGH_PRIORITY]

[STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

select_expr, ...

[FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[PROCEDURE procedure_name(argument_list)]

[INTO OUTFILE 'file_name' export_options

| INTO DUMPFILE 'file_name'

| INTO var_name [, var_name]]

[FOR UPDATE | LOCK IN SHARE MODE]]

Do I need to analyze it all?

@MindzaiNov 22.2008 — #Maybe i'm not understanding the problem properly but I dont get why you can't just build your queries using specific logic for each situation.

@cysinauthorNov 22.2008 — #Maybe i'm not understanding the problem properly but I dont get why you can't just build your queries using specific logic for each situation.[/QUOTE]

Actually I am planning to build many small sites based on Drupal. As you konw, each drupal site has a standalone database, but lots of the tables' structures are the same. So that's why I want to do this, one Drupal code base and one databse~

@MindzaiNov 22.2008 — #So you're trying to absrtact your database access, i.e. make some standard functionality which allows you to interact with your Drupal database easier? If that's the case I would avoid trying to make a single universal function which you can bend to every situation. A better approach would be to write a set of functions and wrap them in a class to encapsulate all of your drupal database operations. Something like this (untested!):

[code=php]class DrupalDatabase {
 
  private $host = 'localhost';
  private $port = 3306;
  private $user = 'root';
  private $pass = 'password';
  private $db   = 'test';
  private $link;
  public  $last_sql;
  private static $me;
 
  /**
   * private contructor prevents instance being created outside the class
   */
  private function __construct() {
 
   $this->connect();
 
  }
 
  /**
   * ensures the class is a singleton, ie only one instance of it can exist at a time
   */
  public function getInstance() {
 
   if (self::$me == null) self::$me = new DrupalDatabase;
   return self::$me;
 
  }
 
  /**
   * connect to the database
   */
  private function connect() {
 
   $this->link = mysql_connect($this->host . ':' . $this->port, $this->user, $this->pass);
   mysql_selectdb($db, $this->link); 
 
  }
 
  /**
   * runs a query against the database and returns an associaltive array of results
   */
  private function getResultsArray($sql) {
 
   $this->last_sql = $sql;
 
   if (!$result = mysql_query($sql, $this->link)) return false;
 
   while ($row = mysql_fetch_assoc($result)) {
   $results[] = $row;
   }
 
   return $results;
 
  }
 
  /**
   * exectues a query an returns true on success or false on failure
   */
  private function execute($sql) {
 
   $this->last_sql = $sql);
 
   return mysql_query($sql, $this->link);
 
  }
 
  /**
   * custom drupal functions can be added here
   * v v v v v v v v v v v v v v v v v v v v v
   */
 
  public function getMembers($userid=null) {
 
   // build a query to get all members
   $sql = 'SELECT * FROM members';
 
   // add in the where clause if a userid was supplied
   if ($userid) $sql .= ' WHERE memberID = ' . intval($userid);
 
   // run the query
   $members = $this->getResultsArray($sql);
 
   return (count($members) == 1) ? $members[0] : $members;
 
  }
 
  public function setAuthor($author, $articleid) {
 
   // build the query
   $sql = "UPDATE articles SET author = '$author', time = NOW() WHERE articleID = $articleid";
 
   // run the query
   return $this->execute($sql);
  }
 
  // etc
 
 }
 
 // example usage
 
 $db = DrupalDatabase::getInstance();
 
 $all_users     = $db->getMembers();
 $specific_user = $db->getMembers(34);
 
 if ($db->setAuthor($specific_user['name'], 67)) {
 echo 'Article 67 updated! Author set to ' . $specific_user['name'];
 }
 
 // etc[/code]

Also in #PHP _↴

Php Dst Multiple select options, how to get all?Noob Question

Success!

Help @cysin spread the word by sharing this article on Twitter...

Tweet This

HELP: Parse a SQL query, do some change, and compse a new query

7 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

HELP: Parse a SQL query, do some change, and compse a new query

7 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

7 Comments(s) _↴

Also in #PHP _↴