Want Like '%term%' where term=[keyword set]


Hopefully the subject describes the problem.

I basically want to select all rows where a particular field matches a given term, where that term could be any term listed in another table.

Is this possible with a single SQL statement, or would I need to run a seperate statement for each term

TIA
Posted On: Friday 12th of October 2012 03:33:08 AM Total Views:  116
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




How i do this like newegg??

http://www.newegg.com/ProductSort/S...SubCategory=48 like on the left you see it sorted on GPU so it is or should be a seperate table for video cards to get this
VIEWS ON THIS POST

88

Posted on:

Friday 12th October 2012
View Replies!

Database names starting with expressions like 1e

I am trying to migrate to MySQL 5 frpm MySQL 4 and I have problem in loading the mysqldump backup to the new database. It refuses to create the database 1e183 in MySQL5. In the documentation it says it is not recomended to do so. Is there any way I can create such databases in MySQL5
VIEWS ON THIS POST

106

Posted on:

Friday 12th October 2012
View Replies!

Select using like case sensitive but should not be.

Here is the problem: I'm doing php/mysql and when I do a select statement, it returns the wrong stuff. Mysql is supposed to not be case sensitive, but it definately appears this way: Code: mysql> select * from logs where problem like "%Something%"; Empty set (0.14 sec) mysql> select * from logs where problem like "%something%"; +-----------+----------------+---------------------+----+----------+ | problem | solution | date | id | username | +-----------+----------------+---------------------+----+----------+ | something | something else | 2006-03-08 09:53:34 | 55 | NULL | +-----------+----------------+---------------------+----+----------+ 1 row in set (0.02 sec) What's the deal and how can I fix this. I have searched the net and the only option I can find it to do the binary function with the select statements, but that will require a LOT of coding with php to get that to work properly. Any other suggestions
VIEWS ON THIS POST

104

Posted on:

Friday 12th October 2012
View Replies!

Dynamic Facebook like/send buttons for Coldfusion

I am trying to dynamically create facebook like/send button while looping through a Coldfusion query. Although the loop is creating the fb:like tag correctly when I look at the source code, when I click on the send button I get different results for each one even though they should be the same. Do I have to create new forms with dynamic og tags Any help would be appreciated. I want it working similar to how you would like a dynamically created blog post for each entry.
VIEWS ON THIS POST

110

Posted on:

Saturday 20th October 2012
View Replies!

asp and sql help with like and wildcard

Hi I have a database of postcodes. People go onto our website to book a place at our events. However if the postcode they entered is not in the database they will receive an email. I have used the sql like and wildcard functions and they work well, the problem is, the postcode entered by the user is longer than the postcodes in my database. For example in the database the postcode is only the first 3/4/5 characters (i.e. CV37 6) and a user will type in their full postcode (i.e. CV37 6KL) Here is my sql statement: strSQL = "SELECT * FROM postcodes_DB WHERE postcodes LIKE '" & search & "%' " So if I type in the full postcode in the field, I would like CV37 6 come up in the search results. How can I make this happen Hope that makes sense. Any advice/help
VIEWS ON THIS POST

86

Posted on:

Sunday 21st October 2012
View Replies!

Fulltext query isn't working - but where like does

I'll get right to it: CREATE TABLE `items` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` char(48) NOT NULL, `price` decimal(10,2) unsigned NOT NULL, `via` char(32) NOT NULL DEFAULT 'Amazon.com', `description` text NOT NULL, `img` char(192) NOT NULL, `url` tinytext NOT NULL, `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `price` (`price`), FULLTEXT KEY `name` (`name`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 SELECT items.name, price, via, description, img, url, GROUP_CONCAT(categories.id, ",", categories.name SEPARATOR ",") FROM howtogobroke.items LEFT JOIN howtogobroke.item_cats ON items.id = item_cats.item_id LEFT JOIN howtogobroke.categories ON item_cats.cat_id = categories.id WHERE MATCH(items.name) AGAINST('test' WITH QUERY EXPANSION) GROUP BY items.id ORDER BY items.id DESC The items.name column is FULLTEXT indexed just fine. I even tried rebuilding just in case. I have also tried it without query expansion. I just made 3 entries on the table for testing purposes. Two of the items have the word "test" in the name field. When I switch to a WHERE LIKE query, it returns results as expected. Even if I strip this query down to a simple select from the items table and a where match()-against(), I get nothing. What the heck is going on
VIEWS ON THIS POST

