MYSQL :: Search On Several Conditions

LIKE Conditions
Basically I have a table, with a column which holds a list of related tags separated by spaces. The user can search for a row using a key word that will search this column (and the column named header too) and return if the keyword appears in the tags (or heading)... I tried to achieve this by using the following SQL but it actually returns every entry in the database... I have never used "LIKE" before so if someone could give me a slap and say I am doing something stupid that would be awesome .

SELECT * FROM VIDEOS WHERE VIDEO_HEADER LIKE '%$seachText%' OR VIDEO_TAGS LIKE '%$searchText%'

WHERE Conditions
I'm trying to optimize a search and I'm wondering if anybody knew whether the order of the conditions in the WHERE clause actually matters. Is it parsed from left to right? If so, then should I put the conditions likely to eliminate the most records first, followed by those that will eliminate the least?
Also - I need to join several tables for this search (1 master table that contains user info and about 7 secondary tables that include additional information on users) and I am trying to decide whether I should do the joins one at a time and eliminate users in a series of steps (to prevent getting an overly large table), or if I should evaluate and combine everything in a single statement. The problem is I don't really know what the parsing order of the SELECT statement is.

2 Of 5 Conditions Match
I need my query to match at least 2 of 5 given conditions.

Example:
I have conditions
content REGEXP 'A'
content REGEXP 'B'
content REGEXP 'C'
content REGEXP 'D'
content REGEXP 'E'

I need database to return this entry only if at least 2 conditons are true.

Need Help With Multiple WHERE Conditions...
i'm wondering if anyone knows how to have multiple WHERE conditions...i'm using php/mysql as my programing languages...

example --> SELECT * FROM table WHERE rid=$ride && uid=$userid && accepted=Ɔ'

I need help selecting rows where all three of the conditions above are met...anyone knows how to do this?

MySQL 5.0 And OR Conditions
I have heard that version 5.0 of MySQL has performance gains for "OR" conditions. Can anyone confirm this? If so, how significant are the performance gains over 4.1?

For instance, I am referring to something like: SELECT * FROM some_table WHERE this_thing = 'something' OR that_thing = 'whatever';

Sums For Two Different Conditions
I want to combine the following 2 queries into 1 query where it would give me 2 results (proflad and proftot) but don't know how

select sum(return-inv)as proflad
from allbets where com="lad"

select sum(return-inv)as proftot
from allbets where com="tot"

Transactions And Race Conditions
In an application I'm developing, I'm at a point where there needs to be tight integration of application-level logic sprinkled in between several consecutive SQL statements. This negates the option to use stored procedures since subsequent SQL statements depend on application logic that cannot be transferred into the database. I need the ability to rollback on errors (i.e. I need to use a transaction), but I also need to avoid race conditions.

Reading the MySQL documentation, START TRANSACTION will implicitly issue an UNLOCK TABLES statement; conversely, LOCK TABLES will implicitly COMMIT any active transaction. At this point it appears that I have to choose between transactions and locks, but I need the functionality of both.

Two Alike Conditions In Query?
Example. I have a table with information about payments (id, amount, pay_date, customer_id). Can I select two sums for two different date ranges, grouped by 'customer_id' (for each customer) in one query?

Left Join Conditions
Two tables.

table1, table2

Table 1 contains all unique items.
Table 2 contains x references to table 1, unique by customerId

I want to do a join onto table2 where table1.id = table2.table1Id AND table2.customerId = the user's ID

I can't quite figure out how to work in the idea of an AND into my left join. Unless both those conditions are two, I don't want any data from table2.

Only solution I can think of so far is to just left join on the id's, but select the customerId from table2, and as I loop through, check to see if it's supplied and the right id, and keep track of duplicate items from multiple customers myself.

Suggestions?

Conditions On Left Join
I'm having trouble constructing this left join query. I want to retrieve all rows from tProducts, and relevant matches from pictures, however what I have got just jams up the server and I have to restart. Am I doing it wrong?

SELECT prodCode, prodName
FROM tProducts LEFT JOIN pictures
ON productID = relProdId
AND prodCode = 'SF2'

Select With Multiple Conditions
I've a table called tag_log.

In this table are two columns called tag_id and sub_id. This table is used to link tag names to stories.

I'm trying to write the query for a search. The search is looking for all the sub_id that matches the tag_id given.

Example of what I have currently:
select distinct(sub_id) from tag_log where tag_id in(101, 102, 103);

In the above example, it was performing a union instead of what I really want. It was returning results that have either one or all of the tags.

What's required is for the story to have ALL the three tags. It's should be like a typical search, where the more search terms you enter, the more focused the results will be.

Conditions In UPDATE Statement
i have some data to be updated in a specific row - WHERE id=1

data:
$min_time, $max_time, $time, $attempts

so, i would like to calculate new avg and replace min_time with new value, if new min_time is smaller then that one stored in the database.

