MYSQL :: How Exactly The LIKE Keyword Works?

THE Keyword
If, for example, you have a database full of movie names, a lot of them will begin with "THE", and you'll usually find such names alphabetically sorted with the 'the' at the end (e.g. "SHAWSHANK REDEMPTION, THE")

other than entering data into the database in this way, does mysql offer any "order by" parameters or other options to enable this type of alphabetical ordering?

IN Keyword
Sorry if this has been answered before, but searching for "IN" returns more results than I have time to look through.Does MySQL allow the IN keyword in queries?
I'm using it, but not getting any results returned, so I'm thinking MySQL doesn't use IN, or i'm doing it wrong

Two Keywords First Each Keyword Later
data in myTable

(id) myString
(1) I love you.
(2) Here we go.
(3) I love my boy
(4) Exactly wrong
(5) Everything's OK
(6) This is my love.
(7) This is my book.
(8) That is a book.
(9) I knew it.
I have data in myTable like the above.

The following code will produce the following result......

Keyword Searching
I have a table of events which holds data such as title, description, town etc. When adding an event the user is also asked for a list of keywords which they enter into a textarea and separate with commas. At the moment these keywords are just entered as one field into the database which has a datatype 'TEXT'.

So what is the best way to query the database when a seach is performed? For example, if a user searches for 'music festival' then I'd need to search the events title and keywords for the given search term. So is it best to perform a FULLTEXT search on the keywords or store the keywords differently?

Random Keyword
I have a question about what query I should use to achieve the following...

I have a table called 'keywords' that has the following fields

id (auto_increment)
pagename
keyword

There can be multiple keywords for one pagename, but each keyword has it's own row. For example, this could be in the table:

id pagename keyword
11 test-news test
10 test-news news
9 test-news article
What I want to achieve is to select 1 random keyword for each unique pagename. So there can only be 1 keyword selected for a pagename, but it should happen for all unique pagename entries. What query would I use to achieve this?

GO Keyword From SQL Server
In SQL Server I can use GO between statements to execute multiple statements in the query window.

What is the mysql equivalent?

Is 'leave' A Keyword?
I have a column named 'leave'. It works fine under debian and mysql 4.x. But each time under windows XP and mysql 5.X I got a sql syntax error. So is 'leave' a key word in mysql5.x?

Actually I dont want to change the column name now. Is there any other way to solve it?

Keyword Searching
I've created a web site using MySQL and PHP to interact with the database and with several days of blood, sweat, and many tears I have been successful in being able to set up the database and tables with several search options.

However, I can't seem to get a "keyword" search to pull up the results based on entering a keyword.

I thought this would work but it doesn't:

$get_prods = "select id, prod_name, city, prod_brief, prod_desc from products where prod_keywords and prod_desc like 'prod_keywords%' order by prod_name, city";

Can anyone tell me if I'm close or far away from success?

Search Keyword In Db
Im trying to implement a search function in my database but not sure what would be a smart way to do it.
Suppose I have a blog column and want to search if "middle school" is inside, I would do: columnName like '%middle school%' but if the search key words are "middle school soccer", I don't know how to recognize and break this into two logic parts as "middle school" and "soccer". How would this be implemented or is it has to deal with some complext AI concept?

Right now I only know to use columnName like '%middle%' or columnName like '%school%' or columnName like '%soccer%'.

DISTINCT Keyword
i was wondering if there is a way to select more then on field using the DISTINCT keyword

i have a query like this:
$str_sql = "SELECT id, product_id, name, description, price FROM products ORDER BY name ASC";

However i know adding the Distinct keyword like this won't work

$str_sql = "SELECT id, product_id, DISTINCT(name), description, price FROM products ORDER BY name ASC";

Searching Keyword
i would like to be able to search a keyword, and match it against 2 columns.
so for example, I have cli_fname and cli_sname fields, and I want to be able to search for "mark williams" and have mysql match the search term against the two fields, but treated as one (if I am making sense)
I have tried CONCAT(cli_fname,' ',cli_sname) as cli_name, but if I add %keyword% LIKE cli_name, I get an "unknown field" error.
is there a way to do this?

Distinct Keyword
i have the following query.Code:

SELECT DISTINCT(card_no), cardtype FROM orders WHERE acc_id = 5

but it is not doing what i want or expect
what i need is for it to select the distinct records for card_no and just grab the card type but don't add that to the distinct bit...does that make sense?
If anyone can help that would be appreciated. Oh and these are not Credit cards i am dealing with before i get told off

