MYSQL :: Query Search Direction

How To Use LIMIT And Show Results In Opposite Direction?
I've got a table like this:

-------------------
| id | data | exp |
-------------------
1 ab 123
2 ve 156
3 fe 204
4 fg 256
... .. ...
n dc 486

in a database that every 15 minutes add some lines at the end of table. The exp field is the only one that increase gradually. Suppose I've got 2800 lines at a certain moment of the day and I want to take just the last 96 from the min value to max, how can I do? I've tried with "ORDER BY exp DESC LIMIT 96" but I need last 96 lines in ASC order.

Date Search Query Help
I'm trying to filter my search results by date. The field name I'm running this for is final_date and is a DATE field.

I want my results to show records that have sales_reps.final_date within the past 30 days.

Query:

SELECT
sales_reps.sr_id,
sales_reps.name,
sales_reps.job_number,
sales_reps.status,
sales_reps.stage,
UNIX_TIMESTAMP(sales_reps.final_date) as final_date,
UNIX_TIMESTAMP(sales_reps.date_to_shop) as date_to_shop,
shop_orders.community,
users.user_id,
u.fname,
u.lname,
users.builder,
users.division,
ei.shop_result,
ei.good_tape,
sa.shopper_id,
shop_orders.order_id,
UNIX_TIMESTAMP(shop_orders.date) as order_date,
pi.fname as pfname,
pi.lname as plname ,
ei.exit_id
FROM sales_reps
LEFT JOIN shop_orders ON sales_reps.order_id=shop_orders.order_id
LEFT JOIN users ON users.user_id=shop_orders.builder_id
LEFT JOIN report_types ON report_types.type_id=sales_reps.report_type
LEFT JOIN shop_assignments as sa ON sa.sr_id=sales_reps.sr_id
LEFT JOIN exit_interviews as ei ON ei.shop_id=sa.shop_id
LEFT JOIN users as u ON u.user_id=sa.shopper_id
LEFT JOIN users as pi ON pi.user_id=sa.pi_id
WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <=UNIX_TIMESTAMP(sales_reps.final_date)
AND (sales_reps.status = 'Completed' OR sales_reps.status = 'Rejected')
ORDER BY users.builder, users.division, community ASC, final_date ASC
Is this:

WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <=UNIX_TIMESTAMP(sales_reps.final_date)
incorrect?

I'm getting dates such as 2007-03-22 and 2007-03-19 in my result set and they shouldn't be there.

Could I get some pointers?

Correct Search Query
Can anyone help me out with this please. I am looking at searching among comma delimited id's stored inside "IDs", trying to figure out what the correct SQL query for that would be. I need to extract the ID where IDs contains the value 7. I know I can't use something like "WHERE IDs LIKE '%7%'" because the result would be inaccurate. So how do I go about it, please? Any directions would be appreciated, thanks!

+------+-----------+
| ID | IDs |
+------+-----------+
| 1 | 3,17,20 |
+------+-----------+
| 2 | 1,7,9,12 |
+------+-----------+

Search Database Query
I am looking for a query that will search all of my database without having to use all the individual table names and fields as there are 6 tables and over 30 fields in the database is there any special expression for this the search criteria would only be one word.

Mysql Search Query
i would like to write a query to seach for the related information to display.

let say the user key in the word "sql", and in one of my keyword field i have data such as "mysql, database, java" and i want to call this row out, how can i do this?

Search Query By Date
I'm trying to adjust an older search query to perform a search by date where the date column is an INT field (unix date stamp).

SELECT * FROM incidents WHERE date < DATE_ADD (CURDATE(), INTERVAL $searchDate DAY);

Where date, formally a DATE field, is now an INT(11) field. $searchDate is an int, indicating how many days back to search. 1 (day), 5 (days), etc.I'm assuming mysql's DATE_ADD function is specific to the DATE field. How can I achieve the same result, but with an INT field?

MySQL Search Query
I have two tables as follows:

table 'question' with fields 'patient_id', 'answer_id' and 'terms', and

