hi guys
having some problems and for the life of me cant work it out how can i get all users that have a registerd date
of 3, 6, 12 months which i think i can make as an array and get the users that have been registerd at these intervals
and display it on a page the reg date is an int 11 (timestamp) in my database the code i have been working on is useless so havent posted it as i'm going to restart it from scracth if pointed in the right direction
any help much appreciated
Maybe something like this? This works off a 'standard' 30 day month. If your regdate was in date time, you could use a range of functions to add/subtract intervals. I'm not sure if this is possible with timestamps in SQL.
$r = mysql_query("SELECT username, FLOOR((UNIX_TIMESTAMP() - reg_date)/2592000) AS monthsago FROM regdate WHERE user_id = $user_id");
$d = mysql_fetch_assoc($r);
if($d['monthsago'] >= 12){
$time = 'Over a year ago';
}elseif($d['monthsago'] >= 6){
$time = 'Over 6 months ago';
}elseif($d['monthsago'] >= 3){
$time = 'Over 3 months ago';
}else{
$time = 'Less than 3 months ago';
}
There must be a better way. Anybody else?
Thanks diafol
I'll give it a go and see if i can get something working with the helpfull code you posted
much appreciated
Hiya that didnt really work or i did something wrong :(
changed it a bit to look like
$r = mysql_query("SELECT * from users WHERE `created` >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH) ") or die (mysql_error());
while ($d = mysql_fetch_assoc($r)) {
echo $d["name"],"<br >";
}
but it shows all users even if i created them today im basically only after the guys that have been with us for 3 months at present just to get me getting something but afterwards i want a seperate list with guys that have been with me for 3, 6 and 12 months i know its my lack of knowledge here :( but if you could repoint me in right direction i would be very thankfull
$r = mysql_query("SELECT username, FLOOR((UNIX_TIMESTAMP() - reg_date)/2592000) AS monthsago FROM regdate WHERE user_id = $user_id");
$output = "<table><thead><tr><th>User</th><th>Member Since</th></tr></thead><tbody>";
while($d = mysql_fetch_assoc($r)){
if($d['monthsago'] >= 12){
$time = 'Over a year ago';
}elseif($d['monthsago'] >= 6){
$time = 'Over 6 months ago';
}elseif($d['monthsago'] >= 3){
$time = 'Over 3 months ago';
}else{
$time = 'Less than 3 months ago';
}
$output .= "<tr><td>{$d['username']}</td><td>{$d['time']}</td></tr>";
}
$output .="</tbody></table>";
echo $output;
OK, fleshed it out - forgot the while loop - doh! Your SQL approach I'm not so sure about. Doesn't this assume create is in yyyy-mm-dd format? I thought you were using an unix timestamp (int). One drawback with the php processing approach is that the resultset won't be sorted. you could sort the original SQL on 'monthsago'.
Hi diafol,
Thanks bud this definatly gives me something to work with thanks alot much appreciacted
cool almost got it all working
ive joined it to a table that has the awards for the user time frame that has an id and image fields the id is linked to the id in the users table i just need it to find any users that has not got there award for a time frame but are due it so i can update the awards table what i have got is
$array_imag = array
(
"6",
"12",
"30",
"60",
"90",
"120",
"180"
);
$r = mysql_query("SELECT users.* , FLOOR( (UNIX_TIMESTAMP()- users.created )/2592000 ) AS monthsago
FROM users
LEFT JOIN awards ON users.uid = awards.uid
WHERE users.uid = awards.uid
AND awards.award_imag != '". $array_imag ."' ORDER by users.created")or die (mysql_error());
$output = "<table><thead><tr><th>User</th><th>Member Since</th></tr></thead><tbody>";
while($d = mysql_fetch_assoc($r)){
if($d['monthsago'] >= 180){
$time = 'Over a 15 Years ago';
}elseif($d['monthsago'] >= 120){
$time = 'Over 10 Year ago';
}elseif($d['monthsago'] >= 90){
$time = 'Over 7.5 Years ago';
}elseif($d['monthsago'] >= 60){
$time = 'Over 5 Years ago';
}elseif($d['monthsago'] >= 30){
$time = 'Over 2.5 Years ago';
}elseif($d['monthsago'] >= 12){
$time = 'Over 1 Years ago';
}elseif($d['monthsago'] >= 6){
$time = 'Over 6 Months ago';
}else{
$time = 'Less than 3 months ago';
}
$output .= "<tr><td>{$d['name']}</td><td>{$time}</td></tr>";
}
$output .="</tbody></table>";
echo $output;
so basically i want it only to display those that have been with my site for a certain amount of time that have not recived the award thats due so latter on i can update the awards table with an insert if that makes sense at the moment though it gives me a list of my users multiple times as there is other awrads available and there stored in the same table so i guess it sees awards.award_imag != '". $array_imag ."' and gives me all there other awards which makes sense any one know how i can limit it to just the awards in the array $array_imag ?
Getting an image should be straightforward. You can do this directly in the SQL, but easier IMO would be to implement an array or vanilla php within the control structure itself. This method would allow you to make the awards dynamic, without having to store the award (or update) the user table:
$r = mysql_query("SELECT *, FLOOR( (UNIX_TIMESTAMP() - created )/2592000 ) AS monthsago FROM users ORDER BY created") or die (mysql_error());
$output = "<table><thead><tr><th>User</th><th>Member Since</th></tr></thead><tbody>";
while($d = mysql_fetch_assoc($r)){
if($d['monthsago'] >= 180){
$time = 'Over a 15 Years ago';
$img = '180.jpg';
}elseif($d['monthsago'] >= 120){
$time = 'Over 10 Year ago';
$img = '120.jpg';
}elseif($d['monthsago'] >= 90){
$time = 'Over 7.5 Years ago';
$img = '90.jpg';
}elseif($d['monthsago'] >= 60){
$time = 'Over 5 Years ago';
$img = '60.jpg';
}elseif($d['monthsago'] >= 30){
$time = 'Over 2.5 Years ago';
$img = '30.jpg';
}elseif($d['monthsago'] >= 12){
$time = 'Over 1 Years ago';
$img = '12.jpg';
}elseif($d['monthsago'] >= 6){
$time = 'Over 6 Months ago';
$img = '6.jpg';
}else{
$time = 'Less than 6 months ago';
$img = '3.jpg';
}
$output .= "<tr><td><img src=\"img/awards/$img\" /> {$d['name']}</td><td>{$time}</td></tr>";
}
$output .="</tbody></table>";
echo $output;
Admittedly, it's not pretty. But it's relatively simple.
heya
Thanks but i think i may have gotten you confused here what i'am trying to get
is if i have a guy with say $d['monthsago'] >= 120 ie hes been with me for 10 years or over and he hasnt got the award then that guy will show up in the list
but guys that have been with me for 10 years and do have that award dont show up
so i tried a join of the 2 tables but am lost as to the 'where' part as it needs to check first the users table for the id and created date then check the awards table and see if they have the award for the length of time (ie the list in the award_imag array only) they been with me and IF they dont have it then there shown in the list IF they do then there not shown as nothing needs to be done for them..
Hope that makes it a bit clearer as to what i'am trying to do here
Thanks so much for help so far
OK, just to clarify. You need to run something / do something when you give an award? It can't be done automatically based on time served?
BTW the WHERE may need to be HAVING - depending what you're searching on.
Heya
what im trying to do is Based on Time serverd is give the guy a award BUT as i have quite a few guys that may allready have it ie manually gave them the award is to search out the guys that DONT have one of the awards in the array BUT do deserve one from the select based on time and then run an insert on those guys only as i dont want to give another award of the same type to the guys that allready have one if that makes sense
Thanks again
Okidoki think i'm getting close now LOL heres what i have
$array_imag = array(
"6",
"12",
"30",
"60",
"90",
"120",
"180"
);
$award = implode(", ", $array_imag);
$r = mysql_query("SELECT users .*, FLOOR( (UNIX_TIMESTAMP()- users.created )/2592000) AS monthsago
FROM users
WHERE NOT EXISTS (SELECT NULL FROM awards v
WHERE v.uid = users.uid
AND v.award_imag = %$award%
")or die (mysql_error());
and this gives me an 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 '%6, 12, 30, 60, 90, 120, 180%' at line 5
so i checked using v.award_imag = '12'
and it worked kind of as it dispalyed all those that didnt have the award :) so why not with the array ? and it dispalyed the guys from the 6 month membership aswell so i have to add a check in the if statements ???
SELECT NULL FROM awards AS v ?
SELECT *, FLOOR( (UNIX_TIMESTAMP()- created )/2592000 ) AS monthsago FROM users LEFT JOIN (SELECT user_id, MAX(award_imag) AS award FROM awards GROUP BY user_id) AS t1 ON users.user_id = t1.user_id HAVING monthsago < award
I'm not able to test this properly, but that should give you just the users that have an award value less than the monthsago value.
It will still return the ones over 180 months, but you can alter the HAVING condition to not include those or filter them out with PHP.
Hi bud
thanks for your help but that didnt work either :( no one was displayed
let me try to explain this better here
i have two tables
users that stores uid, name and created date in unix timestamp and is in int(11) form
users_awards that has UID (linked to users table), award_imag that just has an image name without the .jpg stuff as thats sorted with my code for display.
now from these two tables i just need to get the guys that for example have been with me for at least 6 months but have not got the award name from the array that i posted.
If they have got the award_imag then I dont want to display them I think the problem is that the select is just seeing all the array items and if some arnt seen then it dispalys the guys I think i may just have to do multiple selects for each award here :( ??
thought it would simple :D
Cheers
Maori
heya didnt even think of that :( okidoki i have shrunk the tables for ease of use
Users table looks like
CREATE TABLE IF NOT EXISTS users (uid int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Primary Key: Unique user ID.',name varchar(60) NOT NULL DEFAULT '' COMMENT 'Unique user name.',created int(11) NOT NULL DEFAULT '0' COMMENT 'Timestamp for when user was created.',
PRIMARY KEY (uid),
UNIQUE KEY name (name),
KEY created (created)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Stores user data.';
--
usersINSERT INTO users (uid, name, created) VALUES
(0, '', 0),
(1, 'Maori', 1318080158),
(2, 'test', 1349529758),
and awards table looks like
CREATE TABLE IF NOT EXISTS users_awards (award_id int(11) NOT NULL AUTO_INCREMENT,uid int(4) NOT NULL DEFAULT '0',award_name varchar(255) NOT NULL DEFAULT '',award_date date NOT NULL DEFAULT '0000-00-00',award_imag varchar(255) NOT NULL DEFAULT '',reason varchar(200) NOT NULL DEFAULT '',
PRIMARY KEY (award_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
users_awardsINSERT INTO users_awards(1, 2, 'six months', '0000-00-00', '1', 'for being here 6 months'),
(3, 1, 'one year', '0000-00-00', '2', 'been here for 12 mths');
as you can see in this example is UID 1 has been a member for 12 months but has not got the 6 month award so i need a select that grabs the uid and created date from users table goes though the array and sees he has been here for six months but not got the award so shows him on the page but there might be others aswell and some may even been members for longer and not got any of the awards in the array that also need to be shown but guys that have only been here for 6 months may need that award but dont need any of the others in the array if that makes sense ? i really hope so
heres a link http://sqlfiddle.com/#!2/695c72/1
as you can see it picks up the guys that dont have the award in the v.award_imag = '2' change that to one or 3 and you should see the problem it just see that the guys dont have the award but ignores the monthsago as it shows the guy with 6 months in the next two :(
From that, I'm assuming this should be an automated award - there is absolutely no need for a linked user-awards table at all - this can be calculated on the fly. You don't need to manually award anything via update/insert command. That schema is really demanding and probably unsustainable. Just a thought.
hi Diafol
Indeed it is and ive just taken over from the last guy and unfortunatly i cant change the structure my hope was to make this automated to make it easier for us.
when you say no need to link the user_award table at all how can this be done ? as wont you end up with giving people 2 awards of the same type or awards there not entitled to ?
Here's a really convoluted idea. It uses 2 calls, BUT that may still be quicker than having to compare each user record against a long (manual) conditional if-elseif or switch block. Feel free to bin this idea. I'm sure there's an easier way but I was trying to avoid using subqueries - this may include quite an overhead if you're returning 100s or 1000s of records.
HOWEVER - I am not an SQL expert so I can't be sure. I've changed the fieldnames slightly for my own comprehension. 'period' relates to min no.months required to earn this award. If your structure is slightly different, nothing prevents you from actually adding certain columns to tables if it helps.
users
user_id | username | other_fields | created (unix timestamps)
awards
award_id | award_image | period | reason
1 | noob.jpg | 0 | For joining
2 | 3month.jpg | 3 | For being a member for 3 months
//Get all entries in awards table
$sql = mysql_query('SELECT * FROM awards ORDER BY period');
//initialize counters and concatenating string variables
$i = 0;
$string = '';
$output = '';
//loop over all awards and build an array based on the no. of months as the key and an SQL substring for inserting into the main query further on
while($d = mysql_fetch_assoc($sql)){
//the SQL substring
$string .= ($i < $count-1) ? "IF(FLOOR(created/2592000) >= {$d['period']},{$d['period']}," : $d['period'] . str_repeat(')',$count-1);
//the no. month key array
$awards[$d['months']]=$d;
$i++;
}
//run main query
$sql = mysql_query("SELECT *, $string AS award_no FROM users");
//loop over resource and decide on elements to show
while($d = mysql_fetch_assoc($sql)){
//show username with current award image with the reason text
//user data from $d array, award data from $awards[$d['award_no']] array
$output .= "Username: " . $d['username'] . " image: <img src=\"images/{$awards[$d['award_no']]['award_image']}\" /> Reason: {$awards[$d['award_no']]['reason']}<br />";
}
//echo the list at the relevant place in code
echo $output;
There's probably a really succinct way of doing this in SQL, but it's beyond me at the moment. Just to explain the rationale here is to make the whole thing dynamic with no duplication of values in the DB. You may find that you need to change values in the DB, like add a 240 month or remove an award or whatever. You should be able to do this without having to mess with the code above. It should just work.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.