FRIHOST • FORUMS • SEARCH • FAQ • TOS • BLOGS • COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


mysql querries and subquerries problem..

   Frihost Forum Index -> Scripting -> Php and MySQL
 




akoni176
i have a small mysql dbase that contains tables. then inserting data into tables... the tables are..:main table, history table.

my problem is how to get a correct and accurate result in two tables, i have a main table that contains the master list or personal info, then the history table contains a daily time records.

i want to display list of person are not listed in history table in a current day..
powers1983
So your main table contains all user info (username, age, date of birth, hair colour that sort of stuff) and the history table logs all the actions of each user (or at least the time of each action)?

And you want to display a list of people who are registered but who have not been active in the last 24 hrs? (or on a certain day, or from Mon-Wed or something)

If you store the UNIX timestamp in the history table (use the time() function then store the returned value in the db - I assume this is how you've done it), then you just need a script that finds the UNIX timestamps of the earliest time you want to check and the latest time.
Then its just a case of searching the history table :
Code:

SELECT username FROM history WHERE access_time>$earliest_time AND access_time<$latest_time;

Read the data from the returned result and you'll have an array containing the username of everyone who did have an action logged within your timeframe. Note though you might have the same name repeated lots of times depending on if each action is logged or just the last action. To get the list so each name is only listed once use:
Code:

$users_who_were_active = array_unique($users_who_were_active);

You now need to read in all your users:
Code:

SELECT username FROM main;

Then process the result to get an array of all your users.

Then just take the first array away from the second array:
Code:

$users_who_were_not_active=array_diff($all_users, $users_who_were_active);

You now have an array containing the usernames of all the users who weren't active during your specified times.

PS you can get the timestamps from a user form by getting them to input the dates in 3 drop down lists for day, month, year and then constructing the input into a string and using:
Code:

$time_string="2007/05/04"
$time_string="4th May 2007";
$time_string="4 May 2007 17:30";

$time_wanted=strtotime($time_string);


found this list of allowed formats which might help but the easist is probably the first one (remember US convention though which may or may not be an issue):
Quote:
1972-09-24 # ISO 8601.
72-9-24 # Assume 19xx for 69 through 99, # 20xx for 00 through 68.
72-09-24 # Leading zeros are ignored.
9/24/72 # Common U.S. writing.
24 September 1972
24 Sept 72 # September has a special abbreviation.
24 Sep 72 # Three-letter abbreviations always allowed.
Sep 24, 1972
24-sep-72
24sep72


Hope that helps.

David.
akoni176
thanks David it helps me alot.
Related topics
mysql problem
Mysql database problem
can't access local mysqlserver. help plz!
mysql and apache problem.
Is there any mysql version that supports subquerries or not?		mySQL application install password problem in JSAS
Detecting accents
MySQL and PHP problem in the future
mySQL create table problem (parse error)..
MySQL problem :'(		the mysql is allowed connect from other website?
MySQL Server settings problem
[help]Mysql & php problem
Newbie PHP/MySQL question
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.