table 'answer' with fields 'answer_id' and 'answer_path'.

I need to search question.terms for a keyword match and then join that match to the corresponding foreign key (answer_id) in table answer to return 'answer_path'.

[PHP, MySQL, FLASH] - Search Query
just a quick question what would be the best way to search a database, how should i query it? i have this so far:

<?
$name=$_GET['name'];
$company=$_GET['company'];
$address=$_GET['address'];
$phone=$_GET['phone'];
$email=$_GET['email'];
$web=$_GET['web'];

mysql_pconnect("host","random","random") or die ("didn't connect to mysql");
mysql_select_db("database") or die ("no database");

$query = "SELECT * FROM table_name WHERE Name, Company = '$name' AND '$company'";
$result = mysql_query( $query ) or die ("didn't query");

$num = mysql_num_rows( $result );
//echo ($num);
if ($num == 1){
while ($line=mysql_fetch_array($result)) {
$profile = "name_show=" . $line['Name'];
$profile = "company_show=" . $line['Company'];
//$profile = "address_show=" . $line['Address'];
//$profile = "phone_show=" . $line['Phone'];
//$profile = "email_show=" . $line['Email'];
//$profile = "web_show=" . $line['Web'];

}
print $profile;
} else {
print "error=Sorry, but I can't show results";
}
?>

it works but not very dynamically, when both companys are commented out in the while and query statements.

What i hope to have is a search where users can type in 1 letter and it will give them a result with all records having that letter in them and just by filling in one item in flash instead of all of them in order to begin the search.

Incorporate OR Operator In Search Query
I have 2 tables, one with restaurants and one with US centroids, latitudes longitudes, zipcodes for varius central cities in the us.

I have a query that I got working, that uses these 2 tables, it selects all restaurant data from table Restaurants city/state/zip... where the restaurant zipcodes are within the dynamic radius specified of the zipcode(centroid) specified.

This works fine, It also returns the distance as well.

I would also like to select restaurants records regardless if the the distance between zipcodes only IF the Restaurant City matched the CIty I pass to the query. I tried adding an OR operator but my query hung.

Here is my query:

Query A Thesaurus For Search Terms?
I was wondering what the best way is to expand on user-inputted search terms.

For instance, when conducting a search, I would like to ALSO query all synonyms and related words to the inputted search terms.

The database my script searches is very large, so inputting my own related words and synonyms alongside every searched value in the database would be a real pain in the neck if I had to go that route.

Is there some sort of downloadable database of all words and their relations I could use?

Query Search In Different Table Columns
I'm trying to set up an sql-query to find a value in the table, which can exist in different columns......

Tire Size Search Query
I have a table of tire sizes... example: 245/40 YR18, 245/40 W18
I need to search through that table, and pick out sizes based on a search that would like like this: P245/40ZR18
I want to find tires based on the 'P245/40' and '18'... any combination of letters between the two are fine....
seems like a regular expression seach?

Query To Search Multiple Fields
I have a table with over 50 columns that contain yes/no values. I want to know if there is a way to write a query to get all fields that have a value of "no".

SQL Query Problems (for Use With A Sort Of Live Search)
I created some code that loaded information from an XML file, but many thanks to jimfraser on here I'm working on a solution that'll drag it directly from the database.

Problem is, it's not displaying anything at all, so I assume there's a problem with the SQL query or the way it's being presented on the page.
PHP
<?php

$network = new COM("WScript.Network");
include_once('../../../functions/datalib.php');

$db = new oracleClass();
$conn = $db->connect();

//get the q parameter from URL
$q=$_GET["q"];

