set a null value within a foreach array

[sanchez77]

Hey Everyone,

 

So i have this script that I use to read a text file and insert the records into a table.

 

Here is the script

function getFile($url) { 
   $ch = curl_init();
   curl_setopt($ch, CURLOPT_URL, $url);
   curl_setopt($ch, CURLOPT_HEADER, 1);    
   curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)");   
   curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);    
   
   $file = curl_exec($ch);    
   curl_close($ch);    
   return explode("\n", $file);
}

include "connect.php";
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$wx = array_map('trim',getFile("http://www.mysite.net/tides.txt"));
$newwx = array();
foreach($wx as $i => $line) {
   if ($i > 11 && $i < 250) {
        $tmp = array_filter(explode(',',$line));
        $q = "insert into tides (Date,Day,Time,Predft,Predcm,HighLow) values ('" . implode("','",$tmp) . "')";
        $rw = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
   }
}

 

Here is the sample text file where i encounter the problem. The problem I am having is that the above script does not insert a null value for a null value, it just skips it. Causing the script to fail because the number of values to insert is missing a value due to the null. The fourth record below, is 2011/01/07,Fri,02:53 AM,0.0,,L

 

How do I change the script above to make the null value in between ,, equal 0?

 

2011/01/06,Thu,08:29 AM,4.6,140,H

2011/01/06,Thu,02:56 PM,-0.3,-9,L

2011/01/06,Thu,08:55 PM,3.5,107,H

2011/01/07,Fri,02:53 AM,0.0,,L

2011/01/07,Fri,09:08 AM,4.4,134,H

2011/01/07,Fri,03:34 PM,-0.2,-6,L

2011/01/07,Fri,09:34 PM,3.4,104,H

 

Thanks everyone, i have tried various versions of the above and  I can't get it right. Any help is greatly appreciated.

 

Also, i just have to say, PHP is awesome!!!

[Psycho]

I'll start with a suggestion. Give your variables meaningful names. Names such as "$tmp", "$q", and "$newwx" tell me nothing about what the variable is for or what kind of value it holds. Second, add comments to your code. It may seem more efficient not to do those things but, believe me, it will save countless hours in the long run. In this case it makes it more difficult for us to help you as we have to read each line and interpret what it is doing to understand the next line.

 

For this, the problem is that you are trying to do a shortcut my just adding the values without any validation. Always validate data before using it in your code - especially queries. Interestingly, I would think the array_filter() function would cause an error since you are only passing one parameter and it requires two.

 

In the code below I have only provided a means to validate the "predft" value, but you shoudl do an appropriate validation of each piece of data to ensure it is the appropriate type using functions to convert to an appropriate numeric type or use mysql_real_escape_string() on string data. Also, you have separate fields for date, day and time. I expect you are storing those as string values. You should consider having a single datetime or timestamp field in the database and then use those three values from the input to generate an appropriate value. That will give you a lot more functionality when querying the data.

 

include "connect.php";
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

function getFileAry($url)
{ 
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_HEADER, 1);    
    curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)");   
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);    
   
    $file = curl_exec($ch);    
    curl_close($ch);    
    return explode("\n", $file);
}

//Get the file as an array
$fileAry = getFileAry("http://www.mysite.net/tides.txt");
//extract out only lines 12 - 249
$dataAry = array_slice($fileAry, 12, 249);
//Process each line in the data array
foreach($dataAry as $dataLine)
{
    $dataAry = array_filter(explode(',', $dataLine), 'trim');
    list($date, $day, $time, $predft, $predcm, $highlow) = $dataAry;
    //Validate each data for the type of value expected
    //Only providing a validation for $predft

    $predft = intval($predft); //Convert value to in integer

    $query = "INSERT INTO tides (`Date`, `Day`, `Time`, `Predft`, `Predcm`, `HighLow`)
              VALUES ('$date', '$day', '$time', '$predft', '$predcm', '$highlow')";
    $esult = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
}

[sanchez77]

Thanks, I tried your code as below and it returned "Problem with the query: Query was empty", any ideas why it would do that or what i need to change?

 

<?php
error_reporting(E_ALL);
ini_set("display_errors", 1);


include "connect.php";
if (!$con){
    die('Could not connect: ' . mysql_error());
}

function getFileAry($url){ 
  $ch = curl_init();
  curl_setopt($ch, CURLOPT_URL, $url);
  curl_setopt($ch, CURLOPT_HEADER, 1);
  curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)"); 
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
  $file = curl_exec($ch); 
  curl_close($ch);  
  
  return explode("\n", $file);}
  //Get the file as an array
  $fileAry = getFileAry("http://www.mysite.net/tides.txt");
  //extract out only lines 12 - 249
  $dataAry = array_slice($fileAry, 12, 249);
  //Process each line in the data array
  foreach($dataAry as $dataLine){    $dataAry = array_filter(explode(',', $dataLine), 'trim');
    list($date, $day, $time, $predft, $predcm, $highlow) = $dataAry;    
//Validate each data for the type of value expected
//Only providing a validation for $predft    
$predft = intval($predft);
$predcm = intval($predcm);
//Convert value to in integer    
$query = "INSERT INTO tides (`Date`, `Day`, `Time`, `Predft`, `Predcm`, `HighLow`) VALUES ('$date', '$day', '$time', '$predft', '$predcm', '$highlow')";    $esult = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());}
mysql_close($con);
?>

[Psycho]

I changed the variable name that the query is stored in from $q to $query, but didn't change the variable name in the mysql_query() function. As my sig states, I don't always test my code - especially when the code requires specific input or me having to create a db.

 

NOTE: I also changed the name of the variable that I assigned the result of mysql_query(). It was $rw and I meant to change it to $result, but instead made it $esult. Although, since you have an "or die" clause on teh query call, there is really no point in assigning the result of an INSERT query to a variable.

[sanchez77]

Thanks, that was it.

 

Apprecaite your help with this.

 

Cheers