Database (MySQL) multiple queries at once failing, working in phpmyadmin

[eluser]Scal[/eluser]
Hello everyone;
I face an odd issue; I build SQL queries (delete, insert, update, whatever actually) and submit it all at once to MySQL via $this->db->query() but I get back an MySQL error saying it has an incorrect syntax.

So off I go to phmypadmin to run the generated SQL queries and oh surprise there is no error there.
Could the $this->db->query() format the string in a way it becomes invalid?

SQL queries I run at once are, for example:

DELETE FROM CodeIgniter_PhotosTags WHERE Photo_ID = '60'; DELETE FROM CodeIgniter_PhotosAlbums WHERE Photo_ID = '60'; INSERT INTO CodeIgniter_PhotosAlbums (Photo_ID, Album_ID) VALUES ('60', '5');

This is exactly wht my code generates since I did copy it from the error reported by MySQL when running the query via CI.

Thanks

[eluser]imn.codeartist[/eluser]
paste your script here so that we can figure it out.
what you have missing.

[eluser]jedd[/eluser]
[quote author="Scal" date="1255069710"]
DELETE FROM CodeIgniter_PhotosTags WHERE Photo_ID = '60'; DELETE FROM CodeIgniter_PhotosAlbums WHERE Photo_ID = '60'; INSERT INTO CodeIgniter_PhotosAlbums (Photo_ID, Album_ID) VALUES ('60', '5');
[/quote]

As suggested, post actual code from your application.

I'd suggest that a loop that generates multiple $this->db->query calls - or just a code block where you spell them each out - will be just as efficient as what you're trying to do here - you're hitting the database the same number of times, plus it would be far more readable.

[eluser]Scal[/eluser]
The controller is 180+ lines Basically, this is the code failing:

$_sql = "DELETE FROM CodeIgniter_PhotosTags WHERE Photo_ID = ?; DELETE FROM CodeIgniter_PhotosAlbums WHERE Photo_ID = ?; INSERT INTO CodeIgniter_PhotosAlbums (Photo_ID, Album_ID) VALUES (?, ?);"

$_status = $this->db->query($_sql, array($ID, $ID, $ID, $this->input->post('PhotoAlbumID')));

$_sql being generated is:

DELETE FROM CodeIgniter_PhotosTags WHERE Photo_ID = '60'; DELETE FROM CodeIgniter_PhotosAlbums WHERE Photo_ID = '60'; INSERT INTO CodeIgniter_PhotosAlbums (Photo_ID, Album_ID) VALUES ('60', '5');

The error thrown is a MySQL DB error, not php code itself:
<h1>A Database Error Occurred</h1>
<p>Error Number: 1064</p><p>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
DELETE FROM CodeIgniter_PhotosAlbums WHERE Photo_ID = '60'; INSERT INTO CodeIg' at line 1</p><p>DELETE FROM CodeIgniter_PhotosTags WHERE Photo_ID = '60'; DELETE FROM CodeIgniter_PhotosAlbums WHERE Photo_ID = '60'; INSERT INTO CodeIgniter_PhotosAlbums (Photo_ID, Album_ID) VALUES ('60', '5');</p>

Thanks

[eluser]Scal[/eluser]
[quote author="jedd" date="1255104468"]I'd suggest that a loop that generates multiple $this->db->query calls - or just a code block where you spell them each out - will be just as efficient as what you're trying to do here - you're hitting the database the same number of times, plus it would be far more readable.[/quote]

I believe it's less "efficient", performance talking, if you build using looping to build an individual SQL query each time rather than building it in one line of code.
I agree it would be more readable though

I'll change it for debugging though, I might spot something else that is actually not reported in the error being generated.

Thanks

[eluser]Scal[/eluser]
Just tried to implement it as:

$_sql = "DELETE FROM CodeIgniter_PhotosTags WHERE Photo_ID = ?";
$Status = $this->db->query($_sql, array($ID));
$_sql = "DELETE FROM CodeIgniter_PhotosAlbums WHERE Photo_ID = ?";
$Status = $this->db->query($_sql, array($ID));
$_sql = "INSERT INTO CodeIgniter_PhotosAlbums (Photo_ID, Album_ID) VALUES (?, ?)";
$Status = $this->db->query($_sql, array($ID, $this->input->post('PhotoAlbumID')));

and no error reported this way; there must be something going on with multiple SQL statements sent as one SQL query.

Thanks

[eluser]jedd[/eluser]
Yeah, I was about to suggest again, mostly for readability, that you go down the single query per query path. I just had a glance through the DB_ files in the core, but nothing stood out - of course, searching for ';' in a PHP file is always going to be a bit challenging.

If you need this grouping to be atomic, then you'd need to wrap them in a transaction anyway - just in case you thought the grouping in one sequence of commands offered this.

[eluser]renownedmedia[/eluser]
Is your pma and ci running under the same mysql user?

[eluser]BrianDHall[/eluser]
[quote author="Scal" date="1255105047"][quote author="jedd" date="1255104468"]I'd suggest that a loop that generates multiple $this->db->query calls - or just a code block where you spell them each out - will be just as efficient as what you're trying to do here - you're hitting the database the same number of times, plus it would be far more readable.[/quote]

I believe it's less "efficient", performance talking, if you build using looping to build an individual SQL query each time rather than building it in one line of code.
I agree it would be more readable though

I'll change it for debugging though, I might spot something else that is actually not reported in the error being generated.

Thanks[/quote]

I think you'll find, as with most local/premature optimizations, that the gain is so tiny that its walking over dollars to pick up pennies - when an experienced programmer working at a 'good' job could be making $15-60+ per hour. You'll likely find that doing things like that regularly costs you hundreds of dollars in lost productivity, for machine efficiency that is so small that you are unable to quantify any real world price.

...and from a personal perspective, debugging highly optimized uber-efficient code SUCKS Monkey Eggs.

[eluser]Scal[/eluser]
I do agree that it can be not worth the effort but it all depends on the project you work on.
Add a few SQL queries here and there, against the number of visitors you aim/get COULD cost the gain of the productivity but costs WILL be large at the end than the gain if you need to optimize later on.

I always try to use the best practices even if there is always an "easy fix" work around.

Now you could tell me to use FUNCTIONS (store procedures of MySQL5+) which I would do if I had the ability to connect using the MySQL client tools as FUNCTIONS needs to be created with a specific MySQL delimiter command. Unfortunately changing the delimiter is not granted through phpmyadmin for some reason.

Maybe a VIEW could do this actually, no need of parameters for this SQL commands block.

Thanks for all the feed back guys