Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Sql query by year and user's name
Hi.
I have 2 questions about sql query.
I have a single table composed by:
id = primary key
user= varchar: user’s name
year= year: year of registration.
In this table there are more than 300 records.
First output has to show (in alphabetical order) all the users splitted into 9 groups: first group with all users’ names starting by A,B,C; the second one with D,E,F and so on…
Second output has to show all the users registered in the last year in the D/B.
Thanks a lot
Offline
Re: Sql query by year and user's name
all users in aphabetic order:
SELECT user FROM table ORDER BY user ASC;Splitting in groups is probably easier to do in PHP than in MySQL. It makes sense to do it in MySQL if it results in a smaller result set, but if you want to show them all anyway, then you might as well use a query that returns them all at once. But… if you want to show ABC on the one page and DEF on another page, you could use something like this which compares the first character from the username to a set of three characters (and returns 0 if it doesn’t match)… actually, two options:
SELECT user FROM table WHERE FIELD(SUBSTRING(user FROM 1 for 1), 'a', 'b', 'c') != 0 ORDER BY user ASC;
SELECT user FROM table WHERE LOCATE(SUBSTRING(user FROM 1 for 1), 'abc') != 0 ORDER BY user ASC; all users from current year:
SELECT user FROM table WHERE year=2011;But… how did you store the year? As a varchar or as a date/time string… because “from last year” can mean three things: current year, previous year… or from the past 365 days. In the latter case you need to have the registration date (including day and month) instead of just the year.
This all completely untested.
Offline