MYSQL :: Random Keyword

Random Rows, Non-random Ordering
Normally to select random rows from a table I'd do something like:

SELECT field1, field2, RAND() AS random
FROM mytable
ORDER BY random
LIMIT 5
Unfortunately, this time I need 5 random rows sorted on a different field:

SELECT field1, field2, RAND() AS random
FROM mytable
ORDER BY field2
LIMIT 5
Is there a way that I can still sort by field2 and yet still select 5 rows at random? Could the former query be run as a subquery and then a second ORDER BY clause run on those results? Worst-case I can re-sort the results in my host language, but a pure SQL solution would be best I think.

Bonus points if the answer can always put a particular row first in the results.

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?

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.

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?

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.

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 + 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?

Automatically Generate Text Excerpt For Text Near Keyword Hits?
I would like to generate an automatic text excerpt showing the text close to the first occurance of a keyword needle in a text haystack.

For example: searching for

keyword = "foo"

in a table called Webpages in the column Content = "Yadda, yadda, yadda. Bla, bla, bla foo yadda yadda yaddda bla bla bla"

would return a column called Excerpt = "...a, bla foo yadda yadd..."

Is this best done with PHP after fetching the entire column Content, or will it be more efficient to do directly with MySQL?

What would that SQL query look like?

The MYISAM Storage Engine And The "KEY" Keyword
1. Does MyISAM support FOREIGN KEY constraints? When I read the online
MySQL Reference Manual, it said "InnoDB also supports FOREIGN KEY
constraints". Does it mean MyISAM does not support FOREIGN KEY constraints?
It this is the case, what should I keep in mind when I design MyISAM tables?

2. I have seen the following CREATE statement somewhere, but I have never
known that the "KEY" keyword can be used that way(the last 4 "KEY"). I
guess the "KEY" is equivalent to "CREATE INDEX". Is it the syntax for older
versions of MySQL? Code:

Sql Random Pwd
i read somewhere that it is not safe to use a mysql randomly generated password . I have a table with many records and want to reset everyone's password to a random alpha-numeric password.
What is the best way to do this one ? something using a php random pwd and then update the table ? or there is something better than that ?

Random Row
I am trying to get one random row at a time. Right now I am using this SQL:

Code:

SELECT * FROM `trtrack` ORDER BY RAND( ) LIMIT 0 , 1

But it's really slow, some queries taking up to .5 to 1 second. Is there any other way? I use PHP.

Add A Random Value
in php, this would simply be rand(1,5)
in mysql, I tried

update `table` set column=(column+rand(1,5));

but that was not a correct guess.
how can I add a random value of 1 digit bt x and y?

Random Row Select?
if I have a table with 200 rows in it. I want to select 1 random row. Does anyone know how to do that in a select statement?

Random Retrieving
data in myTable

(id) name
(1) Tom
(4) Jane
(8) John
I have data in myTable like the above.

Can I retrieve one of names in name column randomly?

Random Images
is there sombody who can helpme witht he following, i am ''pretty new'' to
php and am looking for a script to show images randomly at a 10/ 15 seconds
interval.

Random Query
I have been tasked with getting daily quotes out of a sql database, there needs to be 3 that are randomly selected. I have not found any documentation in regards to doing this.

How would I qurey a database and pull 3 quotes of the day out randomly. This should even occur when a user refreshes the page, is there a sql function that would do this? or do I need to do it via php, perl, etc...

Random Query
$querystring = $querystring." ORDER BY status, dateposted desc";

How can i make my query that its sorts first by status and then random for date posted.

Random Function?
I would like to know if there is an existing random function I can use to generate a random number so I can concatenate it to a string?

Select At Random?
I have a db that I'm using as a mailing list. In many (but not all) cases, there are several contacts per company. I rarely want to send my mail to more than 1 person at any one company, and I never want to send to more than 3.

I don't have any criteria that I can use to select which contact to select, so I want to do it essentially at random. So I want to contruct a SELECT query that says, "when there are >1 contact at company X, pick 1 (or 2) of them at random. When there is only 1 contact at a company, include that contact/company in the result as well."

I'm sure there's an easy way to do this, but having searched online and read a lot of the 1500-page manual, I haven't figured it out.

Inserting Random #
I have a db name image records. It has 3 columns recno_no, file_name and active_fl.

is there a way to insert 100 random #'s into the database table and its columns for testing?

Random Records
I'm trying to get a random record for use with my website. I've tried figuring out some premade code and it's led me nowhere..

Here's basically the rundown...

I have a database setup by my site's shopping cart system, and in it are 2 tables i need to read from, "specials" and "featured". I want to pull 4 random entries from "specials" and 1 from "featured" to have posted on my site's main page outside of the store.

I know how to connect to and select the database, I even know how to send queries, but the whole selecting a random entry is what's got me at a brick wall.

I'm not used to PHP and MySQL, i'm just now learning after 4 years of using Perl(and never using a database system, but creating my own database-like files).

Random Select
When I do a select with a limit I get always the same records. Usually this
is the desidered effect. But now I want to do a random select. So every time I do a select I should get a different set of results. What is the best way to achieve this effect?

Order By Random
Is there anyway to order records randomly? instead of order by id desc to order it randomly??

ORDER BY Random
Is it possible to pull results from a database using a SELECT... ORDER BY and pull them in a random order?

Random Pick
When an artist isn't picked, there is a random artist picked. Now I have it setup so it counts the rows, and picks a random number from 1-nr of rows. This number is then used as the id and gotten a mysql query from this id.

$query = mysql_query("SELECT * FROM `artists`");
$total = mysql_num_rows($query);
$random=rand(1,$total);
$right = mysql_query("SELECT * FROM `artists` WHERE `id`=$random");

Now, I want something a bit different. I also have ABC....Z, by the first letterof Artist Name. If the first letter is picked, but no artist, I want the random artist to be start with this letter. I wanted to use something like above but the id won't be in order. it could be like 1, 20, 32, etc etc.

Random Data
I keep getting random data put in to by database??? Any idea how to stop this?

Random Row Order
What do I need to put into a SQL statement for it to retrieve 100 Random Rows (containing certain criteria in the WHERE clause). Something like this (theoretically) is what i'm looking for:

SELECT *
FROM table
WHERE field="yes"
ORDER RANDOM
LIMIT 0, 25

Any Ideas?

Random Ports
mysql 5.0 was working fine in Debian "etch".

Then I installed awstats, which changed something somewhere and now the server starts on a random port. However, no clients can FIND it! How do I resolve this issue?

Generating Random
I have a database set up and all I need to do is make a field with just something random for each entry that is already in the database. Something just 5 numbers and/or letters is fine. How would I go about doing this?

Selecting Random
so i have a table with three columns: sentence, auto_id and createdate (datetime). I want to display random sentences (from sentence) up to 15 from each day. That's easy enough:
$query = "SELECT sentence FROM sentences WHERE createdate LIKE '$today%' ORDER BY RAND() limit 15";
but now I'm running into a problem and that is if a user submits a huuuge block of sentences into this db I end up displaying mostly that users stuff (and often only that users stuff) so I'd like to come up with a query that will use the information afforded it to select:only one or two randomly selected sentence(s) from each entry and return it randomly up to 15.
what does that query look like?

Select Random Row
I have read that

"select * from table_name order by rand()" will gives poor performance when there are thousands of rows in the table. Does the same holds true even if there is a where condition (returns only hundres of rows at most)?
For example:

"select * from table_name where col1=2 order by rand()". If I have thousands of rows in the table but the where condition limit it down to couple of hundred, will this query still gives bad performance?