duanou1904
duanou1904
2016-10-19 08:31

导出-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("
");

//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("/
|
|
|/", " ", $schema_insert);
    $schema_insert .= "\t";
    print(trim($schema_insert));
    print "
";
}   

Can any one help me on this?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dongping4273 dongping4273 5年前

    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.

    点赞 评论 复制链接分享