"UPDATE mytable SET
avg=(avg*attempts+$time) DEV (attempts+1),
min_time=**here i could use help** ??IF(min_time>$min_time)$min_time ELSE min_time??
**and similar with max_time .

Query Performance On Column Conditions
I have a question regarding MySQL performance on column conditions. How MySQL reacts where you add a condition to the same column?

For example:

SELECT * FROM users WHERE status<>4
and the same query with extended column check
SELECT * FROM users WHERE status<>4 AND status<>5 ...

I am specially interested in situation where the conditions checks a column value and whether the data is specified:

SELECT * FROM users WHERE status<>4 OR status IS NULL

What's with the MySQL's performance in such cases? Is the same column (status in the above example) checked twice or the optimizer takes care of it in some special way?

OUTER JOIN With Extra Conditions?
I need to get a list of products in a certain category, along with the quantity of each item already added to the shopping cart for a given session number. I'm having a heck of a time satisfying the latter condition.

shoppingCart table:
+--------+-----------+-----------+-----+
| cartID | sessionID | productID | qty |
+--------+-----------+-----------+-----+
product table:
+-----------+--------------+------+--------+
| ProductID | ProductCatID | name | Active |
+-----------+--------------+------+--------+
Here's my base query, which just gets all the products in a given category.

SELECT product.*
FROM product
WHERE `ProductCatID`='{$id}'
AND `Active`=Ƈ'
Here's the query I've got so far:

SELECT product.*, shoppingCart.qty
FROM product
LEFT OUTER JOIN shoppingCart ON shoppingCart.productID = product.ProductID
WHERE `ProductCatID` = Ɗ'
AND shoppingCart.sessionID = '{$sessionID}'
AND `Active` = Ƈ'
Obviously, this does NOT work, because it limits the query to ONLY products that have the specified sessionID. I need it to return ALL the products in the category, but give me the quanity for items in the shoppingCart table, ONLY IF the sessionID matches (otherwise it should return NULL)!

Combining UPDATE Statements With Different Fields And Conditions
I'm slightly paranoid that I haven't unearthed an existing answer to this question, but the ones I have been able to find didn't have two different variables requiring two different conditions. Basically, I want to know if it's possible to combine the following UPDATE statements into a single query:

UPDATE table SET field1 = field1 - n WHERE field 1 > x
UPDATE table SET field2 = field2 - n WHERE field 2 > x

.......

How Do I Write Multiple Conditions In An MySQL IF Statement?
I am using version 3.23 and want to write something like this:

Granting User Access On Field With Restrictions Or Conditions
I am planning to develop a database that will have many users. Assume I have a table called Customer having these fields (id, name, user). Assume we have these Data:

ID Name User
1 Cust1 user1
2 Cust2 user1
3 Cust3 user1
4 Cust4 user2
5 Cust5 user2

I want the users to have access on those records that are created by them only or those that have their name on in the (User Field).

For example, if (user1) accessed the database and called: "SELECT * FROM customer;" it should return these data only automatically (or throw an exception at least):

Result:
1 Cust1 user1
2 Cust2 user1
3 Cust3 user1

(or throw an exception at least) that he cant call this statements without a where cluse like (USER=user1).

also, the user should have only the right to insert records having their name on in the (User) Field. For example: user1 can only call : "INSERT in CUSTOMER Values (6 , Cust6, user1), if he inserts user2 instead, the system should through an exception.

I hope I made clear examples of what I would like to build. How can I acheive this?

Specify Conditions For The Left Side Of A Multiple Left Join
I'm doing a left join that looks like this standard example:

SELECT t1.c1, t2.c2, SUM(t3.c3)
FROM t1
LEFT JOIN t2 ON t1.c1=t2.c1
LEFT JOIN t3 ON t1.c1=t3.c1
GROUP BY t1.c1

The problem is that I also want to specify a condition for selecting records from t1:

WHERE t1.c1='x'

so that only the records with that value in c1 will be returned on the left side of the join. I don't know where to put this in the SQL.

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".

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's
the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift
and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order.

now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it
does work.
Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there
is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that
does not accept an ON condition, so I have shifted the clauses to the WHERE
and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this
approach it makes the code clearer regarding intent. Code:

Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's the explain gives the best join order, and it completes in 1.5 secs

I add a single ORDER BY (a calculated column) and the join orders all shift and the query takes 85secs!

So I read the docs and it suggests STRAIGHT_JOIN to force join order. now I was using:

JOIN myTable ON xyx=abc

but in the docs it seems the ON condition is not permisible here, though it does work. Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there is no difference.

However the only way I can force the join order is to use STRAIGHT_JOIN that
does not accept an ON condition, so I have shifted the clauses to the WHERE
and it works fine.

Is there any syntax I can use to keep the ON conditions, I prefer this
approach it makes the code clearer regarding intent. Code:

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.