Solved

PHP MYsql only last line of csv file is imported

Posted on 2010-08-20
22
414 Views
Last Modified: 2013-12-13
Hi there I have a CSV file that I am trying to import into a database. All the sql side of things is well and I can import no problem. However it only seems to import the last line of the file. Here is the code I am using.
foreach($arr as $str)
{
	list($name,$price,$Description)=explode(",",$str);
}
$sql="INSERT INTO test1 (name, price, Description) VALUES ('$name', '$price', '$Description')";

$result=mysql_query($sql);

Open in new window


It seems to work fine when I print the output to screen as follows.

$arr=file("conts.csv");

    foreach($arr as $str)
    {
    list($name,$price,$Description)=explode(",",$str);

    echo "<li>".$name;
    echo "<li>".$price;
    echo "<li>".$Description;

    echo "<hr>";
    }

Open in new window


Anyone have ideas about what may be causing my issue?
0
Comment
Question by:gweeble1
  • 8
  • 8
  • 5
  • +1
22 Comments
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 33483866
You need to include the SQL query within your Loop. At the moment it loops through your array, and once that's finished it inserts a record - i.e the last one.

Change your code to the following so that it runs an INSERT on  each loop.



foreach($arr as $str)

{

	list($name,$price,$Description)=explode(",",$str);



        $sql="INSERT INTO test1 (name, price, Description) VALUES ('$name', '$price', '$Description')";



        $result=mysql_query($sql);

}

Open in new window

0
 

Author Comment

by:gweeble1
ID: 33483894
Okay so had thought about that Chris, adding the sql code to the foreach loop as below, however that just seemed to generate an error with the if statement and didn't add any data to the sql table.
[code]
foreach($arr as $str)
{
      list($name,$price,$Description)=explode(",",$str);

$sql="INSERT INTO test1 (name, price, Description) VALUES ('$name', '$price', '$Description')";

$result=mysql_query($sql);

}

if($result){
echo "Successful";
echo "
";
echo "View result";
echo "$sql";
}

else {
echo "ERROR";
echo "$sql";
}
[/code]
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 33483954
You should put everything inside the loop. You can do the same as above without an if statement using the DIE function.



foreach($arr as $str)

{

	list($name,$price,$Description) = explode(",",$str);

	$sql="INSERT INTO test1 (name, price, Description) VALUES ('$name', '$price', '$Description')";

	

	$result = mysql_query($sql) or die("Error: " . $sql)



	echo "Successful";

	echo "View result";

	echo $sql;

}

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33483976
You might want to do exactly what ChrisStanyon says, and add this diagnostic information.

$result=mysql_query($sql);
if (!$result)
{
    echo "<br/>" . mysql_errno();
    echo "<br/>" . mysql_error();
    echo "<br/>" . $sql;
}
0
 
LVL 1

Expert Comment

by:correlje
ID: 33483986
You get better feedback on the error using this.

OUT:
$sql="INSERT INTO test1 (name, price, Description) VALUES ('$name', '$price', '$Description')";
$result=mysql_query($sql);

IN:
$result = mysql_query("INSERT INTO test1 (name, price, Description) VALUES ('".$name."','".$price."','".$Description."')") or die('A error occured: ' . mysql_error());
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33483995
Then once we know what the value of mysql_error() has, we can fix the error.  Are you sure you have connected and selected the data base?  And the table exists and the column names are correctly spelled?  IIRC MySQL table and column names are case-sensitive.
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 33484096
To give you more diagnostic info, just change a line in my code to the following:

Bear in mind that using DIE will stop your script. If you need to carry on with your script, even with an error, then you should use the if ( ! $result ) method as shown by Ray.






$result = mysql_query($sql) or die("MySQL Error: " . mysql_error() . "<br />Error Num: " . mysql_errno() . "<br />SQL: " . $sql);

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33484210
While we are waiting for your SQL diagnostics, I looked back at this line of code:

list($name,$price,$Description)=explode(",",$str);

