SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Dec 2004
    Location
    scotland
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Querying multiple mysql tables

    Hi, I have inherited a site which used to use oscommerce but which the owner now wants to be completely redesigned. There is already product data in the database tables left behind from oscommerce and I was hoping to use this in the new design.

    The problem is that the info is spread over numerous tables and I am not sure how to query the tables to display what I want. Ideally I want a list of products, broken down by category. See the tables in question below:

    CREATE TABLE products_to_categories (
    products_id int(11) NOT NULL default '0',
    categories_id int(11) NOT NULL default '0',
    PRIMARY KEY (products_id,categories_id)
    ) TYPE=MyISAM;

    CREATE TABLE products (
    products_id int(11) NOT NULL auto_increment,
    products_quantity int(4) NOT NULL default '0',
    products_model varchar(12) default NULL,
    products_image varchar(64) default NULL,
    products_price decimal(15,4) NOT NULL default '0.0000',
    products_date_added datetime NOT NULL default '0000-00-00 00:00:00',
    products_last_modified datetime default NULL,
    products_date_available datetime default NULL,
    products_weight decimal(5,2) NOT NULL default '0.00',
    products_status tinyint(1) NOT NULL default '0',
    products_tax_class_id int(11) NOT NULL default '0',
    manufacturers_id int(11) default NULL,
    products_ordered int(11) NOT NULL default '0',
    PRIMARY KEY (products_id),
    KEY idx_products_date_added (products_date_added)
    ) TYPE=MyISAM;

    I know they both have the products_id field and I was wondering if there was a way to query the database that would show the category each was in. Hope that makes sense. Please be patient with me, i'm a newbie.

    With best wishes.

    Steven

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what version of mysql are you on, and please show the categories table as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2004
    Location
    scotland
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks for the help so far! Am I anywhere near the right ballpark with the following code? I am trying to query two MySQL tables.

    // Perform a standard SQL query:

    $sql_shopdata = mysql_query (SELECT"pd.*, p.* FROM products_description as pd, products as p, WHERE pd.products_id = p.products_id AND p.products_id = '$products_id'") or die (mysql_error());

    while ($row = mysql_fetch_array($sql_shopdata)) {
    $pd.products_id = $row["pd.products_id"];
    $pd.language_id = $row["pd.language_id"];
    $pd.products_name = $row["pd.products_name"];
    $pd.products_description = $row["pd.products_description"];
    $pd.products_url = $row["pd.products_url"];
    $pd.products_viewed = $row["pd.products_viewed"];
    $pd.categories_id = $row["pd.categories_id"];
    $p.products_id = $row["p.products_id"];
    $p.products_quantity = $row["p.products_quantity"];
    $p.products_model = $row["p.products_model"];
    $p.products_image = $row["p.products_image"];
    $p.products_price = $row["p.products_price"];
    $p.products_date_added = $row["p.products_date_added"];
    $p.products_last_modified = $row["p.products_last_modified"];
    $p.products_date_available = $row["p.products_date_available"];
    $p.products_weight = $row["p.products_weight"];
    $p.products_status = $row["p.products_status"];
    $p.products_tax_class_id = $row["p.products_tax_class_id"];
    $p.manufacturers_id = $row["p.manufacturers_id"];
    $p.products_ordered = $row["p.products_ordered"];

    I am also trying to carry a variable from a previous page using (example) pagename.php?products_id=222

    If you could help me to get this working I would be eternally grateful!

    Warmest wishes.

    Steven

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your first post showed two tables, products and products_to_categories, but you wanted to show product categories, which is why i asked to see the categories table

    then your next post shows a query against the products and products_description tables, but no categories table

    what actually did you want to show?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •