Display data from a CSV in a form using PHP

Robert-B

Member
I am looking at building a simple app that will display data. The data in the csv is User, User Group, Quarter, Year, Estimated and Actual Costs.

I would like to use a form to display the data dynamically and when you select a set of options the data is displayed on the page. Is this possible?
 

Robert-B

Member
Thanks Chris,

I have tried that and managed

<?php
$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
echo "<p> $num fields in line $row: <br /></p>\n";
$row++;
for ($c=0; $c < $num; $c++) {
echo $data[$c] . "<br />\n";
}
}
fclose($handle);
}
?>

This just returns the whole csv. How would I limit it using a search box and only show in a table the matches?
 

chrishirst

Well-Known Member
Staff member
Provide an input field and submit button for the user to provide their search parameter(s), then before the echo statement in your display loop add a
conditional test so it only runs the echo when the test condition is true.
 

Robert-B

Member
I have done the following:

index.php

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
</head>
<body>
<form action="search.php" method="get">
<label>
Search
<input type="text" name="keywords" autocomplete="off">
</label>

<input type="submit" name="Search">
</form>
</body>
</html>
search.php

<?php

if (isset($_GET['keywords'])) {

$searchYear = isset($_GET['keywords']) ? (int) trim($_GET['year']) : null;

}

$row = 1;
$output = [];

if (($handle = fopen("data.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$row++;

// if the user tried to search on year and it doesn't match, continue skips to the next row
// casting ensures we compare integers with integers
if (!empty($searchYear) && (int) $data[CSV_INDEX_YEAR] !== $searchYear) {
continue;
}

// if the user tried to search on user and it doesn't match, skip
if (!empty($searchUser) && $data[CSV_INDEX_USER] !== $searchUser) {
continue;
}

$output[] = $data;
}
fclose($handle);
}
?>

<?php if (!empty($output)): ?>
<table>
<tr>
<th><strong>Landlord</strong></th>
<th><strong>Landlord Group</strong></th>
<th><strong>Quarter</strong></th>
<th><strong>Year</strong></th>
<th><strong>Estimated Project Costs</strong></th>
<th><strong>Actual Project Cost</strong>
<tr>
<?php foreach ($output as $row): ?>
<tr>
<td><?=$row[0]?></td>
<td><?=$row[1]?></td>
<td><?=$row[2]?></td>
<td><?=$row[3]?></td>
<td><?=$row[4]?></td>
<td><?=$row[5]?></td>
</tr>
<?php endforeach; ?>
</table>
<?php endif; ?>
It looks good, however I am struggling to get it to return actual results based on what is searched. Any ideas?
 

Robert-B

Member
Landlord Landlord group Quarter Year Estimated project costs (000s) Actual project cost (000s)
Housing Leeds Yorkshire LL 3 2013 221 235
Housing Leeds Yorkshire LL 2 2014 206
Manchester housing Northwest housing associates 3 2012 134 130
Liverpool properties Northwest housing associates 2 2012 539 592
Liverpool properties Northwest housing associates 3 2014 567
Manchester housing Northwest housing associates 2 2013 157 157
Liverpool properties Northwest housing associates 2 2014 527
Housing Leeds Yorkshire LL 3 2012 238 240
Liverpool properties Northwest housing associates 4 2012 585 460
Manchester housing Northwest housing associates 1 2012 125 136
Manchester housing Northwest housing associates 3 2014 150
Liverpool properties Northwest housing associates 3 2012 569 585
Housing Leeds Yorkshire LL 1 2013 195 214
Manchester housing Northwest housing associates 2 2012 132 140
Manchester housing Northwest housing associates 2 2014 152
Liverpool properties Northwest housing associates 2 2013 555 577
Housing Leeds Yorkshire LL 3 2014 215
Manchester housing Northwest housing associates 4 2014 114
Manchester housing Northwest housing associates 1 2014 140
Manchester housing Northwest housing associates 3 2013 160 157
Liverpool properties Northwest housing associates 3 2013 528 537
Liverpool properties Northwest housing associates 1 2014 596
Housing Leeds Yorkshire LL 2 2012 226 231
Manchester housing Northwest housing associates 4 2013 111
Manchester housing Northwest housing associates 1 2013 135 136
Housing Leeds Yorkshire LL 1 2014 231
Liverpool properties Northwest housing associates 4 2013 536
Manchester housing Northwest housing associates 4 2012 105 96
Liverpool properties Northwest housing associates 1 2013 527 560
Housing Leeds Yorkshire LL 4 2013 226
Housing Leeds Yorkshire LL 2 2013 198 214
Housing Leeds Yorkshire LL 4 2014 235
Liverpool properties Northwest housing associates 1 2012 494 536
Housing Leeds Yorkshire LL 4 2012 181 197
Liverpool properties Northwest housing associates 4 2014 568
Housing Leeds Yorkshire LL 1 2012 201 209
 

chrishirst

Well-Known Member
Staff member
That is not comma separated data, so fgetcsv($handle, 1000, ",")) has nothing to 'separate' on.
 

