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

[niran 0]

Hi all..

 

 

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

 

Im having 3 MySQL tables

Sales_users

Sales_products

Sales_details

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)

Sales_users

 

User_id name

 

1 Test1

 

2 Test2

 

3 Test3

 

4 Test4

 

 

Sales_products

 

Product_id ProductName ProductDesc

 

1 Product1 ProductDesc1

 

2 Product2 ProductDesc2

 

3 Product3 ProductDesc3

 

4 Product4 ProductDesc4

 

 

Sales_details

 

sales_id Product_id FromUser ToUser

 

1 2 1 4

 

2 3 4 3

 

3 1 2 1

 

4 2 3 2

 

 

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:

 

 

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!

 

Niru

[pyost 0]

I have a feeling that this can be done with one long query, but I prefer splitting everything into smaller chunks 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:

 

$sales[id][column]

 

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

[niran 0]

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!

 

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!!

 

Niru

[pyost 0]

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

[TavoxPeru 0]

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.

Best regards,