Selecting all tables in database to display table name and sum of amount

[oriental_express]

Hi there everyone

 

I have a database with 15 tables in my database format

address ccn expirydate securitycode amount

 

I would like to display a table that will name all 15 tables and the sum of amount

so eg

 

table 1      456

table 2      465

table 3      987

.

.

table 15    432

 

I think it might be like SELECT * TABLES, SUM(amount); ORDER TABLES

 

any ideas ?

 

How would i use php to display it like that

I have this already and it doesnt work

 

<?PHP

mysql_pconnect('localhost','admin','admin');

mysql_select_db("wml");

   $results = mysql_query("SELECT * TABLES, SUM(amount); ORDER TABLES");

   if ($results){

       while ($a = mysql_fetch_array($results)) {

        echo'<TABLE><TR>
              <TD>'.$a['name'].'</TD>
              <TD>'.$a['amount'].'</TD>
     </TR></TABLE>';
       };

       print("</TABLE>");

       } else {

       print("No results to display");

       };
?>
   

 

any ideas ? thanks guys

[teng84]

You need to use "show tables " then once you get all the table loop your it and query it one by one?

 

or you declare all your table and form a join then count each id of each tables..

[oriental_express]

You need to use "show tables " then once you get all the table loop your it and query it one by one?

 

or you declare all your table and form a join then count each id of each tables..

 

didnt understand the 2nd but the first gives me the idea

 

show tables sum(amount);    but doesnt work grrrrrrrr please help

[teng84]

show tables gives you all the table name right? ..

 

once you get all the tables perform a loop that will select or count the table record

 

eg

$query = // your table show

while($x= mysql_fetch_array($query)){

select count (*) from  $x

}

 

something like that

does that make sense

[oriental_express]

show tables gives you all the table name right? ..

 

once you get all the tables perform a loop that will select or count the table record

 

eg

$query = // your table show

while($x= mysql_fetch_array($query)){

select count (*) from  $x

}

 

something like that

does that make sense

 

 

could you tell me where you would put that in terms of this

 

