Filtering a recordset with both a URL parameter and a session variable

Report · Mar 19, 2011

I have a membership site and am trying to display data for just a specific user. I have created a search box that will get the data from a recordset and the filter is a URL parameter 'date'. This works fine, but what I need to do is limit the data displayed to the user that is logged in. Right now it displays all data in the table for the searched date. I use a session variable('_amember_id') to display a users specific data in other places in the site but this is the only page I have the search by date function. The search works perfectly but is bringing back all the data in the table for that date(all users). The current code is as follows. I am still a newbie and would love some suggestions. Thank you.

<?php require_once('Connections/connExercise.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

$maxRows_rsGetResist = 10;

$pageNum_rsGetResist = 0;

if (isset($_GET['pageNum_rsGetResist'])) {

$pageNum_rsGetResist = $_GET['pageNum_rsGetResist'];

}

$startRow_rsGetResist = $pageNum_rsGetResist * $maxRows_rsGetResist;

$colname_rsGetResist = "-1";

if (isset($_GET['date'])) {

$colname_rsGetResist = $_GET['date'];

}

mysql_select_db($database_connExercise, $connExercise);

$query_rsGetResist = sprintf("SELECT * FROM journal_resist_entry WHERE `date` = %s ORDER BY `date` DESC", GetSQLValueString($colname_rsGetResist, "date"));

$query_limit_rsGetResist = sprintf("%s LIMIT %d, %d", $query_rsGetResist, $startRow_rsGetResist, $maxRows_rsGetResist);

$rsGetResist = mysql_query($query_limit_rsGetResist, $connExercise) or die(mysql_error());

$row_rsGetResist = mysql_fetch_assoc($rsGetResist);

if (isset($_GET['totalRows_rsGetResist'])) {

$totalRows_rsGetResist = $_GET['totalRows_rsGetResist'];

} else {

$all_rsGetResist = mysql_query($query_rsGetResist);

$totalRows_rsGetResist = mysql_num_rows($all_rsGetResist);

}

$totalPages_rsGetResist = ceil($totalRows_rsGetResist/$maxRows_rsGetResist)-1;

session_start(); ?>

Report · Mar 20, 2011

Assuming _amemberid_ session is an integer and the column in the database that corresponds to the value of the session variable is named member_id you must append an AND argument to your query to filter by date AND session variable value.

On first line of page add code to start the session:

<?php session_start(); ?>

then edit your query to add the AND argument:

$query_rsGetResist = sprintf("SELECT * 
FROM journal_resist_entry 
WHERE `date` = %s 
AND 'member_id' = %s", 
GetSQLValueString($colname_rsGetResist, "date"), 
GetSQLValueString($SESSION['_amemberid_'], "int"));

Report · Mar 20, 2011

shocker,

Thank you for the quick response. I made the changes you suggested and now the search brings back no data instead of all the data. Maybe I'm missing something. The updated code is below. Any ideas?

<?php session_start(); ?>
<?php require_once('Connections/connExercise.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }
  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
$maxRows_rsGetResist = 10;
$pageNum_rsGetResist = 0;
if (isset($_GET['pageNum_rsGetResist'])) {
  $pageNum_rsGetResist = $_GET['pageNum_rsGetResist'];
}
$startRow_rsGetResist = $pageNum_rsGetResist * $maxRows_rsGetResist;
$colname_rsGetResist = "-1";
if (isset($_GET['date'])) {
  $colname_rsGetResist = $_GET['date'];
}
mysql_select_db($database_connExercise, $connExercise);
$query_rsGetResist = sprintf("SELECT * 
FROM journal_resist_entry 
WHERE `date` = %s 
AND '_amember_id' = %s 
ORDER BY `date` DESC", 
GetSQLValueString($colname_rsGetResist, "date"), GetSQLValueString($SESSION['_amember_id'], "int"));
$query_limit_rsGetResist = sprintf("%s LIMIT %d, %d", $query_rsGetResist, $startRow_rsGetResist, $maxRows_rsGetResist);
$rsGetResist = mysql_query($query_limit_rsGetResist, $connExercise) or die(mysql_error());
$row_rsGetResist = mysql_fetch_assoc($rsGetResist);
if (isset($_GET['totalRows_rsGetResist'])) {
  $totalRows_rsGetResist = $_GET['totalRows_rsGetResist'];
} else {
  $all_rsGetResist = mysql_query($query_rsGetResist);
  $totalRows_rsGetResist = mysql_num_rows($all_rsGetResist);
}
$totalPages_rsGetResist = ceil($totalRows_rsGetResist/$maxRows_rsGetResist)-1;
 session_start(); ?>