//lookup all links from the xml file if length of q>0
if (strlen($q) > 0)
{
$hint="";

// connect to the database
$sqlstatement = 'select * from TEST."WFO_REPORTS_ALLEMPS" order by "Surname", "Forenames"'
$sqlListings = OCI_Parse($conn, $sqlstatement);

OCI_Execute($sqlListings);
while(OCI_Fetch($sqlListings)) {

$name = oci_result($sqlListings, "Forenames") . ($sqlListings, "Surname")

//find a link matching the search text
if (strchr($name,$q))
{
if ($hint != "") {
$hint .= "<br />";
}

$hint .= "<a href='#' onclick='employee_form.Line_Manager_Staff_No.value="
. oci_result($sqlListings, "Gc_Staff_Number")
. ";checknumber('Line_Manager_Staff_No','displayusername');'>"
. oci_result($sqlListings, "Surname") . ", " . oci_result($sqlListings, "Forenames")
. "</a>";
}
}
}

// Set output to "no suggestion" if no hint were found
if ($hint == "")
{
$response="No user found.";
}
else
{
$response=$hint;
}

//output the response
echo $response;
?>

On a side note, is there a way I can change it so that the search will present any users based on their first name, as well as the surname?

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 Search And Retrieve File For Download
I am new to web development and have this challenge. I want to design a dynamic website for a college community using PHP and MYSQL. The objective of the site is to hold the annual report of all the departments such that visitors can select the desired department and the year of the report. The years could stretch back to as long as the college existed but I want to limit the backdate to 10 years for now. The visitor should be able to select a department and desired year, view the report online and also be able to download the same report stored in a directory in ms word or PDF format. What should the table structure look like? Also the PHP and SQL script to search for the stored files and make them available for download.

SELECT Search Query - Table Join Required? Help Please!! (PHP + MySQL)
I have a search form that has:
- drop down with states (nsw, vic etc)
- drop down with all business categories (retail, commercial etc)
- keyword / postcode field (2000, or 'builders')

The user gets results returned from the business table filtered by state (mandatory), which category is selected (mandatory) and by keyword (optional) or postcode (optional).

If keyword / search phrase is given then it will do search of the keywords fields of the business table (has already been indexed) in the selected category only. If postcode it will return all businesses in that category in order of distance from the given postcode.

I have 5 tables (additional fields ommitted):

'state'
state_id, name

'businesses'
business_id, keywords, name, postcode, state

'postcodes'
fromPostcode, toPostcode, distanceKMS

'categories'
category_id, name

'business2category'
business_id, category_id

Please dont thing i'm just pawning off my work here!!
Basically, i've got this working already, but only just, and in a very long and convulted format. Its far too long to post here, but since i'm not fully versed in table joins, i've been searching individual tables (e.g. SELECT *,MATCH AGAINST etc), building arrays, searching arrays again, and then building results at the end to fit into the paginator. Now the search is taking too long to perform, and i need a leaner alternative. not to mention theres way too many lines of code, and i just know theres a better way.

There must be a very simple way to achieve the following searches using table joins, can anybody please help me with 3 search examples below so i can try to understand joins better?

Search 1:
State + Category only

Search 2:
State + Category + Keyword

Search 2:
State + Category + Postcode

If there is no postcode, the others still need to display the data
filtered by distance from a default postcode of 2000.

I'd really appreciate if anybody has a few minutes free to help out here, and hopefully teach me something about effective table joins and searching. I've omitted the extra fields and tables from the real structure, and just left the relevant ones above - if theres anyhing missing or not making sense please let me know and i'll fix up asap.

Search: Remove Quotes From Database For Search
I am making a book site and currently in the database are book titles likes "John's Trip" and "Peter's new bike". I have a search function where the customer can type in "Peter's" and it will bring up everything with "Peter's" in it.

But i was wonderind is there any way i can search for "Peters" and "Johns".

I Have A Search Form To Search Through The DB.
I have a DB with 50,000 entries. I have a search form to search through the DB. If the recno=30123456 and you search the recno, you get that one file. what if i want a query that searches alll recno's that contain 30?? Can I do that using mysql or do I need to program that in my java app?

How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy!

I can make a simple single query using MYSQL Query Browser, say:

qry1: SELECT ID, Area FROM data GROUP BY Area

How can I store this as a query inside MYSQL, rather than having to code it each time?

In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query:

qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE

How can I store this as a query and then pass the variable from code?