mysql_select_db("dontwork_wml");

   $results = mysql_query("SELECT * FROM wwf ORDER BY name");

   if ($results){

       while ($a = mysql_fetch_array($results)) {

        echo'<TABLE><TR>
              <TD>'.$a['name'].'</TD>
              <TD>'.$a['amount'].'</TD>
     </TR></TABLE>';
       };

       print("</TABLE>");

       } else {

 

[teng84]

<?
mysql_select_db("dontwork_wml");

   $results = mysql_query("show tables");
   if ($results){
       while ($a = mysql_fetch_array($results)) {
   		$query = "select count(*) from $a";
		$Q=mysql_query($query);
		$fetch = mysql_fetch_array($Q);

        echo'<TABLE><TR>
              <TD>'.$fetch['name'].'</TD>
              <TD>'.$fetch['amount'].'</TD>
     </TR></TABLE>';
       };

       print("</TABLE>");

       } else {

 

not tested but try this

[oriental_express]

<?
mysql_select_db("dontwork_wml");

   $results = mysql_query("show tables");
   if ($results){
       while ($a = mysql_fetch_array($results)) {
   		$query = "select count(*) from $a";
		$Q=mysql_query($query);
		$fetch = mysql_fetch_array($Q);

        echo'<TABLE><TR>
              <TD>'.$fetch['name'].'</TD>
              <TD>'.$fetch['amount'].'</TD>
     </TR></TABLE>';
       };

       print("</TABLE>");

       } else {

 

not tested but try this

 

it replied me with that mysql warning statement error

[teng84]

what is the error?

[oriental_express]

says

warning:

mysql_fetch_array() supplied argumetn is not a valid mysql results reser in /..charity/wml.donations.php on line 16

 

 

this is line 16

 

$fetch = mysql_fetch_array($Q);

[teng84]

<?

mysql_select_db("dontwork_wml");

 

  $results = mysql_query("show tables") or die(mysql_query());

  print_r(mysql_fetch_array($results));

  if ($results){

      while ($a = mysql_fetch_array($results)) {

  $query = "select count(*) from $a";

$Q=mysql_query($query)or die(mysql_query();

$fetch = mysql_fetch_array($Q);

  print_r(mysql_fetch_array($fetch));

 

 

        echo'<TABLE><TR>

              <TD>'.$fetch['name'].'</TD>

              <TD>'.$fetch['amount'].'</TD>

    </TR></TABLE>';

      };

 

      print("</TABLE>");

 

      } else {

 

try that and tell me  what happen

[oriental_express]

i get parse error syntax error , unexpected ';' in /chairty/wml.dontations.php line 16 which is  $Q=mysql_query($query)or die(mysql_query();

 

hmm

[teng84]

<?
mysql_select_db("dontwork_wml");

   $results = mysql_query("show tables") or die(mysql_query());
   print_r(mysql_fetch_array($results));
   if ($results){
       while ($a = mysql_fetch_array($results)) {
   		$query = "select count(*) from $a";
		$Q=mysql_query($query)or die(mysql_query());
		$fetch = mysql_fetch_array($Q);
		   print_r(mysql_fetch_array($fetch));


        echo'<TABLE><TR>
              <TD>'.$fetch['name'].'</TD>
              <TD>'.$fetch['amount'].'</TD>
     </TR></TABLE>';
       };

       print("</TABLE>");

       } else {

 

error is obvious grrr.. try

[oriental_express]

Array ( [0] => actiondeafness [Tables_in_dontwork_wml] => actiondeafness )

Warning: Wrong parameter count for mysql_query() in /home/dontwork/public_html/charity/wml/donations.php on line 16

 

lol round in circles.

If there anything you need to see in terms of sql database structures and whole of wml file to help ?

[oriental_express]

has it got anything to do with attribute names ? name and amount ? cause i know name is not the name of the table but name of the data input.

[teng84]

mysql_select_db("dontwork_wml");
   $results = mysql_query("show tables") or die(mysql_query());
   if ($results){
       while ($a = mysql_fetch_array($results)) {
	   echo $a;
   		$query = "select COUNT(*) from $a";
		echo $query;
		$Q=mysql_query($query)or die(mysql_query());
		$fetch = mysql_fetch_array($Q);
        echo'<TABLE><TR>
              <TD>'.$fetch['name'].'</TD>
              <TD>'.$fetch['amount'].'</TD>
     </TR></TABLE>';
       };

       print("</TABLE>");

       } else {

try

[oriental_express]

Arrayselect COUNT(*) from Array

Warning: Wrong parameter count for mysql_query() in /home/dontwork/public_html/charity/wml/donations.php on line 16

 

??? i really do appreciate you trying !

 

Im not sure if im understanding correctly but what is it your COUNT(ing) ?

As far as i understand " $query = "select COUNT(*) from $a";"

 

means its counting the amount of tables right ? Can you tell me how it would

sum up the "amount" colum of each table to display ?

 

thanks

[teng84]

<?
mysql_select_db("dontwork_wml");
   $results = mysql_query("show tables") or die(mysql_query());
   if ($results){
       while ($a = mysql_fetch_array($results)) {
   		$query = "select COUNT(*) from {$a['Tables_in_dontwork_wml']} ";
		echo $query;
		$Q=mysql_query($query)or die(mysql_query());
		$fetch = mysql_fetch_array($Q);
        echo'<TABLE><TR>
              <TD>'.$fetch['name'].'</TD>
              <TD>'.$fetch['amount'].'</TD>
     </TR></TABLE>';
       };

       print("</TABLE>");

       } else {

i guess this one works

[teng84]

Arrayselect COUNT(*) from Array

Warning: Wrong parameter count for mysql_query() in /home/dontwork/public_html/charity/wml/donations.php on line 16

 

??? i really do appreciate you trying !

 

Im not sure if im understanding correctly but what is it your COUNT(ing) ?

As far as i understand " $query = "select COUNT(*) from $a";"

 

means its counting the amount of tables right ? Can you tell me how it would

sum up the "amount" colum of each table to display ?

 

thanks

 

it will get all the table name then use that table name

$query = "select COUNT(*) from $a  <--- so this will count the rows from the table $a 

[oriental_express]

hmm might be getting somewhere cause now its giving

 

select COUNT(*) from actiondeafness

 

select COUNT(*) from agedcarersuk

 

select COUNT(*) from agedguildcare

 

select COUNT(*) from agedpillgrimhomes

 

select COUNT(*) from blindcare

 

select COUNT(*) from childhopeuk

 

select COUNT(*) from depaultrust

 

select COUNT(*) from hampshiredeafassociation

 

select COUNT(*) from landheritage

 

select COUNT(*) from manchesterdeafcentre

 

select COUNT(*) from reachfund

 

select COUNT(*) from royalblindsociety

 

select COUNT(*) from vegansociety

 

select COUNT(*) from visionaidoverseas

 

select COUNT(*) from wwf

 

 

[trq]

Can you show us what two of these tables look like structure wise? I think the big question here might be why do you have so many tables?

[oriental_express]

Can you show us what two of these tables look like structure wise? I think the big question here might be why do you have so many tables?

 

one of my tables is

 

blindcare

name

address

ccn

expirydate

securitycode

amount

 

basically my application is a charity application, to donate to using wml

Each wml has a form to fill to submit to each table hence 15 different charity

to store the info. I cant tink of anyother way to use just one table but also use different forms to record different charity donations.

 

Cananyone else help ? Thank you

[teng84]

i think the codes work now! can you explain your next question better