Two quick PHP-MySQL questions

[Rex_Fenris]

I'm trying to have a search function for my user database that will list everyone who has newly registered in the last 7 days or the last 30 days. Can anyone suggest the best way to go about this? I have a DateCreated field in the table made using the php date() function, but I'm not sure how to translate that into something the server can understand and process.

My second question also involves searching the database. I'm trying to set this one up so if the user were to type in a partial name and search on it, it will return everything that begins with the partial bit of information they put in. For instance if they enter "Da" into the search, it would return "David", "Daniel", "Danny", "Daryl"... every name that starts with "Da".

Any suggestions or advice as to the best way to go about doing these two searches would be greatly appreciated.

[sned]

mysql has quite a few date and time functions [dev.mysql.com]. The first example on that page shows a query selecting something from the last 30 days.

As far as selecting usernames based on a few letters, the '%' symbol is a sort of wildcard.

From your example:

"SELECT * FROM tblusers WHERE username LIKE 'Da%'"

would return all the users whose username started with Da.

-sned

[Rex_Fenris]

The partial search bit worked great, thanks!

As for the dates... I'm not entirely sure how to go about all of this. I want dates stored in the DB that the users can see and understand easily, but at the same time I need the database to be able to do temporal searches. Should I set the dates using PHP functions or MySQL functions?

For instance, when a new user account is added and the DateCreated field is set... how should I set it. I want it to have the date and the time, and, as I said before, be easy for the user to read.

I apologize if this is sort of a dumb question, I'm having a bit of trouble figuring out the date/time stuff.

[sned]

When the new user is created, I would use the NOW() mysql command:

"INSERT INTO tblusers (username, first, last, DateCreated) VALUES ('someuser','me','too',NOW())";

You can always format the date on your select queries as however you want to display it:

"SELECT DATE_FORMAT(tblusers.DateCreated,'%l:%i on %M %e, %Y') AS displaydate FROM tblusers WHERE username='someuser'";

This query would produce something like: 10:32 on August 19, 2004

-sned