Forum Moderators: coopster
$result = mysql_query( "SELECT * FROM ttime where (SNUM = '".$_SESSION['SNUM']."') and (TIDATE LIKE '%".$TIDATEA."%') OR (TIDATE LIKE '%".$TIDATEB."%')")
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
print "You currently have $num_rows time entries for "
My problem is the query returns records for only the two dates posted! I have tried using > and < BETWEEN = <= >= all to no avail. The only thing that seems to remotely work is LIKE as I have used it in my sample above. I am wondering if the data type in my table has anything to do with my lack of success. Data type for the field TIDATE is varchar lenght 11. If anyone can help me I will be forever grateful. I am using a MySQL database and the field I am selecting on is TIDATE. The user is entering TIDATEA and TIDATEB for the two date variables.
Thanks!
For example, I store the date in the format YYYYMMDD. This means that you can use the MySQL <,>,BETWEEN operators with no problems.
Today's date (16th November 2004) would be stored 20041116 - Christmas would be 20041225.
Thanks for the reply! I am storing the data in mm/dd/yyyy format. Do you think this could be my problem?
Thanks again for your reply! I am feeling pretty lousy about not being able to solve this right now.
DATETIME, DATE - These rely on MySQL parsing a string you pass as a date. If it can't do this it stores the date as 0000-00-00 *without telling you*.
TIMESTAMP - only valid from 1970 to 2037, you can't work out what the date actually is from looking at it. 1100638959, anyone? (It's actually 2004-11-16 21:02:39 :)
mm/dd/yyyy format
When you ask MySQL to compare two dates in that format, it will evaluate them as strings.
For example, 11/17/1970 will be after 11/16/2004, because the string "11/17/1970" is greater than the string "11/16/2004".
Change the date format to YYYYMMDD and your problem will be solved!
The real big plus, however, is that by using one of the MySQL date/time formats, you can use a number of MySQL functions, like DATE_FORMAT(), to easily format the data any way you wish when you query it.
I am going to try it! Thanks for taking the time! Have an Awesome Day!
You will be quite happy down the road if you create your table correctly. In my humble opinion this means storing dates as column type
DATE. The only reason MySQL will store a date column value as '0000-00-00' is when you pass it illegal (nonsensical) date parts.
Illegal DATETIME, DATE, or TIMESTAMP values are converted to the 'zero' value of the appropriate type ('0000-00-00 00:00:00', '0000-00-00', or 00000000000000).
This means you should be verifying the date before you pass it to your database. You should be doing this anyway, especially if you are receiving the date from user input (which is really what we are talking about because if we are building the date ourselves on the server-side, we can guarantee it will always be correct and correctly formatted). Validate any user input and give the user an error message if the date is incorrect.
The nice part is that PHP offers us the checkdate() [php.net] function, specifically made for this task.
by using one of the MySQL date/time formats, you can use a number of MySQL functions, like DATE_FORMAT()
Salsa - that's a very good point. There are lot of useful MySQL date and time functions that require a date/time format column.
I use both MSSQL and MySQL a lot, so I tend to avoid DB-specific SQL commands instinctively - doesn't mean my instincts are always correct, of course :)
Also, Shaman, consider putting your query into a variable, and then use the variable as the argument in mysql_query(), like:
$sql = "SELECT * FROM ttime WHERE SNUM = '".$_SESSION['SNUM']."' AND (TIDATE LIKE '%$TIDATEA%' OR TIDATE LIKE '%$TIDATEB%')";$result = mysql_query($sql);One advantage to this is that, during testing, you can echo out $sql to see what the query will really look like without all the concatenation stuff, etc:
echo "\$sql = $sql<br>\n";Once you get in the habit of this, it will even be necessary when building more complex queries using conditions and loops.
Also note that, above, I messed with your query a bit. You really don't need to concatenate your scalar variables, but array elements can cause problems if you don't. Most especially, though, I changed your parentesis. As you had them, they were doing nothing. As I changed them, you'll return results WHERE SNUM... AND (TIDATE is like either $TIDATEA OR $TIDATEB)--assuming that's what you want.
I hope this helps.
...I tend to avoid DB-specific SQL commands instinctively...
This isn't such a bad practice, pete_m. If you ever want to port your code to another DB and you did indeed use DB-specific functions, you'll have to rework the statements. However, you'll have to balance the two to find a happy medium.
One primary example I can think of is the MySQL LIMIT clause. It is quite handy. Oracle, MS, Postgresql all have their own working versions of the same, but different. Too bad there weren't more standards ;)
