Codeigniter从框架导入数据库|SQL语法中有错误;

ui7jx7zq 于 2022-12-06 发布在其他

关注(0)|答案(1)|浏览(135)

我编写了函数，用于在提交时导入数据库：
这是一个表，首先执行DROP，然后执行CREATE和INSERT：

public function demo_importer_post()
    {
        $folder_name = 'dumps';
        $file_name = 'feed_ui2.sql';
$path = 'assets/backup_db/'; // Codeigniter application /assets
$file_restore = $this->load->file($path . $folder_name . '/' . $file_name, true);
$file_array = explode(';', $file_restore);
foreach ($file_array as $query)
 {
 $this->db->query("SET FOREIGN_KEY_CHECKS = 0");
 $this->db->query($query);
 $this->db->query("SET FOREIGN_KEY_CHECKS = 1");
 }
    }

首先我尝试直接在PhpMyAdmin中导入sql文件。工作正确。
现在，我尝试从CodeIgniter导入，然后：

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''

@更新
我检查了PHPMYADMIN，并使用以下查询正确导入。https://prnt.sc/bViDzHpr5jz3

UPDATE `general_settings`
SET custom_css_codes = "

.product-content-details .price .lbl-price {
    position: relative;
    display: block;
    float: left;
    font-size: 24px;
    line-height: 30px;
    color: red;
}"

但是从Codeigniter我有问题。当我从css代码中删除:和;时

UPDATE `general_settings`
SET custom_css_codes = "

.product-content-details .price .lbl-price {
    position relative
    display block
    float left
    font-size 24px
    line-height 30px
    color red
}"
WHERE id = 1;

那么如何用css代码中的:和;从codeigniter更新呢？

codeigniter

来源：https://stackoverflow.com/questions/74668599/codeigniter-import-database-from-framework-you-have-an-error-in-your-sql-synta

1条答案

按热度按时间

eimct9ow1#

这是100%的工作在我的项目。

public function export(){
    
        
        $this->load->database();
        $host = $this->db->hostname;
        $username = $this->db->username;
        $password = $this->db->password;
        $database_name = $this->db->database;

        // Get connection object and set the charset
        $conn = mysqli_connect($host, $username, $password,$database_name);
        $conn->set_charset("utf8");

        // Get All Table Names From the Database
        $tables = array();
        $sql = "SHOW TABLES";
        $result = mysqli_query($conn, $sql);

        while ($row = mysqli_fetch_row($result)) {
            $tables[] = $row[0];
        }

        $sqlScript = "";
        foreach ($tables as $table) {
            
            // Prepare SQLscript for creating table structure
            $query = "SHOW CREATE TABLE $table";
            $result = mysqli_query($conn, $query);
            $row = mysqli_fetch_row($result);
            
            $sqlScript .= "\n\n" . $row[1] . ";\n\n";
            
            
            $query = "SELECT * FROM $table";
            $result = mysqli_query($conn, $query);
            
            $columnCount = mysqli_num_fields($result);
            
            // Prepare SQLscript for dumping data for each table
            for ($i = 0; $i < $columnCount; $i ++) {
                while ($row = mysqli_fetch_row($result)) {
                    $sqlScript .= "INSERT INTO $table VALUES(";
                    for ($j = 0; $j < $columnCount; $j ++) {
                        $row[$j] = $row[$j];
                        
                        if (isset($row[$j])) {
                            $sqlScript .= '"' . $row[$j] . '"';
                        } else {
                            $sqlScript .= '""';
                        }
                        if ($j < ($columnCount - 1)) {
                            $sqlScript .= ',';
                        }
                    }
                    $sqlScript .= ");\n";
                }
            }
            
            $sqlScript .= "\n"; 
        }

        if(!empty($sqlScript))
        {
            // Save the SQL script to a backup file
            $backup_file_name = $database_name . '_backup_' . time() . '.sql';
            $fileHandler = fopen($backup_file_name, 'w+');
            $number_of_lines = fwrite($fileHandler, $sqlScript);
            fclose($fileHandler); 

            // Download the SQL backup file to the browser
            header('Content-Description: File Transfer');
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename=' . basename($backup_file_name));
            header('Content-Transfer-Encoding: binary');
            header('Expires: 0');
            header('Cache-Control: must-revalidate');
            header('Pragma: public');
            header('Content-Length: ' . filesize($backup_file_name));
            ob_clean();
            flush();
            readfile($backup_file_name);
            exec('rm ' . $backup_file_name); 
        }
    }

赞(0）回复(0）举报 2022-12-06

我来回答

Codeigniter从框架导入数据库|SQL语法中有错误;

1条答案

相关问题

热门标签

最新问答