php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #5586 Subselects in MySQL
Submitted: 2000-07-14 09:56 UTC Modified: 2000-07-20 20:21 UTC
From: d dot rethans at jdimedia dot nl Assigned:
Status: Closed Package: Feature/Change Request
PHP Version: 4.0.1pl2 OS:
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: d dot rethans at jdimedia dot nl
New email:
PHP Version: OS:

 

 [2000-07-14 09:56 UTC] d dot rethans at jdimedia dot nl
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);



Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2000-07-20 20:21 UTC] sterling at cvs dot php dot net
Re list discussion:
  1) Not appropriate for MySQL functions
    a) put in a PEAR module
    b) implement in PHP.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 14 00:01:26 2024 UTC