[RESOLVED] Adding a day

[gabe33]

I've got a database storing information entered by others and I am trying to be able to list entries that occurred between one date and including the next date. I'm sure it's been done before however I can't get it to work here. I know my default values don't work, so I could use help there as well. Here's what I'm working with:

$fromDate = "";
if (isset($_POST['fromDate'])) {
  $fromDate = $_POST['fromDate'];
}
$toDate = "";
if (isset($_POST['toDate'])) {
  $toDate = $_POST['toDate'];
}
mysql_select_db($database_custsupadmin, $custsupadmin);
$query_listCalls = sprintf("SELECT calls.created, 
								calls.agent_id, 
								agents.first_name, 
								agents.last_name, 
								calls.repair_order, 
								calls.`description`, 
								issues.issue_desc 
								FROM calls, agents, issues 
								WHERE calls.agent_id = agents.agent_id 
								AND issues.issue_id = calls.issue_id 
								AND calls.created BETWEEN '" . $fromDate . "' AND DATE_ADD('" . $toDate . "', INTERVAL 1 DAY)
								ORDER BY calls.created DESC", 
								GetSQLValueString($fromDate, "date"),
								GetSQLValueString($toDate, "date"));

I've updated this to what I am currently using and it works. I'm hoping that there is no problem with the code that could pose an issue in the future.

[bradmasterx]

Make these corrections,
line 5:
$toDate = DATE('YYYY-MM-DD');

[bradgrafelman]

DATE('YYYY-MM-DD') makes no sense at all in PHP code.

Which date are you trying to add one day to? If it's the $fromDate variable, you could try something like this in your SQL query:

AND calls.created BETWEEN '%s' AND DATE_ADD('%s', INTERVAL 1 DAY)

You would also get rid of the $toDate variable in your code and replace both instances of %s with the $toDate value.

Also, I don't think you know how to use the [man]sprintf/man function properly; in your code - you're supposed to use placeholders for values that you list as parameters (e.g. '%s' in my code above, since the value is a string).

[gabe33]

I know I am horrible at PHP. I was taking an Oracle class at the same time so my mind is a little scrambled. The $fromDate and $toDate were %d before I changed it. %d is for a decimal I know that was an issue. Dreamweaver does some weird stuff and I am trying to get rid of the junk and make sure it still works. I am assuming that since I replaced %d with '" . $fromDate . "' I can remove the sprintf() function?

[bradgrafelman]

I would recommend using the sprintf() function as I showed above; that way you can still use the GetSQLValueString() function easily (which, I assume, sanitizes the data with mysql_real_escape_string() when appropriate? If not - maybe this function isn't so useful after all! :p).

[gabe33]

I did that and input 2007-12-02 as $fromDate and 2007-12-14 as $toDate

I got this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2007-12-02'' AND DATE_ADD(''2007-12-14'', INTERVAL 1 DAY) ORDER BY cal' at line 11

My code changed to this:

$query_listCalls = sprintf("SELECT calls.created, 
								calls.agent_id, 
								agents.first_name, 
								agents.last_name, 
								calls.repair_order, 
								calls.`description`, 
								issues.issue_desc 
								FROM calls, agents, issues 
								WHERE calls.agent_id = agents.agent_id 
								AND issues.issue_id = calls.issue_id 
								AND calls.created BETWEEN '%s' AND DATE_ADD('%s', INTERVAL 1 DAY) 
								ORDER BY calls.created DESC", 
								GetSQLValueString($fromDate, "date"),
								GetSQLValueString($toDate, "date"));

[bradgrafelman]

Looks like the GetSQLValueString() function is adding quotes by itself, so either remove the single quotes in the query or don't use that function and just use [man]mysql_real_escape_string/man to sanitize the data.

[gabe33]

I just changed a few things. This works now, I appreciate the help. That GetSQLValueString was a Dreamweaver CS3 addition. I dislike Dreamweaver more and more every day. You should see the rest of the code that is generated. I don't even know where to start with it.

$fromDate = "";
if (isset($_POST['fromDate'])) {
  $fromDate = mysql_real_escape_string($_POST['fromDate']);
}
$toDate = "";
if (isset($_POST['toDate'])) {
  $toDate = mysql_real_escape_string($_POST['toDate']);
}
mysql_select_db($database_custsupadmin, $custsupadmin);
$query_listCalls = "SELECT calls.created, 
								calls.agent_id, 
								agents.first_name, 
								agents.last_name, 
								calls.repair_order, 
								calls.`description`, 
								issues.issue_desc 
								FROM calls, agents, issues 
								WHERE calls.agent_id = agents.agent_id 
								AND issues.issue_id = calls.issue_id 
								AND calls.created BETWEEN '" . $fromDate . "' AND DATE_ADD('" . $toDate . "', INTERVAL 1 DAY) 
								ORDER BY calls.created DESC";