I think you might want to learn about fgetcsv() and forget about list() and explode() for this application.
http://us.php.net/manual/en/function.fgetcsv.php

Consider what would happen if you have this in your CSV file:
Ray, 1446 Colleen Lane, "McLean, VA", 22101

list() + explode() will produce the wrong data.  fgetcsv() will probably work the way you want.

You might also want to learn about escaping your data strings.  please read the man page here:
http://us.php.net/manual/en/function.mysql-real-escape-string.php
0
 

Author Comment

by:gweeble1
ID: 33484543
Chris,

For some reason I can't fathom out the code fails to execute if the following is used..

foreach($arr as $str)
{
      list($name,$price,$Description)=explode(",",$str);

        $sql="INSERT INTO test1 (name, price, Description) VALUES ('$name', '$price', '$Description')";

        $result=mysql_query($sql);
}

If I revert back to how I had it before

foreach($arr as $str)
{
      list($name,$price,$Description)=explode(",",$str);
}
$sql="INSERT INTO test1 (name, price, Description) VALUES ('$name', '$price', '$Description')";

$result=mysql_query($sql);

Then it works fine, but still only updates the last line of the file. So i was wondering is there a way we can do it a slightly different way, identify the first line form the csv file, copy line from a file, submit to mysql, go to line 2 e.t.c.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33484566
You MUST test mysql functions for success or failure.  This is not adequate:
$result=mysql_query($sql);

What is the value of $result?  If you do not test, you have no way of knowing if the query worked.  Please change the script to add the diagnostic information suggested above, thanks.
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 33484627
As Ray says, without the diagnostic info, we can't tell what the problem is. Add that in, run the script an report back on any errors you find.

The other way you want to work won't solve the problem, as I guess this is what you are already doing. I'm assuming from your code that $arr is an array of lines from your CSV File.




0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 33484662
This is untested code, and it's kind of minimalist from the perspective of error-handling, but maybe it will provide some guidance on the usual patterns involved in loading a data base with information from a CSV file.

HTH, ~Ray
<?php // RAY_csv_to_db.php
error_reporting(E_ALL);
echo "<pre>\n";  //READABILITY FOR var_dump()


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES



// THE NAMES OF OUR COLUMNS
$query_cols = "name, price, Description";

// TEST DATA URL
$csv = "http://path/to/my.csv";
$fpo = fopen($csv, 'r');
if (!$fpo ) die('CRUMP');

// SET A ROW COUNTER
$counter = 0;

// KEEP TRACK OF ROWS THAT HAVE THE WRONG NUMBER OF FIELDS
$errors = array();

// LOOP THROUGH THE CSV RECORDS PERFORMING CERTAIN TESTS
while (!feof($fpo))
{
    $counter++;

    // GET A RECORD
    $csvdata = fgetcsv($fpo);

    // SKIP OVER EMPTY ROWS
    if (empty($csvdata)) continue;

    // ESCAPE THE INFORMATION FOR USE IN THE QUERY
    foreach ($csvdata as $ptr => $value)
    {
        $csvdata[$ptr] = mysql_real_escape_string($value);
    }

    // SET UP VALUE FIELDS
    $query_data = "'" . implode("', '", $csvdata) . "'";

    // SET UP A QUERY
    $sql = "REPLACE INTO myTable ( $query_cols ) VALUES ( $query_data )";

    // RUN THE QUERY HERE....
    $res = mysql_query($sql);
    
    // IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
    // MAN PAGE: http://us.php.net/manual/en/function.mysql-error.php
    if (!$res)
    {
        $errmsg = mysql_errno() . ' ' . mysql_error();
        echo "<br/>QUERY FAIL: ";
        echo "<br/>$sql <br/>";
        die($errmsg);
    }

}


// SHOW THE NUMBER OF ROWS PROCESSED
echo "<br/>RECORDS PROCESSED $counter \n";

Open in new window

0
 

Author Comment

by:gweeble1
ID: 33486372
Guys thanks for your help...

I have integrated the error logging as follows, but all it seems to do is generate a blank screen.

