Hi everyone,
My PHP skills are not good enough to figure out this obstacle. I would like to make a page that shows me data from 4 different mysql tables, and i have been playing around with MYSQL Join but there must be an easier way of doing this. so if people dont mind posting their ideas and advise. Would be forever greatful if i can learn something new...
Page to be shown:
Vendor name 1:
- Product 1 (Engineer1, Engineer2, Engineer3)
- Product 2 (Enginner2, Engineer3
- Product 3 (Engineer1, Engineer3)
Vendor name 2:
- Product 4 (Engineer1, Engineer2, Engineer3)
- Product 5 (Enginner2, Engineer3
- Product 6 (Engineer1, Engineer3)
So list all the Vendors and in between the vendors list all the products for that vendor. Then i need to put in what engineers can do the specific products. I have been looking at using MySQL JOIN but then not sure how i would make my php script to recognize that vendorname should be bold, and then in between the vendors how does it know to put the products that belong to that vendor.
My MySQL tables are as this:
VENDOR TABLE:
id, vendName, vendID
********************************
1, McAfeee, mcafeee
PRODUCT TABLE:
id, prodName, prodID, vendID
*************************************
1, Web Gateway, webgateway, mcafee
PRODUCTSKILLS TABLE:
id, prodID, userID, vendID
*************************************
1, webgateway, johndoe, mcafee
PEOPLE TABLE:
id, engineerName, engineerID
*************************************
1, John Doe, johndoe
As you can see i have tried to tie everything in to eachother, but now i just need the final help.
THANK YOU SO MUCH FOR THEM WHO CAN HELP ME !!
Regards
Rippie
HELP - With arrays and mysql select
Moderator: General Moderators
Re: HELP - With arrays and mysql select
So been doing some scripting, and this is how i got it to spit out data:
Bluesocket
Access Gateway : Nick Barnett, Stuart Lawrence, Richard Parkin,
Borderware
Security Platform : Brian Simpson, Ronnie Jorgensen,
Steelgate : no engineers
Clearswift
Email Appliance : Wil Jones, Wil Jones, Stuart Lawrence, Chris Page,
Web Appliance : Wil Jones, Stuart Lawrence, Chris Page,
This is my code:
Can i improve this code in anyway? make it better and maybe not have so many queries ?
Bluesocket
Access Gateway : Nick Barnett, Stuart Lawrence, Richard Parkin,
Borderware
Security Platform : Brian Simpson, Ronnie Jorgensen,
Steelgate : no engineers
Clearswift
Email Appliance : Wil Jones, Wil Jones, Stuart Lawrence, Chris Page,
Web Appliance : Wil Jones, Stuart Lawrence, Chris Page,
This is my code:
Code: Select all
<?php
include 'details.php';
#connect to db server, select db
$connectionString = @mysql_connect(host, db_user, db_pass);
$selectDb = @mysql_select_db(db);
$query = mysql_query("SELECT * from spvendors ORDER BY vendName asc");
while($row = mysql_fetch_array($query)) {
echo "<strong>".$row['vendName']."</strong> <br />";
$query2 = mysql_query("SELECT * from spproducts WHERE vendID = '".$row['vendID']."' ORDER BY prodName asc");
while($row2 = mysql_fetch_array($query2)) {
echo "".$row2['prodName']." : ";
$query3 = mysql_query("SELECT *
FROM spproductskills
INNER JOIN sppeople
ON spproductskills.userID=sppeople.userID
WHERE spproductskills.prodID = '".$row2['prodID']."'
ORDER BY spproductskills.prodID");
$count = mysql_num_rows($query3);
if($count == 0) {
echo "no engineers";
} else {
while($row3 = mysql_fetch_array($query3)) {
echo "".$row3['userName'].", ";
}
}
echo "<br />";
}
}
?>Re: HELP - With arrays and mysql select
One solution:
Clearly you can build one Select using Left Join to get in one shoot all the data you want (joining all the tables properly)... create a database view with that select.
after that in your php code you you just use the view... and get and display the records as you want.
.. One select... one while loop
Miko
Clearly you can build one Select using Left Join to get in one shoot all the data you want (joining all the tables properly)... create a database view with that select.
after that in your php code you you just use the view... and get and display the records as you want.
.. One select... one while loop
Miko
Re: HELP - With arrays and mysql select
With LEFT JOIN, can i still use my if command if there is nothing in my productskills table that matches a product from the product table ? i.e there is no engineers on this product. Currently i have this in my example above..:mikosiko wrote:One solution:
Clearly you can build one Select using Left Join to get in one shoot all the data you want (joining all the tables properly)... create a database view with that select.
after that in your php code you you just use the view... and get and display the records as you want.
.. One select... one while loop
Miko
Code: Select all
$count = mysql_num_rows($query3);
if($count == 0) {
echo "no engineers";
} else {
while($row3 = mysql_fetch_array($query3)) {
echo "".$row3['userName'].", ";
}
}Rippie
Re: HELP - With arrays and mysql select
Does noone have any feedback ?