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);