SEARCH YOUR SOLUTION HERE  

key1 without binary and key2 with binary

I have two search keyes, key1 and key2.

The following is where clause.
Code: where say like '%#key1#%' and say like binary '%#key2#%' I have to use binary for key2 like the above.



Now I like to make the following where clause.
Code: say like '%#key1#%#key2#%' The where clause above will retrieve all records which has #key1# and #key2# in that order.


My problem is in the use of binary, i.e I have to use binary for key2.

I can make it like the following for using binary.

Code: say like binary '%#key1#%#key2#%' But the code above makes not only key2 but also key1 bianry.


Key1 doesn't need bianry but key2 needs bianry.


How can I make the where clause key1 without binary and key2 with binary



The following would-be code doesn't work correctly, but it'll show what I want.

Code: woudl-be code say like '%#key1(non-binary)#%#key2(binary)#%'

Posted On: Thursday 25th of October 2012 11:14:55 PM Total Views:  399
View Complete with Replies




Related Messages:

How to upload SQL file without Import function in MYSQL   (240 Views)
hi, I bought a database from a friend, he given me a SQL extenstion file and he said just go to your database and Import it. But in my Mysql PHP Myadmin there is not import function what i will do now vinodkv
Access Hosts without CPanel?   (148 Views)
Is there a way a can add an access host without using a CPanel My host's control panel is way different with CPanel. PHPmyadmin is provided, and maybe I can use that. But how
How to concatenate a address - without empty fields appearing.   (178 Views)
Hi Devshed, I store addresses with 7 fields, address_1, address_2, address_3, city, county, postcode, country. Naturally not all of these fields are used by each and every address. I would like to concatenate the address, with the values separated by commas, but not return the empty values in the list. My unsucessful attempts so far have revolved around, CONCAT_WS. SELECT CONCAT_WS( ', ', address_1, address_2, address_3, city, county, postcode, country ) AS combined_address FROM addresses This returns a string with 7 commas in it regardless of how many fields the address uses, eg a sample address 49 Rookery Rd, Selly Oak, , , Birmingham, B297DG , But I would like 49 Rookery Rd, Selly Oak, Birmingham, B297DG Is there a way to do this easily, COALESCE perhaps
Table without an index   (125 Views)
Hi I have a table with the following definition: Code: CREATE TABLE `user_email_address` ( `user_id` int(11) unsigned NOT NULL, `email_address` varchar(80) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 Table purpose: This table will be used to store all email address of all users. One user may have multiple email address. Purpose: I have not defined an index for this table because the only 2 things i'd need to do with this table are "SELECT" and "DELETE" records. If I want to SELECT or DELETE all email address for a particular user, I'd use any of the following SQL Queries: Code: SELECT * FROM user_email_address WHERE user_id=1 Code: DELETE FROM user_email_address WHERE user_id=1 Question: If my purpose is to only SELECT or DELETE the records of a specific user, do I still have to define an index Any inputs on this would be appreciated. Many
SQL Syntax - query without join   (150 Views)
I have this version of query: SELECT Pessoa.Nome, Pessoa.CPF, Profissao.description FROM Profissao INNER JOIN (Pessoa INNER JOIN AssociationProf ON Pessoa.IDPessoa = AssociationProf.IDPessoa) ON Profissao.IDProfissao = AssociationProf.IDProfissao WHERE (((Pessoa.sexo)="F") AND ((Profissao.IDProfissao)=1)); What would be the correct version of this query without using join
mysqldump without "create database"?   (154 Views)
hey all, just a quick question. is it possible to create a mysqldump without the "create database" and "use database" comments in the resulting file I need to restore a database but it needs to go into a temporary table first.
MySQL without SP or MS SQL Server   (220 Views)
, I'm planning a new site and have the option to decide to use .NET or PHP. At first I wanted to use .NET, I have been using SQL Server for several years and find it very fast for web using Stored Procedures. The lack I found is that almost all Windows hostings, don't offer access to automated tasks or jobs (like the cron jobs in Linux) and I need it for this site. I also have the possibility to use a Linux hosting with MySQL 4.1 (without Stored Procedures), and I was wondering if this would be ok. I remember that when I started using Stored Procedures in SQL Server in a site, the perfomance of the site changed a lot. I would like to hear your opinions. Thank you
Using a limit clause, but return the number of rows of the query without the limit   (137 Views)
Hi folks, I have heard of a cool feature that mysql provides a way to return the number of rows of an sql statement that contains a LIMIT as if the LIMIT had not been there. I search the mysql manual, but could not find anything. Anybody knows Thank you!
SQL Query Help, Possible without Subqueries?   (113 Views)
I have a few tables: Projects ProjectID, ProjectName ProjectCosts CostID, ProjectID, CostTypeID, YearId, CostAmount CostTypes CostTypeID, CostTypeName Years YearId, YearName How can I make a report to show the following without having to make a subquery for each filtered total ProjectName, TotalofCostTypeID1Year1, TotalofCostTypeID1Year2, TotalofCostTypeID1Year3, TotalofCostTypeID2Year1, TotalofCostTypeID2Year2, TotalofCostTypeID2Year3 I was able to do it using a subquery but the query is very long (there are two types and 7 years for each).
Select row without reg exp   (129 Views)
hello i have a table record digit combination, the allow digit set is [1,2,3,4,5,6,7,8,9], the table have structure Code: CREATE TABLE `combos` ( `id` SMALLINT NOT NULL UNSIGNED AUTO_INCREMENT `slot1` TINYINT NOT NULL `slot2` TINYINT NOT NULL `slot3` TINYINT NOT NULL `slot4` TINYINT NOT NULL PRIMARY KEY (`id`) UNIQUE (`slot1`,`slot2`,`slot3`, `slot4`) ) more condition for each row slot1 as you can see this is poor, is there a better solutionall suggestion are welcome
Relevance, without full text   (148 Views)
dear all, I would like to display results sorted by relevance, but not necisarily using fulltext. basically, what I am trying to achive is this. a user wants to add a new client to the system, so they enter the first and surname in a single field. i want to then query the client table and show them any exact or close matches to the data they entered... so if they entered John Smith, the following page would show them all the John Smiths in the database, then all the other Smiths and all the other Johns, in that order. I am currently using WHERE fname LIKE %John% OR sname LIKE %John% OR sname LIKE %Smith% OR fname LIKE %Smith%, which gives me the correct reulsts but not ordered in the way I want. Can any help Many
Count() like columns without grouping   (239 Views)
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 | +--------+----------------+--------------+--------+
display records using xml without using php (was "Help")   (116 Views)
i just want to know if for example i have a database with a table and some records in it, can i display those records in the database using xml without using php.
recovering a db without a dump   (192 Views)
hi. I just lost my hard drive Although I don't have a dump of the lost database, I do have a fairly recent backup of the mysql directory which contains everything, including the frm, myi and myd files. After installing my new hard drive, my plan for the db is to install mysql. Then just replace that entire directory. This seems like a pretty simple solution. I'm just wondering if anyone has ever done this and run into any unexpected problems Can this approach successfully restore the table definitions and data
ERROR 1170 (42000) BLOB/TEXT colunm 't0' used in key specification without key length   (354 Views)
I've created a .sql page to create a table and get the following error: ERROR 1170 (42000) BLOB/TEXT colunm 't0' used in key specification without key length The page is as follows: use simple_user; drop table if exists text; create table text( username varchar(16) not null, t0 text, t2 text, t3 text, t4 text, t5 text, t6 text, t7 text, t8 text, t9 text, index(username), index(t0), index(t1), index(t2), index(t3), index(t4), index(t5), index(t6), index(t7), index(t8), index(t9) ); I've tried several different ways to add what it wants but at best I just get the error mesage to switch to 1064 syntax error. How do I add the key length that is needed
Help with query. Join with Order By different than without Order By   (130 Views)
I have a query that produces an item from the forum ...links to the messages table twice...one for the ID_FIRST_MSG and the second for the ID_LAST_MSG and then adds two tables of member based on whomever wrote the messages. The first message is for the one that posts the topic and the second is for the person that last replied to it. See query here: Code: SELECT mess1.ID_MEMBER AS post_member_id, mess2.ID_MEMBER AS reply_member_id, mem1.memberName AS post_member, mem2.memberName AS reply_member FROM smf_topics LEFT JOIN smf_messages AS mess1 ON smf_topics.ID_FIRST_MSG = mess1.ID_MSG LEFT JOIN smf_members AS mem1 ON mem1.ID_MEMBER = mess1.ID_MEMBER LEFT JOIN smf_messages AS mess2 ON smf_topics.ID_LAST_MSG = mess2.ID_MSG LEFT JOIN smf_members AS mem2 ON mem2.ID_MEMBER = mess2.ID_MEMBER LIMIT 1 It produces: 4, 3, user2, user1 However, when I add an ORDER BY on it like this to do get the latest entry: Code: ORDER BY mess1.posterTime DESC It produces: 3, 3, user1, user1 What gives I've put STRAIGHT_SORT in the select as well and that didn't do anything different. Not sure why it's doing this. Any ideas , Using Inner joins will make it so I get 3, 3, user1, user1 as well (just like when using the order by). LIMIT 1 is only for getting the first item. I could have used limit 5 above because that's what I'm really using but for simplicity sake I only used the one.
Cant export database without weird characters showing up == ?   (156 Views)
1) My local database has weird characters () in there which should be spaces. 2) So I do a find and replace on the entire database to remove them: Code: UPDATE report SET overview = REPLACE(overview, '', ''); 3) I check the data locally and all the 's are removed. 4) I export the database and try to import it on my testing server online, and the characters are still in there! I try to import the same SQL file locally and the characters come back as well. Something is happening when I export the database to an SQL file. I have tried a MyISAM/UTF8 and it didnt work. I tried a MyISAM/latin and it didnt work. what am i doing wrong
Connect to Database without Socket ?   (182 Views)
Hi. I'm in the process of migrating a website to a new host and found to my horror that all of my database connections stopped working. This is my old code... Code: mysql_connect(localhost,$username,$password); mysql_select_db($database)or die("Unable to select database"); ...which I'm accustomed using and it worked just fine on my old host. But this code on the new host is giving my the following error message... Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) in /home/www/path/to/file.php on line 124 I contacted the host about it and they told me... , Unfortunately, you cannot make connection through socket, please use MySQL settings to establish connection. The correct MySQL database settings are: DB Host: See your MySQL Database Manager section under DB Host column; DB Port: 3306 DB Name: The database name you have assigned, listed inside Database Manager section of your Control Panel; DB Username: The database username you have assigned, listed inside Database Manager section of your Control Panel; DB Password: The database password you have assigned; If there is anything else we can assist you with, please contact us again. ...which went completely over my head. How does one connect to a MySQL database without using a "socket"
Increment Column without Auto-Increment   (118 Views)
What's the fastest way I can use an INSERT statement to insert a new record including an 'ID' which is one greater than the current highest ID in the table. Is this possible without first doing a SELECT query I'll be using php too btw, so if there's a way to do this in php, then I'm open to such suggestions also.
How to cascade delete related records without deleting the main record?   (183 Views)
Let's say I have the following database tables: Code: CREATE TABLE user ( id INT UNSIGNED NOT NULL PRIMARY KEY, username VARCHAR(20) NOT NULL, ) ENGINE = INNODB; CREATE TABLE user_email ( user_id INT UNSIGNED NOT NULL PRIMARY KEY, email VARCHAR(100) NOT NULL, FOREIGN KEY (user_id) REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB; CREATE TABLE user_phone ( user_id INT UNSIGNED NOT NULL PRIMARY KEY, phone VARCHAR(100) NOT NULL, FOREIGN KEY (user_id) REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB; What I want to do is delete all records in the 'user_email' and 'user_phone' tables without deleting the record in the user table. I have a database without nearly 20 tables with one-to-one and one-to-many relationships with the user record and I need a way to cascade delete records in relationship tables.