MYSQL :: Searching A Range

Selecting A Range Across A Range Of Fields
I am trying to gather stats from various categories. Each "item" can fit in as many as 8 categories and I have 25 categories to get stats on, which each have a unique numeric ID.

What I am using now is VERY processor intensive, so I am wondering if there is some way I can put everything into the SQL statement instead of running it through a loop as shown below:

$category_stats = array();
foreach ($categories as $k=>$v) {
$query_result = mysql_query('SELECT SUM(stats) FROM items_table WHERE '.$v.' IN(catid0,catid1,catid2,catid3,catid4,catid5,catid6,catid7)');

$result = array();
$result = mysql_fetch_row($query_result);

mysql_free_result($query_result);

$category_stats[$k]['stats'] = $result[0];
}

The $categories variable is just an array of 25 numbers, each corresponding to a category's unique ID. The stats field is an integer that is incremented on each stat increase.

Only Get Certain Age Range
im making a user search system for my website, an im trying to get it to display only users whos ages are within to criteria... e.g to display all users between 18 and 25...
Does anyone have any ideas how to do this?
the table is: hq_user an the dob cell is user_birthday whis is in 'date' layout.

Range
I'm trying to write a script that reports the number of users in each country. I have a users table that contains an ip address field and another table that contains the ip-ranges of each country. It has 3 significant columns, (1) name of the country (2) ip range start (3) ip range end.
Is it possible to write one SQL statement that will have this result:

1. First column is the name of the country
2. Second column is the number of users within that country's ip-range
3. All countries with no user records will be disregarded

I'm thinking about using GROUP BY but I'm not sure if it's possible to use it with ranges instead of just one column match.

Age Range
I am working on my first web application and I am having some difficulties selecting an age range from Mysql.
Let's say the user filled out that he is looking for a woman between 18 and 25 years old. This is the SQL I use to get it:

SELECT t1.custnr, t1.name, t2.gender
FROM klantnaw AS t1, klantspecs as t2
WHERE gender = 'woman'
AND DATE_SUB(DATE_ADD(CURDATE(), INTERVAL -18 YEAR), INTERVAL -7 YEAR) <= t2.birthDate
AND t1.custnr = t2.custnr

The 7 is the difference between the first and the second age. This doesn't work. It gives some results, but wrong results.
Could you tell me how to select an age range from a table that contains birthDates?

Particular Range
I have a query, which selects and sorts some data. I need to get a particular range of these results, rather than all of them (e.g. the first twenty, or from the twenty first to the fortieth). Is there any way I can modify my query, to achieve that?

Not Between Date Range
I'm working on a booking system that allows users to search for properties that are available on a certain date.

For instance, if the user were to search for properties between 2007-02-15 and 2007-02-22...

+----------------------+
| name | date |
+----------------------+
| Property1 | 20070221 |
| Property1 | 20070220 |
| Property1 | 20070219 |
| Property2 | 20070214 |
| Property2 | 20070213 |
| Property2 | 20070212 |
| Property3 | 20070216 |
| Property3 | 20070215 |
| Property3 | 20070214 |
+----------------------+
Only Property2 would be displayed because Property1 is booked from the 19-21st and property 3 is booked from the 14-16th.

I had a look through the manual and found NOT BETWEEN which seems ideal on paper, but it's including Property3 because that's available on the 14th, despite also being booked on the 15th and 16th.

