Getting syntax error while exporting-php mysql

Question

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?

Shadow · Accepted Answer · 2016-10-19 08:36:24Z

1

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.

answered Oct 19, 2016 at 8:36

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

R9102 Over a year ago

thanks for your reply...i will accept your suggestion......i will try to use PDO or mysqli.....also i tried execute sql one by one ...error has gone but output has not come

Shadow Over a year ago

That's a different question then. Although, I'm not sure it is worth the hassle of determining the list of fields in a dynamic way. In a proper application database structure should not change that often. Even if you go down the dynamic route, it may be simpler to return the list of fields to php and use a normal query, rather than using a prepared statement.

R9102 Over a year ago

now without error i can export the file but data is not seen over there

Shadow Over a year ago

As I said, that's a different question, pls ask it in separate post, along with debugging information. This specific question has been answered.

Collectives™ on Stack Overflow

Getting syntax error while exporting-php mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related