Hướng dẫn mysql select two columns

I'm trying to search two fields as one from a MySQL database using PHP.

e.g.

mysql_query("
  SELECT (first_name,last_name) As name
  FROM people
  WHERE (name LIKE '%" . $term . "%')
");

I thought that this was the code to use, but to no avail. It has been a while since I've done this and I can't remember exactly how to achieve the desired result.

Zuul

16k6 gold badges59 silver badges88 bronze badges

asked Aug 31, 2011 at 11:10

You're looking for the CONCAT function.

mysql_query("SELECT CONCAT(first_name, last_name) As name FROM people WHERE (CONCAT(first_name, last_name) LIKE '%" . $term . "%')");

or even...

mysql_query("SELECT CONCAT(first_name, ' ', last_name) As name FROM people WHERE (CONCAT(first_name, ' ', last_name) LIKE '%" . $term . "%')");

I couldn't explain you the reasons behind this (...but maybe someone can leave a comment?), but you can't use the name alias to search for both fields, you have to explicitly CONCAT again.

answered Aug 31, 2011 at 11:14

dee-seedee-see

23k5 gold badges59 silver badges89 bronze badges

6

dee-see

23k5 gold badges59 silver badges89 bronze badges

answered Aug 31, 2011 at 11:21

1

Totally out of ideas here, could be needing a simple solution.

Nội dung chính

• How do I select two data in SQL?
• How do I select two columns in MySQL?
• How do I select multiple values from the same column in MySQL?
• How do I select multiple values from a table in SQL?

Basically my desired query is :

SELECT * FROM table WHERE id = 3,4

I want to select only the row which has ID 3 and 4, or maybe name "andy" and "paul"

Thank you very much for the answer

You need to use index to select multiple rows effectively. Let us first create a table −

mysql> create table DemoTable1501
   -> (
   -> Id int NOT NULL PRIMARY KEY,
   -> URL text
   -> );
Query OK, 0 rows affected (0.62 sec)

Here is the query to create index −

mysql> create index id_index on DemoTable1501(Id);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

Insert some records in the table using insert command −

mysql> insert into DemoTable1501 values(101,'www.facebook.com');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable1501 values(110,'www.google.com');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable1501 values(220,'www.gmail.com');
Query OK, 1 row affected (0.06 sec)
mysql> insert into DemoTable1501 values(350,'www.youtube.com');
Query OK, 1 row affected (0.08 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1501;

This will produce the following output −

+-----+------------------+
| Id  | URL              |
+-----+------------------+
| 101 | www.facebook.com |
| 110 | www.google.com   |
| 220 | www.gmail.com    |
| 350 | www.youtube.com  |
+-----+------------------+
4 rows in set (0.00 sec)

Following is the query to select multiple rows efficiently −

mysql> select * from DemoTable1501
   -> where Id in(101,220,350);

This will produce the following output −

+-----+------------------+
| Id  | URL             |
+-----+------------------+
| 101 | www.facebook.com |
| 220 | www.gmail.com    |
| 350 | www.youtube.com  |
+-----+------------------+
3 rows in set (0.00 sec)

To prove this, use the SHOW command in which the Handler_read_key uses 3 out of 4 Ids −

mysql> SHOW STATUS LIKE 'Handler_%';

This will produce the following output −

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             |     1 |
| Handler_delete             |     0 |
| Handler_discover           |     0 |
| Handler_external_lock      |     2 |
| Handler_mrr_init           |     0 |
| Handler_prepare            |     0 |
| Handler_read_first         |     0 |
| Handler_read_key           |     3 |
| Handler_read_last          |     0 |
| Handler_read_next          |     0 |
| Handler_read_prev          |     0 |
| Handler_read_rnd           |     0 |
| Handler_read_rnd_next      |     0 |
| Handler_rollback           |     0 |
| Handler_savepoint          |     0 |
| Handler_savepoint_rollback |     0 |
| Handler_update             |     0 |
| Handler_write              |     0 |
+----------------------------+-------+
18 rows in set (0.00 sec)

Updated on 11-Dec-2019 05:53:17

• Related Questions & Answers
• MySQL query to select too many rows?
• MySQL select query with multiple WHERE?
• MySQL query to select top n rows efficiently?
• MySQL query to count rows in multiple tables
• MySQL query to check if multiple rows exist?
• MySQL query to select rows older than a week?
• How to insert multiple rows with single MySQL query?
• MySQL query to get result from multiple select statements?
• Insert multiple rows in a single MySQL query
• MySQL query to select rows one batch at a time
• How to obtain multiple rows in a single MySQL query?
• How to get multiple rows in a single MySQL query?
• MySQL query to select rows except first row in descending order?
• MySQL select query to select rows from a table that are not in another table?
• MySQL Select Multiple VALUES?

How do I select two data in SQL?

The SQL SELECT Statement.

SELECT column1, column2, ... FROM table_name;.

SELECT * FROM table_name;.

Example. SELECT CustomerName, City FROM Customers;.

Example. SELECT * FROM Customers;.

How do I select two columns in MySQL?

To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.

How do I select multiple values from the same column in MySQL?

Note – Use of IN for matching multiple values i.e. TOYOTA and HONDA in the same column i.e. COMPANY. Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (MATCHING_VALUE1,MATCHING_VALUE2);

How do I select multiple values from a table in SQL?

The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.