Jump to content

Reading data from a file into a database


VTS

Recommended Posts

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?
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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!!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.