Hi,
Can someone tell me what I am missing? I have a PHP function that creates a sql query to create a temporary table for later use. When I run manually the returned query on mysql console to create the said temporary table, the query succeeds whith no error and the temporary table is created. However, when I use the PHP function through a browser, the script seems to have no problem, but when I check to see if the temporary table has been created on mysql system, although I connect to mysql as the mysql root user in the PHP script, the table is not created on mysql.
Is there something I am doing wrong? For those asking the code of the function, this it:
public static function createTmpTbl(){
// Make new connection to server
$sql = $_SESSION['sql'];
$conx = mysqli_connect('localhost','root','securepwd','colltest');
// Find position of 'GROUP'
$groupPos = strpos($sql, 'GROUP');
//return substr from beginning up to $groupPos
$sql = substr($sql,0,$groupPos);
// Find position of $where
$wherePos = strpos($sql,'WHERE');
$SQLFromWhere = substr($sql,$wherePos); // Start from where to the end of string
$newSQL = "SELECT centre_de_collecte.code_centre as 'CENTRE DE COLLECTE', recette.sous_compt as COMPTE, sous_compte.label as 'LIBELLE DU COMPTE', devise as DEVISE, mode_pay as 'MODE DE PAIEMENT','$date_min' as 'ENTRE LE', '$date_max' as 'ET LE' FROM recette, sous_compte, centre_de_collecte ".$SQLFromWhere;
$createTmpTblSQL = "CREATE TEMPORARY TABLE tmp_tbl as ".'('.$newSQL.')';
/*
* $createTmpTblSQL var contains next script which obviously is has no problem to create the tmp tbl:
* CREATE TEMPORARY TABLE tmp_tbl as (SELECT centre_de_collecte.code_centre as 'CENTRE DE COLLECTE', recette.sous_compt as COMPTE, sous_compte.label as 'LIBELLE DU COMPTE', devise as DEVISE, mode_pay as 'MODE DE PAIEMENT','' as 'ENTRE LE', '' as 'ET LE' FROM recette, sous_compte, centre_de_collecte WHERE recette.date_paie >= '2013-02-12' AND recette.date_paie <= '2014-02-18' AND recette.code_centre=centre_de_collecte.code_centre AND recette.sous_compt = sous_compte.sous_compt AND recette.code_centre IN (010,016,017,018,019,021,022,023,024,025,026,027,028,029,030,031,032,033,034,035,036,040,041,042,048,072,073,083,084,085,087,088,089,090,091,092,093,094,095,096,098,106,110,111,112,113,114,115,116) )
*/
// execute this query to create the tmp_tbl
$result = mysqli_query($conx,$createTmpTblSQL) or die('Could not create the tmp table because: '.mysqli_error($conx));
return $createTmpTblSQL;
// exit($createTmpTblSQL);
}
Thanks for your kind help.
