Avatar of doctorbill
doctorbill
Flag for United Kingdom of Great Britain and Northern Ireland asked on

php csv import

I am using cPanel to create a database

The database has been created and I have added the correct UN and PW to access the database in the cPanel

I have put the Database Name, UN and PW in the .php connection page

The page is supposed to import a .csv file and works perfectly on my localhost 

I have created the database and tables on the remote host

The page can be accessed via the site remote link

When I try to import a file the page returns a 500 error - The page is not working

http://ticktockitsandbox.co.uk/import-csv/


PHPDatabases

Avatar of undefined
Last Comment
doctorbill

8/22/2022 - Mon
doctorbill

ASKER
This is the import file:
import-template.csv
doctorbill

ASKER
Lat me know any other info required
doctorbill

ASKER
The question is - do I need to do anything special in cPanel re. database connections
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Scott Fell

I am concerned first of all that your link is open to the public. I can get to it myself so it is a good thing you are getting an error. I assume in production this will be behind a password-protected page.

As far as your error, I would suspect the issue is you have to make sure the permissions are correct for wherever the file is being uploaded to. This is a wild guess though because the 500 error is generic. Turn on detail errors or look in your logs to get the specific error.
Dave Baldwin

What does your "php_error_log" show?
doctorbill

ASKER
The link was just opened for the purposes of this question
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
doctorbill

ASKER
How do I access the php error log in cPanel
I am more used to Plesk
doctorbill

ASKER
ok - found them
doctorbill

ASKER
ok - the import is now working
There were incorrect database connection credentials


Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Scott Fell

I'm glad you found it. That is what both Dave and I suggested to look in your logs or turn on errors. When you are testing, you can put something like this at the top of your page
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

Open in new window

You should also be able to do this in CPanel as you did in Plesk.  Just remember to turn it off for production.
doctorbill

ASKER
The import is working fine but the following does not return any results
The page works fine on my local system with the same php version:
<?php
use Phppot\DataSource;

require_once 'DataSource.php';
$db = new DataSource();
$conn = $db->getConnection();

if (isset($_POST["import"])) {
    
    $fileName = $_FILES["file"]["tmp_name"];
    
    if ($_FILES["file"]["size"] > 0) {
        
        $file = fopen($fileName, "r");
        
        while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
            
            $userId = "";
            if (isset($column[0])) {
                $userId = mysqli_real_escape_string($conn, $column[0]);
            }
            $userName = "";
            if (isset($column[1])) {
                $userName = mysqli_real_escape_string($conn, $column[1]);
            }
            $password = "";
            if (isset($column[2])) {
                $password = mysqli_real_escape_string($conn, $column[2]);
            }
            $firstName = "";
            if (isset($column[3])) {
                $firstName = mysqli_real_escape_string($conn, $column[3]);
            }
            $lastName = "";
            if (isset($column[4])) {
                $lastName = mysqli_real_escape_string($conn, $column[4]);
            }
            
            $sqlInsert = "INSERT into users (userId,userName,password,firstName,lastName)
                   values (?,?,?,?,?)";
            $paramType = "issss";
            $paramArray = array(
                $userId,
                $userName,
                $password,
                $firstName,
                $lastName
            );
            $insertId = $db->insert($sqlInsert, $paramType, $paramArray);
            
            if (! empty($insertId)) {
                $type = "success";
                $message = "CSV Data Imported into the Database";
            } else {
                $type = "error";
                $message = "Problem in Importing CSV Data";
            }
        }
    }
}
?>
<!DOCTYPE html>
<html>

<head>
<script src="jquery-3.2.1.min.js"></script>

<style>
body {
    font-family: Arial;
    width: 550px;
}

.outer-scontainer {
    background: #F0F0F0;
    border: #e0dfdf 1px solid;
    padding: 20px;
    border-radius: 2px;
}

.input-row {
    margin-top: 0px;
    margin-bottom: 20px;
}

