combine multiple tables

[Labrat]

Hi, I have been trying to get several pieces of date from multiple tables and have it to where it spits out the data just fine but what I am wanting is for it to count the total entries in ALL tables then combine the Month(s) and show how many ads for that specific month has been approved/not approved. Mind you that I will have several more tables then just these 3 but I figure if I can get at least 3 to work right then the others will be a breeze to add in to the code. Here is the code with a link to the example page.

 

Example: http://classify.kellywebserv.com/3tables.php note: everything from  the table down has been hand counted from the database, it is NOT the results from the code, only the top 4 lines are from the code.

 

<?php

include ('.global.php');


?>                 

<!--start ads chart-->

                 <?php
                 
$result7 = mysql_query("SELECT ad_id FROM ads
UNION ALL 
SELECT ad_id FROM ads_announce 
UNION ALL 
SELECT ad_id FROM ads_jobs") or die("Sql error : " . mysql_error());

$num_rows4 = mysql_num_rows($result7);


$result2 = mysql_query("select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads where approved='1'
UNION ALL
select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads_announce where approved='1'
UNION ALL
select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads_jobs where approved='1' group by month(ad_date)") or die("Sql error : " . mysql_error());

                 $data1 = array();
                 $approved = array();
                 $i = 0;
while ($row = mysql_fetch_array($result2)) {
$data1[$i] = (int) $row['num1'];
$approved[$i] = (int) $row['approved'];
          $cats1[$i] = $row['admonth'];
          $i++;
                         
}

                 $query3 = ("select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as unapproved from ads where approved='0'
UNION ALL
select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as unapproved  from ads_announce where approved='0'
UNION ALL
select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as unapproved from ads_jobs where approved='0' group by month(ad_date)") or die("Sql error : " . mysql_error());


                                  $result3 = mysql_query($query3);
                 $data1 = array();
                 $unapproved = array();
                 $i = 0;
while ($row = mysql_fetch_array($result3)) {
$data1[$i] = (int) $row['num1'];
$unapproved[$i] = (int) $row['unapproved'];
//          $cats[$i] = $row['rmonth'];
          $i++;
                         
}


$data1 = json_encode($data1);
$cats1 = json_encode($cats1);
$approved = json_encode($approved);
$unapproved = json_encode($unapproved);

                 ?>

                                  <?php
echo "Total number of ads=" .$data1. "Should equal the TOTAL Ads in the system which is " .$num_rows4. "<br><br>";
echo "Months=" .$cats1. " Should combine the months which in this case should be April and May<br><br>";
echo "Total number of ads approved=" .$approved. " Should combine the total number of ads in all 3 tables that are approved and seperated by month.<br><br>";
echo "Total number of ads unapproved=" .$unapproved. " Should combine the total number of ads in all 3 tables that are not approved and seperated by month.<br><br>";


/* from this point down has been hand counted and not generated from the code*/

echo 'The actual numbers are as follows:<br>
<table border="1">
<tr>
<th>Table</th><th>Total Ads</th><th>Month(s)</th><th>Approved</th><th>Not Approved</th></tr>
<tr>
<td>ads</td><td>29</td><td>April, May</td><td>4</td><td>25</td></tr>

<tr><td>ads_announce</td><td>11</td><td>April</td><td>4</td><td>7</td></tr>

<tr><td>ads_jobs</td><td>21</td><td>May</td><td>1</td><td>20</td></tr>
</table>';

echo 'Final output should read:<br>
Total Number of ads=61<br>
Months = April, May<br>
Total number of ads approved=[7,2] (April=7, May=2)<br>
Total number of ads unapproved=[20,32] (April=20, May=32)';
?>                 

               

 

[Labrat]

anyone have any ideas?

[fugix]

sounds like should look into using a JOIN....here

[Labrat]

trying the JOIN but its only showing the data from the first table. please check my code and see if i`m going in the right direction as i`ve never tried using the join before

 

$result2 = mysql_query("select *, count(ads.ad_id) as num1, monthname(ads.ad_date) as admonth, count(ads.approved) as approved from ads

LEFT JOIN

ads_announce

ON ads.ad_id=ads_announce.ad_id AND ads.ad_date=ads_announce.ad_date AND ads.approved=ads_announce.approved

LEFT JOIN

ads_jobs

ON ads_announce.ad_id=ads_jobs.ad_id AND ads_announce.ad_date=ads_jobs.ad_date AND ads_announce.approved=ads_jobs.approved") or die("Sql error : " . mysql_error());

 

[fenway]

That means there are no matching rows.

[Labrat]

thats just it, theres not going to be an y matching entries in the tables, this is a classified ads script  I am trying to write. all 3 tables have the same 3 column names (ad_id, ad_date and approved) what I am trying to do is tie all tables together and sort out the amount of ads that have been approved and not approved for each month, such as ads for April are in table 1 and table2 but theres also ads for May in those tables, I need it to join both tables and sort out the number of ads in those months

[fenway]

Sorry, I don't follow.

[Labrat]

ok for example:

Table1 has 10 ads for April with 2 approved and also has 10 ads from May that has only 1 approved,

now

Table 2 has 5 ads for April with 2 approve but has no ads for May.

 

Final tally should show :

Total ads = 25

April ads approved = 4

May ads approved = 1

 

[fenway]

Why are there 2 tables/

[dk4210]

Hi Fenway,

 

I am with Labrat on this project and the reason there is two different tables is because we are adding data to both tables...

 

Form A submits to Table 1

Form B submits to Table 2

 

We want to count table contents of both..

[mikosiko]

Hi Fenway,

 

I am with Labrat on this project and the reason there is two different tables is because we are adding data to both tables...

 

Form A submits to Table 1

Form B submits to Table 2

 

We want to count table contents of both..

.

 

fenway question still valid... why 2 (or 3) different tables to hold the same information?... according to Labrat

"all 3 tables have the same 3 column names (ad_id, ad_date and approved)"

[dk4210]

They don't have the exact information.. Think of an application with 6 forms. All of them have a member id ect but different options..

 

Combining all the together in one table would result in a mile long table, therefore we separated the forms into separate tables.

 

 

[mikosiko]

we don't know exactly how are you modeling the business and the reasons behind your decisions, therefore it can be right... but doesn't "smell good"... anyways... what you can try to do is something along this lines (code fired from the hip... non tested in your real case... you must adjust it to your reality)

 

SELECT t.num1, t.admonth, count(approved)    /// replace whatever you need here.... the count(ad_date) doesn't look good in your queries
FROM ( 
     select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads where approved='1'
     UNION ALL
    select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads_announce where    approved='1'
     UNION ALL
    select count(ad_date) as num1, monthname(ad_date) as admonth, count(approved) as approved from ads_jobs where approved='1' ) AS t
GROUP BY t.num1, t.admonth  /// replace with whatever you want here