Multiple Keyword Query
Ive currently got a table with (shopId, Keyword) used for searching on a product. So a shop could have multiple keywords and a keyword could be associated with multiple products

so if a user wants to do a such as "stationary AND office NOT warehouse" how would i get from the database all the shopIds that have the keywords stationary and office, but do not contain the keyword warehouse

This would seem like a common problem, what is the best way to perform this type of query?

Keyword SELECT In MySQL
I have an array with a number of keywords (php). I want to SELECT articles in my table that have headlines which contain the keywords in my array.

how do I do this using SELECT?

obviously "SELECT id, headline, permalink, body FROM anews2 WHERE headline LIKE $headlineKeywords" doesn't work. Short of creating a loop in PHP to search for each keyword in my array independently, how can I do this in mySQL?

FYI: I am trying to created a "Related Articles" feature. If a news item is posted then below the news item I want to list all of the related news articles based on keywords in the headline.

Checking If A Keyword Is In The Top 1000
How can I structure a query so that I only get back a result if the keyword I am searching for is in the top 1,000 of the results?

I am currently using the following query and then running through the array using PHP to check if the keyword is in the top 1,000. However, I would like to eliminate the PHP step to save processing time and CPU usage.

SELECT keyword FROM lookup_table ORDER BY lookup_count DESC LIMIT 0, 1000

After getting back the result above I would use a loop to get all 1,000 keywords. If I could just check whether the query succeeded or failed based on the keyword that would be much better.

Is 'finding' A MySQL Keyword Or Such
I'm using PHP to create a simple, dynamic MySQL SELECT query.

The user chooses a selection from a HTML Form SELECT element's many options
and submits the form via a POST action.

The SELECT query is built as follows:

$itemtype = stripslashes(trim($_POST['selType']));
$query = "select * from item where item_subtype like '%$itemtype%'";

I've used:
echo "<center><b>".$query."</b></center><br />";
to see that the queries are being properly constructed.

When the option with the value of 'finding' is chosen, no records are
displayed, although there are many records in the item_subtype field of the
item table that contain the string 'finding', such as 'Earring finding' and
'chandelier finding'.

The display of records works fine when other values are chosen (other than
the value of 'finding')

Help!

Can anybody help me figure out what might be preventing records with
'finding' in the item_subtype field from showing.

I have no problem with any other of the SELECT element's values being passed
to the query.

Here is the 'offending' query: select * from item where item_subtype like
'%finding%'.

Fast Keyword Search
I´m asked to "speed up" a keyword search based on MySQL.
The material i´m given to work with is a quite large MySQL table with
1.5 mio rows, defined something like:

CREATE TABLE datarecords (
id BIGINT(20) NOT NULL auto_increment,
[3-4 other INT type columns],
keywords TEXT NOT NULL,
updated TIMESTAMP(14) NOT NULL,
PRIMARY KEY (id)) TYPE=MyISAM;

The search requires the id-fields of the datarecords table to be
returned, based on keywords within the keywords-field, like so:

SELECT id FROM datarecords WHERE (
(keywords LIKE '%someword%' AND keywords LIKE '%otherword%')
AND
(keywords NOT LIKE '%notthisword%' AND keywords NOT LIKE
'%thisneither%')
)

The table has grown unexpectedly large and will keep growing, so a
different method for keyword searching appears to be required, since
search-speed has decreased significantly (now at about 12 secs,
depending an the number of searched keywords)

Using FULLTEXT indexing has brought no performance increase...

Question:
Does anybody have any suggestion on how to tackle this type of
problem, to get search-speed down to about 2-3 secs?

One way i´m looking at this is to create a seperate keyword-table
something like this:

CREATE TABLE testkwdlist (
keyword varchar(32) NOT NULL default '',
ids text NOT NULL,
PRIMARY KEY (keyword)
) TYPE=MyISAM;

and filling this table up with the keywords and listing the IDs in the
ids-field, doing seperate queries for the positive and negative
searches and compiling the actual resultset in code...

Does this sound it has potential for a performance boost or is it
competely insane?

Works
I have this and it works just fine but I cant help but think there is a much better way of accomplishing the same thing in one sweep verses 6
PHP Code:
mysql_query("update residential set bed='1' where bed like 'ONE%'");
mysql_query("update residential set bed='2' where bed like 'TWO%'");
mysql_query("update residential set bed='3' where bed like 'THREE%'");
mysql_query("update residential set bed='4' where bed like 'FOUR%'");
mysql_query("update residential set bed='5' where bed like 'FIVE%'");
mysql_query("update residential set bed='6' where bed like 'SIX%'");

