SqlBuilder.class.php

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!