Page 1 of 1

retrieving multiple dates from mysql database

Posted: Mon Sep 12, 2011 10:05 am
by jvance6477
I hope someone can point me in the right direction. I have a form that has an input field called "rundate" which is populated with a jquery multiSelect Calendar that allows a user to select up to 6 dates to query. My database contains a "rundate" column that contains dates entered in this type of format 01/01/2011. I am trying to utilize the multiselect calendar to query the database for any dates in the database that match the selected dates from the form which are seperated by a "," like so 01/01/2011, 01/02/2011, 01/03/2011 etc. My form at the moment only will return results if the rundate only contains one selected date, but when i select more than one date, i don't get any results returned. Does anyone know what I can do to retrieve all the results in the database that match the dates selected from the form? I'm thinking that the comma seperating the dates may be the problem but, I'm hoping for some guidance. Thanks in advance.

Code: Select all


<?php require_once('../Connections/connAdmin.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;
}
}

$currentPage = $_SERVER["PHP_SELF"];
$colname_getRundate = "-1";
if (isset($_POST['rundate'])) {
  $colname_getRundate = $_POST['rundate'];
}
mysql_select_db($database_connAdmin, $connAdmin);
$query_getRundate = sprintf("SELECT * FROM daily_budget WHERE rundate LIKE %s", GetSQLValueString("%" . $colname_getRundate . "%", "text"));
$getRundate = mysql_query($query_getRundate, $connAdmin) or die(mysql_error());
$row_getRundate = mysql_fetch_assoc($getRundate);
$totalRows_getRundate = mysql_num_rows($getRundate);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script type="text/javascript" src="/new_budget/advanced_budgeting/jquery.min.js"></script>
<style type="text/css">
@import "/new_budget/advanced_budgeting/flora.datepick.css";.mainNav {
	font-size: 20px;
	font-weight: bold;
	background-color: #FFF;
}
</style> 
<script type="text/javascript" src="/new_budget/advanced_budgeting/jquery.datepick.min.js"></script>
</head>

<body><form action="" method="get" name="rundate">
 <label for="rundate">Rundate:</label>
<input type="text" name="rundate" size="30" value="" id="multi999Picker"/>

  </p>
  
<script type="text/javascript">
// BeginWebWidget jQuery_UI_Calendar: range2Picker
$('#multi999Picker').datepick({ 
    multiSelect: 6, multiSeperator: ',',  monthsToShow: 2, 
    showTrigger: '#calImg'});
// EndWebWidget jQuery_UI_Calendar: range2Picker
</script>
<input type="submit" align="middle" name="search" id="search" value="Search" />
</form>
<table id="results" width="100%" align="left" bgcolor="#FFFFFF">
  <?php do { ?>
    <tr>
      <td><p><?php echo $row_getRundate['rundate']; ?>      
        <p><?php echo $row_getRundate['slug']; ?>        
        <p><?php echo $row_getRundate['reporter']; ?>        
        <p><?php echo $row_getRundate['budgetInfo']; ?><br/>
        <hr />
    <?php } while ($row_getRundate = mysql_fetch_assoc($getRundate)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($getRundate);
?>


Re: retrieving multiple dates from mysql database

Posted: Mon Sep 12, 2011 10:27 am
by AbraCadaver
That's alot to look through, but if I understand you correctly this method should work:

Code: Select all

$dates  = "'" . implode("','", array_map('mysql_real_escape_string', explode(',', $_POST['rundate']))) . "'";
$query_getRundate = sprintf("SELECT * FROM daily_budget WHERE rundate IN (%s)", $dates);
In short, you need to explode the comma separated list into an array and then join it back into a comma separated list with quotes around each date, then use it in the WHERE IN () clause. I added mysql_real_escape_string() for safety.

Re: retrieving multiple dates from mysql database

Posted: Mon Sep 12, 2011 11:27 am
by jvance6477
Actually, the comma separated list does not need to be joined back into a comma seperated list with quotes around each date. It needs to query the mysql database that contains a column called rundate that contains individual dates in each row that may or may not match the list selected in the form. If it does, I would like the results of that row to be displayed on the same page as the form.

Re: retrieving multiple dates from mysql database

Posted: Mon Sep 12, 2011 12:11 pm
by AbraCadaver
Actually yes, that's exactly what I gave you. You need a quoted list to use in the WHERE IN () clause. This strategy gives you what you want based on what you have said.

Re: retrieving multiple dates from mysql database

Posted: Mon Sep 12, 2011 1:37 pm
by jvance6477
Well, there must be something that may need to be setup in the database end because this generates no results. The field column is a varchar type, could that make a difference?

Re: retrieving multiple dates from mysql database

Posted: Mon Sep 12, 2011 1:40 pm
by AbraCadaver
What does this show:

Code: Select all

echo "SELECT * FROM daily_budget WHERE rundate IN ($dates)";

Re: retrieving multiple dates from mysql database

Posted: Thu Sep 15, 2011 9:59 am
by jvance6477
I have been trying to post a response back but was unable to up until now. I'm getting no results back when I echo the select statement.
When I use your posted code for implode and explode, I get no results. This is the form and php that works but only if one date is entered into the input field but perhaps if you can show me where to insert your code to make it work, that may be helpful. Thank you.

Code: Select all

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

ini_set('display_errors',1); 
 error_reporting(E_ALL);
 
 

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;
}
}