chrishirst

Well-Known Member
Staff member
Thats just the way its in Sublime Text, its a .CSV file.
No it isnt, it might have a .csv extension, but the data in it, is NOT comma separated, and THAT is what matters.

To qualify as comma separated, the data items MUST BE separated by a comma NOT a space or a tab character as your data is. As in:
Code:
Housing,Leeds,Yorkshire,LL 3,2013,221,235

Without that distinction, it is merely a plain text file that happens to have a .csv extension. Certainly Open Office, Libre Office or Excel are able to recognise several different separators within the file text content during an import process, but PHP cannot. To be a real CSV file, the data must be separated by commas, always and ONLY by commas.
 

chrishirst

Well-Known Member
Staff member
Just to add.

Only M$ Windows 'decides' what the file content might be according to the extension, no other operating system is so easily 'fooled'.

If you renamed a text file to have a .mp4 extension M$ Windows would try to open it with the default media player.
 

Robert-B

Member
reopened a new file in sublime:

Housing Leeds,Yorkshire LL,3,2013,221,235
Housing Leeds,Yorkshire LL,2,2014,206,
Manchester housing,Northwest housing associates,3,2012,134,130
Liverpool properties,Northwest housing associates,2,2012,539,592
Liverpool properties,Northwest housing associates,3,2014,567,
Manchester housing,Northwest housing associates,2,2013,157,157
Liverpool properties,Northwest housing associates,2,2014,527,
Housing Leeds,Yorkshire LL,3,2012,238,240
Liverpool properties,Northwest housing associates,4,2012,585,460
Manchester housing,Northwest housing associates,1,2012,125,136
Manchester housing,Northwest housing associates,3,2014,150,
Liverpool properties,Northwest housing associates,3,2012,569,585
Housing Leeds,Yorkshire LL,1,2013,195,214
Manchester housing,Northwest housing associates,2,2012,132,140
Manchester housing,Northwest housing associates,2,2014,152,
Liverpool properties,Northwest housing associates,2,2013,555,577
Housing Leeds,Yorkshire LL,3,2014,215,
Manchester housing,Northwest housing associates,4,2014,114,
Manchester housing,Northwest housing associates,1,2014,140,
Manchester housing,Northwest housing associates,3,2013,160,157
Liverpool properties,Northwest housing associates,3,2013,528,537
Liverpool properties,Northwest housing associates,1,2014,596,
Housing Leeds,Yorkshire LL,2,2012,226,231
Manchester housing,Northwest housing associates,4,2013,111,
Manchester housing,Northwest housing associates,1,2013,135,136
Housing Leeds,Yorkshire LL,1,2014,231,
Liverpool properties,Northwest housing associates,4,2013,536,
Manchester housing,Northwest housing associates,4,2012,105,96
Liverpool properties,Northwest housing associates,1,2013,527,560
Housing Leeds,Yorkshire LL,4,2013,226,
Housing Leeds,Yorkshire LL,2,2013,198,214
Housing Leeds,Yorkshire LL,4,2014,235,
Liverpool properties,Northwest housing associates,1,2012,494,536
Housing Leeds,Yorkshire LL,4,2012,181,197
Liverpool properties,Northwest housing associates,4,2014,568,
Housing Leeds,Yorkshire LL,1,2012,201,209
 

