importing CSV to MYSQL via PHP

2019-06-27 09:48发布

站内问答 / PHP
1449 4 6

I am importing a CSV file to my admin area and I want to add files to my database. My PHP code for import.php is:

<?php 

include_once('../include/connection.php');

if ($_FILES[csv][size] > 0) {

    //get the csv file
    $file = $_FILES[csv][tmp_name];
    $handle = fopen($file,"r");

    //loop through the csv file and insert into database
    do {
        if ($data[0]) {
             mysql_query("INSERT INTO mobi(promo_title, promo_content, promo_image, promo_link, promo_cat, promo_name) VALUES
                (
                    '".addslashes($data[0])."',
                    '".addslashes($data[1])."',
                    '".addslashes($data[2])."',
                    '".addslashes($data[3])."',
                    '".addslashes($data[4])."',
                    '".addslashes($data[5])."'
                )
            ");
        }
    } while ($data = fgetcsv($handle,1000,",","'"));
    //

    //redirect
    header('Location: import.php?success=1'); die;

}

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Import a CSV File with PHP & MySQL</title>
</head>

<body>

<?php if (!empty($_GET[success])) { echo "<b>Your file has been imported.</b><br><br>"; } //generic success notice ?>

<form action="" method="post" enctype="multipart/form-data" name="form1" id="form1">
  Choose your file: <br />
  <input name="csv" type="file" id="csv" />
  <input type="submit" name="Submit" value="Submit" />
</form>

</body>
</html>

The code displays correctly but when I select my test file and hit submit, I am hit with these problems: 

Warning: mysql_query() [function.mysql-query]: Access denied for user 'root'@'localhost' (using password: NO) in admin/import.php on line 23

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in admin/import.php on line 23

Warning: mysql_query() [function.mysql-query]: Access denied for user 'root'@'localhost' (using password: NO) in admin/import.php on line 23

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in admin/import.php on line 23

Warning: mysql_query() [function.mysql-query]: Access denied for user 'root'@'localhost' (using password: NO) in admin/import.php on line 23

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in admin/import.php on line 23

Warning: mysql_query() [function.mysql-query]: Access denied for user 'root'@'localhost' (using password: NO) in admin/import.php on line 23

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in admin/import.php on line 23

Warning: Cannot modify header information - headers already sent by (output started at admin/import.php:1) in admin/import.php on line 29

Can anyone see the reason?

my connection.php code is this:

<?php

try {
$pdo = new PDO('mysql:host=localhost;dbname=*********', '*********', '*********');
}catch (PDOException $e){
  exit('Database Error.');
}

?>

please note that my other pages such as add.php, delete.php, edit.php and so on all work with the same include_once for connection.php

SO THERE IS NOTHINGWRONG WITH MY DATABASE CONNECTION.

thank you.

4条回答
虎瘦雄心在
2楼-- · 2019-06-27 10:12

You are using PDO to establish a database connection. But $pdo instance is not used anymore!

To executes an SQL statement, need to use like $pdo->query("INSERT INTO mobi ...")

Instead, You are using mysql_query to send a MySQL query. So you get these warnings:

Access denied for user 'root'@'localhost' (using password: NO)

A link to the server could not be established

Also make sure you are not output any strings, etc... before your header() function to remove this warning: headers already sent.

查看更多
0人赞 添加讨论(0) 举报
Bombasti
3楼-- · 2019-06-27 10:17

Have you tried using php to load the file so you don't have to loop through it?

I would recommend using require_once instead of include_once. __DIR__ is also always a good thing to have in front of your include path. That way you can say where the file is from the current file, and not the current running script.

It would look something like this:

<?php
require_once __DIR__ . '/../include/connection.php';

if ($_FILES[csv][size] > 0) {

    //get the csv file
    $file = $_FILES[csv][tmp_name];

    mysql_query("
        LOAD DATA LOW_PRIORITY LOCAL 
        INFILE '$file'
        REPLACE INTO TABLE mobi 
        FIELDS TERMINATED BY ',' 
        OPTIONALLY ENCLOSED BY '\"' 
        ESCAPED BY '\"' 
        LINES TERMINATED BY '\n' 
        (promo_title, promo_content, promo_image, promo_link, promo_cat, promo_name)
        SET promo_name = TRIM(BOTH '\r' FROM promo_name);
    ");
}
查看更多
0人赞 添加讨论(0) 举报
男人必须洒脱
4楼-- · 2019-06-27 10:26

Your database credentials may be wrong. Check if the server is accessible.

and the header information already sent is caused by the blank space before your php code

 <?php 

include_once('../include/connection.php');

with 

<?php 

include_once('../include/connection.php');
查看更多
0人赞 添加讨论(0) 举报
放我归山
5楼-- · 2019-06-27 10:28

Your database credentials are incorrect. You should also look at using the mysqli object oriented approach to databases and should use mysqli_real_escape_string instead of addslashes. The header error is a result of the mysql errors being thrown and ouput to the page.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)