using session variable with url variable in SQL

Question

I have a PHP page that shows a table of data from a Database Table ( enquiries ). I am wanting to use a Session variable to show all the data for the person who is logged in ( The Member ). This session variable uses the users primary key ( members_id ). So at this point when the user opens the web page they see all there relevant data ( Enquiries )

The Table of data ( enquiries ) has a column to give the 'Status' of 'Enquiry' which is either 'Active' or 'Closed'. I am wanting to use this column to filter the data and when the user clicks 1 of 2 links at the top of the page which are a choice of 'Active' or 'Closed' it shows the relevant data to their choice. ( either 'Active enquiries only' or 'Closed enquiries only'.

I thought I could use a URL variable with the Session variable using the OR operator to achieve this but I can't get it to work.

So to Clarify, when the user first opens the page all of their data is displayed, but when they select either the 'Active Link' or the 'Closed Link' they only see the relevant data of their choice.

Im not accomplished in SQL and PHP so I use a Dreamweaver plugin to write a lot of it as well as a little of my own knowledge.

Below is a snippet of my code which I hope helps to show what I am trying to do!

<?php
$colname_rsEnquiriesList = "-1";
if (isset($_GET['status'])) {
$colname_rsEnquiriesList = $_GET['status'];
}
$allEnquiries_rsEnquiriesList = "-1";
if (isset($_SESSION['members_id'])) {
$allEnquiries_rsEnquiriesList = $_SESSION['members_id'];
}
mysql_select_db($database_LA, $LA);
$query_rsEnquiriesList = sprintf("SELECT * FROM enquiries WHERE status = %s OR     members_id = %s ORDER BY date_e DESC", GetSQLValueString($colname_rsEnquiriesList, "text"),GetSQLValueString($allEnquiries_rsEnquiriesList, "int"));
$rsEnquiriesList = mysql_query($query_rsEnquiriesList, $LA) or die(mysql_error());
$row_rsEnquiriesList = mysql_fetch_assoc($rsEnquiriesList);
$totalRows_rsEnquiriesList = mysql_num_rows($rsEnquiriesList);
?>

Hope someone can help! Thanks.

Answer 1

What you want to do when first time user comes ?

And next thing is that if u want to filter data by active inactive use 'AND' condition instead of 'OR'.

$query_rsEnquiriesList = sprintf("SELECT * FROM enquiries WHERE status = %s AND members_id = %s ORDER BY date_e DESC", GetSQLValueString($colname_rsEnquiriesList, "text"),GetSQLValueString($allEnquiries_rsEnquiriesList, "int"));

Answer 2

What you need is something like the following query:

SELECT *
FROM inquiries
WHERE member_id='".$memberId."'
AND status='".$status."'
ORDER by date_e DESC;

In which $memberId is the id you've retrieved from the session and $status holds the $_GET parameter the user reqired.

EDIT:

I've made a little example using your code

<?php

// Set status
if (isset($_GET['status']) && is_numeric($_GET['status'])) {
    switch ($_GET['status']) {
        case '1':
            $status = "active";
            break;
        case '2':
            $status = 'closed';
            break;
    }
} else {
    $status = "active";
}

// Set memerId
if (isset($_SESSION['members_id'])) {
    $memberId = isset($_SESSION['members_id']) ? $_SESSION['members_id'] : 0;
}

// Connect to database
mysql_select_db($database_LA, $LA);

// Prepare query
$query = sprintf("SELECT *
FROM enquiries
WHERE status=%s
AND members_id=%s
ORDER BY date_e DESC
", GetSQLValueString($status, "text"),
GetSQLValueString($memberId, "int"));

$resource = mysql_query($query, $LA) or die(mysql_error());
$results = array();
$totalRows = mysql_num_rows($resource);
if ($totalRows > 0) {
    while ($record = mysql_fetch_assoc($resource)) {
        $results[] = $record;
    }
}

// Now $results contains all records
// You can view them using a print_r or var_dump
?>