I have a file that contains data from a device, and I need to insert this data into a database. The text file looks like this:
10/01/15-20:37:30 = 40.2,25.4,42.3,54.3,70.0,66.6
10/01/15-20:38:26 = 67.3,18.4,30.6,39.3,70.0,66.6
10/01/15-20:39:21 = 74.5,16.8,28.2,36.0,70.0,66.6
10/01/15-20:40:16 = 76.8,16.6,27.6,35.2,70.0,66.6
10/01/15-20:41:12 = 78.1,16.4,27.3,34.9,70.0,66.6
And my code currently looks like this:
<?php
$servername = "localhost";
$username = "root";
$password = "pass";
$dbname = "dbase";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
<?PHP
$file_handle = fopen("datalog.txt", "r");
while (!feof($file_handle) ) {
$line_of_text = fgets($file_handle);
$parts = explode(' ', $line_of_text);
print $parts[0] . $parts[1]. "<BR>";
}
$datainsert = "INSERT INTO reading (val1, val2, val3, val4, val5, val6, val7, val8, val9, val10) VALUES ($parts[0])";
if (mysqli_query($conn, $datainsert)) {
echo "New record created successfully";
} else {
echo "Error: " . $datainsert . "<br>" . mysqli_error($conn);
}
?>
<?php
$conn->close();
?>
The error I get is this:
Error:
INSERT INTO reading (Box_ID, Time_Stamp, Temperature, Humidity, CO_Level, VOC_Reading_UOM, VOC_Reading_V1, VOC_Reading_V2, VOC_Reading_V3, VOC_Reading_V4) VALUES () Column count doesn't match value count at row 1
My database and site do connect without errors, and this page is the only one with problems. What is going wrong?
You appear to be exploding by a string containing two spaces. In your text file, however, your 'fields' are separated by a single space. You should change your call to explode() to use a single space (or get fancy and look at preg_split()).
It looks to me you're trying to explode() your line string using a space as a delimiter. But that's not how it's put together according to your question.
Here's a line string, with numbers showing how it's exploded with a space.
10/01/15-20:37:30 = 40.2,25.4,42.3,54.3,70.0,66.6
00000000000000000 1 22222222222222222222222222222
Perhaps you want a sequence of two explode() calls:
$a = explode (' = ', $line_of_text);
$vals = explode(',' $a[1]);
That will break up your line like so
10/01/15-20:37:30 = 40.2,25.4,42.3,54.3,70.0,66.6
00000000000000000 11111111111111111111111111111
0000 1111 2222 3333 4444 5555
At the end of both these explode() calls you'll have a $vals array with your six numbers in it. Then you can do your insert like this.
INSERT INTO reading
(tstamp, val1, val2, val3, val4, val5, val6)
VALUES (STR_TO_DATE('%m/%d/%y %H:%i:%s',?), ?,?,?,?,?,?)
You can bind $a[0] -- the timestamp from your observation line -- to the first ? placeholder, and the six elements of $vals to the rest of the placeholders.
Edit OK, let's talk about placeholders. They go with bind_param() in MySQLi. Read up on that. Seriously.
Your code might look something like this:
$linecount = 0;
$file_handle = fopen("datalog.txt", "r");
while (!feof($file_handle) ) {
$line_of_text = fgets($file_handle);
$a = explode (' = ', $line_of_text);
$vals = explode(',' $a[1]);
if ($linecount == 0) {
/* rig up your SQL statement, just once */
$sql = "INSERT INTO reading (tstamp, val1, val2, val3, val4, val5, val6) VALUES (STR_TO_DATE('%m/%d/%y %H:%i:%s',?), ?,?,?,?,?,?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('dssssss', $a[0], $vals[0],$vals[1],$vals[2],$vals[3],$vals[4],$vals[5]);
}
if (!$stmt->execute()) {
printf("Error: %s.
", $stmt->error);
}
$linecount++;
}
$stmt->close();
Please note: I haven't had a chance to debug this code; if you use it that's left to you.