CODEWITHSUNDEEP
phpmysql
R9102
R9102

Reputation: 727

Getting syntax error while exporting-php mysql

I am trying to export data from mysql table.

The query i have written is executing properly in sql workbench and i can see the out put of that but if i use the same query in my code it giving error .

Error

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 'PREPARE data FROM @sql; EXECUTE data' at line 5

sql query

$sql = "SET @sql = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM 
information_schema.columns WHERE table_schema = 'wpdb_wp1' AND table_name = 
'wp3_wpsp_ticket' AND column_name NOT IN ('guest_name', 'guest_email')), 
' from wpdb_wp1.wp3_wpsp_ticket limit 1'); 
PREPARE data FROM @sql;
EXECUTE data;";

code

$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());

//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());  

//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());  

$file_ending = "xls";

//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");

 /*******Start of Formatting for Excel*******/   
 //define separator (defines columns in excel & tabs in word)
 $sep = "\t"; //tabbed character

 //start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");

//end of printing column names  
//start while loop to get data
while($row = mysql_fetch_row($result))
{
    $schema_insert = "";
    for($j=0; $j<mysql_num_fields($result);$j++)
    {
        if(!isset($row[$j]))
            $schema_insert .= "NULL".$sep;
        elseif ($row[$j] != "")
            $schema_insert .= "$row[$j]".$sep;
        else
            $schema_insert .= "".$sep;
    }
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    $schema_insert .= "\t";
    print(trim($schema_insert));
    print "\n";
}

Can any one help me on this?

Upvotes: 0

Views: 80

Answers (1)

Shadow
Shadow

Reputation: 34231

mysql_query() cannot execute multiple sql statements in one go. Period.

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

You can use multiple mysql_query() calls, one for each statement. But before doing this, see the next point.

Pls do not use mysql_*() functions, they have been deprecated for years and have been removed from php 7.0. Use mylsqi or PDO instead.

Warning This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information.

Upvotes: 1

Related Questions