Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

PHP MYsql only last line of csv file is imported

Posted on 2010-08-20
22
416 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 109

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 109

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 109

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 109

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
 
LVL 109

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 109

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 109

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 109

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

861 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