I assumed that the error coded needed to nested in the main command braces.

foreach($arr as $str)
{
      list($name,$price,$Description)=explode(",",$str);
      $sql="INSERT INTO test1 (name, price, Description) VALUES ('$name', '$price', '$Description')";
      $result=mysql_query($sql);
if (!$result)
{
    echo "<br/>" . mysql_errno();
    echo "<br/>" . mysql_error();
    echo "<br/>" . $sql;
}
}
0
 

Author Comment

by:gweeble1
ID: 33486381
Ray thanks for the code you supplied, will review and see how I get on.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33487270
Once you have a new script and have tested it, please post the entire script here if you have any further difficulties.  Also, please post some of the test data.  I think we are guessing with only fragments of the necessary information right now.  Thanks, ~Ray
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 33487286
Your code looks fine. The error messages will only display if there's an error with the mysql_query function (which is what you want). The fact that you're not getting an error indicates that the query didn't return one (assuming your script isn't dying before it gets that far.

Apart from the blank screen, do the records get inserted into the database.

You may also benefit from making sure that php error reporting is turned ON.

Use the following code to turn it on and off. When your site goes live, turn it off :)









//Turns Error Reporting On

ini_set('display_errors',1);

error_reporting(E_ALL|E_STRICT);



//Turns Error Reporting Off

error_reporting(0);

Open in new window

0
 

Author Comment

by:gweeble1
ID: 33487422
Ray heres the data from my import file.

Item1,27.99,Description for Item 1
Item2,38.99,Description for Item 2

Keeping it pretty simple, I had three lines in at the start, but took the third line out. Guy
0
 

Author Comment

by:gweeble1
ID: 33487531
Chris,

Below is the full code from the file. as you can see, this is a two stage. The first prints the contents of csv file to the screen. Just so I know that the file is getting some of the way through. The last bit that prints to the screen is 'echo "connected to database" just after the $con. But it never seems to progress and it doesn't write any data to the table. But if I uncomment my code at the bottom and replace the uncommented code it puts the last line of the file into the database. I am stumped. Going to give Rays code a try and will update in a while. Have a great weekend helpers and thanks for the assistance.

<?
$arr=file("conts.csv");

    foreach($arr as $str)
    {
    list($name,$price,$Description)=explode(",",$str);

    echo "<li>".$name;
    echo "<li>".$price;
    echo "<li>".$Description;

    echo "<hr>";
    }
      
$con = mysql_connect("10.0.0.5","user","password");
mysql_select_db("dbname", $con);
echo "Connected to Database<br>";
$arr=fopen("conts.csv", "r");
foreach($arr as $str)
{
      list($name,$price,$Description)=explode(",",$str);
      $sql="INSERT INTO test1 (name, price, Description) VALUES ('$name', '$price', '$Description')";
      $result=mysql_query($sql);
if (!$result)
{
    echo "<br/>" . mysql_errno();
    echo "<br/>" . mysql_error();
    echo "<br/>" . $sql;
}
}

//      $sql="INSERT INTO test1 (name, price, Description) VALUES ('$name', '$price', '$Description')";
//      $result=mysql_query($sql);
//      echo "that worked<br>";
//      echo $sql;
?>
0
 

Author Comment

by:gweeble1
ID: 33487634
Ray it works like a charm. Many thanks...
0
 

Author Closing Comment

by:gweeble1
ID: 33487666
Ray,

I owe you a couple of beers! Absolutely perfect.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33487773
Please use the code snippet when you post code; it makes the line numbers visible and that makes dialog easier.

Near the top you have this:
$arr=file("conts.csv");
foreach($arr as $str)

Later down the script you have this:
$arr=fopen("conts.csv", "r");
foreach($arr as $str)

Note that fopen() does not return an array, so foreach() does not have anything to work on.

Let me know if you have trouble after you've tried the script I posted, thanks. ~Ray
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33487777
OK, great - I guess we were posting at the same time!  Anyway, glad it's working for you now.  Cheers, ~!Ray
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now