82

Posted on:

Wednesday 24th October 2012
View Replies!

Would like to avoid iterating through data and UPDATING multiple times

I've got product data that belongs to a category table. The business requirement is for the user to be allowed to update the order in which products appear in that category. There is a field called product.sequence for that very purpose. I'm being given the product id's in order for each category. Do I have to iterate through the data in PHP and UPDATE the table w/ many SQL statements, or is there one SQL statement where I can provide the list of products to update in order I've used MySQL user variables before (e.g. SELECT @m:=0; ) but I don't see how I can use it here... Is what I want possible in one query I hope I explained this clearly.
VIEWS ON THIS POST

134

Posted on:

Wednesday 24th October 2012
View Replies!

Mysql doesnt like this query

Code: INSERT INTO `news` SET `subject` = '{$_POST['subject']}', `content` = '{$_POST['message']}', `date` = '" . date('Y-m-d') . "', `owner` = '{$_SESSION['user_id']}' I see no problem
VIEWS ON THIS POST

169

Posted on:

Wednesday 24th October 2012
View Replies!

How to store american DATE formate( like 27-02-2007) in mysql table???????????

hi, i need to know desperately the way of storing american DATE formate( like 27-02-2007) in mysql table.i searched on the web a lot,but didnt find anything.PLZ i need someone's help. , ok thanzzz to all......one thing.......if i hv a ORACLE server and a MYSQL server connected to a single network,then how could i do data operations among them since they store date in two different formats\t , so finally ......i take it that (forget about retrieving or any other thing)....in a MYSQL db table the only format DATE type takes is YYYY-MM-DD....isn't it\t\t\t , I think you are getting to technical Rudy, Yes the only date format that MySQL supports is YYYY-MM-DD.
VIEWS ON THIS POST

225

Posted on:

Wednesday 24th October 2012
View Replies!

MySQL 4.0.24 doesnt like COUNT(*)?

Seems my Host uses version 4.0.24 and it is fussy about using COUNT(*) if I am reading the error message correctly. Is there another way to make this work for this version Code: LEFT OUTER JOIN ( select Players_Player_ID , count(*) as player_goals from goals group by Players_Player_ID ) as G
VIEWS ON THIS POST

217

Posted on:

Wednesday 24th October 2012
View Replies!

Count() like columns without grouping

Could someone tell me whether or not this is possible. I have a very long query and I want to count the resulting rows by a certain field, but I don not want to group by that field. Grouping will throw off the results I am shooting for. here is an example: Code: +----------------------------------------+ | the_table | +--------+----------------+--------------+ | id | animal | name | +--------+----------------+--------------+ | 1 | dog | jocko | | 2 | dog | max | | 3 | cat | mr pipper | | 4 | rabbit | cottontail | | 5 | cat | hank | | 6 | dog | spot | | 7 | snake | monty | +--------+----------------+--------------+ SQL: SELECT *, count(animal) AS total FROM the_table +-------------------------------------------------+ | the_results | +--------+----------------+--------------+--------+ | id | animal | name | total | +--------+----------------+--------------+--------+ | 1 | dog | jocko | 3 | | 2 | dog | max | 3 | | 3 | cat | mr pipper | 2 | | 4 | rabbit | cottontail | 1 | | 5 | cat | hank | 2 | | 6 | dog | spot | 3 | | 7 | snake | monty | 1 | +--------+----------------+--------------+--------+
VIEWS ON THIS POST

