0

I am attempting to populate a database table using the data contained within a file. The file contains data in CSV format. I think I am really close but I cannot quite figure it out. I tried looking into the explode() functions as well but I could not make it work.

Would anybody with more experience using PHP and SQL together be able to provide some suggestions? Thanks ahead of time.

Lines 41-60 of code are the ones directly related to populating the table. The db_connect.php file referred to at the top of the code works. The rest of the code works as well. I tested the program by hardcoding data to populate the table before trying to implement this reading from file function.

At the moment, I am getting a error that says "Parse error: syntax error, unexpected end of file at line 103" which from doing some googling means I have an error in my PHP.

UPDATE: I was able to fix it. I have updated the code below and included some screenshots. Thank you to those of you that chimed in and provided some suggestions.

<?php
require_once './php/db_connect.php';
?>
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>BABYNAMES Table Test</title>
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet">
  </head>
  <body>
    <div class="container">
      <div class="page-header">
        <h1>BABYNAMES Table Test</h1>
      </div>

<?php
// Create table with three columns: Name, Gender, and Count
$createStmt = 'CREATE TABLE `BABYNAMES` (' . PHP_EOL
            . '  `Name` varchar(255),' . PHP_EOL
            . '  `Gender` char(1),' . PHP_EOL
            . '  `Count` int NOT NULL,' . PHP_EOL
            . '  PRIMARY KEY (`Name`, `Gender`)' . PHP_EOL
            . ') ENGINE=MyISAM DEFAULT CHARSET=latin1;';
?>
      <div id="step-one" class="well">
        <h3>Step One <small>Creating the table</small></h3>
        <pre><?php echo $createStmt; ?></pre>
<?php
if($db->query($createStmt)) {
    echo '        <div class="alert alert-success">Table creation successful.</div>' . PHP_EOL;
} else {
    echo '        <div class="alert alert-danger">Table creation failed: (' . $db->errno . ') ' . $db->error . '</div>' . PHP_EOL;
    exit(); // Prevents the rest of the file from running
}
?>
          </div>

<?php
// Read data from yob2016.txt into BABYNAMES table
        $query = "LOAD DATA LOCAL INFILE 'yob2016.csv'
                 INTO TABLE BABYNAMES
                 FIELDS TERMINATED BY ','
                 LINES TERMINATED BY '\n'
                 (Name,Gender,Count)";
?>
        <div id="step-two" class="well">
        <h3>Step Two <small>Inserting into the table</small></h3>
        <pre><?php echo $query; ?></pre>
<?php
if($db->query($query)) {
    echo '        <div class="alert alert-success">Values inserted successfully.</div>' . PHP_EOL;
} else {
    echo '        <div class="alert alert-danger">Value insertion failed: (' . $db->errno . ') ' . $db->error . '</div>' . PHP_EOL;
    exit();
}
?>
      </div>

<?php
// Get the rows from the table
$selectStmt = 'SELECT * FROM `BABYNAMES`;';
?>
      <div id="step-three" class="well">
        <h3>Step Three <small>Retrieving the rows</small></h3>
        <pre><?php echo $selectStmt; ?></pre>
<?php
$result = $db->query($selectStmt);
if($result->num_rows > 0) {
    echo '        <div class="alert alert-success">' . PHP_EOL;
    while($row = $result->fetch_assoc()) {
        echo '          <p>Name: ' . $row["Name"] . ' - Gender: ' . $row["Gender"] . ' - Count: ' . $row["Count"] . '</p>' . PHP_EOL;
    }
    echo '        </div>' . PHP_EOL;
} else {
    echo '        <div class="alert alert-success">No Results</div>' . PHP_EOL;
}
?>
      </div>

<?php
// Drop the TEST table now that we're done with it
$dropStmt = 'DROP TABLE `BABYNAMES`;';
?>
      <div id="step-four" class="well">
        <h3>Step Four <small>Dropping the table</small></h3>
        <pre><?php echo $dropStmt; ?></pre>
<?php
if($db->query($dropStmt)) {
    echo '        <div class="alert alert-success">Table drop successful.</div>' . PHP_EOL;
} else {
    echo '        <div class="alert alert-danger">Table drop failed: (' . $db->errno . ') ' . $db->error . '</div>' . PHP_EOL;
    exit();
}
?>
      </div>

    </div>
  </body>
</html>

This is a snippet of the data contained within the file. The file contains 20,000 lines of data.

Emma,F,19414
Olivia,F,19246
Ava,F,16237
Sophia,F,16070
Isabella,F,14722
Mia,F,14366
Charlotte,F,13030
Abigail,F,11699
Emily,F,10926
Harper,F,10733
Amelia,F,10702
Evelyn,F,10060
Elizabeth,F,9493
Sofia,F,9134
Madison,F,8982
Avery,F,8733
Ella,F,7866
Scarlett,F,7680

