PHP SELECT *

Robert Granlund
Robert Granlund used Ask the Experts™
on
PHP SELECT * .  I am performing a query on two tables and they both have a column user  They both are associated by their ID.  I want to give table 1 user the alias user and table two the alias alt_user .  How do I do that?
This is wrong but it will help:

 $sql = "SELECT * dd.user AS user, oi.user AS alt_user FROM data dd JOIN orders oi ON dd.id=oi.id  WHERE dd.name LIKE :term";

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Year 2008
Top Expert 2008

Commented:
Instead of select *, you will need to list all the fields that you want from each dd and oi, not just the user aliases:

//assuming that dd has a column named customer_name:
$sql = "SELECT  dd.user AS user, oi.user AS alt_user,  dd.id, dd.customer_name FROM data dd JOIN orders oi ON dd.id=oi.id  WHERE dd.name LIKE :term";

Open in new window

Author

Commented:
I was hoping there was a way around that.  There are a bunch of fields.
Ryan ChongSoftware Team Lead

Commented:
I was hoping there was a way around that.  There are a bunch of fields.
workaround would be named your fields uniquely in these 2 tables, else you gonna write the Select query to explicitly select and liaise the fields when necessary.
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
You can do this just prefix the * with the table alias and put a comma between them

 $sql = "SELECT oi.*, dd.*, dd.user AS user, oi.user AS alt_user FROM data dd JOIN orders oi ON dd.id=oi.id  WHERE dd.name LIKE :term";

Open in new window

Bear in mind though, with the above your user fields will be repeated. You will have three 'user' fields and one alt_user - and which value comes through in the 'user' field will be up to whatever is consuming you might want to consider changing dd.user to dd.primary_user to distinguish it from the other fields.

This is provided only as a "yes it can be done" - personally I would do the explicit naming of fields and only the ones you actually need.

I use SQLYog - it has a neat feature where you can right click a table and generate an INSERT / SELECT statement - it produces the query with all the fields - which is a good starting point to modify it - especially when the table has a lot of fields.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial