The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

2 Pages V < 1 2  
Reply to this topicStart new topic
> Creating a view from multiple tables
CharlesEF
post Aug 26 2018, 02:39 AM
Post #21


Programming Fanatic
********

Group: Members
Posts: 1,543
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



QUOTE(tudsy @ Aug 26 2018, 12:48 AM) *
$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'";

Based on the column names you posted earlier your query makes no sense. Your not using the same column names. Try this:
CODE
$query = "CREATE VIEW ecovib2d AS
         SELECT
         Customers.Itemid, Customers.email, Customers.name, Customers.Price, Customers.terms, Customers.Age, Customers.Date, Customers.Softbought,
         CustomersArt.Graphic
         FROM Customers JOIN CustomersArt ON Customers.Itemid = CustomersArt.Itemid
         WHERE Customers.email ='$Email'";
Most of your problems were typos, I think. Also, I didn't include any columns with duplicate names. First see if this query works. If it does then you can try adding columns with the same name. I have already given you an example of this, check post #7. That post uses the column 'name' from 3 tables. Notice the variable name next to each one?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Sep 4 2018, 11:49 PM
Post #22


Advanced Member
****

Group: Members
Posts: 162
Joined: 30-September 14
Member No.: 21,611



Hi

Thanks for that.

I renamed the column names with different names.

I have now decided to go with a simple select statement.


$query ="SELECT

Customers.Customersitemid,Customers.email,Customers.name,Customers.Price,Customers.terms,Customers.Age,Customers.Date,Customers.Softbought,CustomersArt.CustomersArtitemid,CustomersArt.nameof,CustomersArt.Priceofart,CustomersArt.Dateof,CustomersArt.termsof,CustomersArt.Ageof,CustomersArt.graphic,CustomersArt.emailadd

FROM Customers JOIN CustomersArt ON Customers.Customersitemid = CustomersArt.CustomersArtitemid";


But its not working. I cant see what is the problem.

Any help will be appreciated. Thanks.


User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Sep 5 2018, 12:11 AM
Post #23


Programming Fanatic
********

Group: Members
Posts: 1,543
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



QUOTE(tudsy @ Sep 4 2018, 11:49 PM) *

$query ="SELECT Customers.Customersitemid, Customers.email, Customers.name, Customers.Price, Customers.terms, Customers.Age, Customers.Date, Customers.Softbought, CustomersArt.CustomersArtitemid, CustomersArt.nameof, CustomersArt.Priceofart ,CustomersArt.Dateof, CustomersArt.termsof, CustomersArt.Ageof, CustomersArt.graphic, CustomersArt.emailadd
FROM Customers JOIN CustomersArt ON Customers.Customersitemid = CustomersArt.CustomersArtitemid";
So, you're telling me that table 'Customers' has a column named 'Customersitemid' and table 'CustomersArt' has a column named 'CustomersArtitemid'?
Or, does table 'Customers' have a column named 'Itemid' and does table 'CustomersArt' have a column named 'Itemid'?

Which is correct?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Sep 5 2018, 12:15 AM
Post #24


Advanced Member
****

Group: Members
Posts: 162
Joined: 30-September 14
Member No.: 21,611



QUOTE(CharlesEF @ Sep 5 2018, 02:41 PM) *

QUOTE(tudsy @ Sep 4 2018, 11:49 PM) *

$query ="SELECT Customers.Customersitemid, Customers.email, Customers.name, Customers.Price, Customers.terms, Customers.Age, Customers.Date, Customers.Softbought, CustomersArt.CustomersArtitemid, CustomersArt.nameof, CustomersArt.Priceofart ,CustomersArt.Dateof, CustomersArt.termsof, CustomersArt.Ageof, CustomersArt.graphic, CustomersArt.emailadd
FROM Customers JOIN CustomersArt ON Customers.Customersitemid = CustomersArt.CustomersArtitemid";
So, you're telling me that table 'Customers' has a column named 'Customersitemid' and table 'CustomersArt' has a column named 'CustomersArtitemid'?
Or, does table 'Customers' have a column named 'Itemid' and does table 'CustomersArt' have a column named 'Itemid'?

Which is correct?



The former.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Sep 6 2018, 12:24 AM
Post #25


Programming Fanatic
********

Group: Members
Posts: 1,543
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



