Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Jan 2012
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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...
    Code:
    //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)...

    Code:
    /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

  • #2
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    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:
    PHP Code:
    // 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.
    Useful function to retrieve difference in times
    The best PHP resource
    A good PHP FAQ
    PLEASE remember to wrap your code in [PHP] tags.
    PHP Code:
    // Replace this
    if(isset($_POST['submitButton']))
    // With this
    if(!empty($_POST))
    // Then check for values/forms. Some IE versions don't send the submit button 
    Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.

  • #3
    New Coder
    Join Date
    Jan 2012
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    You're a star

  • #4
    New Coder
    Join Date
    Jan 2012
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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...
    Code:
    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.

    Code:
    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
    Code:
    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?

  • #5
    New Coder
    Join Date
    Jan 2012
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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

    Code:
    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

  • #6
    Regular Coder djh101's Avatar
    Join Date
    May 2009
    Location
    California
    Posts
    614
    Thanks
    48
    Thanked 64 Times in 64 Posts
    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 Code:
    <?php for($i=0$i<10; ++$i){ ?>
        rows.push(['<?php echo $parther[$i]?>', 100]);
    <?php ?>
    or
    PHP Code:
    <?php for($i=0$i<10; ++$i){
        echo 
    "rows.push(['".$parther[$i]."', 100])";
    ?>
    "Yeah science!"
    Online Science Tools

  • Users who have thanked djh101 for this post:

    kdalts (01-21-2012)

  • #7
    New Coder
    Join Date
    Jan 2012
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I love you guys

  • #8
    New Coder
    Join Date
    Jan 2012
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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

  • #9
    Senior Coder
    Join Date
    Jul 2011
    Posts
    1,226
    Thanks
    3
    Thanked 171 Times in 171 Posts
    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.
    Useful function to retrieve difference in times
    The best PHP resource
    A good PHP FAQ
    PLEASE remember to wrap your code in [PHP] tags.
    PHP Code:
    // Replace this
    if(isset($_POST['submitButton']))
    // With this
    if(!empty($_POST))
    // Then check for values/forms. Some IE versions don't send the submit button 
    Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.

  • #10
    New Coder
    Join Date
    Jan 2012
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by BluePanther View Post
    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....

  • #11
    New Coder
    Join Date
    Oct 2011
    Location
    New york
    Posts
    14
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Thumbs up

    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 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
    Last edited by JackieBolinsky; 01-23-2012 at 05:37 PM.

  • #12
    New Coder
    Join Date
    Jan 2012
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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...
    Code:
    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:
    Code:
    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 -
    Last edited by kdalts; 01-23-2012 at 11:37 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •