0

I'm trying to make a filter option using a select dropdown box and I don't know where I am failing. I have a search bar that works perfectly but I want to able to select the location where I would like to search . For example I can type the name of the job I'm looking for but I would like to filter the locations to only view in one city Here is my code

Edit : Thanks to ADyson I made some edits to the code I will post the new code over the last one 

        <?php
    require 'views/header.php';
    $connection = getDbConntection();

    // Search // 
    if (!empty($_GET['search'])) {

        $data = [
            'job_name' => '%' . $_GET['search'] . '%',
            'location_id' => $_GET['location_id']
        ];
        $searches = $connection->prepare("select jobs.id, jobs.name as job_name, salary as job_salary, description, location_id, domain_id , locations.name as location_name , domains.name as domain_name from jobs 
    LEFT JOIN locations ON jobs.location_id = locations.id
    LEFT JOIN domains on jobs.domain_id = domains.id "
                . "where jobs.name like :job_name"
                . 'AND location_id = :location_id');
        $searches->execute($data);
        $searches= $query->fetchAll();
    // List // 
    } else {
        $query = $connection->query("select jobs.id, jobs.name as job_name, salary as job_salary, description, location_id, domain_id , locations.name as location_name , domains.name as domain_name from jobs 
    LEFT JOIN locations ON jobs.location_id = locations.id
    LEFT JOIN domains on jobs.domain_id = domains.id ");
        $searches = $query->fetchAll();
    }
    ?>

    <div class="w3-row-padding w3-padding-64 w3-container">
        <div class="w3-content">

            <h1 class="center"> Jobs table </h1>
            <br>
            <form style="text-align:center" action="index.php" method="GET">
                <input type="text" name="search" value="Search jobs..." onfocus="this.value = ''" class="btn btn-danger">
                <select name="location_id"  class="btn btn-danger">
                    <?php foreach ($searches as $location): ?>
                        <?php $selectedText = ($location['id']) ?>
                        <option value= <?= $selectedText ?> > <?= $location['location_name'] ?></option>
                    <?php endforeach; ?>
                </select>
                <input type="submit" value="Search" class="btn btn-danger">
                <a href="index.php" class="btn btn-danger">Back to list </a>
            </form>
            <br>

            <div  class="center">
                <table>
                    <tr>
                        <th>ID</th>
                        <th> Job Name</th>
                        <th> Job Location</th>
                        <th> Job Domain</th>
                        <th> Job Description</th>
                        <th> Job Salary</th>

                        <th>Actions</th>
                    </tr>
                    <?php foreach ($searches as $key => $job_name) : ?>
                        <tr>
                            <th><?= $job_name['id'] ?></th>
                            <th style="background-color: lightskyblue"><?= $job_name['job_name'] ?></th>
                            <td><?= $job_name['location_name'] ?></td>
                            <td><?= $job_name['domain_name'] ?></td>
                            <td><?= $job_name['description'] ?></td>
                            <td><?= $job_name['job_salary'] ?></td>
                            <td> <a  class="btn btn-success" href="edit.php?id=<?= $job_name['id'] ?>">&nbsp; &nbsp; Edit &nbsp;</a> 
                                <a class="btn btn-danger" href="delete.php?id=<?= $job_name['id'] ?>">Delete</a> </td>
                        </tr>
                    <?php endforeach; ?>
                </table>  
            </div>

            <style>
                table td, table th {
                    padding: 15px;
                    text-align: center;

                }
                table th {
                    background:#3390FF;
                }
                table {
                    width: 100%;
                    border: 3px solid #ccc;
                    border-collapse: collapse;
                }

                .ce

nter {
                margin: auto;
                width: 100%;
                border: 3px solid red;
                padding: 10px;
                text-align: center;
            }
        </style>
    </div>
</div>

Sorry for the messy code, I'm new to coding in general

Improve this question
14
  • 1
    It looks like 'location_id' => $_GET['search'] should be changed to 'location_id' => $_GET['location_id']. Right now, you're using the "search" text as the location ID! Also you have a syntax error later - AND location_id = ;location_id needs the ; changing to :. Commented Apr 9, 2020 at 10:46
  • P.S. these should have been fairly easily to spot if you did some simple debugging and ensure you've got error logging set up properly. Have you spent any time learning how to debug? It's almost as important a skill as the programming itself. phpknowhow.com/basics/basic-debugging has a simple guide to debugging with PHP. There may still be other problem which are not immediately obvious from a quick read-through of the code. Commented Apr 9, 2020 at 10:48
  • Oh and $searchText = $_GET['search']; is redundant because you never use the $searchText variable after that line. It has no purpose. You can simply remove this line of code. Commented Apr 9, 2020 at 10:51
  • Thnaks for the help ,i noticed the syntax error and the logical error after posting the code here and changed it. After i run the code i get this Commented Apr 9, 2020 at 11:12
  • Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:\xampp\htdocs\TemaCarantina\index.php:17 Stack trace: #0 C:\xampp\htdocs\TemaCarantina\index.php(17): PDOStatement->execute(Array) #1 {main} thrown in C:\xampp\htdocs\TemaCarantina\index.php on line 17error Commented Apr 9, 2020 at 11:14

1 Answer 1

Reset to default
1

Thanks to ADysom I solved the problem , here is the correct code 

<?php
require 'views/header.php';
$connection = getDbConntection();

$locations = $connection->query("select * from locations");

// Search // 
if (!empty($_GET['search'])) {

    $data = [
        'job_name' => '%' . $_GET['search'] . '%',
        'location_id' => $_GET['location_id']
    ];
    $query = $connection->prepare("select jobs.id, jobs.name as job_name, salary as job_salary, description, location_id, domain_id , locations.name as location_name , domains.name as domain_name from jobs 
LEFT JOIN locations ON jobs.location_id = locations.id
LEFT JOIN domains on jobs.domain_id = domains.id "
            . "where jobs.name like :job_name "
            . "AND location_id = :location_id ");
    $query->execute($data);
    $query = $query->fetchAll();
//    print_r($_GET);
// List // 
} else {
    $query = $connection->query("select jobs.id, jobs.name as job_name, salary as job_salary, description, location_id, domain_id , locations.name as location_name , domains.name as domain_name from jobs 
LEFT JOIN locations ON jobs.location_id = locations.id
LEFT JOIN domains on jobs.domain_id = domains.id ");
    $query = $query->fetchAll();
}
?>

<div class="w3-row-padding w3-padding-64 w3-container">
    <div class="w3-content">

        <h1 class="center"> Jobs table </h1>
        <br>
        <form style="text-align:center" action="index.php" method="GET">
            <input type="text" placeholder='Search jobs..' name="search"  onfocus="this.value = ''" class="btn btn-danger">
            <select name="location_id"   class="btn btn-danger">
                <?php foreach ($locations as $location): ?>
                    <option value="<?= $location['id'] ?>">
                        <?= $location['name'] ?>
                    </option>
                <?php endforeach; ?>
            </select>
            <input type="submit" value="Search" class="btn btn-danger">
            <a href="index.php" class="btn btn-danger">Back to list </a>
        </form>
        <br>

        <div  class="center">
            <table>
                <tr>
                    <th>ID</th>
                    <th> Job Name</th>
                    <th> Job Location</th>
                    <th> Job Domain</th>
                    <th> Job Description</th>
                    <th> Job Salary</th>

                    <th>Actions</th>
                </tr>
                <?php foreach ($query as $key => $job_name) : ?>
                    <tr>
                        <th><?= $job_name['id'] ?></th>
                        <th style="background-color: lightskyblue"><?= $job_name['job_name'] ?></th>
                        <td><?= $job_name['location_name'] ?></td>
                        <td><?= $job_name['domain_name'] ?></td>
                        <td><?= $job_name['description'] ?></td>
                        <td><?= $job_name['job_salary'] ?></td>
                        <td> <a  class="btn btn-success" href="edit.php?id=<?= $job_name['id'] ?>">&nbsp; &nbsp; Edit &nbsp;</a> 
                            <a class="btn btn-danger" href="delete.php?id=<?= $job_name['id'] ?>">Delete</a> </td>
                    </tr>
                <?php endforeach; ?>
            </table>  
        </div>

        <style>
            table td, table th {
                padding: 15px;
                text-align: center;

            }
            table th {
                background:#3390FF;
            }
            table {
                width: 100%;
                border: 3px solid #ccc;
                border-collapse: collapse;
            }

            .center {
                margin: auto;
                width: 100%;
                border: 3px solid red;
                padding: 10px;
                text-align: center;
            }
            ::placeholder { 
                color: white;
                opacity: 1; 
            }
        </style>
    </div>
</div>
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

BTW Small thing, but the query could still be made into all one string: select jobs.id, jobs.name as job_name, salary as job_salary, description, location_id, domain_id , locations.name as location_name , domains.name as domain_name from jobs LEFT JOIN locations ON jobs.location_id = locations.id LEFT JOIN domains on jobs.domain_id = domains.id where jobs.name like :job_name AND location_id = :location_id". There' no need for the where clause to be separate strings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.