MYSQL :: Query By Dates To Find History Records

Where To Find A History Of Queries Executed By Mysql?
I have a problem with one application I have written and I think you can help me to find the solution. My problem is that once my application is running for some hours (sometimes 20 hours, sometimes 250 hours) the CPU used by mysql is 99%. In this case the PC and the application is for sure very slow.
I cannot reproduce this problem on my machine but I imagine that somewhere (for some reason) my application is bombing mysql with a lot of queries.
I now have to find which query is it to find where do I have this query in my code and try to unsderstand where is the problem.
My question is the following:
Is there any way to get an history of all queries executed by mysql where we can see the time of each query?

Query History/log
does MySQL server log the queries that users execute?

if it does, where is that log/history or how can I get access to it?

Find Average Amt Of Days Dates
I've been working on this problem for quite some time now. I've searched google and all over these forums, but didn't quite find what I'm looking for.

Here's what I'm doing:

mysql> SELECT idno, sro, pname, adate, cdate , TO_DAYS(cdate)-To_DAYS(adate) as days FROM turnaround group by idno,sro order by adate;
+------+----------+--------------+------------+------------+------+
| idno | sro | pname | adate | cdate | days |
+------+----------+--------------+------------+------------+------+
| 4 | S1111115 | Tupperware | 2003-05-03 | 2003-05-05 | 2 |
| 5 | S1111116 | Tupperware | 2003-06-03 | 2003-06-05 | 2 |
| 3 | S1111114 | Blab | 2004-05-03 | 2004-05-05 | 2 |
| 2 | S1111112 | Product Test | 2005-03-11 | 2005-03-11 | 0 |
| 1 | S1111113 | Big Tester | 2005-04-06 | 2005-04-08 | 2 |
| 6 | S1111111 | blah blah | 2005-11-18 | 2005-11-22 | 4 |
+------+----------+--------------+------------+------------+------+
6 rows in set (0.00 sec)

What I need to do now is find the average number of days. I've tried several combos of AVG() without sucess. Could someone point me in the right direction?

Find Days, Hours And Minutes Between 2 Dates
I have a datetime field in a mysql database and i want to output how many days, hours and minutes are left between the current date and the future date.

So far i just convert the datettime to a timestamp like so: $start = strtotime($began);

Where $began is the current time, basically i just need to know how to convert a timestamp to days, hours ect If i was to minus the current timestamp from the future timestamp.

Find Records
I need to delete all "question" records in a table whose NEWEST related "answer" record is 30 days old (purging the old stuff). The best I could come up with is:

delete from questions where questions.id=answers.question_id and max(answers.creation) < date_sub(curdate(), interval 30 day)

it returns no error, but doesn't delete anything

Find Records
I have a table t1 and a related table t2. In t2 is a number field. I want to find all t1 records where the sum of all the values in the related t2 records is at least 25. Nothing I try works:Code:

select t1.*,
sum(t2.numberfield) as grandtotal
from t1
left outer
join t2
on t1.id=t2.id
where
grandtotal>25
group by t1.id

This gives me "unknown column 'grandtotal' in 'where clause'." I just can't seem to come up with a where clause that will do what I want.

How To Get Records Between Two Dates
In database I have stored dates in &#55612;&#57200;-00-00 00:00:00' format.
And from user I am getting dates in 'mm/dd/yyyy' format.

1- How can I get records from database between user entered dates.

Getting Records Between Certain Dates?
Is there a way to get records from a database between certain date periods if each record has been stamped with a NOW() date/time entry?

Find Unrelated Records
I need to find records in one table without a match in another.

Right now, I have three tables (staff, topic, topicstaff). There is a many-to-one relationship between topic and staff that's recorded in the topicstaff table. Each topic and each staff member has an ID. The topicstaff table is only these two columns.

My question is, how do I find the topics aren't associated with a staff member? It should be easy as pulling the topic IDs from the topic table that aren't in the topicstaff table. Right?

Find IP Among 1.5mill Records
I have recently purchased the ip2location database. This databse containts 1.5million rows with IP ranges (ipFROM,ipTO) and some other data like countryname, cityname etc. I need to write a query that is extremely much faster than my current ones.

