MySQL UNION Work-Around Question

Anon

I'm trying to get the code in the first example below to work. According to the MySQL doc and SQL documentation, this should work. I don't get an error when I run it, but I get nothing in the result set. I do get results when I run the second example. If the second produces results, then the first should also. Any ideas?

thanks,
michael

1st Example
"CREATE TEMPORARY TABLE tmpBudget SELECT SUM(bud_amt), bud_code FROM budget WHERE bud_group = 1 AND bud_year = '2001' GROUP BY bud_code;INSERT INTO tmpBudget SELECT SUM(bud_amt), bud_code FROM budget WHERE bud_group = 2 AND bud_year = '2001' GROUP BY bud_code;INSERT INTO tmpBudget SELECT SUM(bud_amt), bud_code FROM budget WHERE bud_group = 3 AND bud_year = '2001' GROUP BY bud_code;"

2nd Example
"SELECT SUM(bud_amt), bud_code FROM budget WHERE bud_group = 1 AND bud_year = '2001' GROUP BY bud_code"

[deleted]

You print the queries as one large string...

Are you aware that PHP can only execute one query at a time?

Anon

PHP would pass the sql statement as one long string, which would be processed by the MySQL server as the following instructions:
1. create a temp table
2. take the result set from select statement as the structure and populate the table with the results
3. perform the multiple inserts
4. results = select all from temp table
5. return results

So, PHP will only accept, as a return from a query, a single result set. However,multiple sql statements can be passed in a single query.

smarlowe

Well, that's a nice theory, but have you proven it to be true? I just now proved it to be UNTRUE for postgresql by some local testing.

[deleted]

"So, PHP will only accept, as a return from a query, a single result set. However,multiple sql statements can be passed in a single query."

meanwhile, back in the real world, you will get an SQL syntax error after the first query.

You might want to actually start using error-checking, mysql_error() will tell you that you cannot do more than one query at a time.

Which makes sense, because if you could only get a result from the last query, then how are you going to know wether the first queries worked?

Also, imagine the impact on security, you'd have to parse each and every variable you put into your query to make sure there isn't a hidden '; DROP DATABASE' in it.

Anon

I can easily pass the following sql statement, which will not produce an error:

"insert into people(first_name, last_name) values("michael", "holm");insert into people(first_name, last_name) values("joe", "smith");insert into people(first_name, last_name) values("tom", "collins");"

Therefore, my syntax is correct. Also, check the documentation to confirm it. Again, my query doesn't produce an error. I do have error checking in place.

Now, my query works - in that it doesn't produce an error- but doesn't return anything.

ANY HELP?

Anon

<a href="http://www.mysql.com/doc/I/N/INSERT_SELECT.html">Here's</a> some info in the MySQL doc to back it up.

[deleted]

Feel free to wake up any time :-)

try this:

if (!$result = mysql_query("insert into people(first_name, last_name) values('michael', 'holm');insert into people(first_name, last_name) values('joe', 'smith');insert into people(first_name, last_name) values('tom', 'collins');"))
{
echo mysql_error();
}
else
{
print_r($result);
}

what do you get? yup, an error message:

You have an error in your SQL syntax near ';insert into people(first_name, last_name) values('joe', 'smith');insert into pe' at line 1

[deleted]

yeah, your SQL syntax is correct, but you don't seem to want to understand that PHP simply does not let you send more than one query at a time. MySQL's C-api simply does not do that!

Anon

This works:

insert into people(first,last) values('michael', 'holm');insert into people(first,last) values('Mr', 'jones');insert into people(first,last) values('sam', 'smith');

I appreciate your "help", but it's not getting the issue resolved.

Anon

well it does work if you do this:

$sql ="insert_statement;insert_statement;"

mysql_query($sql)

