Data from two tables with same column names

Question

I have a table for users. But when a user makes any changes to their profile, I store them in a temp table until I approve them. The data then is copied over to the live table and deleted from the temp table.

What I want to achieve is that when viewing the data in the admin panel, or in the page where the user can double check before submitting, I want to write a single query that will allow me to fetch the data from both tables where the id in both equals $userid. Then I want to display them a table form, where old value appears in the left column and the new value appears in the right column.

I've found some sql solutions, but I'm not sure how to use them in php to echo the results as the columns in both have the same name.

Solution 1

Adding AS to a column name will allow you to alias it to a different name.

SELECT table1.name AS name1, table2.name AS name2, ...
  FROM table1
  INNER JOIN table2
    ON ...

Solution 2

Here is your required query. Let suppose you have for example name field in two tables. Table one login and table 2 information. Now

SELECT login.name as LoginName , information.name InofName 
FROM login left join information on information.user_id = login.id

Now you can use LoginName and InofName anywhere you need.

Solution 3

If you use the AS SQL keyword, you can rename a column just for that query's result.

SELECT
    `member.uid`,
    `member.column` AS `oldvalue`,
    `edit.column` AS `newvalue`
FROM member, edit
WHERE
    `member.uid` = $userId AND
    `edit.uid` = $userId;

Something along those lines should work for you. Although SQL is not my strong point, so I'm pretty sure that this query would not work as is, even on a table with the correct fields and values.

Comments

[salmanhijazi]

How can you automatically alias multiple column names? For example if I wanted to SELECT * from both tables?

[salmanhijazi]

How can you automatically alias multiple column names? For example if I wanted to SELECT * from both tables?

[Ignacio Vazquez-Abrams]

You can't. You must alias each name on your own.

[salmanhijazi]

That's painful :(. Okay let me try.

[Muhammad Raheel]

This will not do the trick or else you table column names should be different then it will be ok but the good practice is that only select those columns which are required