2

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.

Steve Joiner
  • 493
  • 2
  • 9
  • 21

2 Answers2

1

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
?>
Peter
  • 8,776
  • 6
  • 62
  • 95
  • I feel like I'm drifting into deep water here!! not quite sure what the i is doing in the syntax. I tried to replicate your post Peter with no joy. – Steve Joiner Dec 22 '14 at 11:58
  • @SteveJoiner It's an alias to make it shorter. Just something i'm used to doing but i have removed it for you to make it easier. – Peter Dec 22 '14 at 12:54
  • Right OK. Much appreciated Peter. Thanks for your help! – Steve Joiner Dec 22 '14 at 13:33
  • @SteveJoiner Has this resolved your issue, or are you still stuck? – Peter Dec 22 '14 at 15:27
  • Unfortunately no! I think its a combination of my lack of understanding, and that I am trying to combine your efforts with pre-built code. – Steve Joiner Dec 22 '14 at 15:32
  • Just didn't want to hassle you any further Peter – Steve Joiner Dec 22 '14 at 15:32
  • If you could tell me what is going wrong, I will edit my answer to help you solve your issue. Could you also add a code example on how you are trying to use the databse results? – Peter Dec 22 '14 at 15:34
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/67479/discussion-between-steve-joiner-and-peter). – Steve Joiner Dec 22 '14 at 15:35
-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"));
Jigisha Variya
  • 207
  • 1
  • 8