In Microsoft Access I could base a query on the results of another query, so following example above:

qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area;

How can I store this as a query in MYSQL.

Best Way To Search
I am a little confused on the best way to search a mySQL database with PHP.

Which is better?
1. Search with LIKE and a trimmed term

SELECT * FROM tablename WHERE field1 LIKE "%$trimm%" OR field2 LIKE "%$trimm%" OR field3 LIKE "%$trimm%" ORDER BY field1 DESC
2. Using mySQL's 'match against'

Search WHERE SUM
What would be the proper way of generating a query like the following;

SELECT id FROM sales WHERE SUM( totalsales) <= '20000' GROUP BY id;

Assuming the table looks like this;
Sales:

Id| totalsales| monthyear
1 | 100| 2003-09
1 | 10000| 2003-08
1 | 2000| 2003-07
2 | 30000| 2003-05
3 | 10000| 2003-06
3 | 10000| 2003-05

I want the query to return id 1 and 2 as the sum of their totalsales is
smaller then 20000.

DB Search
I am working a search tool for users to find members based on First Character of lastname.
How can I have mysql or PHP only look at the first character of the string?

Search
I have an ASP page that searches a database. Although the entire database is searched, I use a filter on certain fields. The below filter (I could only use a little bit for the post to work)is for a field [LEDef_ActiveInd] that has either the number 1 or 2 or the text of true or false and it works and returns all "LEDef_ActiveInd" that carries the value of true.
..strQS = ..strL & "...LEDef_ActiveInd+true nd all+" & Trim(strR)

What I would like to do is also filter a field [LEDef_Cd] which has text data such as "10-Name" or "40-Name", etc. I need a filter/code that would return the results of all the "10-Names".

My SQL Search
I'm currently using the following mysql statement to perform a search. Right now it is only returning the results which are found in the description field. How do I modify this statement so that it is searching

the following fields: company_name, product_name, description.

SELECT product_id, company_name, product_name, url, description
FROM product, company
WHERE product.product_id = company.company_id AND description regexp
'#FORM.query#

Search
i have several tables. they have some fields in common, but some different ones.

table 1

id author title journal year
1 brown java computing 2005
2 black sql database 2000

table 2

id author title publisher year
1 brown c++ pen 2006

how do i search both tables for i.e. author ? the author can exist in only the one table or both. so if i search brown i get:

id author title journal year
1 brown java computing 2005

id author title publisher year
1 brown c++ pen 2006

if i search black i just get :

id author title publisher year
2 black sql database 2000

Search
I have a search box on my site which works fine but I would like to improve. at the moment it will not return a result for a two word search unless those words are adjacent ie red box but not for return for red big box I would like to make it give results for multiple word searches when the:

words are found
In this order

all words in search adjacent
all words in search (not neccesarily adjacent)
any of the words

I assume the following tells it what to look for and how to display results (I hope so any way) Code:

Search Box
I am trying to have a search box on every web page in the site - like everybody seems to have these days, anyway, according to DW help the search page results holds the key to how it searches the database, but the trouble I am having is what to put in the sql box for the query?

I know SELECT * FROM table will select everything and I know SELECT * FROM table WHERE location = 'town' ORDER BY price etc,etc. But what I need to know is how to have the results page display one set of results if a user enters one topic in the search box and another set of results if something else is entered. And if there is nothing to display, how do you configure that?

SQL To Use For Search
I am creating a script that searches the table "sites" in the database "actionhostsearch".

I have a column in my table called "keywords" and i want to check that against my entered text, I am currently using LIKE but don't get a good result, is there a way I can refine it?

The keywords are space separated, but i want it to search like multiple keywords. Is there anyway of doing this or would i have to separate the keywords out using php?

Search
I've heard that a friend searches by using a "like" querywith a select... But i searched in the mysql manual, and gcoudlnt' find anything on it... What are some other methods of searching in mysql databases?

