MySQL query fail in CI and PHP but not with MySQL client

[eluser]Unknown[/eluser]
Hello,

I'm baffled by this one.

I have this long MySQL query that fails in CI and with the mysql_* PHP functions, but works perfectly with the MySQL client on the command line, or with Navicat.

Here's the query and my code in CI. If I run this query in CI, or in "classic" PHP, I get an Error Number: 1064. But if I copy the query shown in the web page to the MySQL client, or Navicat, everything is fine. What's going on? It seems that its a more PHP problem than a CI problem. TIA.

Cheers
-Emmanuel


function obtient_id($numero_cohorte, $annee_programme) {

$sql = <<<EOF
DROP TEMPORARY TABLE IF EXISTS temptable1;
DROP TEMPORARY TABLE IF EXISTS temptable2;
CREATE TEMPORARY TABLE temptable1 (id int);
INSERT INTO temptable1
SELECT usagers_admin.id FROM usagers_admin
WHERE usagers_admin.nom='statut_annee_programme'
AND usagers_admin.valeur='$annee_programme';
CREATE TEMPORARY TABLE temptable2 (id int);
INSERT into temptable2
SELECT usagers_admin.id from usagers_admin
WHERE usagers_admin.nom='statut_cohorte_selection'
AND usagers_admin.valeur='$numero_cohorte';
SELECT temptable1.id from temptable1, temptable2
WHERE temptable1.id=temptable2.id;
EOF;

$requete_resultat = $this->db->query($sql);

if ( $requete_resultat->num_rows() > 0 ) {
$utilisateurs_id = array();
foreach ( $requete_resultat->result() as $rang ) {
$utilisateurs_id[] = $rang->id;
}
return $utilisateurs_id;
} else {
return FALSE;
}
}

[eluser]obiron2[/eluser]
put quotes round your SQL string, otherwise PHP sees the first semicolon and thinks it is the end of the PHP command.

I'm not sure what $this->db->query will return when there are multiple statements in your SQL.

you would do better to store the SQL script as a stored procedure inside MySQL - It would be faster and less prone to error.

If you have multiple hits to this function at the same time, you will run the risk of dropping the temptables while you are still using them and you should look at some sort of locking or unique table naming. If you use in-memory (hash) tables they will automatically be dropped on completion of the SQL. don't know if you can have hash tables in MySQL - it may be a MSSQL function.

Obiron

[eluser]Unknown[/eluser]
Hello obiron2,

Thanks for replying.

put quotes round your SQL string, otherwise PHP sees the first semicolon and thinks it is the end of the PHP command.

I've already done that. And put also the sql statement on one line.

I don't want to use procedures because of portability.

Also, I think you have a misunderstanding about the way MySQL use temporary table.

Temporary tables differ from normal tables in that temporary tables exist only
for the duration of the current session and are automatically deleted after it ends.
(For web applications, this means that temporary tables generally cease to exist
upon completion of the current page or script.)

http://dev.mysql.com/tech-resources/arti...n-ch5.html (p264 of the referred PDF)

And...

A MySQL temporary table can have any legal name, even the same name as an existing regular table. In this case it will mask the regular table.

http://www.xaprb.com/blog/2006/03/26/tem...-in-mysql/

Cheers
-Emmanuel

[eluser]Garry Claridge[/eluser]
Have you tried working your way through it; i.e. one statement at a time to narrow down where the problem may be occurring?

Good Luck

Garry

[eluser]cwt137[/eluser]
Could it be a classic case of sql injection? Make sure you escape your values. Parameterize your sql statements (aka Query Bindings) http://ellislab.com/codeigniter/user-gui...eries.html. I do it to all my queries.