Create a Mysql many-to-many database using phpmyadmin and URL parameters

[harshamutu]

Hi! Everyone,

I am quite new to PHP and Mysql, but capable of creating a simple database, upload it and make a PHP query to get a result displayed on a webpage using Phpmyadmin. Because of the challenge that I am going to detail below I read a lot on M-to-M databases and creating mapping tables etc. But I am still not sure where to start my assignment yet. My problem has two parts:

1) How to create a MySQL database using Phpmyadmin, for a Many-to-Many relationship. There are many suppliers and a limited number of products. One supplier may have more than one product to offer and one product can be available with several suppliers? (I guest Supplier, Products and the Mapping should have different tables in this database.)

 

2) How can I get all the suppliers of one particular product displayed on the webpage dedicated to that particular product? If a URL parameter could be used for this how can I define a URL parameter for this purpose?

 

I shall appreciate very much if some one can advise me on these tasks and/or let me have URLs of tutorials that address these specific issues.

 

Thanking you in advance

Harsha Mutucumarana

 

[Barand]

Here's a simple example for you

 

+-----------+          +-----------------+          +------------+

| product  |          | productsupplier |          | supplier  |

+-----------+          +-----------------+          +------------+

|*prod_id  |---------<| *prod_id        |    +-----| *supp_id  |

| prod_name |          | *supp_id        |>----+    |  supp_name |

+-----------+          +-----------------+          +------------+

 

To list the suppliers of product 123 your URL would be something like this

 

http://my.domain.com/supplierlist.php?product=123

 

supplierlist.php

<?php
include('dbconnect.php');   // connect to database

if (!isset($_GET['product']) || empty($_GET['product'])) {
    exit('No product id provided');
}
else {
    $product = intval($_GET['product']);    // get the product code from the URL's query string
    
    $sql = "SELECT s.supp_name
            FROM supplier s
            INNER JOIN productsupplier ps ON s.supp_id = ps.supp_id
            WHERE ps.prod_id = $product
            ORDER BY s.supp_name";
    $res = mysql_query($sql);
    
    if (mysql_num_rows($res)==0) {
        exit("No suppliers found for product $product");
    }
    
    echo "Supplier of product $product<br /><br />";
    while ($row = mysql_fetch_assoc($res)) {
        echo "{$row['supp_name']}<br />";
    }
}
?>

[Christian F.]

First off you're mixing the terms "database" and "table", which is clouding your issue somewhat. A database contains many tables, which in turn contains many fields (in which rows of data is inserted).

 

Now, to your questions:

1. [*]You create a M2M relationship in phpMyAdmin as you'd do it anywhere else. By using SQL queries to define the tables, and their relationships. There are many great tutorials on this, and by reading them it should be quite apparent what you need to do.

I recommend to actually write the SQL script to create the entire database, prior to importing it in phpMyAdmin. That way you get to work with the SQL code in one place, without being limited by the import tool.

[*]You do this by querying the cross-reference table for the supplier ID, where the product ID matches the one defined by the user. Just like any other SQL query, using JOIN to fetch the data from the supplier table.