Reading data from a file into a database

[VTS]

How would I go about reading data into a database from a text tile? The format would be similar to this:

23 343.03 23
24 43.56 19


Where the first column would be an id number, the second would be a total price, and the third would be how many of that item were sold. I am pretty new to php and I am not quite sure how to go about this.

Anyone have any ideas on how to do this or where I can look to learn about it?

[wildteen88]

Okay to do this you'll need to use the following functions:
fopen, fread and fclose to read the contents of the file

Next you'll need to use a function called explode, to get each row out of the text file.

And finally you'll want to use a while loop to insert each row into the database.

Hope that helps. If you need help with coding it thnen first consult the fine manual over at [a href=\"http://www.php.net\" target=\"_blank\"]http://www.php.net[/a] and sue the search box to search for each function I have mentioned in order to understand how to use them.

If you're still stuck I'll provide some code.

[VTS]

Thanks alot wildteen, that helps out alot. I should be able to figure it out from here.

[wildteen88]

Okay no problem! I have missed out a little detail in my last post.

You will need to use explode again in your whle loop in order for you to put the the id, price and quanty into their own sepreat columns in the database.

[VTS]

Ok, thanks again!!

[VTS]

Ok, I have looked up how the functions work but I am still a little confused on how I am going to get the data stored into an array.

If the data looks like this:

23 42.00 24
29 20.00 3
34 78.00 5

Column 1 being the item id, column2 being the price, and column 3 being the quantity.


How will I get this data stored as such? Sorry, this is the first time I have ever tried to read data from a file into a database so I am still having a rough time with it.

[wildteen88]

Have a read of the comments here:
[code]<?php

// connect to your database here


// replace prices.txt with your file that stores the info
$file = "prices.txt";

// open the file
$handle = fopen ($file, 'r');

// get the contents of the file
$contents = fread ($handle, 9999);

// close file
fclose ($handle);

// extract each row from the file:
$row = explode ("\n", $contents);

// get total number of rows
$rows = count ($row);

// now loop through each row and put each column into the database
for($i = 0; $i <= $rows-1; $i++)
{
    // this gets each column from the row!
    list ($id, $price, $qty) = explode (" ", $row[$i]);

    // now insert each column into the database!
    $query = "INSERT INTO table_name (id, price, qty) VALUES ('$id', '$price', '$qty')";
    $result = @mysql_query ($query) or ($error[] = "row{$i} failed to insert<br /><code>{$query}</code><br />" . mysql_error() . "<br /><br />");
}

if(isset($error))
{
    foreach ($error as $k => $v)
    {
        echo $error[$k];
    }
}
else
{
    echo "Data has been inserted successfully!";
}

?>[/code]
Also change where it says [i]table_name (id, price, qty)[/i] in the code above to the table that stores the prices in the database and id, price and qty to the correct field names

[VTS]

Wow!! That is great! Thanks alot for all the help and the quick replies. I just want to say that this is by far the best forum for php that I have ever come across.

Thanks again,
VTS

[wildteen88]

HA! No problem. If you have any problems post them here I'll try to help.

The script should catch a few errors that may arise.

[VTS]

Well I got it all written now but for some reason the data that gets entered into the database is all zeroes except for one field. Would the spacing in the file I am reading from have anything to do with this? Some of the fields have 5 spaces between them, others have more or less. Also, there are about 5-8 spaces between the leftmost column and the left margin in the file.

I opened up the file it was reading from just to make sure it has data in it, and it does so I am not quite sure what would cause the date that gets read in to be all zeroes.

Also, is there a size limit on how big the file can be that I am trying to read from? The file is 31kb.

[VTS]

Well I found out that it won't read the data in correctly when there is more than one space between the rows in the file that I am reading.

So my next question is how can I go about fixing this without having to have to manually delete spaces.

[wildteen88]

Sorry I didn't reply to your message the otherday.

Is the spaces at the start of each line before the first column? (the id coloumn). If it is you can use a function called trim within the for loop which will get rid of any space before and after a line. So change this:
[code]list ($id, $price, $qty) = explode (" ", $row[$i]);[/code]
to:
[code]list ($id, $price, $qty) = explode (" ", trim($row[$i]));[/code]

[VTS]

Yup they are before the first column and also the spaces between the columns are different (anywhere from 1-9). Thanks again for the help!! I will try that out now and see how that works.

[wildteen88]

If there are spaces between each column then it may require a bit of regex in order to convert mulktiple spaces into one. Trim only rmoved space form the start and end of a string.

[VTS]

Thanks for not giving up on me here.....once I get this figured out, I should be good to go. By the way, what is regex?

[wildteen88]

Okay change this:
[code]list ($id, $price, $qty) = explode (" ", trim($row[$i]));[/code]

to the following:
[code]// swap mulitple spaces with just one space
$row[$i] = preg_replace("/(\s+)/", " ", $row[$i]);

// this gets each column from the row!
list ($id, $price, $qty) = explode (" ", trim($row[$i]));[/code]

[VTS]

Thanks alot wildteen!!! I will get right on this after lunch. This stuff would take me forever to learn...I just looked up some stuff on regex and it seems to be something that would take awhile. Thanks again for all the help!!

[wildteen88]

No problem! Any problems please post back. Regex is quite hard to get around but is fun to use and learn. Regex is very powerful. It is used in a lot of applications, and programming lanaguages.