$currentPage = $_SERVER["PHP_SELF"];

$maxRows_getBudgets = 10;
$pageNum_getBudgets = 0;
if (isset($_GET['pageNum_getBudgets'])) {
  $pageNum_getBudgets = $_GET['pageNum_getBudgets'];
}
$startRow_getBudgets = $pageNum_getBudgets * $maxRows_getBudgets;

$colname_getBudgets = "-1";
if (isset($_GET['rundate'])) {
  $colname_getBudgets = $_GET['rundate'];
}

mysql_select_db($database_connAdmin, $connAdmin);
$query_getBudgets = sprintf("SELECT id, rundate, slug, reporter, budgetInfo, notes, `section`, sidebar, sBudget, print_desc, multimedia, pickup FROM daily_budget WHERE rundate = %s", GetSQLValueString($colname_getBudgets, "text"));
$query_limit_getBudgets = sprintf("%s LIMIT %d, %d", $query_getBudgets, $startRow_getBudgets, $maxRows_getBudgets);
$getBudgets = mysql_query($query_limit_getBudgets, $connAdmin) or die(mysql_error());
$row_getBudgets = mysql_fetch_assoc($getBudgets);

if (isset($_GET['totalRows_getBudgets'])) {
  $totalRows_getBudgets = $_GET['totalRows_getBudgets'];
} else {
  $all_getBudgets = mysql_query($query_getBudgets);
  $totalRows_getBudgets = mysql_num_rows($all_getBudgets);
}
$totalPages_getBudgets = ceil($totalRows_getBudgets/$maxRows_getBudgets)-1;

$queryString_getBudgets = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_getBudgets") == false && 
        stristr($param, "totalRows_getBudgets") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_getBudgets = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_getBudgets = sprintf("&totalRows_getBudgets=%d%s", $totalRows_getBudgets, $queryString_getBudgets);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>


<script type="text/javascript" src="/new_budget/advanced_budgeting/jquery.min.js"></script>
<style type="text/css">
@import "/new_budget/advanced_budgeting/flora.datepick.css";.mainNav {
	font-size: 20px;
	font-weight: bold;
	background-color: #FFF;
}
</style> 
<script type="text/javascript" src="/new_budget/advanced_budgeting/jquery.datepick.min.js"></script>
</head>

<body><form id="form1" name="form1" method="get" action="">
 <label for="rundate">Rundate:</label>
<input type="text" name="rundate" size="30" value="" id="multi999Picker"/>

  </p>
  
<script type="text/javascript">
// BeginWebWidget jQuery_UI_Calendar: range2Picker
$('#multi999Picker').datepick({ 
    multiSelect: 6, multiSelector: ",", monthsToShow: 2, 
    showTrigger: '#calImg'});
// EndWebWidget jQuery_UI_Calendar: range2Picker
</script>

<input type="submit" align="middle" name="search" id="search" value="Search" />
</form>
<table id="results" width="100%" align="left" bgcolor="#FFFFFF">
  <?php do { ?>
    <tr>
      <td><p><?php echo $row_getBudgets['reporter']; ?> <br/>
        <?php echo $row_getBudgets['rundate']; ?><br/>
        <?php echo $row_getBudgets['section']; ?><br/>
        <?php echo $row_getBudgets['slug']; ?><?php echo ":"; ?> <?php echo $row_getBudgets['budgetInfo']; ?> <?php echo $row_getBudgets['multimedia']; ?><br/>
        <?php echo $row_getBudgets['notes']; ?><br/>
        <?php echo $row_getBudgets['print_desc']; ?><br/>
        <?php echo $row_getBudgets['pickup']; ?><br/>
        <br/>
        <br/>
      <hr />
    <?php } while ($row_getBudgets = mysql_fetch_assoc($getBudgets)); ?>
</table>

</body>
</html>
<?php
mysql_free_result($getBudgets);
?>