Create a new PHP script and place this code in it.
CODE
<?php
$conn = new mysqli("host", "user", "pass", "dbname");
if($conn->connect_error)
{
die("Can't connect to database: " . $conn->error);
exit();
}
$sql = "SELECT b.CustomersArtitemid, b.nameof, b.Priceofart, b.Dateof, b.termsof, b.Ageof, b.graphic, b.emailadd, a.Customersitemid, a.email, a.name, a.Price, a.terms, a.Age, a.Date, a.Softbought FROM Customers a INNER JOIN CustomersArt b ON a.Customersitemid = b.CustomersArtitemid";
if(!$result = $conn->query($sql))
{
die("Database Query Error: {$conn->error}");
exit();
}
$rows = mysqli_fetch_all($result);
var_dump($rows);
$result->close();
$conn->close();
?>
Be sure to put your database login information in. Let me know the results of this test.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Sep 6 2018, 09:54 PM
Post #26


Advanced Member
****

Group: Members
Posts: 162
Joined: 30-September 14
Member No.: 21,611



QUOTE(CharlesEF @ Sep 6 2018, 02:54 PM) *

Create a new PHP script and place this code in it.
CODE
<?php
$conn = new mysqli("host", "user", "pass", "dbname");
if($conn->connect_error)
{
die("Can't connect to database: " . $conn->error);
exit();
}
$sql = "SELECT b.CustomersArtitemid, b.nameof, b.Priceofart, b.Dateof, b.termsof, b.Ageof, b.graphic, b.emailadd, a.Customersitemid, a.email, a.name, a.Price, a.terms, a.Age, a.Date, a.Softbought FROM Customers a INNER JOIN CustomersArt b ON a.Customersitemid = b.CustomersArtitemid";
if(!$result = $conn->query($sql))
{
die("Database Query Error: {$conn->error}");
exit();
}
$rows = mysqli_fetch_all($result);
var_dump($rows);
$result->close();
$conn->close();
?>
Be sure to put your database login information in. Let me know the results of this test.



Hi

Thanks for that.

The test produced this:

NULL
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Sep 6 2018, 10:26 PM
Post #27


Programming Fanatic
********

Group: Members
Posts: 1,543
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



That tells me the script did run but returned no results. There might be a logic error in the query. I don't have access to your database so you need to double check that each table/column contains what you think. Example: Are you sure 'Customers.Customersitemid' is equal to 'CustomersArt.CustomersArtitemid'?

This post has been edited by CharlesEF: Sep 6 2018, 10:31 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Sep 6 2018, 10:59 PM
Post #28


Advanced Member
****

Group: Members
Posts: 162
Joined: 30-September 14
Member No.: 21,611



QUOTE(CharlesEF @ Sep 7 2018, 12:56 PM) *

That tells me the script did run but returned no results. There might be a logic error in the query. I don't have access to your database so you need to double check that each table/column contains what you think. Example: Are you sure 'Customers.Customersitemid' is equal to 'CustomersArt.CustomersArtitemid'?



Hi

Thanks for that.

Here are the contents of the Customers and CustomersArt tables.

Thanks.

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Sep 6 2018, 10:59 PM
Post #29


Advanced Member
****

Group: Members
Posts: 162
Joined: 30-September 14
Member No.: 21,611



QUOTE(CharlesEF @ Sep 7 2018, 12:56 PM) *

That tells me the script did run but returned no results. There might be a logic error in the query. I don't have access to your database so you need to double check that each table/column contains what you think. Example: Are you sure 'Customers.Customersitemid' is equal to 'CustomersArt.CustomersArtitemid'?



Hi

Thanks for that.

Here are the contents of the Customers and CustomersArt tables.

Thanks.



Attached File(s)
Attached File  test.zip ( 297.83k ) Number of downloads: 29
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Sep 7 2018, 12:40 AM
Post #30


Programming Fanatic
********

Group: Members
Posts: 1,543
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



That doesn't help much. Customers contains no data and CustomersArt contains 1 row. Since there is no matching row in Customers nothing is returned.
Let me try to explain what the query does. Customers is considered the header table while CustomersArt is considered the detail table. The query will return all rows in the header table. When a matching row is found in the detail table then the detail row data is returned along with the header row data. Since the table Customers is empty no data is returned.

Exactly what do you want the query to do?

