There has to be an easier way?

Guys, not sure if this should be in the PHP or MySQL thread...


I have a large database (over 60,000 records) that will grow about 5,000 records per month
I need to pull records (mainly record counts) based on a selected Month
I also need to pull records for trending based on a selected month less 1,2,3 or whatever the trend period is


So here is my challenge...
Currently my code uses 3 SELECT WHERE clauses just to find records for a 3 month period...

//FETCH THE TOTAL FSO'S
//Current selected month ($mth_1)
$all_FSOs_sql = "SELECT REGION FROM dispatches WHERE Month = '" . $mth_1 . "' AND Region = '" . $region . "'" ;
$result = mysql_query($all_FSOs_sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$all_FSOs_sql_mth1 = mysql_num_rows($result);
//Current selected month less 1 month ($mth_2)
$all_FSOs_sql = "SELECT REGION FROM dispatches WHERE Month = '" . $mth_2 . "' AND Region = '" . $region . "'" ;
$result = mysql_query($all_FSOs_sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$all_FSOs_sql_mth2 = mysql_num_rows($result);
//Current selected month less 1 month ($mth_3)
$all_FSOs_sql = "SELECT REGION FROM dispatches WHERE Month = '" . $mth_3 . "' AND Region = '" . $region . "'" ;
$result = mysql_query($all_FSOs_sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$all_FSOs_sql_mth3 = mysql_num_rows($result);




This works fine... however, I then want to count the number of records for several partners for each of those given months. I have 10 partners. So I then end up doing 10 more SELECT WHERE clauses (1 for each partner)...



/FETCH FSO' BY PARTNER
$partner1_sql = "SELECT REGION FROM dispatches WHERE Dispatched_TPM = '" . $partner1 . "' AND Month = '" . $selected_mth . "'" ;
$result = mysql_query($partner1_sql, $conn) or trigger_error("SQL", E_USER_ERROR);/**** end deal with the database ****/
$partner1_num_rows = mysql_num_rows($result);

$partner2_sql = "SELECT REGION FROM dispatches WHERE Dispatched_TPM = '" . $partner2 . "' AND Month = '" . $selected_mth . "'" ;
$result = mysql_query($partner2_sql, $conn) or trigger_error("SQL", E_USER_ERROR);/**** end deal with the database ****/
$partner2_num_rows = mysql_num_rows($result);

etc... for all other partners


This all works but seems like a lot of coding and I know I must be doing it wrong.
Basically, I figure there must be a way to pull the numcount for each partner based on a single SELECT clause for each month but I can't figure it out...any help would be appreciated please

This is in the right thread :)

What I would do, is change your queries to have multiple WHERE clauses, and a group by. Something like this:

// Get region count for every three months, selecting month and count(region) and grouping by month
$query = "SELECT Month, COUNT(REGION) as total FROM dispatches WHERE Month='$mth_1' OR Month='$mth_2' OR Month='$mth_3' GROUP BY Month";

// Do the query, using your preferred error reporting
$results = mysql_query($query, $conn) or trigger_error("SQL", E_USER_ERROR);

// Get all the info
while($info = mysql_fetch_assoc($results)){
echo $info['Month'].' had '.$info['total'].' this many results<br />';
}

You can then apply the same principle to the next query :) That's untested, btw, but I think it should work.

You're a star:)

Ok.. so I've now hit another problem...

I need to pass a PhP vasriable to a javascript. I can do this easily but this php var actually has a variable inside a variable. It's the 2nd variable that jmy JS can't read.. any clues please..


Here is the JS bit...
rows.push(['<?php echo $partner[$i];?>', 100]);

Explanation:
The $partner[$i] is an array created from a database select where the number of partners could change from month to montha nd region to region. So, I used this php code to assign each parnter to an array.

while($row = mysql_fetch_array($results)){
//echo $row['Month'].' had '.$row['total'].' this many results<br />';
$partner[]=$row[0];

}

If I do a for loop using i$ as the increment variable it works in php
for ($i = 1; $i < $num_partners; $i++) {
echo "<tr><td>$partner[$i]</td>";
echo "</tr>";
}

but I have to pass it to the JS to create a google chart

Any clues?

Hmm... edit button has gone?

Anyway, I should have said in previous post... I relaise I cannot take the loop variable ($i) from the Php code to the JS because I am out of loop when the JS executes.

So I was trying to recreate the loop in JS using

var rows = [];
for (var x=0; x<10; x++) {

rows.push(['<?php echo $parther[x]?>', 100]);

}

where the array variable is the JS loop value.. but I can't figure out how to plug the JS var into the php code

PHP is executed by the server first before the client touches anything. After all the PHP is done, the resulting data is sent to the client (where the Javascript is executed). Anyway, you can put your Javascript into the PHP loop:

<?php for($i=0; $i<10; ++$i){ ?>
rows.push(['<?php echo $parther[$i]?>', 100]);
<?php } ?>

or

<?php for($i=0; $i<10; ++$i){
echo "rows.push(['".$parther[$i]."', 100])";
} ?>

I love you guys:)

So I'm now at the tricky bit ...

Using the above SELECT query (or something sinilar)... I now need to pull a total number records per partner by month without doing a new SELECT for each month.

Ideally, I would like to be able to select a month range based on the currently selected month. IE; show me the last 3 months or show me the last 6 or even 12 months...this will be the users choice.... thinking I may use a slider for this.

Anyway, if anyone would be so kind as to show me how I can perform the SELECT I think I'm covered.

Thanks in advance

If you're running your database with MyISAM, then COUNT()'s are cached. So, you should be able to run a COUNT() with conditional WHERE's, grouped by month quite quickly.

If you're running your database with MyISAM, then COUNT()'s are cached. So, you should be able to run a COUNT() with conditional WHERE's, grouped by month quite quickly.

Hi... thanks for the reply... lost me a little. Would you have time to show some code please?

I'm running MySQL....

Hi there,
The PHP will be parsed and the value of $phpVar will become the value of var whatever. The second one is not possible without refreshing the page because PHP is parsed server side and Javascript Refresh Page (http://www.javascriptbank.com/refresh-page-automatic.html) is parsed client side. The PHP is always parsed "before" the page is shown to the client in other words. Your Javascript can read from PHP variables only because the .php page is being processes on the server "first" and the output returned. Send a javascript variable to a php page through AJAX, then the php ajax page will use the $_GET['info'] function to get the javascript variable. Then do whatever you want with that new information. This is very good to use when saving info to a database without having to reload.

Cheers!!
Jackie

Thanks Jackie... I'm over that problem now thanks to the help on this page.

The current challenge now is to pull partner dispatch quantities by month without running multiple SELECT queries. I then need each partner, month and dispatch count into an array in order that the values can be placed in a chart using a javascript.

In theory...
SELECT Partner, Count(Dispatch) as total FROM dispatches Group by partner;

Then, using the WHILE loop I place each partner into an array where I can then use the array outside of the loop.

ie:while($row = mysql_fetch_array($results)){
//echo $row['Month'].' had '.$row['total'].' this many results<br />';
$partner[]=$row[0];
$dispatch_count=$row[1];
}




So, I can then use $partner[1], $dispatch_count[1] etc anywhere else in my code

I think I've now confused myself - :D