About Using DELAYED Keyword In The INSERT Query
I have a table in my database that needs very fast inserts as hundreds of concurrent users might be inserting rows at a given time. Basically, every time a visitor accesses a web page the time needs to be logged in the table. Since there could be hundreds of insert operation in the queue waiting for their turn, a visitor needs to wait before his entry is inserted. So to speed up things a bit I was thinking of using DELAYED keywords in the INSERT statement. The visitor will issue an INSERT query and then proceed. The entry would be inserted when the table is free to accept new insert query.

Works In MYSQL 4.1 But Not In 5.0
MySQL
1054 - Unknown COLUMN 'p.manufacturers_id' IN 'on clause'
SELECT count(*) as total FROM products p, categories c, products_to_categories p2c LEFT JOIN manufacturers m on (p.manufacturers_id = m.manufacturers_id), products_description pd WHERE c.categories_status=1 AND p.products_id = p2c.products_id AND c.categories_id = p2c.categories_id AND p.products_status = Ƈ' AND p.products_id = pd.products_id AND pd.language_id = Ƈ'

Here is the query in PHP

PHP
"select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, p.manufacturers_id, m.manufacturers_name from " . TABLE_PRODUCTS . " p, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.categories_status=1 and p.products_id = p2c.products_id and c.categories_id = p2c.categories_id and p.products_status = Ƈ' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";

UNION - Works In 4.0.16, Not In 4.1.8
I have a query that works in 4.0.16 on my web host, but not 4.1.8 on my dev. box. Web host is upgrading in 10 days.

Notes:
Server language is ColdFusion
gamedate is type DATE

SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate >= now()
UNION (
(SELECT DISTINCT gamedate
FROM schedule
WHERE gamedate < now()
ORDER BY gamedate desc LIMIT 1)
ORDER BY gamedate asc LIMIT 3

The goal is to select the most recent gamedate prior to "today" and the next two game dates including "today".

Query Only Works Once
I'm using jsp with mysql and am querying the database for data. Everything works fine on the first try. If I click back on my browser and hit submit again on my form, I get no data.

I've tried removing the close() commands but that doesn't seem to work.

SSN Changes In Database But Still Works As Key?
I have a ssn field in a table but somthing happens to the data. I use an update to enter the data and set the value to 123-12-1234 for a test but when I look in the table or I get the record from a record set it come out like -1123.

Why is this? But when I run a query and ask it to return all records which have a ssn of 123-12-1234 it returns the correct record. If this is correct then how can I display the ssn number in a java text field.

How Decimal Works
Say that we have an variable that is

var1 = 3
var2 = 3.65

var3 = var1 * var2 '--- Sum = 10.95

Now my Question!
How do I input this variable into MySQL in a COL whit DECIMAL (10,2)?
I only get the result 10.00 in the COL?
It seams that MySQL round every resout I get to .00 at the end.

If I make an varible hard ex.
var3 = "10.95"
that works but if I convert an numeric variable to string it doesnt?!?!?!
ex.
var4 = cstr(var3)

UPDATE Works In MS SQL
I use the following stament work with MSSql, but not MySql, why ?

Update mytable set mycol = 'my_Text' + mycol

Failed, mycol is varchar

Keyword Search - Comma Seperated Values?
1) I have a full text column that contains several paragraphs of content that I want searchable- is it better to have comma separated keywords or is it better to throw all of the text in to a full text TEXT column so it is searchable? Which is better?

2) Currently my SQL query is not giving me the plural of keywords such as "green" as a keyword but the search for "greens" will not produce any result.

3) Also words that appear in the database as "green dog" will not show a result if someone searched for "dog green"

Here is my SQL query

PHP

SELECT *FROM `report`WHERE `company` LIKE '%$P_search%' OR `description` LIKE '%$P_search%'OR `market1` LIKE '%$P_search%'OR `market2` LIKE '%$P_search%'OR `market3` LIKE '%$P_search%'OR `market4` LIKE '%$P_search%'OR `market5` LIKE '%$P_search%'OR `market6` LIKE '%$P_search%'OR `location` LIKE '%$P_search%'OR `date_year` LIKE '%$P_search%'OR `date_month` LIKE '%$P_search%'OR `source` LIKE '%$P_search%'OR `keywords` LIKE '%$P_search%'

