Welcome to WebmasterWorld Guest from 54.162.93.137

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

PHP not able to create a temporary table in mysql

     
9:27 am on Feb 18, 2014 (gmt 0)

5+ Year Member



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.
2:33 pm on Feb 18, 2014 (gmt 0)

5+ Year Member



Hi,
I found the solution. In fact the table was created but could not be visible by any other client than the one that created it.

Thanks to all who read this post!
 

Featured Threads

Hot Threads This Week

Hot Threads This Month