MYSQL :: Search WHERE SUM

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?

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?

Search On Keywords
I have a table which stores keywords as a comma separated list. The records are like

1) Ad Name | car rentals, car advertising
2) Ad Name | car, bmw, bike

I would want to search for 'car' and would like to have only the second record listed and not the first record. But our usual query where keyword like '%car%' wouldn't work here. Is it possible to split these keywords in the query itself and then make it work?

Problem With Search ( Need Help )
Here, i'm having one record in database with rc_title field:

and it have the value:

rc_title: abcdef

so then, i want to make the query to search out this record with this keyword:

abcdefgh

so it can be true?

Search And Purge
Just wondering if there is an easy way to search and purge a pattern from a set of results?

I have inheirited a rather large database with a crappy front end. As a result I have a bunch on <br> from users hitting enter in the entery field.

I want to erase them. I can run a query that returns all the results but I am not too sure how to edit them.

Search And Replace
I want to replace a string inside a text field named "text".

Here is a query which would show the records which contain the offending code which I want to strip out:

SELECT * FROM articlestable where text like Concat('%', '<b class=titler>', '%', '</b>', '%')

Fulltext Search - Doc, Xls, Pdf
make indexing for fulltext from formats in:

1) *.doc
2) *.xls
3) *.html
4) *.pdf

Employee Search
Splitz General Marketing - Employee Search

Seeking employed/unemployed persons for a part-time positon. Must be hard working and willing to learn. Good paychecks, allowances and higher job opputunites are applicable to this position. If you are interested in this job offer please contact us for more information. Subject to our satisfaction with your resume you'll be filed in. Principals only. Recruiters, please don't contact this job poster.

Search Using 2 Tables
ive 2 tables: static & new and both with the same structure and indexing - these work:

$query = "SELECT lat, lng, html, label, icontype, display, advert, banner, banner2 FROM static WHERE MATCH(display) AGAINST ('" . stripslashes (str_replace (""", """, ($_GET['name']))) . "' IN BOOLEAN MODE) ORDER BY label ASC ";

if i change to "new" then this works:

$query = "SELECT lat, lng, html, label, icontype, display, advert, banner, banner2 FROM new WHERE MATCH(display) AGAINST ('" . stripslashes (str_replace (""", """, ($_GET['name']))) . "' IN BOOLEAN MODE) ORDER BY label ASC ";

if however i change it to FROM static,new - nothing happens

Search A SET Column?
I have a table with two columns, the first column is of type VARCHAR and the second of type SET.

column1(VARCHAR)column2(SET)
person1group1
person2group2
person3group1, group2

I want to retrieve everyone in "group1"...

> SELECT * FROM mytable WHERE column2 = 'group1';

Which returns only person1 and not person1 and persom3 as I want.

I have searched through the manual and found several ways to search for data matching several criteria (ANY, IN, SOME, ALL) but not "the other way around".

I guess what I'm really asking is how do I search within a column of type SET?

Search Engine
I have made one database table with embeded videos:
Fields:
Title, Description, Image(img src=thum from Youtube), Video ( embed from Youtube).

Also I made one search script where it search trought Title with LIKE and outputs only: Title and Image as result. Now, when visitor on my page click on Image the script need to display the rest of the database: Description and Embed Video.

So, my problem is in that I don't know how to link the Title to the rest of the content from that row in my database table.

Fulltext Search
Does the fulltext search is a good way to find words in a table which
contains more than 500'000 rows, moreover it will search in blob
field.

What's your opinion about fulltext in big table ?

How To Search For A Strings
I have a table with a field name as course and another as subject the field contains data like this:

Record 1
Course='M.D.-PHARMACY'
Subject='APPLIED SCIENCES'

Record 2
Course='M.S.-GENERAL SURGERY'
Subject='APPLIED BASIC SCIENCE'

I use the following

select * from tablename where fname='".$xcourse."' and subject='".$xsub."' order by year desc;

Where I have a course with two words ie. 'M.S.-GENERAL SURGERY' , no records are selected.

Whereas for course ='M.D.-PHARMACY' works fine.

SQL Search From 2 Or More Tables
i have member, nonmember & non_ind_member table plus a client table.

how can i do a select statement to join member & client, nonmember & client, and non_ind_member & client and retrieve the members' code, members' name and clients' name?

i want to get 4 similar fields from the 3 tables each, where the clientCode on the 3 members tables is a foreign key to clientCode on client table.

now i can only get from 1 table. how to get from 3 more tables?

Fulltext Search
Given a search string of 'NA&SD' my default Fulltext search doesn't find it. Wondered why? Is there a fix?

Special Search
I have have upcids that were stored without 1st and last character and some have the hyphen at a point in the middle. Some have a plus sing at the beginning.

I'd like to know of a way or steps to pull the data up meeting proper 12 digit UPC-A format as one nice number, no '+' or '-'.

Or at least a way to filter out the characters I don't want and add those I do at the proper place.

Search Engine
I am looking for information on how to use MySQL to build a small search engine for my website. Does anyone know of a website, book, or know how to do this please?

Search From 2 Tables
I have 2 tables, A and B. Both have these field names 'Name' and 'Password'. Now I would like to combine A and B together so that I can check whether the password and name a user has entered is valid, regardless in A or B.

Next, I want to know in which table the matching occurs, in A or B, if the entry is valid.

So lets say if the entry entered matched an entry in B, I will know this result:
username and password valid in table B. I know I can do this separately, but just wonder can I do this all in one step.

Plural Search
Can anyone enlighten me on how to include plural searches with a
fulltext mysql database search?

For example, if someone searches 'restaurant' I want it to search the
database for 'restaurant' and 'restaurants'

Getting A Pic To Come Up From With A Search Field
ive got a news table in my database. its got a field where i input a word such as car name e.g ford!

now i also have a pictures table where i store the name of the picture e.g ford1.jpeg

what i want to do is basically bring the news on my page up and also the picture next to it! BUT! i want to do get the data in my search field and then search my pictures table for matching data! so if i have ford in my search field i want ford1.jpeg to come up! does that make sense? i want a ford picture to come up even though the name of the file is ford1.jpg.

News table:

header
body
search_word

pictures:
file_id
file_name

Text Search...
I have a table "general" that contains some columns. I want to do a search thru
it and find posts that have a specific word in the title and text column.

This is the query that I use that creates an error.

$Query = "SELECT * FROM general WHERE MATCH(title,text)
AGAINST('$searchstring')";

This is just modified SQL from the manual on www.mysql.com

Any hints?

Specific Search
I'm creating a PHP application in which I need to do a specific search that I'm not being able to formulate it. On my database, I have a table that register different organizations. Each organization has a primary key called org_id, which is an integer auto_increment.

Well let's say I want to create a search in which I want only specific organizations to show up. Let's say I have 100 organizations registered, and I want to search for organizations with org_id 1, 4, 7, 15, 33, 45 and 85. Well, how do I write a MySQL code that will return these organizations, only these organizations, nothing else?