The IP numbers are stored in long format (int(10) unsigned). The SELECT statement provided by ip2location is extremely slow, and looks like this:

SELECT countryname FROM tbl WHERE (ipFROM <= IP) AND (ipTO >= IP)

I currently do the following:

1. SELECT max(ipFROM) FROM tbl WHERE (ipFROM <= IP)
2. SELECT countryname FROM tbl WHERE (ipFROM = RESULT FROM 1)

This speeds up the query with about 50%, but a query for a high ip-number - ie. 212.78.161.106 still takes about 4 seconds to process (7,5 seconds with original SELECT).

A friend (knowing a lot about mathematics, but little about databases, suggested the following;

Consider IP to be the address we are searching for. The database consists of N elements. N0=1

1. Compare IP with record N/2
2. if IP < N/2 -> N = N/2
if IP > N/2 -> N0 = N/2
if none of these, adress matches.
3. next iteration compare IP with NO/2+NO/2
4. continue this process until none match, and you have the adress..

MY QUESTION: How can I do this? I use php and mysql.

I would also appriciate any other suggestions to speed up the query.

Find Records Without Matches
I have two tables (A and B) with id fields in them, I would like a
list of ids that are in A but not in B. How would I do this in SQL?

Find Redundant Records
I need an SQL statement to find (and eventually) delete and redundant records in my database. The structure is:

TABLE: Survey
surveyID
etc etc

TABLE: Responses
responseID
surveyID
etc etc

So I need to find all the records in responses that DO NOT have a surveyID which is valid. Ie: Any records which have a surveyID that does not exist any longer.

I have done this

SELECT
s.surveyID as survey_surveyID,
r.surveyID as repsonse_surveyID
FROM survey s
left outer join responses r on
r.surveyID != s.surveyID

but it seems to be bringing back records which have a valid survey. Ie: The survey still exists in the survey table.

How To Quickly Find Random Records
I've got a table of 117,000 records and I want to return 5 random records. I've tried:

"select * from table_name order by rand() limit 5;"

but it's too slow, requiring perhaps 2 seconds to execute. Is there a faster way to do this?

Find All Records With A Certain Number Of Characters
I have had a data problem where a field length has been truncated at 25 characters, rather than the 100 specified in the data type, varchar(100). I believe the problem to be on our website form input and not in MySQL (I didn't code the website, and I'm hoping the guy who did can correct the problem), but I would like to be able to find all the records that have been affected. Is there a WHERE statement that can find all records with at least 25 characters in a particular field?

Find & Delete Orphan Records
Have 2 tables
ts_sheets :
TimeSheet_ID
TimeSheet_Date
Emp_ID
Orphan

LineItem
TimeSheet_ID
Client
JobNumber etc

The following sql will show me all the ts_sheets records with
no ts_lineitem records

select s.* , l.timesheet_id
from ts_sheets s
left join ts_lineitem l on s.timesheet_id = l.timesheet_id
where l.timesheet_id is null

I would like to delete these orphan records
Something like

UPDATE `ts_sheets` SET `Orhan` = 'Yes'
left join ts_lineitem l on s.timesheet_id = l.timesheet_id
where l.timesheet_id is null

Than delete `Orhan` = 'Yes'

I can not work the syntax out

How To Get Records Between Two Specific Dates?
i'm a newbee to mysql.

i'm having a table called transaction which stores transaction details with dates.

i'm having another table callled company in which the company info stored,

The company table has the fiscal month details.

what i wnt to do is get the records from the transaction which is between two specific dates(i.e) in the fiscal year

eg: 04-01-2006 to 03-31-2007

as far as now i'm having the query,

SELECT t.* FROM transaction t,company c where t.companyid=c.id AND
(MONTH(t.transdate)>MONTH(str_to_date(c.fiscalmonth,'%M')) AND
YEAR(t.transdate)=YEAR(now())) ||
(MONTH(t.transdate)<MONTH(str_to_date(c.fiscalmonth,'%M')) AND
YEAR(t.transdate)=YEAR(now())+1) and c.id=2 order by transdate
Note: the company table stores the fical months as month names(i.e) March etc.,

i think this is far complex, and is there any easy function to get the records b/w two dates??

Pick Records Between Two Dates
I guess this is more likely a MySQL question.

I would like to get the number of my members grouped by their signup date.

I store the signup_date as DATETIME.

I came up with following dirty php code:

$curday = date("d");
$curmonth = date("m");
$curyear = date("Y");

$days = 7; // date interval

$dates = array();

for ($i=$days; $i>0; $i--) {
$a = $days - $i;
$dates[$a] = date("Y-m-d", mktime(0,0,0,$curmonth,$curday-$i,$curyear));
}

include("db.php");
$query = "SELECT DATE(signup_date), COUNT(name) FROM members WHERE DATE(signup_date) > $dates[0] GROUP BY DATE(signup_date)";
$result = mysql_query($query) or die(mysql_error());

echo '<table>';
while($row = mysql_fetch_array($result)){
echo '<tr>';
echo '<td>' . $row['DATE(signup_date)'] . '</td>';
echo '<td>' . $row['COUNT(name)'] . '</td>';
echo '</tr>';
}
echo '</table>';

This outputs number of members signed up from the beginning. I want just the number of members signed up before 7 days onwards (or between two dates, that would be better).

Exclude Records In Between Two Dates
Basically, I have two tables:

create table dresses (
id int unsigned auto_increment primary key,
reference_number varchar(16)
);

create table bookings (
id int unsigned auto_increment primary key,
dress_id int unsigned,
booked_from date,
booked_to date,
reason varchar(32)
);

Now I can select everything from dresses with all my required criteria, but what I need done is that the select statement excludes any dresses that are within a specified time frame.

For arguments sake:
I need a dress from '2006-01-10' to '2006-01-14'. I want to see all the dresses in my database that arent currently booked out inbetween those two date.

So it would be something along the lines of:

SELECT
dresses.id,reference_number from dresses,bookings
WHERE
(booked_from NOT BETWEEN('2006-01-10') AND ('2006-01-14')) AND
(booked_to NOT BETWEEN('2006-01-10') AND ('2006-01-14'))
;

Problem comes in with the relationship in-between dresses and bookings clearly. How do I make it display all the dresses?

How To Find Records That Are NOT Referenced In A Join Table?
Table structure...

CREATE TABLE category
(
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL
);
INSERT INTO category (id, name) VALUES
(1, 'CSS'),
(2, 'HTML'),
(3, 'XML'),
(4, 'Javascript'),
(5, 'PERL');

CREATE TABLE category_j_article
(
category_id TINYINT UNSIGNED NOT NULL,
article_id INT UNSIGNED NOT NULL,
PRIMARY KEY (category_id, article_id)
);

CREATE TABLE article
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40) NOT NULL
);