where insert_statement is a complete query (I'm just lazy today)

but its not good because your check for errors would only tell you if the last query failed/succedded.

My boss pulled this stunt ... I was like.. WHOA... NO WAY.. BAD BAD BAD .. but it does work..

smarlowe

Sure it is. Handing off more than one query at a time with no error checking is bad form. Break each query out into it's own and check the error each one gives back.

I.e. write your code the right way and you won't have these problems. :-)

[deleted]

Can somebody give me a working example of running two queries in one mysql_query()?

I just checked the MySQL manual and it says this about the mysql_query() function in the C api (which is the only way that PHP can talk to MySQL)

"Executes the SQL query pointed to by the null-terminated string query. The query must consist of a single SQL statement. You should not add a terminating semicolon (`;') or \g to the statement"

So the API does NOT let you do more than one query.

[deleted]

Can you give a working example of that? I cannot get it to work in php 4.0.5, 4.0.6, 4.1.0 and 4.1.1, PHP always comes back with a syntact error in the query.

[deleted]

"I.e. write your code the right way and you won't have these problems"

Nah, always better to just insist that you're right, even if everybody else says you're wrong. 🙂

Anon

Hmmm I tried it... and it didn't work. HEHEHE... guess my boss didn't check it after he wrote it. I saw it and rewrote it because I didn't think that was a good idea.

I guess he saw me do that at the mysql prompt and figured it would work in the php function.

Anon

Here is a post I made awhile back when I first saw this in my boss' code...

http://www.phpbuilder.com/forum/read.php3?num=5&id=14384&loc=0&thread=14384

Anon

I have tried the following code and have no problems with it working. I have also used it in other scripts, which are in production and have been for some time. This is proof that it works.

$sql = "insert into people(first,last) values('michael', 'holm');insert into people(first,last) values('Mr', 'jones');insert into people(first,last) values('sam', 'smith');";
$result = mysql_query($sql);

Here's what I'd like to do:
1. create temp_ table
2. execute some select statements which populate the temp_table
3.select * from temp_table as the result set, which gets returned to php.

I have reviewed the MySQL documentation, all of which states this this will work.

PHP doesn't really care about the SQL statement. It doesn't perform validation or any other action on the query. It only passes it as a string and waits for a result set to be returned by the db.

I get no error msg and no result set from my query. I only get back, in phpMyAdmin, a statement to indicate that my SQL statement exectuted with no errors.

Any help would be greatly appreciated. I've written example code, which is <a href="http://www.phpbuilder.com/forum/read.php3?num=5&id=27933&loc=0&thread=27933">in my first post</a> at the top of this thread.

thanks,
michael

Anon

So, after running this code, ALL those inserts were now in your table? Did you double check? I'm pretty sure that it won't work. I know it won't work for most other db connections, and I'm pretty sure it won't work for mysql.

Just because you don't get an error code back doesn't mean all the inserts worked.

Try this, make an EMPTY table, then write a PHP script that does a double insert in one exec. If both rows are there, let us know.

I have yet to see an example that worked like that, and like vincent said, the API for MYSQL only allows on statement at a time. The mysql command line tool will take apart ; seperated sql statements and perform them one at a time, but in fact, PHP doesn't do that, and isn't designed to do that, for the very reason Vincent mentioned, that it is a security risk that someone might be able to stick a ;delete from table; in the middle of their data and delete all your tuples.

Anon

I have tried the code and I have checked my database to confirm that the records are there. It works.

PHP doesn't care about the SQL statement. It doesn't care if there is one, two or a hundred statements, it treats it as a string and passes it to MySQL, which will perform each statement, separated by ;, sequentially. The results of the last statement will be returned in the result set. Therefore..

$sql = "CREATE TEMPORARY TABLE tmpBudget SELECT SUM(bud_amt), bud_code FROM budget WHERE bud_group = 1 AND bud_year = '2001' GROUP BY bud_code;INSERT INTO tmpBudget SELECT SUM(bud_amt), bud_code FROM budget WHERE bud_group = 2 AND bud_year = '2001' GROUP BY bud_code;INSERT INTO tmpBudget SELECT SUM(bud_amt), bud_code FROM budget WHERE bud_group = 3 AND bud_year = '2001' GROUP BY bud_code;SELECT * from tmpBudget;"
$result=mysql_query($sql);

...should produce a result set with the entries from the newly populated temp table.

http://www.mysql.com/doc/I/N/INSERT_SELECT.html

The above entry in the MySQL doc illustrates how to use a select statement to populate a temp table. Additional documentation in the MySQL doc, as well as in the book MySQL by New Riders.