A regular BETWEEN command returns the expected results though, showing Property1 and Property3 as booked. But what I need is the inverse of that (ONLY properties that aren't booked from the 15-22nd), and it's proving pretty tricky!

Here's the command I've been using:

SELECT DISTINCT name, date FROM properties, pdates WHERE PID=ID AND date NOT BETWEEN 20070215 AND 20070222;
As you can see I've two different tables here: properties and pdates which are tied together using ID/PID. There's an entry for each date booked in pdates, so each date range comprises of three rows.

Hope all this makes sense, any help would be much appreciated as I'm really stumped here!

EDIT: I'm using MySQL 5.1 on an Apache server. The date column is a varchar type.

Query In A Range?
(start_time, end_time) in a table.

is there a way where i can make a query to test if a value of time x is within the range between start_time and the end_time?

(start_time <= x >= end_time)

how can i structure this query?

i am using mysql version 4.0.4

Explain Says 'range'
(actually, explain *used to* say 'ALL', but changing the charset of all the tables seemed change that to 'range', but it's still slow, so...)

Explain for the subquery in:SELECT
sdate AS x, COUNT(*) AS y
FROM (
SELECT
MIN(daydate) AS sdate, number AS snumber
FROM
aux.nogaps AS t1........................

Port Range
What range of ports does MySQL use because I need to set it up with my firewall. At the moment i have added 3306 but when it changes port to one in whatever the range is it says lost connection to server. It works fine with firewall off.

Values NOT In Range
I want to find all values for a field NOT in a range.

In this case, I want to see which membersIDs are NOT used say between 1000-1000.

Finding the used ones is dead easy, but I can't find a simple way (besides creating a list of all numbers between 1000 and 10000) to get the ones that are NOT used.

Date Range
I want to restore the backups made in MySQL within a specfied date range. For eg I had taken backup between the dates 01/01/08 to 01/02/08. Now i want to restore it by specifying the date range as 10/01/08 to 20/01/08.

Date Range
[MySQL] Having unsuccessful attempts at returing records that fall within a given date range...

Given two fields of either DATE or DATETIME types: {startEvent, endEvent} = {2/7/2005, 2/7/2005}

SQL:
SELECT * FROM table WHERE startDate <= '2/7/2005' AND endDate >= '2/7/2005' ORDER BY eventID

This returns nothing. I can just do a startdate > '2/7/2005' and that returns the record - but obviously not what we're trying to do here.

This does work w/ MS Access though:

SELECT * FROM table WHERE startDate <= #2/7/2005# AND endDate >= #2/7/2005# ORDER BY eventID

Selecting A Range Of +10 And -10
I have a USPS zip code table that shows zip, city, state, latitude, and longitude. I hope to allow a person to enter a zip code to find other locations near them. While I can play around with formulas of latitude and longitude for proximity searches, I want to keep things simple by searching a range of zip codes (which are typically adjacent).

If I am searching for 10 zipcodes on either side of '64430', I could do...

SELECT *
FROM zipcodes
WHERE zipcodes.zip >= 64420
AND zipcodes.zip <= 64440;

Range Of Select
I'm creating a user friendly display of all the entries in the MySQL database for a client. There are going to be about 10,000 of them so I want to create predefined pages where she can view around 1000 at a time. I need to do something like :

SELECT * FROM info WHERE 1000 > 'Number > 1

I can't figure out the correct code to make that work though. I also need a way to make it so that it only selects entries that have a 'ATTR' field equaling 0 or 1.

Range Of RELEVANCE
Does anyone know in what range the relevance can be, when doing a fulltext search?

I would like to calculate a percentage out of the relavancy, and then the relevancy 1.489236 says nothing if I don´t know the largest possible value.

Date Range
There is probably an easy way to do this, but for the life of me I cant get it to work no matter what I try.I need to select data between two dates a table - this bit I can achieve no problem. However, my query is not that simple - the date rangeI need to select is always going to be between the current date and 7 days forward, i.e I want to select all data between and including the current date and 7 additional days.I presume I use the CURDATE() function to generate the current date, but how do I work out CURDATE + 7 days and then get the query to select all the data between and including those two dates?

SQL For Specifying Date Range Please
I have a table that has a date field in it, in the format of yyyy-mm-dd In my sql I want to select all records who have a date greater than (>) the current day (today). I tried this but to now avail

sql = "select * from specials where exp > 2006-06-28

and this

sql = "select * from specials where exp > '2006-06-28'

I am using asp for this and here is my table structure:

CREATE TABLE `specials` (
`id` int(10) NOT NULL auto_increment,
`title` varchar(100) NOT NULL default '',
`special` text character set ascii NOT NULL,
`exp` date NOT NULL default '0000-00-00',
`price` double NOT NULL default '0',
`image` varchar(50) NOT NULL default '',
`shop` int(4) NOT NULL default '0',
PRIMARY KEY (`id`)
)

Year Range
I am writing a document download system. In my database I have a From year, and a To year. These are the periods a particular document covers. For example, 2007-2008 or just 2007-2007.On my main page I want to list all documents that fall within the current year. So, I will need to list all documents that contain the year 2007 between the From and the To years....I hope this makes sense!I've tried doing:

"SELECT * FROM docs WHERE " & strcurrentyear & " YEAR(ffrom) BETWEEN YEAR(fto)"

Column Out Of Range
When I try to perform the following update on a table with a column of type "decimal(3,2)" I get the following error:
Code:

#1264 - Out of range value adjusted for column 'score' at row 1

The value that should be filling 'score' is "10.00". The SQL statment is from an exported MySQL 4.0.25 database that I am trying to execute with a MySQL 5.0.15 database.

I am aware of the changes made to the decimal datatype as of 5.0.3 but I can't figure why (3,2) wouldn't be enough to handle two digits to the left and two to the right of the decimal. What am I missing?

Out Of Range Value Adjusted
MySQL Error In: INSERT INTO `users` (`id`, `username`, `password`, `email`, `ip`, `last_activity`, `last_visit`, `time_reg`, `sig`, `pic`, `country`, `gender`, `activated`) VALUES ('', 'DarePoo', 'a36521ac6c5faa82a482fbe9015d1061', 'DarePoo.DareDoo@gmail.com', '68.84.122.60', '', '', '1166940539', 'No Quote', '', 'United States', 'Male', '0')
MySQL Error: Out of range value adjusted for column 'id' at row 1
MySQL Error Code: 1264
MySQL Error On: Dec 24 2006, 02:08 AM

That is the error I'm getting. The 'id' column is an auto increment and index field. I have no idea why this error happens. It is for my game. I lost my datebase file when I reformatted my computer, and just rebuilt the database from the game files, now I get this error.

Column Range
I have this simple question, let's say you select data from 2 different tables and those two tables both have a field named 'ID'.
Is there a way to get all the field of the two tables exept the second 'ID' field but using wildcard, for exemple to illustrate the concept:

SELECT table1.*, (table2.* AFTER table2.ID) WHERE year > '2004';

I know this command doesnt work but is there any way to do this?

Date Range
I have a database where the date is stored in 3 different collums month, day, year. A am trying to retrieve data by a date range something like "1-1-2005" - "1-1-2007" or something but had quite some difficulty in doing it but I found a way and I am not sure that it is the best way but it appears to work well. Let me know what you think.

Code:

"SELECT * FROM database " +
"WHERE datey*365+DAYOFYEAR(CONCAT(datey, '-', datem, '-', dated)) " +
"BETWEEN " + StartDate + " AND " +
"EndDate + " " +
"ORDER BY datey, datem, dated;"

NOTE: StartDate and EndDate use the same formula of (year*365)+dayofyear

Number Range
I have weight ranges of 100,300,500,1000. The user will enter the package weight in the form and the query will return the price per pound. How can I select the correct weight in the query??

$weight=400;
$getprice="SELECT * from priceMatrix
WHERE VENDOR='$vendorArray[$i]'
AND SERVICE=$serviceArray[$s]
AND WEIGHT<=$weight";

Maximum Range
maximum database in a server.
maximum tables in a database.
maximum rows in a table.
I have known about there is 60000 tables and 50 millions record in a server.
I use window XP sp_2 and mysql server 5.0.45.
Can i store image and file, and process them with my web application.

Date Range
This is somthing i've not worked with before so thought i'd come on here for a little help.
I have a database with a whole stack of records, one for each day which is generated by a script on the server.
I am giving my users the ability to choose a startDate and endDate for thier report to be built, i then want it to take all records from the database between those dates and Add the values of all the returned rows together and pass them as a single row back to me.
Is this somthing that can be achieved in MySQL? or will i have to just run a standard query to return the results and then have my SeverSide scripts so the math?

Date Range
I'm constructing a dynamic search page that will search for available facilities based on criteria entered. If the "reservable" box is checked, a set of date range fields will appear so that the user may see what facilities are available for a specific date range.My "event" table holds all reservations and events and so my query should check for a date range NOT in the event table, in order to see what is available. If it is not in the event table, then it is available to be reserved and should be returned in the search results.Syntactically, is there a way to do this or do I need to try using some sort of sub-query? Using (!BETWEEN) doesn't work and I can't find anything else regarding a date range that you DON'T want to view

REGEXP Range
i want to make a query that returns all the values that start from a letter

for example

search a-d
should return all the names that start from the letter a,b,c or d and so on

i have tried so far

SELECT *
FROM tbl_stores a
WHERE a.store_name
REGEXP '[[:<:]][a-d]'

Format Of Number Range
Rewriting my 1st MySQL database and need some help.
I am making a shipping cost table based on weight ranges.
How is a number range, say 1-250 represented in a database?
None of my documentation appears to address this and a (quick) search on the forum turned up nothing. (Probably as I had no idea what to search for )

How To Delete A Range Of Records
i want to delete a range of emp records from a single query.
i.e from 0003 to 0028

Please help, currently i use a for loop in php. but i though there should be a single line query.

SQL Date Range Query
I need some help with this one. I hate admitting defeat but I'm getting frustrated!

I've got a table called 'Events' with these fields...

eID
eName
eStartDate
eEndDate

An example entry would be...

1
'Course Open Day'
12-Nov-2006
14-Nov-2006

What I want to be able to do is search the table and select any records where a given date falls between the the startDate and endDate.

So, if I searched with ཉ-Nov-2006' i'd get 'Course Open Day'.

Case Statement: Where Value In Range
I've got a table with around 40000 records. There's a column called v_pop with a range of 2 - 1000. I'd like to color code some HTML but use the query to do that work. So check out the code below. It runs just fine, with no errors, BUT, it only returns white and pink as the class, even though I know for a fact that there are records with higher values. What am I doing wrong here?

I'm running mySQL 4.1.17

PHP

SELECT v_player,
CASE
 WHEN v_pop < 49 THEN 'white'
 WHEN v_pop > 50 < 99 THEN 'pink'
 WHEN v_pop > 100 < 199 THEN 'salmon'
 WHEN v_pop > 200 < 299 THEN 'yellow'
 WHEN v_pop > 300 < 299 THEN 'orange'
 WHEN v_pop > 400 < 399 THEN 'red'
 WHEN v_pop > 500 < 499 THEN 'brick'
END AS class
FROM x_world

Is That Possible To Select Range Of Rows?
I use simple query right now to select rows from database:

SELECT * FROM cities WHERE country = '$country' ORDER BY city

But what if I need to select not all rows (from 1st to 800th, for example), but only rows from 70th to 140th? Is that possible to select this range with MySQL query?

Datetime & Date Range
MySQL version is 5.0.24 and database engine is MyISAM, the field "algus" in table ajad has the type "datetime".

And here is my problem:

Select Date Range
I need a SELECT query that will select records from the past 12 weeks and one that will SELECT records from the past 12 months. the 'compdate' field is a DATETIME field. I have gotten it so far to select records from the same month, and the same year.

All form current week: "SELECT * FROM table2 WHERE MONTH(compdate)=$month AND WEEK(compdate,1)=$week ORDER BY id DESC"

All from current month: "SELECT * FROM table2 WHERE MONTH(compdate)=$month AND WEEK(compdate,1)=$week ORDER BY id DESC"

$month is date("m") in php
$week is date("W") in php

Getting A Range Of Sorted Data
How to get a a range of sorted data.

If I only want the 900.-1000. row of the sorted rows, then how can I do this?

SELECT * FROM data ORDER BY age DESC LIMIT 1000
and then with a while() cycle I get the 900. - 1000. rows. That seems pointless, because the 1. - 800. rows just waste the memory.

Select Range Of Dates
How can I select a continuous range of dates? For example If i want all of the dates between Jan 01 2007 and Apr 02 2007 to be listed as:

jan-01-2007
Jan-02-2007
jan-03-2007
..
Apr-01-2007
Apr-02-2007

What Select statement could I use? The date format is not important.

Date Range Request
Dates are stored in unix format GMT. I want to find all records either by "day" or by A Date Range "week" i have to deal with users seeing data in their time zone so people in GMT +9 would see records posted accordingly to someone in say GMT -8? Code:

Select Range Between Two Fields
Is there an SQL way of selecting all the values between and including two integer fields that are in a single given record? For example, if one is 1957 and the other is 1965, I need all years between and including those two. I know I can do it using PHP or other programming but I am trying to come up with an SQL way, if one exists. Code:

Date Range Logic
I attempting to returing records that fall within a given date range and any overlapping dates:

For example:
input parameters are:
startDate= 2005-10-31
endDate = 2005-11-30

If I have the following data in my table:

cust dateBeg dateEnd
------------------------
1 2005-10-27 2005-11-15
2 2005-11-1 2005-11-30
3 2005-11-20 2005-12-20
4 2005-12-1 2005-12-10

I want to retrieve cust 1,2,3, but not 4
So far this logic retrieve all dates that fall in between

(dateBeg >= startDate and
dateEnd <= endDate)

How can I include ovelapping dates?

DECIMAL - Out Of Range Value Error
If I:

CREATE TABLE testdec ( x DECIMAL(8,8) );
INSERT INTO testdec (x) VALUES (19.14);

I get the error:

ERROR 1264 (22003): Out of range value adjusted for column 'x' at row 1

same error if I create the table with

CREATE TABLE testdec ( x DECIMAL(8,7) );

however the insert works if i create the table with:

CREATE TABLE testdec ( x DECIMAL(8,6) );

(or if I use a lower value for D than 6)

Can someone explain why this is? When using DECIMAL(M,D) do I have to keep D to less than M-1 ?

Sum Of Groups Within Date Range
I have the standard orders table, with the amount of an order, the order id, the customer id, and the date. What I now need is the ability to group the order totals by quarter. So give customer id, I need to be able to display how much they spent between each quarter.

Is this possible without running the same sql for each quarter?

GROUP BY IP Address Range
I store IP addresses using the INET_ATON function, e.g.

INSERT INTO myTable(IP_Address) Values (INET_ATON(123.123.33.12));

I would like to create a daily report that groups IP addresses by a "dotted
triad", e.g.

(pseudo SQL) SELECT myTable.IP_Address FROM myTable GROUP BY
123.123.33.*;

Any ideas on the best way to go about this?

How To Select An Hour Range
I am trying to select a list of values within a certain hour range for the current month but my query returns no results.

SELECT time,ip,input,output,DATE_FORMAT(time,"%d-%m-%Y") as date, DATE_FORMAT(time,"%k") as hour FROM traffic WHERE hour>=7 ORDER BY time DESC,ip;

Am I not doing this right? Is there some way I can select a wildcard in the date part of the datetime field and put the hour range in there. For example:

WHERE time>='* 07:00:00'

SELECT - Specifying A Range Of Values
Is there a shorthand way of specifying a range of values in a SELECT statement? Given

SELECT this WHERE bla >= 1 AND bla <= 100

I'm wondering if it could be written something like:

SELECT this WHERE bla 1..100

Parameter Index 3 Is Out Of Range
i am doing my thesis project and got really mad with the errors as following when i run my jnuit test:

com.ibm.db2.jcc.b.SqlException: Invalid argument: parameter index 3 is out of range.

my sql query is:

private static final String DEPTH_QUERY = "select count(distinct sender), count(id), lw_thread_tree.depth as d "
+ "from DB2INFO5.lw_entries , DB2INFO5.lw_thread_tree where id = mail and id = ? or top_mess = ?";

my source codes are:
private boolean isCommunicativeConversation(int id, int postingMemberCount) {
boolean isCommunicativeConversation = false;

Connection c = DbConnection.getConnection();
if (c != null) {
try {
PreparedStatement p = c.prepareStatement(COMPARE_QUERY);
if (p != null) {
p.setInt(1, id);
p.setInt(2, id); .....

Select A Range Of Data
Right now i am working on a user management system. I have a page that lists all of the users, but i only want it to show 20 users at a time with a link to go forward and backwards to see more users. How do I select a range of data from a mysql table? For example how would I output users 21-40?

$newest2 = @mysql_query("select * from users order by id asc");
echo "Members: ";
$a = 0;
while($user = @mysql_fetch_array($newest2))
{
$a++;
echo "$a [ <a href='index.php?p=profiles&uid=$user[id]'>$user[username]</a> ] ";
}

I have a feeling that I am missing something that couldnt be more obvious.

Searching A Range

I have a database where the customer wants to be able to find a price range (she has entered a definite price for each item)... how do I set it up for that sort of search?