Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How would I only include tickets with certain status in PHP

I have two files. In table.php, I’ve implemented the display logic for data retrieved from our database. My objective within index.php is to include table.php while selectively filtering tickets based on their status, specifically, those with a status of "OPEN."

I’ve tried to modify sql query in the table.php and include in the index.php like thisinclude table.php=?status=Open; but this did not work infact in produced errors.

How would I approach this? Is it possible to modify include like I’ve tried?

Here is the table.php code:

<?php

include 'menu.php';
include 'connection.php';

// Retrieve user's location using functions from location.php
require_once 'location.php';
$locationName = $locations[$n];

// Set default sorting parameters
$order = isset($_GET['order']) ? $_GET['order'] : 'title';
$sort = isset($_GET['sort']) && ($_GET['sort'] === 'DESC' || $_GET['sort'] === 'ASC') ? $_GET['sort'] : 'ASC';

// Toggle sorting direction
$newSort = ($sort === 'ASC') ? 'DESC' : 'ASC';

// Retrieve status filter
$statusFilter = isset($_GET['status']) ? $_GET['status'] : null;

// Query to retrieve all tickets from the database
$resultSet = $conn->query("SELECT * FROM tickets ORDER BY $order $sort");

// Filter tickets to include only those with status 'Open'
$statusFilter = 'Open'; // Set the status filter to 'Open' for open tickets
$sql = "SELECT * FROM tickets WHERE status = '$statusFilter'";

// Check if user is logged in
if (isset($_SESSION['loggedin'])) {
    // User is logged in, retrieve department ID
    $departmentID = $_SESSION['department_id'];
    if ($departmentID == 0) {
        // Retrieve tickets for admin user
        $sql = "SELECT tickets.id_ticket, tickets.name, tickets.location, tickets.priority, departments.department_name, tickets.title, tickets.creation_date, tickets.status, tickets.update_date, COUNT(files.file_id) AS FileCount
                FROM tickets
                INNER JOIN departments ON tickets.department_id = departments.department_id
                LEFT JOIN files ON tickets.id_ticket = files.ticket_id
                WHERE tickets.hidden = 0
                GROUP BY tickets.id_ticket
                ORDER BY tickets.title ASC";

    } else {
        // Retrieve tickets for non-admin user
        $sql = "SELECT tickets.id_ticket, tickets.name, tickets.location, tickets.priority, departments.department_name, tickets.title, tickets.creation_date, tickets.status, tickets.update_date, COUNT(files.file_id) AS FileCount
                FROM tickets
                INNER JOIN departments ON tickets.department_id = departments.department_id
                LEFT JOIN files ON tickets.id_ticket = files.ticket_id
                WHERE tickets.department_id = $departmentID AND tickets.hidden = 0
                GROUP BY tickets.id_ticket
                ORDER BY $order $sort";
    }
} else {
    // User is not logged in, retrieve tickets by location only
    $sql = "SELECT tickets.id_ticket, tickets.name, tickets.location, tickets.priority, departments.department_name, tickets.title, tickets.creation_date, tickets.status, tickets.update_date, COUNT(files.file_id) AS FileCount
            FROM tickets
            INNER JOIN departments ON tickets.department_id = departments.department_id
            LEFT JOIN files ON tickets.id_ticket = files.ticket_id
            WHERE tickets.location = '$locationName' AND tickets.hidden = 0
            GROUP BY tickets.id_ticket
            ORDER BY $order $sort";
}

// Execute SQL query
$result = $conn->query($sql);
...
?>

Code for index.php:

 <body>
        <?php

        $statusFilter = 'Open';

        include 'ticket_table.php?status=' . $statusFilter;
        ?>
Tags:

2

Answers


  1. 0

    a quick solution is to retrieve the content of the page with file_get_contents() :

    $content = file_get_contents('ticket_table.php?status=' . $statusFilter);
echo $content ;

    Another solution (and maybe better in the long term) is to put the view logic in a separated file/class, and let both index.php and table.php call this unique resource.

    index.php:

    require_once('DataViewer.php');
$content = (new DataViewer($statusFiler))->render();
echo $content ;

    table.php:

    require_once('DataViewer.php');
/* stuff with $_GET variables */
$content = (new DataViewer($statusFilter))->render();
echo $content ;
    Login or Signup to reply.
  2. 0

    Passing a querystring to an include doesn’t work. You can do this:

    index.php

    <h1>INDEX 1</h1>
<?php
$_REQUEST['status'] = 'Open';

 include('table.php');
 ?>

    table.php

    <?php

echo $_REQUEST['status'];
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search