Let's say I want to find all articles that aren't filed under multiple categories? For example, if I want to find articles that aren't filed under the CSS and HTML categories?

Pulling Out Records For Expired Dates
I have what I think is a unix timestamp in a db field - 1146673434 - and I want to try and pull out all the records that are less than that field plus one year. The date is when people registered so I want to find out which records have a date that is over one year ago :-

db date + 1 year <= current date

but this sql is failing to pull out any records :-

SELECT username, id FROM users WHERE DATE_ADD('registered_at', INTERVAL 1 YEAR) <12-09-2006

SELECT Date Records Between Two Given Dates.
Let's say i have a job posting site and a user wants to see the jobs posted between dates.... how would i do that?

here's how a table would look like(sample):

table: Jobs
fields:
JobID
JobStatus
DatePosted
DateToDeactivate

and when i build the query i would make it something like this:

"Select * from Jobs Where JobID = '123' and ( DatePosted BETWEEN 2003-05-08 12:14:37 AND 2004-06-08 12:14:37)"

i know that this does not work! how can i do this.

Finding Records Between Dates Problem
Here is my query.

SELECT * FROM callhistory
WHERE date_format(callhistory.TimeStamp, '%m/%d/%Y') between '09/15/2004' and '01/08/2006'

This returns nothing.

If I change it to

SELECT * FROM callhistory
WHERE date_format(callhistory.TimeStamp, '%m/%d/%Y') between '09/15/2004' and '12/08/2006'

It works fine.

