retrieving multiple dates from mysql database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jvance6477
Forum Newbie
Posts: 4
Joined: Mon Sep 12, 2011 9:53 am

retrieving multiple dates from mysql database

Post 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);
?>

User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: retrieving multiple dates from mysql database

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
jvance6477
Forum Newbie
Posts: 4
Joined: Mon Sep 12, 2011 9:53 am

Re: retrieving multiple dates from mysql database

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: retrieving multiple dates from mysql database

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
jvance6477
Forum Newbie
Posts: 4
Joined: Mon Sep 12, 2011 9:53 am

Re: retrieving multiple dates from mysql database

Post 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?
Last edited by jvance6477 on Mon Sep 12, 2011 1:40 pm, edited 1 time in total.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: retrieving multiple dates from mysql database

Post by AbraCadaver »

What does this show:

Code: Select all

echo "SELECT * FROM daily_budget WHERE rundate IN ($dates)";
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
jvance6477
Forum Newbie
Posts: 4
Joined: Mon Sep 12, 2011 9:53 am

Re: retrieving multiple dates from mysql database

Post 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);
?>
Post Reply