I'm creating a new PHP page and I have a variable which consists out of the addition of two fields of my MySQL database. I would like to find which row corresponds to the variable, which consists of a combination of two fields.  I was unable to find any answers online, and when I did, none of them seem to work.  Database example:   ID | Name 1  | Name 2 ----+---------+--------  1  | john 1  | doe  2  | jane    | doe  3  | john    | doe  4  | john    | doe 1   My variable which I ought to match to a row is $var = "john doe 1";, and splitting my variable is not an option.  I expect the output of said SQL statement to be the row with ID = 4.  EDIT: I do not believe this to be a duplicate. I'm not having any problems with the CONCAT clause itself, I was consulting about which clause to use and how to use it, not how to fix my current one.
121594
I would like to search my table having a column of first names and a column of last names.  I currently accept a search term from a field and compare it against both columns, one at a time with       select * from table where first_name like '%$search_term%' or      last_name like '%$search_term%';   This works fine with single word search terms but the result set includes everyone with the name "Larry". But if someone enters a first name then a space, then a last name, I want a narrower search result. I've tried the following without success.         select * from table where first_name like '%$search_term%' or last_name      like '%$search_term%' or concat_ws(' ',first_name,last_name)      like '%$search_term%';   Any advice?  EDIT:  The name I'm testing with is "Larry Smith".  The db stores "Larry" in the "first_name" column, and "Smith" in the "last_name" column.  The data is clean, no extra spaces and the search term is trimmed left and right.  EDIT 2:  I tried Robert Gamble's answer out this morning.  His is very similar to what I was running last night.  I can't explain it, but this morning it works.  The only difference I can think of is that last night I ran the concat function as the third "or" segment of my search query (after looking through first_name and last_name). This morning I ran it as the last segment after looking through the above as well as addresses and business names.    Does running a mysql function at the end of a query work better than in the middle?
111051
How would you write a prepared MySQL statement in PHP that takes a differing number of arguments each time? An example such query is:  SELECT `age`, `name` FROM `people` WHERE id IN (12, 45, 65, 33)   The IN clause will have a different number of ids each time it is run.  I have two possible solutions in my mind but want to see if there is a better way.  Possible Solution 1 Make the statement accept 100 variables and fill the rest with dummy values guaranteed not to be in the table; make multiple calls for more than 100 values.  Possible Solution 2 Don't use a prepared statement; build and run the query checking stringently for possible injection attacks.
30257
I have a table:  table votes (     id,     user,     email,     address,     primary key(id), );   Now I want to make the columns user, email, address unique (together).  How do I do this in MySql?  Of course the example is just... an example. So please don't worry about the semantics.
62823
I'm trying out the following query:  SELECT A,B,C FROM table WHERE field LIKE 'query%' UNION SELECT A,B,C FROM table WHERE field LIKE '%query' UNION SELECT A,B,C FROM table WHERE field LIKE '%query%' GROUP BY B ORDER BY B ASC LIMIT 5   That's three queries stuck together, kinda sorta. However, the result set that comes back reflects results from query #3 before the results from query #1 which is undesired.  Is there any way to prioritize these so that results come as all for query #1, then all for query #2 then all for query #3? I don't want to do this in PHP just yet (not to mention having to control for results that showed up in the first query not to show in the second and so forth).
21469
I am studying the new database layer, and I am glad it is changed to an OOP layer. I need to implement a query with db_select(). I found I can add a WHERE statement with $query-&gt;condition(), but by default the conditions are AND'ed together.  How can I OR the conditions together?  $query=db_select('users','u')-&gt;fields('u',array('uid','title','created','uid')); $query-&gt;join('flag_content','fc' , 'u.uid = fc.content_id'); $query-&gt;condition('fc.fid', '5' , '='); $query-&gt;condition('fc.uid', $uid , '='); //…
25040
I have to create a report on some student completions. The students each belong to one client. Here are the tables (simplified for this question).  CREATE TABLE  `clients` (   `clientId` int(10) unsigned NOT NULL auto_increment,   `clientName` varchar(100) NOT NULL default '',   `courseNames` varchar(255) NOT NULL default '' )   The courseNames field holds a comma-delimited string of course names, eg "AB01,AB02,AB03"  CREATE TABLE  `clientenrols` (   `clientEnrolId` int(10) unsigned NOT NULL auto_increment,   `studentId` int(10) unsigned NOT NULL default '0',   `courseId` tinyint(3) unsigned NOT NULL default '0' )   The courseId field here is the index of the course name in the clients.courseNames field. So, if the client's courseNames are "AB01,AB02,AB03", and the courseId of the enrolment is 2, then the student is in AB03.  Is there a way that I can do a single select on these tables that includes the course name? Keep in mind that there will be students from different clients (and hence have different course names, not all of which are sequential,eg: "NW01,NW03")  Basically, if I could split that field and return a single element from the resulting array, that would be what I'm looking for. Here's what I mean in magical pseudocode:  SELECT e.`studentId`, SPLIT(",", c.`courseNames`)[e.`courseId`] FROM ...
118445
I am trying to write a condition into a SQL WHERE clause that forces the column comparison to a variable to be compared in lowercase. However the addExpression function isn't accomplishing this (since that is putting the expression in the field selection, not the where clause.  Here is what I have tried:  $category = 'mobile wifi'; $query = db_select('taxonomy_term_data', 'ttd')     -&gt;fields('ttd', array('tid')); $query-&gt;innerJoin('taxonomy_vocabulary', 'tv', 'ttd.vid = tv.vid'); $query-&gt;addExpression("LOWER(ttd.name) = $category"); $result = $query-&gt;condition('machine_name', 'images_cat', '=')     -&gt;execute()     -&gt;fetchAssoc();   And this:  $category = 'mobile wifi'; $query = db_select('taxonomy_term_data', 'ttd')     -&gt;fields('ttd', array('tid')); $query-&gt;innerJoin('taxonomy_vocabulary', 'tv', 'ttd.vid = tv.vid'); $result = $query-&gt;condition('machine_name', 'images_cat', '=')     -&gt;condition('LOWER(ttd.name)', $category, '=')     -&gt;execute()     -&gt;fetchAssoc();   The second one becomes invalid and the first one causes the problem I mentioned above. Any thoughts or suggestions?  Thanks, Patrick
68032
I have a table item having just one column name:-  name ----- toys shirt mobile Shirt speaker Toys ....  ....   I am trying to delete duplicate rows except one duplicated record with the help of below query  DELETE FROM ( SELECT `name`,`rownumber` FROM (SELECT GREATEST(0,@num := IF(`name` = @NAME,  @num + 1, 1),LEAST(1, LENGTH(@NAME := `name`))) AS rownumber,`name` FROM  item ORDER BY `name`) AS result1 ) AS result  WHERE rownumber &gt;1   The Inner query (Starts from SELECT name and ends with result1) returns individual auto incremented number for each repeating group but when i run entire query then it does not work.  I would like to know what changes i need to make in the inner query (contained in round braces) to delete duplicate rows.
60461
What is a fast way to select a random row from a large mysql table?  I'm working in php, but I'm interested in any solution even if it's in another language.
6644
I've got two tables:  TableA ------ ID, Name  TableB ------ ID, SomeColumn, TableA_ID (FK for TableA)   The relationship is one row of TableA - many of TableB.  Now, I want to see a result like this:  ID     Name      SomeColumn  1.     ABC       X, Y, Z (these are three different rows) 2.     MNO       R, S   This won't work (multiple results in a subquery):  SELECT ID,        Name,         (SELECT SomeColumn FROM TableB WHERE F_ID=TableA.ID) FROM TableA   This is a trivial problem if I do the processing on the client side. But this will mean I will have to run X queries on every page, where X is the number of results of TableA.   Note that I can't simply do a GROUP BY or something similar, as it will return multiple results for rows of TableA.   I'm not sure if a UDF, utilizing COALESCE or something similar might work?
79155
I have two tables.   Table A:  Company_name ------------------------------- Tata consultancy  Infosys tech Atm service Air force firm  Table B:   Company1_name                    |  Id --------------------------------   ----- Atm and service.                 |   1 Honey tech.                      |   2 Tata &amp; consultancy.              |   3 Graft soft.                      |   4 Atm, service.                    |   5    I want this result:  Id ---- 1 3 5   Description:     For each Company_name in table A, find a row in table B whose company1_name value has all the words from table A's value, and return the matching row's Id.   How to design a query for this in MySQL?
40938
After I posted this question, I found a similar post that seemed to answer it: . I'll leave this in place for now, as I am not sure how to mark my own question as a duplicate  In attempting to debug a SOQL query error, I encountered a strange behavior with Database.query and bind variables. Specifically, when I run this code:  String userQuery = 'SELECT Id FROM User'; if (true) {     List&lt;String&gt; firstNames = new List&lt;String&gt;{'James','Joe'};     userQuery += ' WHERE FirstName IN :firstNames'; } Database.query(userQuery);   the Database.query statement executes with no errors, despite the fact that the variable firstNames does not exist in its context. However, when I add an else clause:  String userQuery = 'SELECT Id FROM User'; if (true) {     List&lt;String&gt; firstNames = new List&lt;String&gt;{'James','Joe'};     userQuery += ' WHERE FirstName IN :firstNames'; } else {     List&lt;String&gt; firstNames; } Database.query(userQuery);   I receive a null object reference exception. It seems like Salesforce is checking to see if the variable is only declared once beforehand and, if so, uses its value in the context it was declared. This seems strange to me, so I was wondering if anybody here has any insight into what is going on under the hood.
106137
If we have 2 tables  **user** +----+------+------+-----------+ | id | user | pass | gender_id | +----+------+------+-----------+ | 1  | lisa | good |     11    | | 2  | jack | pass |     10    | | 3  | paul | 5555 |     10    | | 4  | rich | none |     10    | +----+------+------+-----------+  **gender** +----+--------+ | id | gender | +----+--------+ | 11 | female | | 10 | male   | +----+--------+   If we join  them like so  SELECT user.id, user.user, user.pass, user.gender_id, gender.gender FROM `user` JOIN `gender` on user.gender_id   The result of query  array(4) {   [0]=&gt;   array(4) {     ["id"]=&gt;     string(3) "2"     ["type"]=&gt;     string(6) "jack"     ["pass"]=&gt;     string(11) "pass"     ["gender"]=&gt;     string(1) "male"     }   [1]=&gt;   array(4) {     ["id"]=&gt;     string(3) "3"     ["type"]=&gt;     string(6) "paul"     ["pass"]=&gt;     string(11) "5555"     ["gender"]=&gt;     string(1) "male"     }   [2]=&gt;   array(4) {     ["id"]=&gt;     string(3) "4"     ["type"]=&gt;     string(6) "rich"     ["pass"]=&gt;     string(11) "none"     ["gender"]=&gt;     string(1) "male"     }   [3]=&gt;   array(4) {     ["id"]=&gt;     string(3) "1"     ["type"]=&gt;     string(6) "lisa"     ["pass"]=&gt;     string(11) "good"     ["gender"]=&gt;     string(1) "female"     }   The order has now changed even though we did not specify this.(user lisa is last) If we change lisa gender_id to 11, and run the same query, the result will be  array(4) {   [0]=&gt;   array(4) {     ["id"]=&gt;     string(3) "1"     ["type"]=&gt;     string(6) "lisa"     ["pass"]=&gt;     string(11) "good"     ["gender"]=&gt;     string(1) "male"     }   [1]=&gt;   array(4) {     ["id"]=&gt;     string(3) "2"     ["type"]=&gt;     string(6) "jack"     ["pass"]=&gt;     string(11) "pass"     ["gender"]=&gt;     string(1) "male"     }   [2]=&gt;   array(4) {     ["id"]=&gt;     string(3) "3"     ["type"]=&gt;     string(6) "paul"     ["pass"]=&gt;     string(11) "5555"     ["gender"]=&gt;     string(1) "male"     }   [3]=&gt;   array(4) {     ["id"]=&gt;     string(3) "4"     ["type"]=&gt;     string(6) "rich"     ["pass"]=&gt;     string(11) "none"     ["gender"]=&gt;     string(1) "male"     }   This result is how the rows are positioned in the table(user lisa first), which is represented correctly in the results as we did not specify any order.  Why is the first result set being ordered when we are not specifying this. It does not seem correct, I want the results to be pulled exactly how they are positioned in the table.  Thanks
37749
I am working on a project involving cards from the Magic: The Gathering game. To be of any use, I must load the basic information about each card into my database, but some of the fields may contain arrays of text, that is, they may contain multiple values .  For example, a card can be classified to be both "green" and "blue". Or it can have several different creature types specified, and so on.  My first thought was to simply store the values as a string, and then search using the LIKE keyword. In combination with the NOT keyword, it should be possible to search for any desired combination of words, and also exclude certain words.   However, LIKE is going to be (relatively) slow as it can't use an index and has to rely on text pattern matching, and probably has other drawbacks as well. With normalization I could improve speed by assigning an integer key to each keyword, and then create a table that connects each card with the relevant keys (see  for a clearer description).  But with normalized tables I have to deals with joins (severely increased code complexity, not only in the SQL part), transactions (to update all relevant tables or rollback everything), and possibly decreased performance due to joins (see ).  AFAIK there are roughly 20000-25000 different magic cards in existence (and more being released every year). With one row for each card, is it worth it overall to normalize the data, or is the benefit not worth the costs? Why/Why not? And how do you decide this question in general?  EDIT: I am currently using a MySQL database, but I would also appreciate answers that apply to any of the other non-commercial/open source databases out there. Who knows - I might switch to a different database at some point.  Answers should not provide a technical solution only, but explain why it would/might be the right solution in terms of best/good enough performance for least amount of effort.
50568
Suppose I have a table with an auto_increment column, and then insert multiple values in a single statement:  insert into foo (bar) values ('a'), ('b'), ('c');  From the documentation, last_insert_id() returns the first generated ID for the query, the ID for a in this case.  Does mysql give any guarantees that a b and c will then be assigned sequential values?  In other words, if last_insert_id() returns 1 for a, is there any guarantee that b = 2 and c = 3? Or could there be a scenario where due to multiple concurrent inserts, I could end up with something like "a = 1, b = 5, c = 9"?
63506
This is a part of my .  I have the below logic to split a string value from a column in table tblProjects as below:  SELECT  @Docs = Documents FROM tblProjects WHERE ID = @ID  SET @Docs = @Docs + '$' SET @pos = 0 SET @len = 0  WHILE CHARINDEX('$', @Docs, @pos + 1)&gt;0 BEGIN   SET @len = CHARINDEX('$', @Docs, @pos + 1) - @pos   SET @value = SUBSTRING(@Docs, @pos, @len)    IF CHARINDEX('|', @value) &gt;0   BEGIN     SELECT  @type = SUBSTRING(@value, 1, CHARINDEX('|', @value) - 1),             @name = SUBSTRING(@value, CHARINDEX('|', @value) + 1, 200)     INSERT INTO tblDocuments VALUES(@ID, @value, @type, @name)   END       SET @pos = CHARINDEX('$', @Docs, @pos + @len) +1 END   A sample input would be   insert into tblProjects(ID, Documents)  values('P_1', 'gdgfg|jkkgk$bkgkkj|kjgkgk$vjffj|Khkgjhg$jkgkvghv|kbkghhkgk');   I need to split the Documents string and insert the values into a separate table. Values delimited by | should go into separate columns of the same row, whereas values for a particular row are delimited by $.   Is there a single query method to complete this operation, for all rows in the table, in the minimum possible time?
67901
I have the following data:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  1   |     ...       |     ...      | MAIN ST           |  2   |     ...       |     ...      |         MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | BERRY LN          |  5   |     ...       |     ...      | BERRY LN          |  6   |     ...       |     ...      | JOHN RD           |  7   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   I want all rows with duplicates in the road column removed. It does not matter to me which duplicates are removed. The end result can look something like this:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   where the id's are any value, as long as the road column is unique.   Is there a simple way to do this in SQL or T/SQL? I followed the guide , but it only applies to duplicate keys with non-unique rows.