Select Count(*) Question

[Sleeper]

I have

 

$sql=mysql_query("SELECT COUNT(*) FROM table1");

echo mysql_result($sql,0);

 

And this works fine. However I want to be able to get the count for multiple tables added together to get the total for all. What I was hoping for was something as easy as

 

$sql=mysql_query("SELECT COUNT(*) FROM table1,table2,table3");

echo mysql_result($sql,0);

 

So that if each table had 10 rows the echo out would show "30". However its not that easy (nothing ever is). So can anyone tell me how to do this in a simple way?

 

I already have a set up that dose

 

$var=mysql_query("SELECT * FROM table1") or die(mysql_error());$varrows=mysql_num_rows($var);

 

However this becomes very cumbersome after counting up the 40th-50th table and not all that easy to edit out if I drop a table. So I'm hoping for a easier way and a less taxing to the server way of adding up the tables.

 

Any Ideas?

[BrettHartel]

I am just starting to learn PHP, but what if you do each seperate table as its own script with a result that is a INT variable. Then you can easly ECHO $Variable1 + $Variable2 + $Variable3. Hope that helps.

 

Enjoy your day,

 

Brett Hartel

Edited December 3, 2012 by BrettHartel

[requinix]

40th-50th table? How many do you have? What are they for and why are there so many?

[Sleeper]

How many I have or what they are for or why so many doesn't have anything to do with answering the question I asked.

[BrettHartel]

Maybe requinix has a way of making your query faster by knowing the information. Requinix has helped me improve my code for eMail unique identification.

[kicken]

Simplest thing to do would be something like this:

SELECT SUM(cnt) FROM (
 SELECT COUNT(*) as cnt FROM table1
 UNION ALL 
 SELECT COUNT(*) as cnt FROM table2
 UNION ALL 
 SELECT COUNT(*) as cnt FROM table3
 UNION ALL 
 SELECT COUNT(*) as cnt FROM table4
) tblcnt

 

Personally, I don't see much point in why you're trying to do such a thing, and makes me question what you're doing.

[Christian F.]

Sleeper: When an experienced programmer asks you "why" you're doing something, that usually means one of two things:

1. Either you haven't explained your problem properly, and s/he needs more information to formulate a solution for you.
   
2. You really should be asking yourself the very same question, as you've gone and done something pretty stupid. Meaning said programmer wants you to think about this, and provide a reasoning so that s/he can tell you a better way of doing it. (Should you fail to realize one yourself.)

 

I'll leave it up to you to decide what meaning requinix was going for in this case.

Edited December 4, 2012 by Christian F.

[Sleeper]

I love how quick everyone is to judge and call something others are doing as "stupid" or "questionable". The question was very simply put and easy to understand. I wanted to avoid hours of work rebuilding a site that was poorly set up in the first place. They had around 100 tables each table had the same 3 columns, and rather then adding a 4th to show what each one was they wanted each separate as they thought having one table with 70k rows would be slower then 100 separate tables. The entire site was coded to work with this set up. So rather then cleaning up the sql and rebuilding the entire site all cause they wanted to show the total of all the tables I figured there would have to be an easier way of doing it.

 

However seeing as the answers I got were not answers at all, I spent the time to do it the right way and rebuild the sql and the site. So next time when someone ask for help rather then having attitude and stating how "stupid" their coding is how about just answering the question? Some of us may not be "PROS" but where not nubes and sometimes we ask questions because we want help avoiding hours of work that were not getting paid for. After all this is a help site is it not?

 

Thank you to those who helped, and if I offend anyone, well sorry for too. However I know a lot of "nubes" who avoid sites like this because of people who would rather post smart a$$ remarks (to get there post count up) rather then answer the question.

Edited December 4, 2012 by Sleeper

[PFMaBiSmAd]

So can anyone tell me how to do this in a simple way?

 

If there was, you would have found it or someone would have posted it. The design was overly complicated, so the solution would have been overly complicated too. You stated you wanted to do this in a simple way. Complicate and simple are mutually exclusive.

 

The simple way turned out to be to fix the design, which probably made most of the code managing the different tables go-a-way and made the code for the current task simple.

[Sleeper]

As the person who spent hours fixing the site and the sql I wouldn't say it was the simplest way. Also kicken did give a simple answer. And it wasn't complicated. If requinix wants to ask questions and get the post count up that's fine. However I'm not going to sit here and keep discussing back and forth the questions legitimacy or lack there of.

 

So thank you for your after the fact comments on a topic marked resolved. No reason for anyone else to continue posting at this time.

 

You all have a wonderful Holiday Season.

[Jessica]

Stop being a whiny child.

[Sleeper]

I see someone else wants to get there post count up and chime in now.

 

Can an admin just remove this post? Cause this is just getting pointless now.

[Jessica]

If you actually paid attention to what people were saying instead of worrying about a number, you might actually learn something.

[Zane]

The question has been answered yet the OP has been offended.. there's no point in keeping this thread open. Although I will agree with Jessica in the pointing out of whining. This is the internet, please try not to take people too seriously and as for the others... watch your words when you know damn well it could set someone off and destroy a thread.