close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

displaying data with relational database tables

Thread began 1/12/2015 10:58 am by s.joiner74419001 | Last modified 1/14/2015 2:17 pm by Jason Byrnes | 712 views | 15 replies |

s.joiner74419001

displaying data with relational database tables

I have a page that is displaying data from 3 tables in a database using sql joins. I have a 'members' table, a 'properties' table and a 's 'enquiry' table. I am using the 'members' table primary key as a Membership number for the user. It is also a foreign key in the 'enquiries' table. incidentally the 'properties table's primary key (property_id) is a foreign key in the 'members' table.

The problem I have is that the 'members_id' binding only displays when there is an enquiry made by the user, which will in hand display on this page. But if the user in session has not made any enquiries, the 'members_id' binding does not show.

I kind of understand that this is because of my query ( recordset ) but I am not sure where or how to adjust this.

Attached is the current page.

Sign in to reply to this post

Jason ByrnesWebAssist

it is because of the join type you are using.

seer this page for details on the different join types and how the function:
http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Sign in to reply to this post

s.joiner74419001

Thanks Jason!

Sign in to reply to this post

Jason ByrnesWebAssist

you're welcome.

Sign in to reply to this post

s.joiner74419001

Hi Jason! I still can't grasp what to do here because I have three tables to join. The blog is helpful but It only covers using joins with two tables!

Sign in to reply to this post

Jason ByrnesWebAssist

joining three tables is not much different than joining 2 tables

SELECT * from table1
<join type> table2 ON <relation columns from 2 tables>
<join type>table3 ON <relation columns from 2 tables>

the blog i linked to describes the different join types and how the effect the results. the join type yo are using in the query is causing the member ID to not return, try experimenting with the different join types.

Sign in to reply to this post

s.joiner74419001

Thanks Jason. I think whats confusing the matter is the where clause which gets the members id from the session, if that makes sense. I get the impression you would rather I figure this out for myself! if I was to ask you for the answer to my problem would you be able to give me the answer?

Sign in to reply to this post

Jason ByrnesWebAssist

in the recordset, you are using INNER JOIN and LEFT JOIN as the join types.

Change the join types to be more inclusive


from the page i linked to:

"Inner join produces only the set of records that match in both Table A and Table B."

meaning that if there is no match on both sides of the join, a result is not returned

"Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null."

meaning that a result is returned whether there is a match or not.

Sign in to reply to this post

s.joiner74419001

Jason,

I was first trying to use FULL OUTER JOIN only to discover you can't use these with MySQL and have to use UNION and UNION ALL with LEFT and RIGHT JOIN. I have tried this and I can not seem to get the results I am looking for as it does not seem to filter the results properly for the user in SESSION.

Here is the query I have tried.

$getID_rsSummary = "-1";
if (isset($_SESSION['members_id'])) {
$getID_rsSummary = $_SESSION['members_id'];
}
mysql_select_db($database_LA, $LA);
$query_rsSummary = sprintf("SELECT * FROM members LEFT JOIN properties ON members.property_id = properties.property_id LEFT JOIN enquiries ON members.members_id = enquiries.members_id UNION ALL SELECT * FROM members RIGHT JOIN properties ON members.property_id = properties.property_id LEFT JOIN enquiries ON members.members_id = enquiries.members_id UNION ALL SELECT * FROM members RIGHT JOIN properties ON members.property_id = properties.property_id RIGHT JOIN enquiries ON members.members_id = enquiries.members_id WHERE members.members_id = %s ", GetSQLValueString($getID_rsSummary, "int"));
$rsSummary = mysql_query($query_rsSummary, $LA) or die(mysql_error());
$row_rsSummary = mysql_fetch_assoc($rsSummary);
$totalRows_rsSummary = mysql_num_rows($rsSummary);

Any pointers? ;-)

Sign in to reply to this post

Jason ByrnesWebAssist

union merges the results of 2 queries, you should not be using union, it is not the same as a join.

go back to the original query i will need to troubleshoot directly, see the private message section.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...