CSV and PHP help

[dflowers]

I need to fetch a csv file that is automatically updated on a server via an url. For example, I need to connect to http://www.domain.com/folder/file.csv and then add the column titles because the csv file does not include column titles. I then need to filter any duplicate rows, and limit the number of rows displayed to say 5, and display the data in a table to be used on a php page. It would also be nice if I had the option to expire rows after a set period of time, say 10 minutes. Finally, I need the php page to refresh every 60 seconds and check for updates to the csv file. I am a webmaster for a fire department and I am trying to add a page that uses a csv file from the CAD server hosted by the county government to display the most recent fire calls. I am not very well versed in scripting, but with a little bit of example code and explanations, I am sure I can figure it out. Any help would be very much appreciated. Thanks, Dorian

[sde]

well that is a lot of asking for 1 post, so i'll just show you how to get started for now. later, you can fire off more direct and specific questions as it starts to take shape.

first of all, php has a great function to read external files. file()

this function reads the file you give it into an array. so each line of the file is one element of the array.

PHP Code:

<?
$my_array = file("http://www.domain.com/folder/file.csv");
?>

now, what you do next is going to be up to you. your pretty limited with arrays for filtering data management, so you might want to use a mysql database to temporarily store the csv information before you add the extra column.

after you read the file into an array, you might do something like this:

PHP Code:

<?
foreach($my_array as $line){
  $row = explode($line,",");

  // maybe you can scan for duplicates here before you insert the line
  $result = mysql_query("select count(*) as count from table where field1='".$row[0]."'");
  $num = mysql_result($result,0,0);

  // now only insert the row into the database table if it's not a duplicate
  if($num==0){
    $result = mysql_query("insert into table (field1,field2,field3) values('".$row[0]."','".$row[1]."','".$row[2]."')");
  }
}
?>

without the data field, it's difficult to make a mock-up. you could either check the csv for updates, or just run the entire process all over again. that may be better since you have to read in the entire csv file anyway.

you would need to set this php script to run on cron or i think windows could run it as a 'scheduled task' (not sure on windows), .. then the php page that is being viewed is just viewing the latest database records.

my response is kind of all over the place, but your post is a little bit too. it's difficult to answer clearly, but i hope this helps a little.

[dflowers]

I can definitely store the data in a MySql database. Sorry of all of the questions all at once. I just figured I would put it all out on the table so any poor soul who responded would have an idea where I was wanting to go. The url to the CSV file is: http://www.fpfr.com/calls/call.csv if this will help.

[dflowers]

Ok, how do I send this information to a database? I set up the page like this:

<?
$my_array = file("http://www.fpfr.com/calls/call.csv");
?>

<?
foreach($my_array as $line){
$row = explode($line,",");

// maybe you can scan for duplicates here before you insert the line
$result = mysql_query("select count(*) as count from table where field1='".$row[0]."'");
$num = mysql_result($result,0,0);

// now only insert the row into the database table if it's not a duplicate
if($num==0){
$result = mysql_query("insert into table (field1,field2,field3) values('".$row[0]."','".$row[1]."','".$row[2]."')");
}
}
?>

I called the page csvtest10.php I got this error

Warning: mysql_query(): Access denied for user: 'apache@localhost' (Using password: NO) in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 10

Warning: mysql_query(): A link to the server could not be established in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 10

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 11

Warning: mysql_query(): Access denied for user: 'apache@localhost' (Using password: NO) in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 15

Warning: mysql_query(): A link to the server could not be established in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 15

Warning: mysql_query(): Access denied for user: 'apache@localhost' (Using password: NO) in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 10

Warning: mysql_query(): A link to the server could not be established in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 10

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 11

Warning: mysql_query(): Access denied for user: 'apache@localhost' (Using password: NO) in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 15

Warning: mysql_query(): A link to the server could not be established in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 15

Warning: mysql_query(): Access denied for user: 'apache@localhost' (Using password: NO) in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 10

Warning: mysql_query(): A link to the server could not be established in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 10

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 11