SQL Search
I am migrating my site over to mysql and was wondering how I can implement my same type of search into the territory of mysql. Basically this is how the search worked for the db files (terribly inefficient but it worked well). Spliting each line at the deliminator, it would match the query against 10 different values and depending on the value being matched if a match was found the item would get a score. Each column had a higher score depending on which was most important. I basically used a regex expression to count the matches and multiply it by the matchscore. Is there anyway to use LIKE and find out how many times it matched? Can I use REGEXP and get a match count with the global switch?

Using LIKE In A Search
First off, I apologize if I am in the wrong forum.

My question is kind of simple, if I use "SELECT from INVENTORY WHERE item_desc like '%blue%' OR item item_desc like '%stand%';

can i assume that LIKE is "not" case sensative?????
so far that is the case, however, I can't find where in the documention to make sure of it. doing a search for LIKE in the documention is just ever so special.
so i want to ask people who know. is LIKE case sensitive, or not?

Search EVERYTHING For ANYTHING
I got a database here with multiple rows/columns inside.
Example:

I have columns date, text1, text2, text3, and so on...

Select * from db where "%" = "1969"

So I wanna search EVERYTHING in the db for ANYTHING that says 1969. Whats the query to do this?

Search?
I builded a little propert database. I added a search function to the site
but have the following problem:
I used "SELECT *
FROM table1 WHERE area ='province' and type like'%type_1%' and town
like'%town_1%' and descr like '%descr_1%' and suburb like '%sub_1%'"
But it do not do the like thing. If I search for 3 bed and the actual data
is 3 bedroom it gets the data, but if I search for 3 bedrooms (extra s) it
do not get it or if I make spelling mistake I do not get a result either. So
it seems that it rather search for the exact term instead of the like term.
How can I get it to really search for only a phrase, part of a word or a
spelling error?

Using LIKE To Search
I have been using the following to search:

WHERE FirstName LIKE '%$firstName%'
AND LastName LIKE '%$lastName%'
AND Email LIKE '%$email%'
AND EmployerLocationName LIKE '%$venue%'
AND State LIKE '%$state%'
AND MemberApproved LIKE '%$status%'
AND tblmanager.mngNBR LIKE '%$mng%'

It has been working well but the only thing I realised with the mngNBR, if the user selects say 'Joe Bloggs' and in the database he is number '2', it also outputs all the results of other managers who have number '2'... so Matt Smith '22', John Smith '24'... I'm assuming this is because I have used like but is there any other way I can do it?

Search Using LIKE
I need to pull all records where the First letter of each word in the record begins with the entered variable. Here's my query:
CODE"SELECT * FROM hgs_contacts WHERE name LIKE '".$_GET['l']."%'";

Search Between A - J
I been looking a ways to do this but I can't seem to do it or get my head around it.
I want to basically search my table for all "titles" begining from A - J.
I want the SQL to check the first letter of the "title" and if the first letter starts with any letter between A - J I want it to be displayed.
How would I do this? So far I have:

Code:

SELECT title
FROM pmc_keyclients
WHERE LEFT(key_title, 1) = 'A'

But this let me search BETWEEN latters

Search By Number
I have a question regarding a mySQl search

Basically I have a list of numbers stored in a field in my database representing different features of a venue

For example 1,16,17,10,8,13,

Now I want to give my client the ability to look for specific venues with a specific feature by typing in the number for the feature they are looking for, ie if they want to find all venues with a bar and 1 = bar then they enter 1 into the search and are presented with all the venues with a bar

Now my problem is if I do a search for Ƈ' using LIKE then and a wild card (ie %1%) I will get all the features that have a 1 (ike 16, 17, 10, 13). Is there anyway I can write a search that will only return venues that just have the number 1?

Also the numbers can appear in ANY order so it might be 10,8,26,1,32 ....

Search Index
I have text stored in a database that I'd like to be searchable. However, searching through long strings and blobs seem like a very inefficient way of doing things.

In MS Server there's an index server that will take all strings and create indexes out of words. This can significantly speed up text searches.

Is there such a feature or tool for MySQL?