Screenshot 1

Screenshot 2 - Just the beginning of a loooooooong list lol

Screenshot 3

Miguel P
  • 13
  • 6
  • I don't see a form. – Funk Forty Niner Apr 30 '18 at 00:42
  • Do I need a form for data from a file? – Miguel P Apr 30 '18 at 00:44
  • 1
    yes you do, for $_POST and $_FILES superglobal arrays. Error reporting should be throwing you undefined index notices, but you didn't set your system to catch them. – Funk Forty Niner Apr 30 '18 at 00:46
  • also, files require a POST method along with a proper enctype. – Funk Forty Niner Apr 30 '18 at 00:48
  • I see, do you have any suggestions for how to implement a form? I'll look for documentation in the meantime. – Miguel P Apr 30 '18 at 00:48
  • you're getting undefined here because of `` which isn't inside a conditional statement. Use a ternary operator for that one alone. – Funk Forty Niner Apr 30 '18 at 00:49
  • I honestly have no idea what that is. I am new to PHP and SQL as it is, and this is my first time using them together. – Miguel P Apr 30 '18 at 00:49
  • suggestion to handle files: https://secure.php.net/manual/en/features.file-upload.post-method.php there isn't anything else I can add here, except write up some code which will take some time. You should read the manual on this and then retest your code. – Funk Forty Niner Apr 30 '18 at 00:50
  • Thank you. I am going to go through that manual before continuing. I would really appreciate some help with the code if you have time though. I am lost at the moment. – Miguel P Apr 30 '18 at 00:56
  • If you're new to MySQL it's important to remember to use InnoDB instead of the creaky old MyISAM engine, plus it's in your best interest to use `utf8mb4` as your default character set instead of `latin1`. Emoji are here to stay. MySQL also doesn't care about newlines, so the `PHP_EOL` stuff is extraneous and can go. – tadman Apr 30 '18 at 01:00
  • **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Apr 30 '18 at 01:01
  • Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman Apr 30 '18 at 01:01
  • Since this appears to be an endpoint a user might connect to, it seems very odd that you're building and populating the table as part of it. You should probably split out the table creation and population code into a separate script, then have one for viewing it. Build the table *once* and query it many times. – tadman Apr 30 '18 at 01:02
  • Consider using a [development framework](http://codegeekz.com/best-php-frameworks-for-developers/) to solve problems like this. These give you patterns to follow for organizing your code into proper model, view and controller contexts and avoids ending up with a confused stew of concerns, with HTML, PHP, SQL, and JavaScript all jumbled together. Frameworks come in many forms from really lean like [Fat-Free Framework](https://fatfreeframework.com/) to exceptionally full-featured like [Laravel](http://laravel.com/) and many spots in between. – tadman Apr 30 '18 at 01:03
  • 1
    Thanks for all the info tadman. I am taking notes lol – Miguel P Apr 30 '18 at 01:09
  • Are you actually trying to upload the CSV file or is it just a local file? – Nick Apr 30 '18 at 01:09
  • I am trying to fill the table with the data in the file. The file is located in the server. – Miguel P Apr 30 '18 at 01:14
  • Another thing to note is you can load in CSV directly into MySQL using [`LOAD DATA INFILE`](https://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile). – tadman Apr 30 '18 at 01:20
  • Funny you mention it, I actually just found another post where somebody recommends using that. I guess its much simpler. https://stackoverflow.com/questions/11448307/importing-csv-data-using-php-mysql – Miguel P Apr 30 '18 at 01:24

1 Answers1

0

Alright, I was able to solve my problem. My PHP code is now correctly reading the CSV file and populating the MySQL database table. Here is the snippet of the code above relevant to the issue.

<?php
// Read data from yob2016.txt into BABYNAMES table
        $query = "LOAD DATA LOCAL INFILE 'yob2016.csv'
                 INTO TABLE BABYNAMES
                 FIELDS TERMINATED BY ','
                 LINES TERMINATED BY '\n'
                 (Name,Gender,Count)";
?>
        <div id="step-two" class="well">
        <h3>Step Two <small>Inserting into the table</small></h3>
        <pre><?php echo $query; ?></pre>
<?php
if($db->query($query)) {
    echo '        <div class="alert alert-success">Values inserted successfully.</div>' . PHP_EOL;
} else {
    echo '        <div class="alert alert-danger">Value insertion failed: (' . $db->errno . ') ' . $db->error . '</div>' . PHP_EOL;
    exit();
}
?>
      </div>

I am pretty new to Stack Overflow so I do not know how answering your own questions is seen. I would imagine it's alright since it lets people know the issue has been resolved and therefore they do not have to waste time on your question.

Miguel P
  • 13
  • 6