GO Keyword Like SQL Server Query Editor--for MYCC?
Is there an equivalent for MyCC, of the go keyword used in SQL servers Query Analyzer to execute multiple statements.

LIke I want to

Use DB1
go

SELECT * from WHATEVER
go

Keyword Searching Accross Multiple Tables
been trying to get this to work correctly for some time now - basically i want to search for multiple keywords accross two tables. The query i have already works to a certain extent but doesnt return the exact results i'd like.

I got two tables, both are for a bulletinboard, one contains the threads (bulletinboard) and the other the replies for each thread (bulletinreplys). They're linked via a column called 'threadid' in the reply table. Code:

MySQL Delete: Works In Php 5 But Not Php 4?
I've developed a small login system using Xampp on my local machine. Everything is running great except when the admin tries to delete something. Apparently I have the wrong syntax for my deletion in mysql.

Here's my original delete statement (from mysql5):
DELETE at.* FROM access_table AS at WHERE at.client_id=2

And here's the error I get from mysql4 (I ran the command in phpmyadmin console):
#1066 - Not unique table/alias: 'at'

So I removed the aliases and re wrote my command as such:
DELETE * FROM access_table WHERE at.client_id=2

And got the following error from php4:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM access_table WHERE at.client_id=2' at line 1

SQL Query Works Only In Win32!
I use MySQL from my php page and from phpMyAdmin.

In Windows Server, following query works fine. In Linux Server instead it returns error.

The SQL Query is:

SELECT DISTINCT a.id, a.container, b.container FROM folder AS a JOIN folder AS b ON a.container=b.id

The PHP error is:

You have an error in your SQL syntax near 'ON a.container=b.id' at line 1

So, I ask: If in Windows this query work fine, where is the error in the Linux version?

SHOW DATABASES Works In 4.0.14
I've been trying to solve this problem for a long time. Only thing
I did when upgrading from 3.25 to 4.0.14 I took the default my.cnf. I
did not convert the privilege tables with mysql_fix_privilege_tables.

Suddenly, the mysql started to show all databases to all users (even
though the user had access denied and could not do show tables on
them). So I started to find help and tried the
mysql_fix_privilege_tables. It didn't work properly (more than just
access denied and errors, that are ok) so I did the changes manually
conforming to source code of mysql_fix_privilege tables.

PhpMyAdmin stopped its warning about privilege tables not conforming
to current mysql version. Everything seemed ok except that SHOW
DATABASES still worked.

The users naturally all have Show_db_priv="N" in the tables. Also
trying "safe_show_database", "skip_show_database" in my.cnf and
"--skip-show-database" witch mysqld didn't help.

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.

select sc.name,
Sum(if(student.SNO is not null, 1, 0)) nmbr,
from school sc
left outer join student
on sc.school_id = student.school_id
group by sc.school_id

this runs just 0.30 sec with Mysql3.23 but 30Sec with Mysql 4.0.13.
i could not find a solution.But i suspect from NULL threatment.

Parentheses: Query Works With 4.1.7, But Not With 4.1.11
Since we updated to the newest MySQL version (Windows) some of our queries do not work any more.

It's reproducable by this (dummy) query:

