Solved

PHP MYsql only last line of csv file is imported

Posted on 2010-08-20
22
412 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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Ray thanks for the code you supplied, will review and see how I get on.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ray it works like a charm. Many thanks...
0
 

Author Closing Comment

by:gweeble1
Comment Utility
Ray,

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

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
OK, great - I guess we were posting at the same time!  Anyway, glad it's working for you now.  Cheers, ~!Ray
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 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

18 Experts available now in Live!

Get 1:1 Help Now