This post has been edited by CharlesEF: Sep 7 2018, 01:06 AM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Sep 7 2018, 01:05 AM
Post #31


Advanced Member
****

Group: Members
Posts: 162
Joined: 30-September 14
Member No.: 21,611



QUOTE(CharlesEF @ Sep 7 2018, 03:10 PM) *

That doesn't help much. Customers contains no data and CustomersArt contains 1 row. Since there is no matching row in Customers nothing is returned.

Exactly what do you want the query to do?


Thanks for that.

I want the visitor to look at his/her data (the whole database) using his/her email address.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Sep 7 2018, 01:48 AM
Post #32


Programming Fanatic
********

Group: Members
Posts: 1,543
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



QUOTE(tudsy @ Sep 7 2018, 01:05 AM) *

I want the visitor to look at his/her data (the whole database) using his/her email address.
Then why have you been using ItemId in the ON condition? That can be fixed. One thing I don't understand, when you say visitor do you mean a customer? If yes then why was there no data in the Customers table? I mean, there was 1 row of data in CustomersArt which telles me a customer bought some art from you. Then why wasn't there a customer in the Customers table? And, how does emailcomm and subscribers tie in to the Customers table? Maybe you need a better database layout design, I mean you do seem to duplicate data between the tables.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Sep 7 2018, 05:40 PM
Post #33


Programming Fanatic
********

Group: Members
Posts: 1,543
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();
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Sep 7 2018, 08:50 PM
Post #34


Advanced Member
****

Group: Members
Posts: 162
Joined: 30-September 14
Member No.: 21,611



QUOTE(CharlesEF @ Sep 8 2018, 08:10 AM) *

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


User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Sep 7 2018, 09:08 PM
Post #35


Programming Fanatic
********

Group: Members
Posts: 1,543
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



QUOTE(tudsy @ Sep 7 2018, 08:50 PM) *
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
Great, that is what should be returned, based on the contents of the database you posted before.

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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Sep 7 2018, 10:38 PM
Post #36


Advanced Member
****

Group: Members
Posts: 162
Joined: 30-September 14
Member No.: 21,611



QUOTE(CharlesEF @ Sep 8 2018, 11:38 AM) *

QUOTE(tudsy @ Sep 7 2018, 08:50 PM) *
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
Great, that is what should be returned, based on the contents of the database you posted before.

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.



Thanks for that. When i add emailcomm I get this error:

Database Query Error: The used SELECT statements have a different number of columns

Can I use a JOIN to access the data from the tables emailcomm and subscribers?. Currently they have no data in them.
When i add a third table it produces the data I want.

Thanks.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
tudsy
post Sep 7 2018, 10:47 PM
Post #37


Advanced Member
****

Group: Members
Posts: 162
Joined: 30-September 14
Member No.: 21,611



QUOTE(tudsy @ Sep 8 2018, 01:08 PM) *

QUOTE(CharlesEF @ Sep 8 2018, 11:38 AM) *

QUOTE(tudsy @ Sep 7 2018, 08:50 PM) *
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
Great, that is what should be returned, based on the contents of the database you posted before.

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.



Thanks for that. When i add emailcomm I get this error:

Database Query Error: The used SELECT statements have a different number of columns


Can I use a JOIN to access the data from the tables emailcomm and subscribers?. Currently they have no data in them.
When i add a third table it produces the data I want.

Thanks.


Here are the column names for emailcomm and subscribers.


emailcommitemid Ageofcommmember nameofcommmember emailaddress Dateofsub


subscribersitemid username emailsub ConfirmationCode Confirmation Dateofmember

Thanks.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Sep 7 2018, 11:46 PM
Post #38


Programming Fanatic
********

Group: Members
Posts: 1,543
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



Telling me about an error but now showing the query doesn't help much. The query should look like this:
CODE
$sql = "(SELECT * FROM `Customers` WHERE `email` = '$email')
        UNION
        (SELECT * FROM `CustomersArt` WHERE `emailadd` = '$email')
        UNION
        (SELECT * FROM `emailcomm` WHERE `emailaddress` = '$email')
        UNION
        (SELECT * FROM `subscribers` WHERE `emailsub` = '$email')";
If you also need to know where each row comes from then the query can be change to show this.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

2 Pages V < 1 2
Reply to this topicStart new topic
2 User(s) are reading this topic (2 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 15th October 2018 - 04:25 PM