Robert-B

Member
When I do a search for Housing Leeds I want to only bring up data relating to that search, at the moment it brings up the whole table.
 

Robert-B

Member
Changed my code a little now brings back everything other than what is searched:

<?php

$search = isset($_GET['search']) ? (int) trim($_GET['search']) : null;
$search = isset($_GET['search']) ? trim($_GET['search']) : null;
$search = isset($_GET['search']) ? (int) trim($_GET['search']) : null;
$search = isset($_GET['search']) ? trim($_GET['search']) : null;
$search = isset($_GET['search']) ? trim($_GET['search']) : null;


define('CSV_INDEX_LANDLORD', 0);
define('CSV_INDEX_LANDLORD_GROUP', 1);
define('CSV_INDEX_QUARTER', 2);
define('CSV_INDEX_YEAR', 3);
define('CSV_INDEX_ESTIMATED_PROJECT_COST', 4);
define('CSV_INDEX_ACTUAL_PROJECT_COST', 5);



$row = 1;
$output = [];

if (($handle = fopen("data.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$num = count($data);
$row++;

// if the user tried to search on year and it doesn't match, continue skips to the next row
// casting ensures we compare integers with integers
if (!empty($search) && stripos($data[CSV_INDEX_LANDLORD], $search) !== false) {
continue;
}

if (!empty($search) && stripos($data[CSV_INDEX_LANDLORD_GROUP], $search) !== false) {
continue;
}

if (!empty($search) && stripos($data[CSV_INDEX_QUARTER], $search) !== false) {
continue;
}

if (!empty($search) && stripos($data[CSV_INDEX_ESTIMATED_PROJECT_COST], $search) !== false) {
continue;
}

if (!empty($search) && stripos($data[CSV_INDEX_ACTUAL_PROJECT_COST], $search) !== false) {
continue;
}

$output[] = $data;
}
fclose($handle);
}
?>

<?php if (!empty($output)): ?>
<table>
<tr>
<th><strong>Landlord</strong></th>
<th><strong>Landlord Group</strong></th>
<th><strong>Quarter</strong></th>
<th><strong>Year</strong></th>
<th><strong>Estimated Project Costs</strong></th>
<th><strong>Actual Project Cost</strong>
<tr>
<?php foreach ($output as $row): ?>
<tr>
<td><?=$row[0]?></td>
<td><?=$row[1]?></td>
<td><?=$row[2]?></td>
<td><?=$row[3]?></td>
<td><?=$row[4]?></td>
<td><?=$row[5]?></td>
</tr>
<?php endforeach; ?>
</table>
<?php endif; ?>


<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Document</title>
</head>
<body>
<form action="search.php" method="get">
<label>
Search
<input type="text" name="search" autocomplete="off">
</label>

<input type="submit" name="Search">
</form>
</body>
</html>
 

chrishirst

Well-Known Member
Staff member
Have you tested what the value of $search is, for ALL the possible parameters or values that can be provided, after it has been through this?
[code php]
$search = isset($_GET['search']) ? (int) trim($_GET['search']) : null;
$search = isset($_GET['search']) ? trim($_GET['search']) : null;
$search = isset($_GET['search']) ? (int) trim($_GET['search']) : null;
$search = isset($_GET['search']) ? trim($_GET['search']) : null;
$search = isset($_GET['search']) ? trim($_GET['search']) : null;
[/code]

Basically the code is having to 'guess' what field is being input and you have no real 'error checking' in place, which is always a risky position with user input.
 

Robert-B

Member
I did have it for each field but that did not work either. I then changed it to one if statement and that did not work. I am not sure why?
 
Top