#62 ✓resolved
Luiz Alberto Zaiats

SqlBuilder.class.php

Reported by Luiz Alberto Zaiats | February 4th, 2009 @ 08:33 PM | in 0.11.1

Kris,

Problem 1

I think that escape column names with (s) is a great idea! My app uses a column named DELETE (a reserved name in MySQL) and i needed to change the insert() and update() method as follow:


public function insert() {
		$this->insertSanityCheck();

		$sql = 'INSERT INTO ' . $this->table;
		
		$columns = '';
		$values = '';
		$first = true;
		$table_prefix = $this->tableAsPrefix() . '.';
		foreach($this->assignments as $assignment) {
			if($first) { $first = false; }
			else { $columns .= ', '; $values .= ', '; }
			--> $columns .= '`' . str_replace($table_prefix, '', $assignment->column) . '`'; <--
			$values .= $assignment->getQueryParameter();
		}
		$columns = ' (' . $columns . ')';
		$values = '(' . $values . ')';
		
		$sql .= $columns . ' VALUES ' . $values;
		
		return $sql;
	}

and


public function update() {
		$this->updateSanityCheck();
		$sql = 'UPDATE ' . $this->table . ' SET ';
		
		$first = true;
		$table_prefix = $this->tableAsPrefix() . '.';
		foreach($this->assignments as $assignment) {
			if($first) { $first = false; }
			else { $sql .= ', '; }
			--> $sql .= '`' . str_replace($table_prefix, '', $assignment->column) . '` = ' . $assignment->getQueryParameter(); <--
		}
		
		$sql .= $this->whereHelper();
		
		return $sql;
	}

Problem 2

When there are multiple JOINS to read data from MySQL, i encountered a problem on the generated sql query. The order of tables is causing a query error. Examining the generated query, i found that reversing the order of joins solved the problem as follow:


protected function joinHelper() {
		$sql = '';
		if(!empty($this->joins)) {
			--> $this->joins = array_reverse($this->joins, true); <--
			foreach($this->joins as $join) {
				$joinStatement = '';
				
				if(isset($join->natural)) {
					$joinStatement .= $join->natural . ' ';
				}
				if(isset($join->leftRightOrFull)) {
					$joinStatement .= $join->leftRightOrFull . ' ';
				}
				if(isset($join->innerOuterOrCross)) {
					$joinStatement .= $join->innerOuterOrCross . ' ';
				}
				
				$onStatement = ' ON ' . $join->tablePrimaryKey . ' = ' . $join->fromTableForeignKey;
				$joinStatement .= 'JOIN ' . $join->table . $onStatement;
				
				$sql .= $joinStatement;
			}
		}
		return $sql;
	}

That's it!

Comments and changes to this ticket

  • Kris Jordan

    Kris Jordan February 16th, 2009 @ 02:18 PM

    • Milestone set to 0.11.1
    • State changed from “new” to “resolved”

    Luiz - Thanks for the changeset! Your additions have been added to the source tree at git hub with commit 077dc79. Really appreciate your input!

  • Kris Jordan

    Kris Jordan February 17th, 2009 @ 04:48 AM

    I've rewritten all unit tests from SimpleTest to PHPUnit and the process turned up some scenarios which needed to take this technique and use it in SELECTs as well. I've added a helper method in the SqlBuilder which is called with self::escapeWithTicks($string) -- that should add s intelligently.

    I suggest getting the latest copy from github with these fixes and new tests. Thanks again!

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

The Recess PHP Framework is an open source, full stack, RESTful PHP framework.

http://www.recessframework.org/

People watching this ticket

Pages