Using wildcards

[Zergman]

Sorry if this has been asked before, search didn't find anything for me.

 

Anyways, I have a mysql database that has a date field thats formatted like 08/07/15.

 

I have that set in my insert form using this

<?php echo(date('y/m/d'))?>" />

.  Works like a charm.

 

Now what im trying to do is build a sql query for another page that will search the date field for only part of the date.  What i've done so far is to set the variable

$date = date ('y/m/d');

to use in my query, but that will only pull up the records for the current date.  What I want is for it to pull up say the entire month.  I thought

$date = date ('y/m/%');

would work but it doesn't.

 

This is my query

SELECT * FROM `data` WHERE uname = %s AND`data`.tdate = '$date' ORDER BY id DESC

 

Not even sure I know how to ask this question, I'm EXTREMELY new to all of this.  Any help would be appreciated.

[mmarif4u]

you can use mktime function.

something like:

echo date("M-d-Y", mktime(0, 0, 0, 06, 15, 2008));

[samshel]

in what format have you stored date in MySQL ? if data type is "date" it will be stored in the format "YYYY-MM-DD"

[mmarif4u]

try something like this:

$date1=date("Y-m-d", mktime(0, 0, 0, 2008, 06, 15));
$date2=date("Y-m-d",mktime(0,0,0, 2008,06+1,15));

And then in your query:

where date between $date1 and $date2

 

hope it will help.

[tommyboy123x]

well mysql is searching literally for '"8/07/%"'.  instead what you want is '"8/07/"%', if that makes any sense?

 

If you want to keep the date how it is formatted but search for only the first part of the date, you need to store date as

$date = date ('y/m/');

 

then the query should look like

SELECT * FROM `data` WHERE ... AND `data` LIKE '$date%' ORDER BY id DESC

 

note you use of "LIKE" instead of "=", and the % wild card at the end of $date

 

However, for a possibly more efficient way to keep records, store your data in the table as unix timestamps (number of seconds from December 31st 1969 12:00), and search for a date range that would correspond to the particular day you want to find (which in this case would be between 1216267200 and 1216353600).  You can find some unix timestamp converters online, use time() to return a unix timestamp, date([FORMATTED DATE], [uNIX TIMESTAMP]) to reverse the process, etc.  They are a lot easier to work with in the long run.

[Zergman]

Wow, such quick replies!  awesome 

 

Just so you all know, Im a huge newb when it comes to coding so you'll probably see it in the way I do things

 

The data type my dates are stored in are just varchar and they're subitted as yy/mm/dd.

 

using mktime is WAY over my head LOL and comfort level at this point... will have to read up on it.

 

I tried what you suggested tommyboy123x but it gives me this error "Warning: sprintf() [function.sprintf]: Too few arguments in /var/www/.../stats.php on line 43

Query was empty"

[samshel]

Try

 

$date = addslashes("8/07/");

"SELECT * FROM `data` WHERE uname = %s AND`data`.tdate LIKE '$date%' ORDER BY id DESC"

[Zergman]

getting same error as before 

[Zergman]

well, still no luck.

 

From all your experiences, what is causing my problem?  the format im putting the dates in as or my query string?

 

Would it be just easier to redo the date formatting in my database?  Now would be the time before the amount of records gets out of control.

[craygo]

You can save yourself a whole lot of trouble by storing dates as actual dates instead of a varchar. by storing it as an actual date you can use mysql built in functions to retrieve the things you want much easier

 

$sql = "SELECT * FROM table WHERE MONTH(`tdate`) = '2' AND YEAR(`tdate`) = '2008'";

 

That will give you all the rows that have a tdate in February 2008

 

Ray

[discomatt]

Yes, I recommend storing them as a DateTime type column, or just Date if time isn't important. MySQL has a few functions for manipulating and searching through those types, and they are quite efficient as well.

 

Queries like

 

SELECT `columns` WHERE `date` BETWEEN `2008-01-01` AND `2008-02-01`

 

Are even easier than using unix timestamps.

[Zergman]

I really appeciate all the wicked help, thanks everyone!

 

so i've decided to reformat the date in my database and switched it to DATE.  Little did I realize is that mysql formatted them for me!  All dates now show as 2008-07-15

 

So my question is this.  In my record entry page, I use this.

 

<input type="hidden" name="tdate" value="<?php echo(date('y/m/d'))?>" />

 

What would I use now to grab the current date and submit it to the database to match my DATE format?

[MadTechie]

if you using the current date then you can get MySQL to do it for you..

using CURDATE()

ie

INSERT INTO table SET datefield = CURDATE()

 

EDIT:

Oh yeah and via PHP

$date = date("Y-m-d");
$SQL = "INSERT INTO table SET datefield = $date";

[Zergman]

WORKING!  Thanks to all of you for your help and knowledge

 

I just set 2 variables to pull the date

 

$year = date ('Y');
$month = date ('m');

 

I just used this as suggested

SELECT * FROM `data` WHERE uname = %s AND MONTH(`tdate`) = '$month' AND YEAR(`tdate`) = '$year'

 

Working like a charm!

 

Again, thanks to all of you for your help, this has made my day