Display data from a CSV in a form using PHP

Discussion in 'Programming' started by Robert-B, Nov 18, 2016.

to remove all ads.
  1. Robert-B

    Robert-B Member

    Messages:
    70
    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?
     
  2. chrishirst

    chrishirst Well-Known Member Staff Member

    Messages:
    2,657
  3. Robert-B

    Robert-B Member

    Messages:
    70
    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?
     
  4. chrishirst

    chrishirst Well-Known Member Staff Member

    Messages:
    2,657
    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.
     
  5. Robert-B

    Robert-B Member

    Messages:
    70
    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?
     
  6. chrishirst

    chrishirst Well-Known Member Staff Member

    Messages:
    2,657
    None at all without seeing the data in the CSV.
     
  7. Robert-B

    Robert-B Member

    Messages:
    70
    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
     
  8. chrishirst

    chrishirst Well-Known Member Staff Member

    Messages:
    2,657
    That is not comma separated data, so fgetcsv($handle, 1000, ",")) has nothing to 'separate' on.
     
  9. Robert-B

    Robert-B Member

    Messages:
    70
    Hi Chris,

    Thats just the way its in Sublime Text, its a .CSV file.

    Thanks
     
  10. chrishirst

    chrishirst Well-Known Member Staff Member

    Messages:
    2,657
    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.
     
  11. chrishirst

    chrishirst Well-Known Member Staff Member

    Messages:
    2,657
    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.
     
  12. Robert-B

    Robert-B Member

    Messages:
    70
    Thanks Chris, I will open it up in sublime and add in the commas. Does the rest of the code look okay?
     
  13. chrishirst

    chrishirst Well-Known Member Staff Member

    Messages:
    2,657
    Nothing appears to be 'jumping out' as a possible error so tentatively I would say yes.
     
  14. Robert-B

    Robert-B Member

    Messages:
    70
    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
     
  15. Robert-B

    Robert-B Member

    Messages:
    70
    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.
     
  16. Robert-B

    Robert-B Member

    Messages:
    70
    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>
     
  17. chrishirst

    chrishirst Well-Known Member Staff Member

    Messages:
    2,657
    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.
     
  18. Robert-B

    Robert-B Member

    Messages:
    70
    I will try that in my code.
     
  19. Robert-B

    Robert-B Member

    Messages:
    70
    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?
     

Share This Page