PHP 4.0 Bug #5586: Subselects in MySQL

From: d.rethans@jdimedia.nl Operating system: PHP version: 4.0.1pl2 PHP Bug Type: Feature/Change Request Bug description: Subselects in MySQL MySQL doesn't support subquery's like "WHERE column IN (SELECT...". I would like to see the following path applied which makes it possible to use such subqueries (at the moment only very simple) with MySQL. The patch creates a new funtion (mysql_ext_query, name can be changed of course). This function rewrites a query with a subquery to a normal query and executes it. This is all transparent to the user. Myself I need this function for an assigment (I must use Mysql), so I would like to see this patch applied. I also applied for a cvs account (for this mather, and hopefully more), but I didn't get any reaction. Derick Rethans Patch follows: ------------- Index: php_mysql.c =================================================================== RCS file: /repository/php4/ext/mysql/php_mysql.c,v retrieving revision 1.51 diff -u -r1.51 php_mysql.c --- php_mysql.c 2000/07/05 20:41:06 1.51 +++ php_mysql.c 2000/07/11 10:15:49 @@ -93,6 +93,7 @@ PHP_FE(mysql_create_db, NULL) PHP_FE(mysql_drop_db, NULL) PHP_FE(mysql_query, NULL) + PHP_FE(mysql_ext_query, NULL) PHP_FE(mysql_db_query, NULL) PHP_FE(mysql_list_dbs, NULL) PHP_FE(mysql_list_tables, NULL) @@ -769,6 +770,263 @@ RETURN_FALSE; } #endif + if ((mysql_result=mysql_store_result(mysql))==NULL) { + if (PHP_MYSQL_VALID_RESULT(mysql)) { /* query should have returned rows */ + php_error(E_WARNING, "MySQL: Unable to save result set"); + RETURN_FALSE; + } else { + RETURN_TRUE; + } + } + ZEND_REGISTER_RESOURCE(return_value, mysql_result, le_result); +} +/* }}} */ + +char* mysql_scanner (MYSQL* mysql, char* query) +{ + long int pos, beginpos, endpos; + int level = 1, i; + int inquotes = 0; + char* keyword; + char* subquery; + char newquery[1024]; + int spos = 0; + MYSQL_RES* res; + unsigned long* lengths; + char* mquery; + char* uc_query; + int found; + + uc_query = estrdup (query); /* Creating uppercase version of */ + for (i = 0; i < strlen (query); i++) { /* query for comparison. */ + uc_query[i] = toupper (query[i]); + } + /* Scanning query for "NOT IN (SELECT" (case-insensitive) */ + if ((void *) (endpos = (long int) strstr (uc_query, " NOT IN (SELECT")) != NULL) + { + /* Getting Keyword */ + endpos = endpos - (long int) uc_query - 1; + found = 0; + for (i = endpos; i > 0; i--) { + if (query[i] == ' ') { + beginpos = i; + i = 0; + found = 1; + } + } + efree (uc_query); + if (found == 0) { + php_error (E_WARNING, "MySQL: Couldn't find keyword for subselect."); + } + keyword = (char*) emalloc (endpos - beginpos + 1); + strncpy (keyword, (char *) query + beginpos + 1, endpos - beginpos); + + strncpy (newquery, query, beginpos); + newquery[beginpos] = 0; + strcat (newquery, " NOT ("); + + uc_query = estrdup (query); + for (i = 0; i < strlen (query); i++) { + uc_query[i] = toupper (query[i]); + } + pos = (long int) strstr (uc_query, " NOT IN (SELECT") - (long int) uc_query + 8; + subquery = (char*) emalloc (strlen (query) - pos + 1); + efree (uc_query); + /* Scanning for subquery */ + do { + pos++; + + if (query[pos] == '"') + inquotes = !inquotes; + if (inquotes == 0) { + switch (query[pos]) { + case '(': level++; + break; + case ')': level--; + break; + } + } + + if (level != 0 || query[pos] != ')') { + subquery[spos] = query[pos]; + spos++; + } + else { + /* At end of subquery, execute it and assemble the new */ + /* query as a string */ + unsigned int num_rows; + unsigned int i; + MYSQL_ROW row; + + mysql_query (mysql, subquery); + res = mysql_store_result (mysql); + num_rows = mysql_num_rows (res); + + for (i = 0; i < num_rows; i++) { + row = mysql_fetch_row (res); + + strcat (newquery, "("); + strcat (newquery, keyword); + strcat (newquery, " = \""); + strcat (newquery, row[0]); + if (i == (num_rows - 1)) + strcat (newquery, "\")"); + else + strcat (newquery, "\") OR "); + } + mysql_free_result (res); + } + } while ((level != 0 || query[pos] != ')') && pos < strlen (query)); + if (level) + php_error (E_WARNING, "MySQL: Sub-select not terminated"); + + strcat (newquery, ")"); + efree (keyword); + efree (subquery); + /* Return the created string, so that it can be processed */ + mquery = (char *) emalloc (strlen(newquery) + 1); + strcpy (mquery, newquery); + return mquery; + } + else + /* Scanning query for "IN (SELECT" (case-insensitive) */ + if ((void *) (endpos = (long int) strstr (uc_query, " IN (SELECT")) != NULL) + { + /* Getting Keyword */ + endpos = endpos - (long int) uc_query - 1; + found = 0; + for (i = endpos; i > 0; i--) { + if (query[i] == ' ') { + beginpos = i; + i = 0; + found = 1; + } + } + efree (uc_query); + if (found == 0) { + php_error (E_WARNING, "MySQL: Couldn't find keyword for subselect."); + } + keyword = (char*) emalloc (endpos - beginpos + 1); + strncpy (keyword, (char *) query + beginpos + 1, endpos - beginpos); + + strncpy (newquery, query, beginpos); + newquery[beginpos] = 0; + strcat (newquery, " ("); + + uc_query = estrdup (query); + for (i = 0; i < strlen (query); i++) { + uc_query[i] = toupper (query[i]); + } + pos = (long int) strstr (uc_query, " IN (SELECT") - (long int) uc_query + 4; + subquery = (char*) emalloc (strlen (query) - pos + 1); + efree (uc_query); + /* Scanning for subquery */ + do { + pos++; + + if (query[pos] == '"') + inquotes = !inquotes; + if (inquotes == 0) { + switch (query[pos]) { + case '(': level++; + break; + case ')': level--; + break; + } + } + + if (level != 0 || query[pos] != ')') { + subquery[spos] = query[pos]; + spos++; + } + else { + /* At end of subquery, execute it and assemble the new */ + /* query as a string */ + unsigned int num_rows; + unsigned int i; + MYSQL_ROW row; + + mysql_query (mysql, subquery); + res = mysql_store_result (mysql); + num_rows = mysql_num_rows (res); + + for (i = 0; i < num_rows; i++) { + row = mysql_fetch_row (res); + + strcat (newquery, "("); + strcat (newquery, keyword); + strcat (newquery, " = \""); + strcat (newquery, row[0]); + if (i == (num_rows - 1)) + strcat (newquery, "\")"); + else + strcat (newquery, "\") OR "); + } + mysql_free_result (res); + } + } while ((level != 0 || query[pos] != ')') && pos < strlen (query)); + if (level) + php_error (E_WARNING, "MySQL: Sub-select not terminated"); + + strcat (newquery, ")"); + efree (keyword); + efree (subquery); + /* Return the created string, so that it can be processed */ + mquery = (char *) emalloc (strlen(newquery) + 1); + strcpy (mquery, newquery); + return mquery; + } + else + { + /* If no subquery is found, return the original query after issueing */ + /* a warning */ + php_error (E_WARNING, "MySQL: Couldn't find a subquery"); + mquery = estrdup (query); + return mquery; + } +} + +/* {{{ proto int mysql_ext_query(string query [, int link_identifier]) + Send an SQL query to MySQL */ +PHP_FUNCTION(mysql_ext_query) +{ + pval **query, **mysql_link; + int id; + MYSQL *mysql; + MYSQL_RES *mysql_result; + char* newquery; + MySLS_FETCH(); + + switch(ZEND_NUM_ARGS()) { + case 1: + if (zend_get_parameters_ex(1, &query)==FAILURE) { + RETURN_FALSE; + } + id = php_mysql_get_default_link(INTERNAL_FUNCTION_PARAM_PASSTHRU, MySLS_CC); + CHECK_LINK(id); + break; + case 2: + if (zend_get_parameters_ex(2, &query, &mysql_link)==FAILURE) { + RETURN_FALSE; + } + id = -1; + break; + default: + WRONG_PARAM_COUNT; + break; + } + + ZEND_FETCH_RESOURCE2(mysql, MYSQL *, mysql_link, id, "MySQL-Link", le_link, le_plink); + + convert_to_string_ex(query); + newquery = mysql_scanner(mysql, (*query)->value.str.val); + + if (mysql_query(mysql, newquery)!=0) { + efree (newquery); + RETURN_FALSE; + } + efree (newquery); + if ((mysql_result=mysql_store_result(mysql))==NULL) { if (PHP_MYSQL_VALID_RESULT(mysql)) { /* query should have returned rows */ php_error(E_WARNING, "MySQL: Unable to save result set"); Index: php_mysql.h =================================================================== RCS file: /repository/php4/ext/mysql/php_mysql.h,v retrieving revision 1.14 diff -u -r1.14 php_mysql.h --- php_mysql.h 2000/07/02 23:46:43 1.14 +++ php_mysql.h 2000/07/11 10:15:49 @@ -50,6 +50,7 @@ PHP_FUNCTION(mysql_create_db); PHP_FUNCTION(mysql_drop_db); PHP_FUNCTION(mysql_query); +PHP_FUNCTION(mysql_ext_query); PHP_FUNCTION(mysql_db_query); PHP_FUNCTION(mysql_list_dbs); PHP_FUNCTION(mysql_list_tables);