homepage Welcome to WebmasterWorld Guest from 54.196.201.253
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP not able to create a temporary table in mysql
dbarasuk




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

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.

 

dbarasuk




msg:4646370
 2:33 pm on Feb 18, 2014 (gmt 0)

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!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved