Page 1 of 1

HELP - With arrays and mysql select

Posted: Sun Feb 28, 2010 1:37 am
by Rippie
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

Re: HELP - With arrays and mysql select

Posted: Sun Feb 28, 2010 4:16 am
by Rippie
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:

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 />";
    }
    
}
?>
Can i improve this code in anyway? make it better and maybe not have so many queries ?

Re: HELP - With arrays and mysql select

Posted: Sun Feb 28, 2010 9:01 am
by mikosiko
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

Re: HELP - With arrays and mysql select

Posted: Sun Feb 28, 2010 12:19 pm
by Rippie
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
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..:

Code: Select all

       $count = mysql_num_rows($query3);
 
        if($count == 0) {
            echo "no engineers";
        } else {
            while($row3 = mysql_fetch_array($query3)) {
                echo "".$row3['userName'].", ";
            }
        }
Would you mind explaining more about the LEFT JOIN and how i achieve the same result as my own example above.

Rippie

Re: HELP - With arrays and mysql select

Posted: Mon Mar 01, 2010 7:42 am
by Rippie
Does noone have any feedback ?