PHP MySQL – Joining tables in PhpMyAdmin

Lets suppose we have a database called Prints with several tables. The two tables we want to join are called ‘prints’ and ‘artists’. When we join the two we will create a new table which we will call ‘printdetails’.

We want to join both tables into one by using  a combination of columns from each table.

From the PRINTS table we will use id, title, price, size, description and src.

From the ARTISTS table we will use givenname, family name and aka.

Click on the SQL tab at the top of the page while you are in the main database which for us is PRINTS (Don’t confuse this with the PRINTS table) Take a look at the breadcrumb trail in the images to see what I am talking about.

We are going to create a new table called PRINTDETAILS which will be a table that has all the elements from two table in the one.

Type the following as your SQL command:


CREATE VIEW printdetails AS
SELECT prints.id,title,price,size,description,src,givenname,familyname,aka
FROM prints INNER JOIN artists ON prints.artistid = artists.id

NB: The prints.id (above) just defines that the id from the prints table should be used as both tables have an id. The capitals denotes that this is SQL command rather than the lowercase which is referring to columns and tables.