SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT COUNT from multiple tables

    Hello guys

    I've been using the SELECT COUNT (*) to retrieve the number of rows from a table in a pagination script.

    PHP Code:
    $sql "SELECT COUNT(*) FROM evans WHERE productbrand ='brompton'"
    I want to do the same but with two tables, I can't figure out the correct syntax, is this possible.

    I've been using UNION ALL to join data from two tables e.g.

    PHP Code:
    $query="SELECT * FROM evans WHERE productbrand ='dahon' UNION ALL SELECT * FROM rutland WHERE productbrand = 'dahon' ORDER BY buynow ASC"
    but can't work out how to use SELECT COUNT and UNION ALL in the same query.

    Can anyone help?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT SUM(numrows) AS numrows
    FROM
      (SELECT COUNT(*) AS numrows 
       FROM evans 
       WHERE productbrand ='dahon' 
       UNION ALL 
       SELECT COUNT(*) 
       FROM rutland 
       WHERE productbrand = 'dahon'
      ) AS a
    But may I ask why you are having two tables with the same structure (or at least I get the feeling they are the same) ?

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guido2004

    Thanks for your help,

    I'm showing all dahon branded bike from two merchants.

    I've tried to do it by using a table for the product and a table from each merchant, then using foreign keys but I couldn't work it out.

    So I'm pulling all dahon bikes from both merchant tables and joining them with UNION ALL.

    It's a temp fix until I nail the foreign keys structure.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by philb View Post
    It's a temp fix until I nail the foreign keys structure.
    if you need help with that, please holler

    separate tables for each manufacturer is not ideal

    i'm wondering how foreign keys are going to help that scenario...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937

    The idea was to have a price comparison set up.

    All the bike details in one table, with the merchants and prices in another table, linking to the product id in the bikes table

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by philb View Post
    Hi r937

    The idea was to have a price comparison set up.

    All the bike details in one table, with the merchants and prices in another table, linking to the product id in the bikes table
    Which sounds like the right answer... so why did you make multiple tables for each merchant?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  7. #7
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    At the moment I only know how to insert the XML datafeed by uploading it to my server.

    So to keep the filesizes down I used 4 separate feeds, this also lets me update each merchant separately.

    I'm a bit of an XML datafeed / php / mysql noob

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by StarLion View Post
    Which sounds like the right answer...
    Almost, but not quite

    I assume a merchant can have more than 1 product.

    So if a product can be delivered by more than 1 merchant, you need another table to link merchants and products (because it's a many to many relationship):

    Merchants: id (PK), name, address, whatever
    Products: id (PK), name, whatever
    Merchant_products: merchantid (PK and FK), productid (PK and FK), price, whatever

    If each product is delivered by only 1 merchant, then you can do with two tables, but the price should go in the products table (1 merchant - many products):

    Merchants: id (PK), name, address, whatever
    Products: id (PK), name, price, whatever, merchantid (FK)

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by philb View Post
    At the moment I only know how to insert the XML datafeed by uploading it to my server.

    So to keep the filesizes down I used 4 separate feeds, this also lets me update each merchant separately.
    i like people who know what they know and are willing to find a workaround for stuff they're not experts on



    loading each feed separately makes a lot of sense

    what you could do, after every feed is loaded into a staging table, is then to copy that data into your main product table

    i trust the sense that you've gotten from this thread is that your main data should be properly designed, and one table per manufacturer is problematic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all your help guys, there's loads to learn, and as soon as you've solved one problem another two pop up lol.

    I'm getting there bit by bit.

  11. #11
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    While I'm here, I have a quick question.

    I want to limit the number of characters in an echo statement.

    My description field in the database has 255 characters.

    But I want to show a summary, so can I limit the echo down to 80?

  12. #12
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    substr (or better, use the query to trim it, if you're not going to use the full description in the same page)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  13. #13
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks

    I'm using

    PHP Code:
    <?php echo substr($productdescription090); ?>
    to display the first 90 characters, works great.

    Will look at query a bit later


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
  •