Warning: mysql_query(): Access denied for user: 'apache@localhost' (Using password: NO) in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 15

Warning: mysql_query(): A link to the server could not be established in /usr/local/psa/home/vhosts/nhcfirerescue.org/httpdocs/csvtest10.php on line 15

It just repeats over and over. Sorry, but I have very little clue what I am doing here. My understanding of PHP is very basic.

Originally Posted by sde

well that is a lot of asking for 1 post, so i'll just show you how to get started for now. later, you can fire off more direct and specific questions as it starts to take shape.

first of all, php has a great function to read external files. file()

this function reads the file you give it into an array. so each line of the file is one element of the array.

PHP Code:

<?
$my_array = file("http://www.domain.com/folder/file.csv");
?>

now, what you do next is going to be up to you. your pretty limited with arrays for filtering data management, so you might want to use a mysql database to temporarily store the csv information before you add the extra column.

after you read the file into an array, you might do something like this:

PHP Code:

<?
foreach($my_array as $line){
  $row = explode($line,",");

  // maybe you can scan for duplicates here before you insert the line
  $result = mysql_query("select count(*) as count from table where field1='".$row[0]."'");
  $num = mysql_result($result,0,0);

  // now only insert the row into the database table if it's not a duplicate
  if($num==0){
    $result = mysql_query("insert into table (field1,field2,field3) values('".$row[0]."','".$row[1]."','".$row[2]."')");
  }
}
?>

without the data field, it's difficult to make a mock-up. you could either check the csv for updates, or just run the entire process all over again. that may be better since you have to read in the entire csv file anyway.

you would need to set this php script to run on cron or i think windows could run it as a 'scheduled task' (not sure on windows), .. then the php page that is being viewed is just viewing the latest database records.

my response is kind of all over the place, but your post is a little bit too. it's difficult to answer clearly, but i hope this helps a little.

[idx]

Yeah, quite a number of issues and ways to tackle them.

I'll hit a few:

1 - Page refresh: A simple META refresh or one line of javascript should be fine. Here's a quick link on the META: http://webdesign.about.com/cs/metatags/a/aa080300a.htm

2 - Only show the last 5 unique entries:

PHP Code:

<?php
$my_array  = file("http://www.fpfr.com/calls/call.csv");
$my_array  = array_unique($my_array);
$new_array = array_splice($my_array, count($my_array)-5, 5);

echo 'Old Array:<hr>';
echo '<pre>'; print_r($my_array); echo '</pre>';
echo '<hr>New Array:';
echo '<pre>'; print_r($new_array); echo '</pre>';

?>

Obviously you want to use some kind of looping logic [foreach/etc.] on the $new_array var, but you get the point.

3 - Expiring messages after 10 minutes. Several options come to mind:

- Insert the data into mysql and give it a timestamp. Then you just query for entries that are greater than 10 minutes

Although in this case, I probably wouldn't try to use mysql at first. I'm not sure how big that CSV file gets, but there were only around 30 rows (26 unique) at 12:42am. If you don't foresee the file getting that big, then forget about the 10 minute expiration for now. It should be easy enough to work in later. (you could always parse the date/time in the message since it seems to be in a consistent format then filter based on current time vs that time.)

Another option might be to show the latest entries first. (i assume this is for someone watching the page) If so then you can just run the array through array_reverse() before displaying.

-r

[dflowers]

Thank you for your response. I hope you understand that I am an extreme newbie and will virtually need someone willing to hold my hand troughout the development process to make this work. I have very little scripting knowledge with very basic PHP and Mysql knowledge. Any additional pointers would be helpful. I also can set up a test database if you are willining to work with me on this. Thanks in advance. Dorian

[idx]

Dorian,

Did you ever get something going with this? I've been pretty busy at work, so I just pop by the forums to check for something new.. Not much time these days.

(just noticed the csv file isn't available)

Oh, also noticed that the fpfr domain has a link to recent calls - an aspx script. Nevermind....

-r