A Query To Find Something From Last 30 Days
So basically I am trying to do a query to pull up a "request" that is over thirty days old. How do I setup the other half of the inequality to automatically adjust for what 30 days old is each day. Date is in this format: 2006-07-18 11:59:59

$result = mysql_query('SELECT * FROM requests WHERE reqtime < "2006-07-18 11:59:59" ');

How To Find Max Value Of Query Using Group By
When I run the following query

select count( username) as count, username
from friends
where status="validated"
group by username
order by count;

it gives me a list of each username in the and how many times it appears in the friends table

Can anyone tell me how to rewrite this query so that it tells me which username appeared the most times in the friends table.

Find And Replace Query
recently i completed a forum move to a new URL, however there are many posts on my board that still use the old URL for reference. Is there a query that i could run on my database to find all the instances of the first URL and replace it with the second?

Using Dates To Query
I have a table with a field for each record with a date in it. I want to get all records before September 12,2000 but I don't know what the query should be.

Use Between In Dates Query
can I safely use between with dates as in:

select * from table where CURRENT_DATE() BETWEEN LASTMONTHDATE AND NEXTMONTHDATE

Query Dates
I am trying to find records where my approved date is not NULL and my shipped date is NULL. Using phpMyAdmin the records show cells with italic NULL and others with dates ie. 2006-06-11, so I assume this is formated right.
However using this query I get no records.
SELECT * FROM records WHERE approved_date != NULL AND shipped_date = NULL
I have used phpMyAdmin's search section with every conceivable way and I can't get any proper results.

Query For Dates
Table Data:
-----------

id__name___from_________to_______
-------------------------------------
1___ABC___2006-10-01___2006-10-31
2___CDE___2006-11-01___2006-11-31

is there a syntax to select both rows at once?

i.e. if I query for dates from 2006-10-15 to 2006-11-15, is there a possibility to have the two rows as a result of a single SQL Statement query?

Query Using Dates
I'm having trouble figuring out how to get two time periods in one query. Basically it's either one month at a time or a full year...So I need to get everything that starts and stops this month, or cases where this month falls between the start and stop dates. I couldn't figure out how to do the between bit, so I settled for calling an expiration date that is not older than this month.I get partially correct results. It's calling the five ads that start on 2006-12-01 and end 2006-12-31, and ignoring the ones that start 2007-01-01 and end 2007-01-31. However, I've got a test ad that starts 2006-01-01 and ends 2006-12-31 that it's ignoring...I think my timeframes are cancelling each other out or something.

$sql = "SELECT ad_id, ad_filename,rally,affiliates,past,upcoming,start_date FROM ads WHERE Month(start_date)='$curmonth' AND Year(start_date)='$curyear' OR Month(end_date)>='$curmonth' AND Year(end_date)>='$curyear'ORDER BY rally='y' DESC";

Need A Query To Find All Spam Entries In My DB
I need to tweak the following query to help weed out spammers who have created accounts. I want to try and:

1. Show accounts that have alpha characters in the zip field
2. Show accounts that use more than 5 numbers in the zip field

select zip, country from users where country = 'United States'

How To Find Out How Many Times A Query Has Been Executed
How can you count the number of times your query has been executed? So I'm trying to set it up, so that I get a variable on every time someone views a review.

I have two querys right now, but honestly don't really know what I'm doing. And I'm assuming I have to increment viewcount everytime that query gets executed.

This query looks up the review:

Query To Find Friends Of A Friend
I'm experimenting with writing my own social network script but I'm struggling a bit with one of the queries. I have a users table, and a friendships table containing user_id and friend_id columns which are both foreign keys linking to users.id (sql dump at end of this post).

if x is a friend of y, and the friendship is approved, then y is also a friend of x. I'm using this query to retreive all the friends of x (in this example, x has an id of 99):

Can I Use Mysql Query In Jsp To Find Table?
we can use sql query to find attribute in jsp. If I want to find table?is it possible?

Mysql Query Search And Find In String
I have following values in a field of mysql table. These values are stored as string [varchar] in the field.

Values : 3,4,10,21,20,8,100,2,6

How can I check through MYSQL query that 100 or 21 or 4 exists in the string?