205

Posted on:

Wednesday 24th October 2012
View Replies!

Mysql %like% question

gday i have a list of keywords i want checked against a field in my db, and rather than doing repetitive sql's with %%, i was wondering if there was a way to, for example, check one field against 5 keywords, seperate by a comma, and only return it if it exists eg. a,b,c,d,e select subject from table where (a,b,c,d,e) IN %subject% any thoughts
VIEWS ON THIS POST

156

Posted on:

Wednesday 24th October 2012
View Replies!

Showing events like Monday - Friday: 5AM - 10AM

I am currently working on a project which has got schedualed shows e.g Monday - Friday: 5AM - 10AM . how can i do this dynamically What i did so far is created a mysql table called datesTimes with : startDatTime, endDateTime and days (from Monday-Sunday). To enter a show: I enter start and end dates and times and then the name of the days e.g Monday, Thursday, Sat... (all in one field) if i want to select current show I go: Code: $sql = " SELECT * FROM shows, datesTimes WHERE shows.show_id = datesTimes.show_id " . " AND NOW() BETWEEN start_dateTime AND end_dateTime" ; My main problem is that there are a wide ragne of shows: e.g only Mondays, How can i arrange the days field to give me a comprehensive answere. e.g Monday-Friday or only Thursdays or ... any help would be appreciated
VIEWS ON THIS POST

125

Posted on:

Wednesday 24th October 2012
View Replies!

Need report like below

i have table like this, |-------------------------------------------------------------------| CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `merchant` varchar(255) NOT NULL default '', `name` varchar(255) NOT NULL default '', `description` text NOT NULL, `image_url` varchar(255) NOT NULL default '', `buy_url` text NOT NULL, `price` decimal(10,2) NOT NULL default '0.00', `search_name` varchar(255) NOT NULL default '', `category` varchar(255) NOT NULL default '', `brand` varchar(255) NOT NULL default '', `dupe_hash` varchar(32) NOT NULL default '', `rating` int(11) NOT NULL default '0', `reviews` int(11) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `dupe_filter` (`dupe_hash`), KEY `merchant` (`merchant`), KEY `name` (`name`), KEY `search_name` (`search_name`), KEY `category` (`category`), KEY `brand` (`brand`), FULLTEXT KEY `name_2` (`name`) ) ENGINE=MyISAM AUTO_INCREMENT=32185 DEFAULT CHARSET=utf8 AUTO_INCREMENT=32185 ; |-------------------------------------------------------------------| i need to display report as follows,( i need first sort by merchant & second brand) Merchant | Brand | No. Of products( related to merchant & brand) please reply soon,
VIEWS ON THIS POST

141

Posted on:

Thursday 25th October 2012
View Replies!

any better way to select like this?

