public static function parseSQL($sql) { $tag = "SQLSelectQuery::parseSQL()"; Log::debug("{$tag}"); // Trim empty characters $sql = trim($sql); // Instantiate a new object $query = new SQLSelectQuery(); // parse sql string to init data $STATE_START = 0; $STATE_SELECT = 1; $STATE_FROM = 2; $STATE_WHERE = 3; $STATE_GROUP_BY = 4; $STATE_HAVING = 5; $STATE_ORDER_BY = 6; $STATE_LIMIT = 7; $state = $STATE_START; $count_open_paren = 0; $bool_open_single_quote = false; $bool_open_double_quote = false; $buff = ""; $strlen = strlen($sql); for ($i = 0; $i < strlen($sql); $i++) { $char = $sql[$i]; // keep track of open parenthesis, single, and double quotes if ("(" == $char) { $count_open_paren++; } else { if (")" == $char) { $count_open_paren--; } else { if ("'" == $char) { $bool_open_single_quote = !$bool_open_single_quote; } else { if ("\"" == $char) { $bool_open_double_quote = !$bool_open_double_quote; } } } } switch ($state) { case $STATE_START: // wait until state changes to STATE_SELECT if ("SELECT" == strtoupper($buff)) { // change state $state = $STATE_SELECT; // clear buffer $buff = ""; } else { if ($strlen == $i + 1) { throw new Exception("{$tag}: Syntax Error: Missing required SELECT clause"); } else { // append current character to the sequence buffer $buff .= $char; } } break; case $STATE_SELECT: // trim leading spaces if ($char == " " && strlen($buff) == 0) { // skip character continue; } else { if ($char == "," && $count_open_paren == 0 && $bool_open_single_quote == false && $bool_open_double_quote == false) { // add sequence to SELECT cluase $query->select($buff); // clear sequence buffer $buff = ""; } else { if ("FROM" == strtoupper(substr($buff, strlen("FROM") - strlen($buff)))) { // add sequence (not including FROM) to SELECT clause $query->select(substr($buff, 0, strpos(strtoupper($buff), "FROM"))); // change state $state = $STATE_FROM; // clear sequence buffer $buff = ""; } else { if ($strlen == $i + 1) { // add final character to sequence buffer $buff .= $char; // add sequence to SELECT clause $query->select($buff); } else { // append current char to sequence buffer $buff .= $char; } } } } break; case $STATE_FROM: // trim leading spaces if ($char == " " && strlen($buff) == 0) { // skip character continue; } else { if ($char == "," && $count_open_paren == 0 && $bool_open_single_quote == false && $bool_open_double_quote == false) { // add sequence to FROM cluase $query->from($buff); // clear sequence buffer $buff = ""; } else { if ("WHERE" == strtoupper(substr($buff, strlen("WHERE") - strlen($buff)))) { // add sequence (not including WHERE) to FROM clause $query->from(substr($buff, 0, strpos(strtoupper($buff), "WHERE"))); // change state $state = $STATE_WHERE; // clear sequence buffer $buff = ""; } else { if ("GROUP BY" == strtoupper(substr($buff, strlen("GROUP BY") - strlen($buff)))) { // add sequence (not including GROUP BY) to FROM clause $query->from(substr($buff, 0, strpos(strtoupper($buff), "GROUP BY"))); // change state $state = $STATE_GROUP_BY; // clear sequence buffer $buff = ""; } else { if ("ORDER BY" == strtoupper(substr($buff, strlen("ORDER BY") - strlen($buff)))) { // add sequence (not including ORDER BY) to FROM clause $query->select(substr($buff, 0, strpos(strtoupper($buff), "ORDER BY"))); // change state $state = $STATE_ORDER_BY; // clear sequence buffer $buff = ""; } else { if ("LIMIT" == strtoupper(substr($buff, strlen("LIMIT") - strlen($buff)))) { // add sequence (not including LIMIT) to FROM clause $query->select(substr($buff, 0, strpos(strtoupper($buff), "LIMIT"))); // change state $state = $STATE_LIMIT; // clear sequence buffer $buff = ""; } else { if ($strlen == $i + 1) { // add final character to sequence buffer $buff .= $char; // add sequence to FROM clause $query->from($buff); } else { // append current char to sequence buffer $buff .= $char; } } } } } } } break; case $STATE_WHERE: // Include entire WHERE clause from sql string to parse as a single element // this simplifies the inclusion of logical operators in the sql string // Subsequent WHERE clauses added with where() will be added to the query with AND operator // trim leading spaces if ($char == " " && strlen($buff) == 0) { // skip character continue; } else { if ("GROUP BY" == strtoupper(substr($buff, strlen("GROUP BY") - strlen($buff)))) { // add sequence (not including GROUP BY) to WHERE clause $query->where(substr($buff, 0, strpos(strtoupper($buff), "GROUP BY"))); // change state $state = $STATE_GROUP_BY; // clear sequence buffer $buff = ""; } else { if ("ORDER BY" == strtoupper(substr($buff, strlen("ORDER BY") - strlen($buff)))) { // add sequence (not including ORDER BY) to WHERE clause $query->where(substr($buff, 0, strpos(strtoupper($buff), "ORDER BY"))); // change state $state = $STATE_ORDER_BY; // clear sequence buffer $buff = ""; } else { if ("LIMIT" == strtoupper(substr($buff, strlen("LIMIT") - strlen($buff)))) { // add sequence (not including LIMIT) to WHERE clause $query->where(substr($buff, 0, strpos(strtoupper($buff), "LIMIT"))); // change state $state = $STATE_LIMIT; // clear sequence buffer $buff = ""; } else { if ($strlen == $i + 1) { // add final character to sequence buffer $buff .= $char; // add sequence to WHERE clause $query->where($buff); } else { // append current char to sequence buffer $buff .= $char; } } } } } break; case $STATE_GROUP_BY: // trim leading spaces if ($char == " " && strlen($buff) == 0) { // skip character continue; } else { if ($char == "," && $count_open_paren == 0 && $bool_open_single_quote == false && $bool_open_double_quote == false) { // add sequence to GROUP BY cluase $query->groupBy($buff); // clear sequence buffer $buff = ""; } else { if ("HAVING" == strtoupper(substr($buff, strlen("HAVING") - strlen($buff)))) { // add sequence (not including HAVING) to GROUP BY clause $query->groupBy(substr($buff, 0, strpos(strtoupper($buff), "HAVING"))); // change state $state = $STATE_HAVING; // clear sequence buffer $buff = ""; } else { if ("ORDER BY" == strtoupper(substr($buff, strlen("ORDER BY") - strlen($buff)))) { // add sequence (not including ORDER BY) to GROUP BY clause $query->groupBy(substr($buff, 0, strpos(strtoupper($buff), "ORDER BY"))); // change state $state = $STATE_ORDER_BY; // clear sequence buffer $buff = ""; } else { if ("LIMIT" == strtoupper(substr($buff, strlen("LIMIT") - strlen($buff)))) { // add sequence (not including LIMIT) to GROUP BY clause $query->groupBy(substr($buff, 0, strpos(strtoupper($buff), "LIMIT"))); // change state $state = $STATE_LIMIT; // clear sequence buffer $buff = ""; } else { if ($strlen == $i + 1) { // add final character to sequence buffer $buff .= $char; // add sequence to GROUP BY clause $query->groupBy($buff); } else { // append current char to sequence buffer $buff .= $char; } } } } } } break; case $STATE_HAVING: // Include entire HAVING clause from sql string to parse as a single element // this simplifies the inclusion of logical operators in the sql string // Subsequent HAVING clauses added with having() will be added to the query with AND operator // trim leading spaces if ($char == " " && strlen($buff) == 0) { // skip character continue; } else { if ("ORDER BY" == strtoupper(substr($buff, strlen("ORDER BY") - strlen($buff)))) { // add sequence (not including ORDER BY) to HAVING clause $query->having(substr($buff, 0, strpos(strtoupper($buff), "ORDER BY"))); // change state $state = $STATE_ORDER_BY; // clear sequence buffer $buff = ""; } else { if ("LIMIT" == strtoupper(substr($buff, strlen("LIMIT") - strlen($buff)))) { // add sequence (not including LIMIT) to HAVING clause $query->having(substr($buff, 0, strpos(strtoupper($buff), "LIMIT"))); // change state $state = $STATE_LIMIT; // clear sequence buffer $buff = ""; } else { if ($strlen == $i + 1) { // add final character to sequence buffer $buff .= $char; // add sequence to HAVING clause $query->having($buff); } else { // append current char to sequence buffer $buff .= $char; } } } } break; case $STATE_ORDER_BY: // trim leading spaces if ($char == " " && strlen($buff) == 0) { // skip character continue; } else { if ($char == "," && $count_open_paren == 0 && $bool_open_single_quote == false && $bool_open_double_quote == false) { // add sequence to ORDER BY cluase $query->orderBy($buff); // clear sequence buffer $buff = ""; } else { if ("LIMIT" == strtoupper(substr($buff, strlen("LIMIT") - strlen($buff)))) { // add sequence (not including LIMIT) to ORDER BY clause $query->orderBy(substr($buff, 0, strpos(strtoupper($buff), "LIMIT"))); // change state $state = $STATE_LIMIT; // clear sequence buffer $buff = ""; } else { if ($strlen == $i + 1) { // add final character to sequence buffer $buff .= $char; // add sequence to ORDER BY clause $query->orderBy($buff); } else { // append current char to sequence buffer $buff .= $char; } } } } break; case $STATE_LIMIT: // trim leading spaces if ($char == " " && strlen($buff) == 0) { // skip character continue; } else { if ($strlen == $i + 1) { // add final character to sequence buffer $buff .= $char; // add sequence to LIMIT clause $query->limit($buff); } else { // append current char to sequence buffer $buff .= $char; } } break; } // END: switch($state) } // END: for(all chars) return $query; }
<?php require_once "ClassLoader.php"; echo "<h1>Testing SQLSelectQuery</h1>"; $sqlSelectQuery = SQLSelectQuery::parseSQL("SELECT id, login, passwd AS Secret FROM Member WHERE status='Active' AND id>'2' ORDER BY login ASC LIMIT 5"); echo $sqlSelectQuery->toString(); echo "<br/><br/>"; $sql = new SQLSelectQuery(); $sql->select("id"); $sql->select("member.name as name"); $sql->select("count(*) as num"); $sql->from("access"); $sql->leftjoin("member", "access.member_id", "member.id"); $sql->where("access.type='GET'"); $sql->having("num > 1"); $sql->groupBy("member.name"); $sql->orderBy("num DESC"); $sql->limit("25"); echo $sql->toString();