Query To Find Data For Previous Week
I am trying to get a query that can find if a Loandate falls in last week...so basically check if loandate is between sunday or monday of last to friday or saturday of lastweek...

Any clues:

Select * from loan where last weeks sunday<Loandate>last week's saturday.

Comma Delimited List Query Find
I am building an app. using ColdFusion along with Mysql. I need to create a select query that checks if certain Element ID's are contained in a comma delimited list which are stored in the MySQL database.

Normally, this would be easy, just something along the lines of:

select * from table_name
where Comma_list like '%ID1%'

The problem comes in the creating of the ID's there's nothing to stop the user from creating an ID that contains the entirty of another id...example:

Find Hightest Number After Finding First Query
i have this code but it does not display the highest number or anything.

it should be searching the field 'name' for the string $name and only display the one that has the highest number in the 'number' field.

what am i doing wrong?

$res = mysql_query("SELECT MAX( number) FROM data WHERE name='".$name."'");
$high = mysql_result($res, 0, 'number') or die(mysql_error());
echo($high);

Comparing Dates Y, M, D , Time In Sql Query
ok.. ive finally got my nice little calender setup to output my dates

now

how do i query my database

for example ive got 2 variables
$startdate = 2006-1-1 00:00:01
$enddate = 2006-1-1 23:59:59

these represent the 1st and last second of the first of january 2006

i have a cell in my table (table called challenges) ( cell called time) that contains a date in the same format for each entry

how do i get all the entries between the start time and the end time..

can i use less than < and more than > as these are not really integar values

the column type is datetime but there is also another column of type datetime

Query Dates Within 1 Week From A Date
I'm fairly new to PHP and have done some basic work with mysql but am not sure how to approach this. I'm trying to figure out how to create a query with PHP to get all rows with a datetime from 1 week ago to the current timedate.

Note: I'm also not sure how to find a date for 1 week ago with PHP. Any help would be great.

Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows

SELECT * FROM news WHERE ((news.date)>$today ORDER BY date

where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.

Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of
the items which are in the future. My query is as follows

SELECT * FROM news WHERE ((news.date)>$today ORDER BY date

where news is my database, news.date is the the field which holds the date
for the item and $today will be replaced my current date.
At the moment it seems to display all values, which suggest its not
functioning properly.

History Table
I want a mechanism for recording the history of our employees' salaries. Using this mechanism, I should be able to easily obtain a salary given an employee's ID and a date. Any suggestions?

Why Doesn't This Work? - SHOW TABLES NOT LIKE 'history%'
Well my question is simple - I want to exclude tables with a certain prefix using this:

SHOW TABLES NOT LIKE 'history%'

With the prefix obviously being 'history'. I cannot understand why I get a nasty syntax error - it works when I have just LIKE rather than NOT LIKE but I want all the tables that are NOT like it.

Clear MySQL History - Remotely
I was wondering if it's possible to Clear MySQL's History Remotely.

Considering that you only have root access to the database but no
access to the linux box.

I found something in Goggle to do this, but you need to have access to
the linux box:

cat /dev/null ~/.mysql_history

How can you do this remotely with only root access to the database and
not to the linux box?

Maintaining History Files In Mysql?
I am running a site with php and mysql. I want to keep the record of all add, update, delete/DML queries which daily run on mysql server in a file. how can i take the history of all those queries in a file.

Mysql Sql History(cross Session)
I have found many docs to see is there any way to figure out how can
mysql store the typed words or sql(whatever it is a valid sql or not)
so that we can just use "arrow up" or "arrow down"
easily to move to the previous sql or even some sqls typed yesterday or
in different sessions(such as by putty/konsole) that is already logged
out ?

Does anyone have any clues about this ? I think that it should be done
by setting some mysql server vairables but i didn't see any mentioned in the mysql
docs.

Query By Dates To Find History Records

what is the correct way to query on a date field in a table to pull a value that is older than 3 days? Date field format is 2007-04-04 I only want records where the date is 2007-04-01 or earlier.

what is the correct way to query on a date field in a table to pull a value that is older than 3 days?

Date field format is 2007-04-04

I only want records where the date is 2007-04-01 or earlier.