Creating a view from multiple tables |
Creating a view from multiple tables |
tudsy |
Aug 5 2018, 11:32 PM
Post
#1
|
Advanced Member Group: Members Posts: 248 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 |
Sep 7 2018, 05:40 PM
Post
#2
|
Programming Fanatic Group: Members Posts: 1,984 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
I want to add that if you are happy with your database layout and just want to search all 4 tables for a certain email address then what you need is UNION, not JOIN. Here is another test script, be sure to change the '$email' value and database login information. Let me know what happens when you run the script.
CODE $email = "xxxxx@yyyyyy.zzz"; $conn = new mysqli("host", "user", "pass", "dbname"); if($conn->connect_error) { die("Can't connect to database: {$conn->error}"); exit(); } $sql = "(SELECT * FROM Customers WHERE `email` = '$email') UNION (SELECT * FROM CustomersArt WHERE `emailadd` = '$email')"; if(!$result = $conn->query($sql)) { die("Database Query Error: {$conn->error}"); exit(); } $rows = mysqli_fetch_all($result); var_dump($rows); $result->close(); $conn->close(); |
tudsy |
Sep 7 2018, 08:50 PM
Post
#3
|
Advanced Member Group: Members Posts: 248 Joined: 30-September 14 Member No.: 21,611 |
I want to add that if you are happy with your database layout and just want to search all 4 tables for a certain email address then what you need is UNION, not JOIN. Here is another test script, be sure to change the '$email' value and database login information. Let me know what happens when you run the script. CODE $email = "xxxxx@yyyyyy.zzz"; $conn = new mysqli("host", "user", "pass", "dbname"); if($conn->connect_error) { die("Can't connect to database: {$conn->error}"); exit(); } $sql = "(SELECT * FROM Customers WHERE `email` = '$email') UNION (SELECT * FROM CustomersArt WHERE `emailadd` = '$email')"; if(!$result = $conn->query($sql)) { die("Database Query Error: {$conn->error}"); exit(); } $rows = mysqli_fetch_all($result); var_dump($rows); $result->close(); $conn->close(); Results: Viewing your data from ECOVIB2DS database with email address ecovib2d@live.com ********DATA************** 0 ecovib2d@live.com 10_02_am_23_06_05.jpg fdeasv 2 on 67 2018-08-25 00:00:00 |
CharlesEF |
Sep 7 2018, 09:08 PM
Post
#4
|
Programming Fanatic Group: Members Posts: 1,984 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Results: Great, that is what should be returned, based on the contents of the database you posted before.Viewing your data from ECOVIB2DS database with email address ecovib2d@live.com ********DATA************** 0 ecovib2d@live.com 10_02_am_23_06_05.jpg fdeasv 2 on 67 2018-08-25 00:00:00 Now, add the 3rd table to the query and run the script again. If that works then add the 4th table to the query. Remember, you may run in to problems if tables contain the same column name. |
Lo-Fi Version | Time is now: 21st September 2024 - 01:47 PM |