I have a category_table with the fields like below: id category_name parent To get the Category Name and its parent name, we can use the simple but bad way like below: $query=mysql_query("Select * from category_table where category_name like '%tech%'"); while($row=mysql_fetch_array($query)){ $query2=mysql_query(""Select * from category_table where parent ='".$row['id']."'"); } May I know how to get the category name with its parent name in one query
VIEWS ON THIS POST

153

Posted on:

Thursday 25th October 2012
View Replies!

How to search like Ask.com?

I need to make a control that allow user to search a string on multiple tables in our database. User can type something such as: -How to find a English book -How much is that furniture Our customer want something like Ask.com search control but it might be simpler. Can you tell me where I should start from. Thank in advance.
VIEWS ON THIS POST

214

Posted on:

Thursday 25th October 2012
View Replies!

what is the diff. b/w match and like

, I try to write this query Code: SELECT SQL_CALC_FOUND_ROWS Ch.id as id, Ch.name_arabic as ChName, Ch.logo as pic , Lang.name_arabic as Lang , Class.name_arabic as Class, Grp.grpname_arabic as 'Group', Owner.ownername_arabic as Owner FROM channels AS Ch , classifications AS Class , languages AS Lang , ownership AS Owner , groups AS Grp WHERE (Lang.id = Ch.language_id) AND (Class.id = Ch.classification_id) AND (Owner.id = Ch.ownership_id) AND (Grp.id = Ch.group_id) AND (MATCH (Ch.name_arabic) AGAINST ('%2M%')) ORDER BY Ch.id LIMIT 0,5 it's not working , but when write it using like Code: SELECT SQL_CALC_FOUND_ROWS Ch.id as id, Ch.name_arabic as ChName, Ch.logo as pic , Lang.name_arabic as Lang , Class.name_arabic as Class, Grp.grpname_arabic as 'Group', Owner.ownername_arabic as Owner FROM channels AS Ch , classifications AS Class , languages AS Lang , ownership AS Owner , groups AS Grp WHERE (Lang.id = Ch.language_id) AND (Class.id = Ch.classification_id) AND (Owner.id = Ch.ownership_id) AND (Grp.id = Ch.group_id) AND (Ch.name_arabic like '%2M%') ORDER BY Ch.id LIMIT 0,5 every thing goes ok , and there is data, I don't know the reson, where my table collection is "cp1256_general_ci" what is the problem \t
VIEWS ON THIS POST

147

Posted on:

Thursday 25th October 2012
View Replies!

Why is this reporting like this

Code:
VIEWS ON THIS POST

213

Posted on:

Monday 29th October 2012
View Replies!

Would like some help with Joins query

Hi , I find Join's hard. Especially involving several tables. In this case I have a simple ACL setup in my PHP app, essentially, a user is assigned to a group and in turn each group is assigned a set of permissions. Ultimately i need to be able to query what permissions (i.e. get a list of permission names) a particular user has, but I cannot seem to get my query to return a deed result acl_ I have the following table structure ; Code: acl_users: uid | username acl_groups gid | groupname acl_permissions pid | permissionname acl_users_to_groups uid | gid acl_groups_to_permissions gid | pid And here's my query: PHP Code: $user_permissions=mysql_query('SELECTacl_users.uid,acl_groups.groupname,acl_permissions.permissionnameFROMuser \t\t\t\tLEFTJOINacl_users_to_groupsONuser_to_groups.uid=acl_users.uid \t\t\t\tLEFTJOINacl_groupsONacl_groups.gid=acl_users_to_groups.gid \t\t\t\tLEFTJOINacl_groups_to_permissionsONacl_groups_to_permissions.gid=acl_groups.gid \t\t\t\tLEFTJOINacl_permissionsONacl_permissions.pid=acl_groups_to_permissions.pid \t\t\t\tWHEREacl_users.uid=1'); while($permissions=mysql_fetch_array($user_permissions)){ \tprint_r($permissions); \techo''; } Code: ['uid'] => 1, ['groupname']=>webmaster, ['permissionsname']=>NULL ['uid'] => 1, ['groupname']=>support,['permissionsname']=>NULL
VIEWS ON THIS POST

172

Posted on:

Monday 29th October 2012
View Replies!

If you like this, you'll like this Mysql query??

, I'm trying to implement a feature on my master product detail product page that basically lists say 12-15 random records based on the same "Genre" of the main product displayed, I'm passing a productID from another page in a url string to my master detail page and i'm stumped on how to match the genres. For example if the main product's genre on the master detail page is "Heavy metal" then i want to randomly display other Cd's on the page for the genre "heavy metal" In my products table i have a "genreID" linked to the "genre table" so i'm wondering what kind of table joins and statements would i need to use to do this and make it random and match the genres based on that url parameter any insight on an approach would be greatly appreciated
VIEWS ON THIS POST

156

Posted on:

Monday 29th October 2012
View Replies!