[RESOLVED] if/while loop times out

[jrough]

I tried to rewrite the fgetcsv() While loop following the example in the manual
however it is timing out somewhere in the WHILE or the IF. Is the problem with the counter $row or somewhere else ?? How do I add a counter?
thanks,

$target_path = "file_uploads/";
$target_path = $target_path . basename( $_FILES['uploadedfile']['name']);
$uploadedfile=$_FILES['uploadedfile']['name'];
$bu=$_POST['bu'];
$cart_id=$_POST['bu'];
if ($_POST['cart_id'] && $_POST['bu'] && move_uploaded_file($_FILES['uploadedfile']['tmp_name'],$target_path) ) {
    echo "The file ".  basename( $_FILES['uploadedfile']['name'])." has been uploaded<br>" ;
$sql="INSERT INTO order.cart_control (uploadedfile) VALUES ('$uploadedfile') ";
$result=mysql_query($sql);
if ( !$result ) {die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br>$sql");}
$controlID=mysql_insert_id();


$row = 1;
if (($fh = fopen($target_path, 'r+'))  !== FALSE ){
    while (($res = fgetcsv($fh, ",")) !== FALSE) {
      if ($row=1 ){
        foreach ($res as $key => $value)  {
            $hdr.=",$value";

    }//end for each
        $hdr=substr($hdr,1);
        echo "$hdr";

  }else{
    foreach ($res as $key =>  $value)  {
        $sData.=",'" . mysql_real_escape_string($value) . "'";
        echo "$sData";
    }//END FOR EACH
  $sData=substr($sData,1);
  $sql="INSERT INTO pharm_cart_order.cart_inventory (control_id, bu, cart_id, $hdr) VALUES ($controlID, '$bu', '$cart_id',$sData)   ";
  echo "$sql<br /> ";
  echo "<b>" . $sData . "</b><br />";
  $result=mysql_query($sql);
  if ( !$result ) {die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br>$sql");}
}//END ELSE
  $row++ ;
     }//end while
";
fclose($fh);

}else{
echo "Error uploading the file";
}

[bradgrafelman]

When posting PHP code, please use the board's [noparse]

..

[/noparse] bbcode tags as they make your code much easier to read and analyze.

As for your code, at a glance I see one problem here:

      if ($row=1 ){ 

In that code, you aren't comparing the value of $row, you're assigning it. If you meant to do the former (which I suspect you did), then you should be using a comparison operator (e.g. the double equal sign, '==').

[jrough]

The first value that is not part of the control table is accountNo. That value
is missing a beginning quote, for example,

accountNo,zonenm,zoneid,sku,supplierName,nationalDrugCode,brandDescription,genericDescription,class,qty,unit) VALUES (217, '0005', '111111',81755','OTC','1','2114','CEBERGEN '......................)

See the 81755 is missing the first quote.
How do I fix $sData? I can't tell where the missing quote should be inserted into.
thanks,

[bradgrafelman]

Rather than using a foreach() loop to loop through the values and append them onto the query, I would suggest using [man]implode/man instead, e.g.:

$array = array("one", "two", "three");
echo "Example: ('" . implode("','", $array) . "')";

[jrough]

Would that be use implode in both the if and the else?
Or is it just the else for $sData where the error is?
So is $res what gets imploded?
Then I assign $sData to the imploded array?
thanks,

$row = 1;
if (($fh = fopen($target_path, 'r+'))  !== FALSE ){
    while (($res = fgetcsv($fh, ",")) !== FALSE) {
      if ($row==1 ){
        foreach ($res as $key => $value)  {
            $hdr.=",$value";

    }//end for each
        $hdr=substr($hdr,1);

  }else{
   // foreach ($res as $key =>  $value)  {
     $sData.= implode(",",$res);

     //   $sData.=",'" . mysql_real_escape_string($value) . "'";
   //     echo "$sData";
    }//END FOR EACH
  $sData=substr($sData,1);

thanks, you have helped a lot already.

[jrough]

$row = 1;
Here is my attempt #2. The $sData would have be separated with commas and the commas and the row would be surrounded with single quotes? I'm not sure I will have to test it at work in the morning. thanks,

if (($fh = fopen($target_path, 'r+')) !== FALSE ){
while (($res = fgetcsv($fh, ",")) !== FALSE) {
if ($row==1 ){
foreach ($res as $key => $value) {
$hdr.=",$value";

    }//end for each 
        $hdr=substr($hdr,1); 

  }else{ 
   // foreach ($res as $key =>  $value)  { 
     $sData.=" (' ". implode(" ' ,' ",$res). " ')"; 

     //   $sData.=",'" . mysql_real_escape_string($value) . "'"; 
   //     echo "$sData"; 
    }//END FOR EACH 
  $sData=substr($sData,1);

[bradgrafelman]

You can use the same method of replacing foreach() with implode() for retrieving the header row as well:

foreach ($res as $key => $value) { 
$hdr.=",$value"; 

}//end for each 
$hdr=substr($hdr,1); 

would be more efficiently written as this:

$hdr = implode(',', $res);

EDIT: Just noticed the omission of [man]mysql_real_escape_string/man as well. You would still want to properly sanitize/escape the data, and you could do so in one line using [man]array_map/man before you do the implode()'ing, e.g.:

$res = array_map('mysql_real_escape_string', $res);

EDIT2: By the way, are you simply trying to insert an entire CSV file into a table? If so, have you looked at MySQL's LOAD DATA syntax that does all of this for you?

[jrough]

IT is much easier to understand. Thanks so much. I don't want to use the sql upload. I don't want it to break for some unseen reason on the user. I think I almost have it except the values don't get inserted? I'm not quite sure on array_map. It takes the imploded string and makes it an array again? I will however never use csv format again only text tab delimited. I see what implode does it puts the "," between the array pieces. I just noticed it doesn't have single qutoes ' ' around the first value in the $hdr .

-----error----------
Invalid query:Column count doesn't match value count at row 1
INSERT INTO mydb.inventory (control_id, bu, cart_id, accountNo,zonenm,zoneid,sku,supplierName,ndc,brandDescription,genericDescription,class,qty,unit) VALUES (222, '0005', '111111',Array)

if (($fh = fopen($target_path, 'r+'))  !== FALSE ){
    while (($res = fgetcsv($fh, ",")) !== FALSE) {
      if ($row==1 ){
          $hdr = implode(',', $res);   


I thought maybe I need to put implode into the else to build the values so I tried that also but got the same error:
[code=php]
if (($fh = fopen($target_path, 'r+'))  !== FALSE ){
    while (($res = fgetcsv($fh, ",")) !== FALSE) {
      if ($row==1 ){
          $hdr = implode(',', $res);   

      }else{

  $sData= implode(',', $res);
 $sData = array_map('mysql_real_escape_string', $sData);
  // echo "<b>" . $sData . "</b><br />";
 // $sData=substr($sData,1);
  $sql="INSERT INTO mydb.inventory (control_id, bu, cart_id, $hdr) VALUES ($controlID, '$bu', '$cart_id',$res)   ";
  echo "$sql<br /> ";

  $result=mysql_query($sql);
  //die($sql);
  if ( !$result ) {die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br>$sql");}
}//END ELSE
  $row++ ;

}else{
$res = array_map('mysql_real_escape_string', $res);
// echo "<b>" . $sData . "</b><br />";
// $sData=substr($sData,1);
$sql="INSERT INTO mydb.cart_inventory (control_id, bu, cart_id, $hdr) VALUES ($controlID, '$bu', '$cart_id',$res) ";
echo "$sql<br /> ";
[/code]

---2nd way--
Invalid query:Column count doesn't match value count at row 1
INSERT INTO mydb.inventory (control_id, bu, cart_id, accountNo,zonenm,zoneid,sku,supplierName,ndc,brandDescription,genericDescription,class,qty,unit) VALUES (225, '08005', '111111',Array)

by the way, thanks for all your help.

[bradgrafelman]

Read my post above again:

using array_map() before you do the implode()'ing

The [man]array_map/man function accepts an array as input and outputs that same array after all of the elements in the array have been passed through the callback function ([man]mysql_real_escape_string/man, in this case).

[jrough]

Sorry, thank you. It is working. I only get a warning on each Insert. Probably because I have error reporting on and I need to turn it off. Thanks again!!! Sorry I didn't read it. I did it right once and then forgot, I worked too much on the weekend. It does stop on one INSERT with an apostrophe in the cell text. Can I get rid of that error using fgetcsv()?

Here is the warning on each INSERT:
Warning: array_map() [function.array-map]: Argument #2 should be an array in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\tools\ etcetc

$row = 1;
if (($fh = fopen($target_path, 'r+'))  !== FALSE ){
    while (($res = fgetcsv($fh, ",")) !== FALSE) {
      if ($row==1 ){
          $hdr = implode(',', $res);   

      }else{

  $res= " ' ". implode( " ', ' ", $res). " ' ";
       $sData = array_map('mysql_real_escape_string', $res);
  // echo "<b>" . $sData . "</b><br />";
 // $sData=substr($sData,1);
  $sql="INSERT INTO mydb.inventory (control_id, bu, cart_id, $hdr) VALUES ($controlID, '$bu', '$cart_id',$res)   ";
  echo "$sql<br /> ";

  $result=mysql_query($sql);
  //die($sql);
  if ( !$result ) {die("<font color='red'>Invalid query:</font>" . mysql_error() . "<br>$sql");}
}//END ELSE
  $row++ ;
     }//end while
//echo "$i <bold>Records Inserted \n</bold>";
fclose($fh);
}//end if

[bradgrafelman]

jrough wrote:

I only get a warning on each Insert. Probably because I have error reporting on and I need to turn it off.

So the solution to a problem isn't fixing it but rather sweeping it under the rug and out of sight? 😉

Your problem is that you still haven't read the part of my post I quoted (in bold and underlined, too).

[jrough]

It is soo good to have this working right. I am going to be doing lots of these. Sorry for any confusion. I did try it that way but forgot to change it in the $sql so I changed it back.

[bradgrafelman]

Any chance you could post one last copy of your code now that you're done making changes to it?