SELECT B.ID from ((SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID);

This works with 4.1.7, but not with 4.1.11, unless the parentheses are removed:

SELECT B.ID from (SELECT * from A) AS T1
INNER JOIN B a ON B.ID = T1.ID;

Does anybody know why MySQL changed its behaviour that way?

I know the parentheses in the first query are not necessary, but it would be quite difficult to search through all our queries for unnecessary parentheses.

JOIN Query Works With 3.23 But Not 4.0.13
I have a simple join query below which queries the name of schools with count of
student numbers for each school from two tables School,Student.....

Exporting MS Works For MySQL
I have a MS Works database and I can't find anything useful on the PHP site for working with this type of database. I did a quick search and it seems that it can be exported for OoenOffice.org and other spreadsheet database programs but none of them give a method. Mostly selling books and MS Access.

If there is a way to access the .MWD or .DBF Microsoft works files with PHP that would be great. If that's not possible, preferably not, I'd rther switch to MySQl easily if possible. I'm still looking but it's not looking good.

Mysql.exe Sort Of Works
I have just installed MySQL (not an easy thing to do). I also have installed PHP. I use Dreamweaver8 for 2 static sites, and eventually will figure out how to use that with PHP/MySQL.
Everything seems to work fine, and I'm working my way through Janet Valade's book, "PHP and MySQL for Dummies." Everything I have tried in the book works perfectly except for the mysql.exe command.
I am in the correct folder and type mysql.exe -u root -p and am prompted for my password. When I enter my password the cursor moves to the next line and just sits there. Nothing happens. Fortunately, the window is not frozen and I can just close it.

Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing.
Code:

SELECT Artists.Artist, Albums.Album_Name
FROM Artists, Albums
WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id

MySql + VB6: Pass Keyword As Fieldname Within Insert Statement
I successfully migrated from access to mysql. The original database had a column name "group"..When i try to pass/insert the value from the form into this field, in the table, i get an error.

I believe group is also a keyword/part of mysql and its causing the error. Is there a way to pass this keyword as the field? Such as wrap it in something (like brackets) to pass the info as a field without changing the column name?

for example the correct form (if it wasn't a field name) would be:
"insert into tbl (group) value('" & groupnum.text & "')"

so how can i pass this "keyword" into the mysql database as a field name?

Return Context From Large Column In Keyword Search
Consider this query:
Code:

SELECT Ticket,Content FROM Attachments,Transactions
WHERE Transactions.id=Attachments.TransactionId
AND Content LIKE "%Keyword%";

The 'Attachments' column is quite large. How could I limit the returned text to say 25 characters before and after the keyword?

GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version.

The query is this:

Query Help. This Works, But I Don't Think It Is Good Practice
comics:
id
date
location

comments:
id
comicid
datetime
header
comment
author

Every comic can have multiple comments. Each comic will have a comment, but there is a period of time where the comic is posted but the first comment is not yet submitted. So for argument's sake we can't assume there is any comment at all for a given comic.

I want the all the comic ids and dates before a certain date along with the most first comment entered relating to that comic.

select comics.date
, comics.id
, comments.header
from comics
left outer
join comments
on comments.comicid = comics.id
where (comics.date>=񟭇-03-01')
group by comics.id
I never defined WHICH header should be returned when many are grouped together. I just know that mysql will return the first one in this circumstance. I seem to recall reading somewhere that different databases handle this sort of thing differently, with some returning a null in that field?

I keep trying to forumate a useful subquery but keep drawing blanks just as I think I have it figured out in my head. Like I forget how the string starts by the time I mentally formulate the end....

Suddenly A Script No Longer Works
I always get the following message when starting a search in my database:

Search for titles in the mylibrary database.

Search for titles beginning with a:

V. 4.1.7 + Php4 Works, + Php5 Problems
I am running:

Mac OS X 10.3.7
apache 2.0.52
MySQL 4.1.7
php 4.9.3 & php 5.0.3

I have an html form that "POST"s data to a db.
I have a "GET" script to repopulate form for editing.
Everything works fine with php 4.9.3.

With php5.0.3 the data is "POST"ed just fine
(I used a different GUI app to check)
When I try to "GET" said data, form is returned empty?

I have all error reporting turned on = nothing.
I have all backwards-compatibility directives in
php.ini config file turned on.

Two Different Websites Connected To One Database But Only One Works...
I have been recently having problems transferring my website from one domain name to another. The first domain name was used whilst at uni, but the second is my own perosnal website. One the original website, the MySQL database allows the user to login and register new users without any problems.

However, the second website does not allow me to do this. I would consider myself a newbie when its comes to MySQL so I was wondering if the community could help me out on this one. Both websites are hosted by the same company, and both websites use the same database but my limited knowledge cannot solve this problem. Are there certain permissions I need to set or something? Can anyone help me out on this one?

Delete Using Multiple Table: Works On 4.1.20, But Not On 4.0.16
I've a simple delete query delete from joingrp as J using joingrp as J,msggrp as M
where J.msgid=M.msgid and M.parent!=0

When I tried it on phpMyadmin at ixweb, having mysql-4.1.20, it works as desired.
But when I tried it on phpMyadmin at iPowerweb, having 4.0.16, it says
#1064 - You have an error in your SQL syntax.

Check the manual that corresponds to your MySQL server version for the right syntax to use near 'as J using joingrp as J,msggrp as M where J.msgid=M.msgid Can I solve this without using any server-side loops?

How Exactly The LIKE Keyword Works?

I have a table named Inventory and have 3 items in it containing the work w069. I want to run a query to return the three items. My query: Select * from Inventory where Description Like 'w069' It doesn't seem to work, returning zero row.

I have a table named Inventory and have 3 items in it containing the work w069.

I want to run a query to return the three items.

My query: Select * from Inventory where Description Like 'w069'

It doesn't seem to work, returning zero row.