Showing posts with label array. Show all posts
Showing posts with label array. Show all posts

Thursday, January 05, 2012

PHP - Parsing a csv file created by Excel

Useful for updating tabular data on a website, without editing any html. Especially if there is no access to a database to upload the info. I found this function at php.net. It returns an array with all the data in the csv file...



function parse_csv($file,$comma=',',$quote='"',$newline="\n") {

$db_quote = $quote . $quote;

// Clean up file
$file = trim($file);
$file = str_replace("\r\n",$newline,$file);

$file = str_replace($db_quote,'"',$file); // replace double quotes with " HTML entities
$file = str_replace(',",',',,',$file); // handle ,"", empty cells correctly

$file .= $comma; // Put a comma on the end, so we parse last cell


$inquotes = false;
$start_point = 0;
$row = 0;

for($i=0; $i<strlen($file); $i++) {

$char = $file[$i];
if ($char == $quote) {
if ($inquotes) {
$inquotes = false;
}
else {
$inquotes = true;
}
}

if (($char == $comma or $char == $newline) and !$inquotes) {
$cell = substr($file,$start_point,$i-$start_point);
$cell = str_replace($quote,'',$cell); // Remove delimiter quotes
$cell = str_replace('"',$quote,$cell); // Add in data quotes
$data[$row][] = $cell;
$start_point = $i + 1;
if ($char == $newline) {
$row ++;
}
}
}
return $data;
}

$filename = "/path/to/file.csv";
$fd = fopen ($filename, "r");
$file = fread ($fd,filesize ($filename));


$new_array=parse_csv($file); // Returns an array of data from the csv file



Then the array can be used the same way a returned mysql result set would be used (see here). The array can be searched (see previous post here) or to simply access elements from the returned array you can simply call individual elements as such...


//line 1 data
echo $new_array[0][0];
echo $new_array[0][1];
echo $new_array[0][2];
//etc
//line 2 data
echo $new_array[1][0];
echo $new_array[1][1];
echo $new_array[1][2];
//etc


To pick out random lines from a csv (eg.with 3 columns year,day,month) and use that data the rand function can be used as follows...



$num=count($new_array);
$num=$num-1;//minus one to cope with 0 in array
$i=rand (0, $num );


$year=$new_array[$i][0];
$day=$new_array[$i][1];
$month=$new_array[$i][2];

echo "The random year is ".$year.". The random day is ".$day.". The random month is ".$month;

Monday, October 17, 2011

MySQL resultset as an associative array

To save time and resources, when a php application is going to require the same resultset often, the resultset can be saved in a session as an associative array. Then when required in the script the saved session data can be referenced instead of querying the database again.

To return a resultset as an array named $ac_arr requires the following mysql...

$result=mysql_query("SELECT name,startdate,enddate FROM accounts") or die(mysql_error());

while(($ac_arr[] = mysql_fetch_assoc($result)) || array_pop($ac_arr));

The array_pop() function simply removes the extra element that would otherwise be appended to the array.

This can then be stored in a session variable and later referenced without re-querying the database...


$_SESSION['ac_array']=$ac_arr;

To list the keys and values of elements in the array

<?php
$acs=$_SESSION['ac_array'];
foreach($acs as $keys=>$values)
{
foreach ($values as $key => $value) {
echo $keys." : ".$key.":".$value."<br />";
}
}
?>

The above will display a list like this
0 : name : AC1
0 : startdate : 2011-08-16
0 : enddate : 2011-12-16
1 : name : AC2
1 : startdate : 2014-07-25
1 : enddate : 2024-07-25
2 : name : AC3
2 : startdate : 2012-05-10
2 : enddate : 2022-05-10

so you can reference any element in the array by calling
$acs[0][name] (will return AC1)
$acs[2][startdate] (will return 2012-05-10)

Monday, June 27, 2011

PHP - Assign variable names to array elements from mysql query

It is useful to be able to take the results of a MYSQL query and assign variable names to the results, so that they can be used later on in a script.

This can be done simply as follows...


$result= mysql_query("SELECT id FROM table WHERE num='567' LIMIT 3") or die(mysql_error());

while ($row = mysql_fetch_array($result)) {

$ids[] = $row['id'];

}
$id1 = $ids[0];
$id2 = $ids[1]
$id3 = $ids[2];


So you now have 3 variables fetched from the mysql array that can be used outside of a loop.

This specific example limits the mysql result to 3. However you could do this for any (change the 3 to how many results you want) and unknown numbers of results doing a COUNT of returned results and then iterating based on that number.