Jump to content
xisto Community

(help With Sql And Php)retrive Datas From Realted Tables And Display Them! Please help me to diplay datas by combining 3 tables

Recommended Posts

Hi all..



Please help me to write the SQL Query for the following requirement!


Im having 3 MySQL tables




1st Table (Sales_users) contains unique userID and username for the users!

2nd Table (Sales_products) Contains unique Product ID, product Name and Product Description!

3rd Table (Sales_details) contacins unique Sales ID, Product ID (Foreign Key), FromUser (Foreign Key) and ToUser (Foreign Key)



User_id name


1 Test1


2 Test2


3 Test3


4 Test4





Product_id ProductName ProductDesc


1 Product1 ProductDesc1


2 Product2 ProductDesc2


3 Product3 ProductDesc3


4 Product4 ProductDesc4





sales_id Product_id FromUser ToUser


1 2 1 4


2 3 4 3


3 1 2 1


4 2 3 2


Posted Image


I need to display the details in some pages!

I mean, I need to get the last table Sales_details and diplay details by using the previous 2 tables!

How can I write the query to Display like this:


Posted Image


How can I write the SQL query to Fetch the Details by combining all those 3 tables??

If you can write the php code, then please help me!

I'm not good in php and SQL!! I'm just a beginner here!! Please help me guys!



Share this post

Link to post
Share on other sites

I have a feeling that this can be done with one long query, but I prefer splitting everything into smaller chunks :ph34r: Assuming that you are already connected to the database, and you want to display all the sales, I will from an two-dimension array like this:




id is just a counter, so we can display all the data, and column can be sales_id, product_id, fromuser or touser. Keep in mind the I will make everything lowercase, so you might have to customize the script in case it is case-sensitive (obviously, I don't know whether that can happen). So here we go.


$sales = array();  // creating a blank array$query = "SELECT * FROM `sales_details` ORDER BY `sales_id` ASC";  // setting up a query to get all the sales$result = mysql_query($query);  // running the query$i = 0;  // we will be needing a counter;while ( $row = mysql_fetch_array($result) ) {  // we process the sales one by one$sales[i] = array();$sales[i]['sales_id'] = $row['sales_id'];$product = $row['product_id'];$query = "SELECT `productname` FROM `sales_products` WHERE `product_id` = $product";  // let's get the product$product_result = mysql_query($query);$sales[i]['product_id'] = mysql_result($product_result,0,'productname');$user = $row['fromuser'];$query = "SELECT `name` FROM `sales_users` WHERE `user_id` = $user";  // let's get one user$user_result = mysql_query($query);$sales[i]['fromuser'] = mysql_result($user_result,0,'user_id');$user = $row['touser'];$query = "SELECT `name` FROM `sales_users` WHERE `user_id` = $user";  // let's get the other user$user_result = mysql_query($query);$sales[i]['touser'] = mysql_result($user_result,0,'user_id');$i++}foreach ($sales as $one_item) {// here you can print out everything you need, since it's all stored in $one_item[]}

Now, I am not particularly good with arrays, so a part of this code might be completely invalid, but it's up to you to try it out. I hope it works :)

Share this post

Link to post
Share on other sites

Hi friend!!

Thanks spending your time!!

But If the database is having so many records then, executing 3 queries will take long time! ( I think so)

I prefer to go for a single query using that I can fetch those records and display them!! Maximum of 2 queries!!

I was going through w3Schools, SQL JOIN Tutorial here: http://www.w3schools.com/sql/sql_join.asp

But I'm not able to implement that one in my page!


Because, I need to get 2 different user names ( For FromUser and ToUser) from the user table!

Thats creating trouble with me! :ph34r:


Can u please update the query with that?? U can avoid the 2nd table! No need to fetch the Product details!

I only needs to display Sales ID and FromUSer and ToUser Details!

Clicking on that ID will show the product details on some popups ( I can do that 8) )


Anyway, thanks for your fast response!!



Share this post

Link to post
Share on other sites

Sorry, but I can't help you with that :) I am no SQL expert, since I've been using it only on smaller web sites that don't need fast queries. It can be done, that's sure - but I can't do it :ph34r:

Share this post

Link to post
Share on other sites

If you want to get the same value more than one time from the same table simply use the join twice and for one of them use AS to rename temporaly the table. Please try this query:

$query = "SELECT sales_details.sales_id, sales_products.productname, sales_users.name as namefrom, s.name as nameto FROM sales_details left join sales_products as sales_products on (sales_products.product_id = sales_details.product_id) left join sales_users as sales_users on (sales_users.user_id = sales_details.FromUser) left join sales_users as s on (s.user_id = sales_details.ToUser) ORDER BY sales_id ASC";$result = mysql_query($query);  // running the queryecho "<strong>id - product - from - to</strong>";while ( $row = mysql_fetch_array($result) ) { echo $row["sales_id"] . " - " . $row["productname"] . " - " . $row["namefrom"] . " - " . $row["nameto"] . "<br />";}
I hope this works without modifications please take care on the case-sensitive of the script and let us know if it works fine.
Also, customize the result set because it is a simply way to show the data. :ph34r:

Best regards,

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.