PHP SELECT *

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

LVL 8
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
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

rgranlundAuthor Commented:
I was hoping there was a way around that.  There are a bunch of fields.
Ryan ChongSoftware Team LeadCommented:
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.
Julian HansenCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.