.btn-submit {
    background: #333;
    border: #1d1d1d 1px solid;
    color: #f0f0f0;
    font-size: 0.9em;
    width: 100px;
    border-radius: 2px;
    cursor: pointer;
}

.outer-scontainer table {
    border-collapse: collapse;
    width: 100%;
}

.outer-scontainer th {
    border: 1px solid #dddddd;
    padding: 8px;
    text-align: left;
}

.outer-scontainer td {
    border: 1px solid #dddddd;
    padding: 8px;
    text-align: left;
}

#response {
    padding: 10px;
    margin-bottom: 10px;
    border-radius: 2px;
    display: none;
}

.success {
    background: #c7efd9;
    border: #bbe2cd 1px solid;
}

.error {
    background: #fbcfcf;
    border: #f3c6c7 1px solid;
}

div#response.display-block {
    display: block;
}
</style>
<script type="text/javascript">
$(document).ready(function() {
    $("#frmCSVImport").on("submit", function () {

       $("#response").attr("class", "");
        $("#response").html("");
        var fileType = ".csv";
        var regex = new RegExp("([a-zA-Z0-9\s_\\.\-:])+(" + fileType + ")$");
        if (!regex.test($("#file").val().toLowerCase())) {
               $("#response").addClass("error");
               $("#response").addClass("display-block");
            $("#response").html("Invalid File. Upload : <b>" + fileType + "</b> Files.");
            return false;
        }
        return true;
    });
});
</script>
</head>

<body>
    <h2>Import CSV file into Mysql using PHP</h2>

    <div id="response"
        class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>">
        <?php if(!empty($message)) { echo $message; } ?>
        </div>
    <div class="outer-scontainer">
        <div class="row">

            <form class="form-horizontal" action="" method="post"
                name="frmCSVImport" id="frmCSVImport"
                enctype="multipart/form-data">
                <div class="input-row">
                    <label class="col-md-4 control-label">Choose CSV
                        File</label> <input type="file" name="file"
                        id="file" accept=".csv">
                    <button type="submit" id="submit" name="import"
                        class="btn-submit">Import</button>
                    <br />

                </div>

            </form>

        </div>
               <?php
            $sqlSelect = "SELECT * FROM users";
            $result = $db->select($sqlSelect);
            if (! empty($result)) {
                ?>
            <table id='userTable'>
            <thead>
                <tr>
                    <th>User ID</th>
                    <th>User Name</th>
                    <th>First Name</th>
                    <th>Last Name</th>

                </tr>
            </thead>
<?php
                
                foreach ($result as $row) {
                    ?>
                    
                <tbody>
                <tr>
                    <td><?php  echo $row['userId']; ?></td>
                    <td><?php  echo $row['userName']; ?></td>
                    <td><?php  echo $row['firstName']; ?></td>
                    <td><?php  echo $row['lastName']; ?></td>
                </tr>
                    <?php
                }
                ?>
                </tbody>
        </table>
        <?php } ?>
    </div>

</body>

</html>

Open in new window

Scott Fell

The import is working fine but the following does not return any results

I guessed (wrongly) on the question and can only guess again. It helps to be more detailed as to what is not working :)

I assume what you mean is the table is not showing up because in line 183 above you have  if (! empty($result)) {

Does this mean the table does not show up at all? Sounds like the $result is empty.

Did you check the database? was the data imported?

If the data is not being imported to the table and this code works in your local environment, then that points to your database connection. Are you sure the credentials are correct?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
doctorbill

ASKER
The data is being imported into the database - I can see the table has been populated
Just nothing is showing up on the page
Scott Fell

Change

 $sqlSelect = "SELECT * FROM users";             
$result = $db->select($sqlSelect);             
if (! empty($result)) {

Open in new window

to
 $sqlSelect = "SELECT * FROM users";             
$result = $db->select($sqlSelect);   

print_r($result); //FOR TESTING

          
if ($result) {

Open in new window


Scott Fell

What are you getting for line 159?
<?php if(!empty($message)) { echo $message; } ?>
Your help has saved me hundreds of hours of internet surfing.
fblack61
doctorbill

ASKER
Scott - if I use your code it kills the page
doctorbill

ASKER
"What are you getting for line 159?
<?php if(!empty($message)) { echo $message; } ?>"
I do not get any message
doctorbill

ASKER
Scott - just tried this again:
 $sqlSelect = "SELECT * FROM users";             $result = $db->select($sqlSelect);    print_r($result); //FOR TESTING           if ($result) { 
I do not get any print out
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

Start breaking apart your code and see where it is failing.

On line 10 you have $fileName = $_FILES["file"]["tmp_name"];
I might try a line after that
echo $fileName;
die();

Open in new window

Keep going down the line until you discover the issue.

you have
 $userId = "";            
if (isset($column[0])) {                
   $userId = mysqli_real_escape_string($conn, $column[0]);             
}

Open in new window

You may try

 $userId = "";            
if (isset($column[0])) {                
   $userId = mysqli_real_escape_string($conn, $column[0]);            
}
echo $user
die();

Open in new window

Keep going down the line until you can pinpoint the cause.
It could be bad data?
It could be a permissions issue?
Database connection?
doctorbill

ASKER
Yes but the code works perfectly in my local apache installation
So why would the code break in this installation?
doctorbill

ASKER
The database connection appears to be fine - the data upload works
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
doctorbill

ASKER
Latest from the php log:

#0 /home/ox42ix9r/public_html/import-csv/index.php(182): Phppot\DataSource->select('SELECT * FROM u...')
#1 {main}
  thrown in /home/ox42ix9r/public_html/import-csv/DataSource.php on line 83
[03-Oct-2021 14:45:52 UTC] PHP Warning:  mysqli::__construct(): Headers and client library minor version mismatch. Headers:50649 Library:50528 in /home/ox42ix9r/public_html/import-csv/DataSource.php on line 56
[03-Oct-2021 14:45:52 UTC] PHP Warning:  mysqli::__construct(): Headers and client library minor version mismatch. Headers:50649 Library:50528 in /home/ox42ix9r/public_html/import-csv/DataSource.php on line 56
[03-Oct-2021 14:45:52 UTC] PHP Fatal error:  Uncaught Error: Call to undefined method mysqli_stmt::get_result() in /home/ox42ix9r/public_html/import-csv/DataSource.php:83
Stack trace:
#0 /home/ox42ix9r/public_html/import-csv/index.php(182): Phppot\DataSource->select('SELECT * FROM u...')
#1 {main}
  thrown in /home/ox42ix9r/public_html/import-csv/DataSource.php on line 83
doctorbill

ASKER
These are the lines being referred to:
 56 $conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME);
83 $result = $stmt->get_result();

The values are referenced at the top of the datasource.php file as:
const HOST = 'localhost';

    const USERNAME = 'ox42ix9r_dev';

    const PASSWORD = 'xxxxxxxxx';

    const DATABASENAME = 'ox42ix9r_import-csv';

    private $conn;
doctorbill

ASKER
Once again, they allow the upload of data to the database
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

Try converting to PDO instead of mysqli. PDO is more ubiquitous and will work from serve to server where you may be experiencing slightly different databases from your local environment to your production.
doctorbill

ASKER
This is the datasource.php
I wouldn't know how to convert it to a PDO connection
<?php
/**
 * Copyright (C) 2019 Phppot
 *
 * Distributed under MIT license with an exception that,
 * you don’t have to include the full MIT License in your code.
 * In essense, you can use it on commercial software, modify and distribute free.
 * Though not mandatory, you are requested to attribute this URL in your code or website.
 */
namespace Phppot;

/**
 * Generic datasource class for handling DB operations.
 * Uses MySqli and PreparedStatements.
 *
 * @version 2.5 - recordCount function added
 */
class DataSource
{

    // PHP 7.1.0 visibility modifiers are allowed for class constants.
    // when using above 7.1.0, declare the below constants as private
    const HOST = 'localhost';

    const USERNAME = 'ox42ix9r_dev';

    const PASSWORD = 'xxxxxxx';

    const DATABASENAME = 'ox42ix9r_import-csv';

    private $conn;

    /**
     * PHP implicitly takes care of cleanup for default connection types.
     * So no need to worry about closing the connection.
     *
     * Singletons not required in PHP as there is no
     * concept of shared memory.
     * Every object lives only for a request.
     *
     * Keeping things simple and that works!
     */
    function __construct()
    {
        $this->conn = $this->getConnection();
    }

    /**
     * If connection object is needed use this method and get access to it.
     * Otherwise, use the below methods for insert / update / etc.
     *
     * @return \mysqli
     */
    public function getConnection()
    {
        $conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME);

        if (mysqli_connect_errno()) {
            trigger_error("Problem with connecting to database.");
        }

        $conn->set_charset("utf8");
        return $conn;
    }

    /**
     * To get database results
     *
     * @param string $query
     * @param string $paramType
     * @param array $paramArray
     * @return array
     */
    public function select($query, $paramType = "", $paramArray = array())
    {
        $stmt = $this->conn->prepare($query);

        if (! empty($paramType) && ! empty($paramArray)) {

            $this->bindQueryParams($stmt, $paramType, $paramArray);
        }
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $resultset[] = $row;
            }
        }

        if (! empty($resultset)) {
            return $resultset;
        }
    }

    /**
     * To insert
     *
     * @param string $query
     * @param string $paramType
     * @param array $paramArray
     * @return int
     */
    public function insert($query, $paramType, $paramArray)
    {
        $stmt = $this->conn->prepare($query);
        $this->bindQueryParams($stmt, $paramType, $paramArray);

        $stmt->execute();
        $insertId = $stmt->insert_id;
        return $insertId;
    }

    /**
     * To execute query
     *
     * @param string $query
     * @param string $paramType
     * @param array $paramArray
     */
    public function execute($query, $paramType = "", $paramArray = array())
    {
        $stmt = $this->conn->prepare($query);

        if (! empty($paramType) && ! empty($paramArray)) {
            $this->bindQueryParams($stmt, $paramType, $paramArray);
        }
        $stmt->execute();
    }

    /**
     * 1.
     * Prepares parameter binding
     * 2. Bind prameters to the sql statement
     *
     * @param string $stmt
     * @param string $paramType
     * @param array $paramArray
     */
    public function bindQueryParams($stmt, $paramType, $paramArray = array())
    {
        $paramValueReference[] = & $paramType;
        for ($i = 0; $i < count($paramArray); $i ++) {
            $paramValueReference[] = & $paramArray[$i];
        }
        call_user_func_array(array(
            $stmt,
            'bind_param'
        ), $paramValueReference);
    }

    /**
     * To get database results
     *
     * @param string $query
     * @param string $paramType
     * @param array $paramArray
     * @return array
     */
    public function getRecordCount($query, $paramType = "", $paramArray = array())
    {
        $stmt = $this->conn->prepare($query);
        if (! empty($paramType) && ! empty($paramArray)) {

            $this->bindQueryParams($stmt, $paramType, $paramArray);
        }
        $stmt->execute();
        $stmt->store_result();
        $recordCount = $stmt->num_rows;

        return $recordCount;
    }
}
?>

Open in new window

Dave Baldwin

"Warning:  mysqli::__construct(): Headers and client library minor version mismatch."
I think this means your PHP is not installed correctly on that server.  You can also get that when someone tries to copy a PHP extension from a different version of PHP.  Extensions from PHP 7.4.1 don't necessarily work with PHP 7.4.2.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Chris Stanyon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
doctorbill

ASKER
Chris - as usual, you are right on the button
I changed the extensions in Cpanel to mysql_nd and nd_mysqli
Now the code is working as expected
Yes - I am trying to convert everything to PDO

doctorbill

ASKER
Correction - that is mysqlnd not mysql_nd