SELECT COUNT from multiple tables

PHP
1

Hello guys

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

$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.

$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 

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

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

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…

5

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

Which sounds like the right answer… so why did you make multiple tables for each merchant?

7

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

Almost, but not quite :slight_smile:

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

i like people who know what they know and are willing to find a workaround for stuff they’re not experts on

:slight_smile:

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

10

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

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

[FPHP]substr[/FPHP] (or better, use the query to trim it, if you’re not going to use the full description in the same page)

13

Thanks

I’m using

<?php echo substr($productdescription, 0, 90); ?>

to display the first 90 characters, works great.

Will look at query a bit later