Creating a view from multiple tables |
Creating a view from multiple tables |
tudsy |
Aug 5 2018, 11:32 PM
Post
#1
|
Serious Coder Group: Members Posts: 251 Joined: 30-September 14 Member No.: 21,611 |
Hi
I have 4 tables, CustomersArt,Customers,subscribers and emailcomm. My question is, how do i create a view from these 4 tables with the column email ? I have done a view from one table (see below): $query = "CREATE VIEW ecovib2d AS SELECT email FROM CustomersArt"; Any help will be appreciated. |
CharlesEF |
Aug 6 2018, 01:42 PM
Post
#2
|
Programming Fanatic Group: Members Posts: 1,990 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
You need to look in to INNER JOIN or OUTER JOIN to link the tables. You don't provide enough detail for me to suggest anything else.
|
tudsy |
Aug 6 2018, 01:57 PM
Post
#3
|
Serious Coder Group: Members Posts: 251 Joined: 30-September 14 Member No.: 21,611 |
You need to look in to INNER JOIN or OUTER JOIN to link the tables. You don't provide enough detail for me to suggest anything else. Hi Thanks for that. More detail. Here are the tables with the column names: CustomersArt(Itemid,email,Graphic,name,Price,terms,Age,Date); Customers(Itemid,email,name,Price,terms,Age,Date,Softbought); emailcomm(Itemid,Age,name,email,Date); subscribers(itemid,username,email,ConfirmationCode,Confirmation,Date); Thanks. |
CharlesEF |
Aug 6 2018, 02:07 PM
Post
#4
|
Programming Fanatic Group: Members Posts: 1,990 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
You still don't tell me what columns you want from each table. Anyway, you should post your own code that shows your attempt and then I can help. You won't learn anything if I write it for you.
|
tudsy |
Aug 6 2018, 11:30 PM
Post
#5
|
Serious Coder Group: Members Posts: 251 Joined: 30-September 14 Member No.: 21,611 |
You still don't tell me what columns you want from each table. Anyway, you should post your own code that shows your attempt and then I can help. You won't learn anything if I write it for you. Thanks for that. The user or visitor enters an email address and displays the entire 4 tables according to the email address. Thanks. |
tudsy |
Aug 10 2018, 03:55 AM
Post
#6
|
Serious Coder Group: Members Posts: 251 Joined: 30-September 14 Member No.: 21,611 |
Hi
A solution: “CREATE VIEW ecovib2d AS SELECT CustomersArt.email,Customers.email,emailcomm.email,subscribers.email”; Is this correct? Thanks. |
CharlesEF |
Aug 10 2018, 12:13 PM
Post
#7
|
Programming Fanatic Group: Members Posts: 1,990 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
No. You're not joining any tables. Here is an example, taken from here:
CODE CREATE VIEW GiftsList AS SELECT b.name user_from, c.name user_to, d.name gift_name, d.price gift_price FROM gift a INNER JOIN users b ON a.user_from = b.id INNER JOIN users c ON a.user_from = c.id INNER JOIN items d ON a.item = d.id |
CharlesEF |
Aug 10 2018, 03:31 PM
Post
#8
|
Programming Fanatic Group: Members Posts: 1,990 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
You should start by joining just 1 table. I haven't tried to explain INNER JOIN because there are hundreds of examples on the internet.
|
CharlesEF |
Aug 11 2018, 12:21 PM
Post
#9
|
Programming Fanatic Group: Members Posts: 1,990 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Ok, here is a hint. Since you never said which columns you want I have included all of them from Customers.
CODE $query = "CREATE VIEW ecovib2d AS SELECT a.email, b.name, b.Price, b.terms, b.Age, b.Date, b.Softbought FROM CustomersArt a You still need to add columns from the other 2 tables but this should get you started. You may not need INNER JOIN, maybe a regular JOIN will work?INNER JOIN Customers b ON a.email = b.email"; |
tudsy |
Aug 11 2018, 10:38 PM
Post
#10
|
Serious Coder Group: Members Posts: 251 Joined: 30-September 14 Member No.: 21,611 |
Ok, here is a hint. Since you never said which columns you want I have included all of them from Customers. CODE $query = "CREATE VIEW ecovib2d AS SELECT a.email, b.name, b.Price, b.terms, b.Age, b.Date, b.Softbought FROM CustomersArt a You still need to add columns from the other 2 tables but this should get you started. You may not need INNER JOIN, maybe a regular JOIN will work?INNER JOIN Customers b ON a.email = b.email"; Thanks for that. Maybe this would work ? $query = "CREATE VIEW ecovib2d AS SELECT CustomersArt.*, Customers.*, emailcomm.*, subscribers.* FROM CustomersArt JOIN Customers ON Customers.* = CustomersArt.* JOIN emailcomm ON emailcomm.* = Customers.* JOIN subscribers ON subscribers.* = emailcomm.* WHERE email = $Email"; Thanks. |
CharlesEF |
Aug 12 2018, 12:16 AM
Post
#11
|
Programming Fanatic Group: Members Posts: 1,990 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
I don't think so, you need to change the ON part. You must join based on the email address. Also, some of your tables have the same column name. You might have to use an alias for those columns (look in to AS). In other words, you might have to specify column names and not use the * character. And you might want to use table names only once. In my post a = table CustomersArt while b = Customers. I've never created a VIEW so you may have to do some testing.
|
tudsy |
Aug 14 2018, 08:21 PM
Post
#12
|
Serious Coder Group: Members Posts: 251 Joined: 30-September 14 Member No.: 21,611 |
I don't think so, you need to change the ON part. You must join based on the email address. Also, some of your tables have the same column name. You might have to use an alias for those columns (look in to AS). In other words, you might have to specify column names and not use the * character. And you might want to use table names only once. In my post a = table CustomersArt while b = Customers. I've never created a VIEW so you may have to do some testing. Hi I have been getting this 1064 error (syntax error) on the following query: CREATE VIEW ecovib2d AS SELECT CustomersVideo.*,Customers.* FROM CustomersVideo FULLJOIN Customers ON CustomersVideo.email = Customers.email WHERE email =".'$Email.'; Any help will be appreciated. |
tudsy |
Aug 14 2018, 09:53 PM
Post
#13
|
Serious Coder Group: Members Posts: 251 Joined: 30-September 14 Member No.: 21,611 |
I don't think so, you need to change the ON part. You must join based on the email address. Also, some of your tables have the same column name. You might have to use an alias for those columns (look in to AS). In other words, you might have to specify column names and not use the * character. And you might want to use table names only once. In my post a = table CustomersArt while b = Customers. I've never created a VIEW so you may have to do some testing. Hi I have been getting this 1064 error (syntax error) on the following query: CREATE VIEW ecovib2d AS SELECT CustomersVideo.*,Customers.* FROM CustomersVideo FULLJOIN Customers ON CustomersVideo.email = Customers.email WHERE email =".'$Email.'; I may have found the error.Thanks. Any help will be appreciated. |
CharlesEF |
Aug 15 2018, 01:06 AM
Post
#14
|
Programming Fanatic Group: Members Posts: 1,990 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Just in case, your error is here:
CODE WHERE email =".'$Email.'; You have an un-terminated string. You want PHP to convert $Email so it must be enclosed by double quotes, like this: CODE WHERE email ="$Email"; |
CharlesEF |
Aug 15 2018, 12:12 PM
Post
#15
|
Programming Fanatic Group: Members Posts: 1,990 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Correction: Since the entire query should be enclosed with double quotes all you need is:
CODE WHERE email = '$Email'; |
tudsy |
Aug 15 2018, 11:27 PM
Post
#16
|
Serious Coder Group: Members Posts: 251 Joined: 30-September 14 Member No.: 21,611 |
Hi
I have another problem. Here is a query: $query = "CREATE VIEW ecovib2d AS SELECT Customers.*,subscribers.*,CustomersArt.*,CustomersVideo.*,emailcomm.* FROM Customers FULLJOIN CustomersArt ON (CustomersArt.Itemid = Customers.Itemid ) FULLJOIN CustomersVideo ON (CustomersVideo.Itemid = Customers.Itemid ) FULLJOIN subscribers ON (subscribers.Itemid = Customers.Itemid ) FULLJOIN emailcomm ON (emailcomm.Itemid = Customers.Itemid ) WHERE email ='$Email'"; the runtime error is as follows: Error Message: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FULLJOIN CustomersArt ON (CustomersArt.Itemid = Customers.Itemid ) ' at line 4 Any help will be greatly appreciated.Thanks. |
CharlesEF |
Aug 16 2018, 12:13 AM
Post
#17
|
Programming Fanatic Group: Members Posts: 1,990 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
I don't see anything wrong, as far as syntax. But, I've never heard of 'FULLJOIN'. I've heard of 'FULL OUTER JOIN' and 'FULL INNER JOIN'. Maybe it should be 'FULL JOIN'?
|
tudsy |
Aug 20 2018, 08:11 PM
Post
#18
|
Serious Coder Group: Members Posts: 251 Joined: 30-September 14 Member No.: 21,611 |
I don't see anything wrong, as far as syntax. But, I've never heard of 'FULLJOIN'. I've heard of 'FULL OUTER JOIN' and 'FULL INNER JOIN'. Maybe it should be 'FULL JOIN'? Hi Thanks for that. I put commas at the end of each join and iot got rid of that error. However, I get this error now: Error Message: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'FULL' I have attached the rest of the code. view1.php ( 1.3k ) Number of downloads: 1784 view.php ( 1.9k ) Number of downloads: 1776 |
CharlesEF |
Aug 21 2018, 02:43 PM
Post
#19
|
Programming Fanatic Group: Members Posts: 1,990 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
I don't see anything wrong with the syntax. As I said before, the 4 tables share some column names. Maybe you need to specify column names instead of using the * character and use AS when column names are duplicated. Have you tested using only 1 joined table? Test with 2 tables that don't share any column names. If it works then add another table join to the test. If you start getting errors when column names are duplicated then you know you can't use the * character.
And, I'm not sure 'FULL JOIN' is valid. I don't see it in the MySQL documentation. But, maybe I just missed it. |
tudsy |
Aug 26 2018, 12:48 AM
Post
#20
|
Serious Coder Group: Members Posts: 251 Joined: 30-September 14 Member No.: 21,611 |
I don't see anything wrong with the syntax. As I said before, the 4 tables share some column names. Maybe you need to specify column names instead of using the * character and use AS when column names are duplicated. Have you tested using only 1 joined table? Test with 2 tables that don't share any column names. If it works then add another table join to the test. If you start getting errors when column names are duplicated then you know you can't use the * character. And, I'm not sure 'FULL JOIN' is valid. I don't see it in the MySQL documentation. But, maybe I just missed it. Hi Here is the latest query: $query = "CREATE VIEW ecovib2d AS SELECT Customers.Customersitemid,Customers.email,Customers.name,Customers.Price,Customers.terms,Customers.Age,Customers.Date,Customers.Softbought,CustomersArt.CustomersArtitemid,CustomersArt.email,CustomersArt.graphic,CustomersArt.name,CustomersArt.Price,CustomersArt.Age,CustomersArt.Date FROM Customers join CustomersArt on Customers.Customersitemid = CustomersArt.CustomersArtitemid WHERE email ='$Email'"; but now I get this error: Error Message: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '='ecovib2d@live.com'' at line 4 Any help will be appreciated. Thanks. |
Lo-Fi Version | Time is now: 19th October 2024 - 02:50 PM |