Fulltext Search
Below code is from a CMS's search script. When you search for plain words like Britney Spears it displays the results correctly. But when you search for a URL like http://www.sitepoint.com then it does not return any results.
Is there anything wrong with the following SQL code?
...
$this->sql = "SELECT story_id, story_date WHERE $where $sterms ";
...
$where = " MATCH (story_title, story_url, story_tags, story_content) AGAINST ('$words') ";

Note: story_url stores URLs exactly as http://www.sitepoint.com not encoding etc.
story_url field is FULLTEXT same as others.

Search On Dates?
I have 2,000 stories saved in a database (Not my DB) and I want to perform a search on the section of story and the date it appeared on the website.

The trouble is the DB is dodgy and dates are saved in a format like:
2007-1-8 //Today
Ie
YYYY-M-D

Now what I want to do is perform a MySQL statement whereby I perform a search on the date in this format:

2007-1-8 //Today
Ie
YYYY-M-D

IE: "Y-n-j"

And then my SQL finds the date I asked for, but then

- Checks itâ€™s not greater than todayâ€™s date (NOW) by using unix_timestamp on both the appreared field and published field.

And then output all the results to the screen.

Now I thought this may work:

PHP

$sql = "select id,headline,appeared from news_stories
where section = '$searchstring' AND
date_format('$appeared','%Y-%m-%d') =
date_format(appeared,'%Y-%m-%d') AND unix_timestamp(published) <=
unix_timestamp(now())";

Search Functionality
When a website adds search functionality, more so than bulletin boards, but take the Facebook for example (not as complex as what they do but a good example, and the only i can think of at the moment). You can do a basic search for 'John' and the results returned will contain people named John, it will contain people who date a John, anyone who remotely has 'John' show up in any field in their profile.

Is the query for this simply a matter of a large amount of OR statements is there something else to it?

How To Search '+','-' Or '(' In Database
How to search " +, - , ( " signs in mysql database....

Search & Replace?
Newbie here.

I have a DB with two tables.

Table 1 has three fields - a unique ID#, a first name (i.e - John), and a last name (i.e. - Smith).

Table 2 had many fields, one of them being a first name/last name combination (i.e. John Smith).

I want to replace the first/last combo field in table 2 with the unique ID# from table 1 which matches the concat of the first name field and the last name field from table 1.

I know I can do it by hand, but there are thousands of them in table 2 that need to be replaced and the data in table 2 is in the format that I will be receiving from an outside source, so I will need to search & replace this data on a regular basis.

Help With REGEXP Search
I have a database of English words. This thing is huge. Which means that when I do a search like

SELECT word
FROM wordlist
WHERE word LIKE 'k__p'
I get almost 300 results. What I want to do is limit my results to only those words with a repeated letter taking up that middle spot, e.g. "keep" but not "kelp".

Is there any way to do this in a MySQL query, or am I stuck doing this in my application (which is what I'm currently doing)? I've tried some REGEXP tricks I know, but MySQL REGEXP apparently doesn't have capturing subpatterns or backreferencing.

Search-function
Does anyone happen to know how to select info from the database based on multiple strings like so:

SELECT title FROM food WHERE title LIKE '%New%' AND WHERE title LIKE '%York%' AND WHERE title LIKE '%restaurant%'

Add Search By Tags.
I have this setup where in 1 table i've got the content and name, and I currently have a search for them, but I also have a tags table that i would want to search, then return not the tag, but rather the row from table a associated with it.

HTML
TABLE 1 - content
id name content

TABLE 2 - tags
id content_id tag
I have 2 problems. a) if i do "SELECT content_id FROM tags ..." and i get a list of all the matching id's, how to i write a query to return them all? and isn't there a more efficiant way of doing it? like if the search result returns like 200 id's, isn't calling them one by one too much.

Another problem i'm having is that sometimes one row of content might have more then 1 tag that match the search criteria, so it will end up being called twice. how would i prevent that?

Query Search Direction

It's possible to tell mysql to start searching rows from highers indexes to lowers?