WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Retrieve Live Data from wpdb (13 posts)

  1. adviceit
    Member
    Posted 10 months ago #

    I am trying to run some live data feeds into my page. I am using the global $wpdb variable to access the custom tables and data which works great.

    However I have a timer running which re-runs the $wpdb->get_results() and rewrites the results into a div. But after the inital run through the data is not updated as if the $wpdb is not updated.

    The function I have is:

    <?php
    	global $wpdb;
    	$total = 0;
        	$sales = $wpdb->get_results( "SELECT NET_AMOUNT FROM wp_dailysales");
    	foreach($sales as $sale)
    	{
    	    $total += $sale->NET_AMOUNT;
    	}
    	$total = $total;
    ?>

    If I refresh the page it pulls in the correct data, just not on the timer. Is there a function that I am missing?

    Thanks

    Simon

  2. graphicgeek
    Member
    Posted 10 months ago #

    I don't think get_results can update the database. You would need to use $wpdb->update() inside your foreach loop (or maybe at the end of it?).

    http://codex.wordpress.org/Class_Reference/wpdb#UPDATE_rows

  3. bcworkz
    Member
    Posted 10 months ago #

    graphicgeek, my take on this is the DB is being updated correctly by other means, the OP's problem is get_results() isn't finding this new data.

    adviceit, it may be get_results() is getting cached, out of date results. Try running $wpdb->flush() to force getting data from the actual DB.

  4. adviceit
    Member
    Posted 10 months ago #

    Hi Guys,

    bcworkz you are correct that is the problem i am having I have tried the $wpdb->flush(); as well but got same result returned.

    I change the script to show what $sale->NET_AMOUNT was and it was still showing the old values after a manual change to the database. But if I refresh the page it shows the new value.

    Wierd...

  5. adviceit
    Member
    Posted 10 months ago #

    I have also tried

    $wpdb = new wpdb( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
    				$sales = array();
    				$total = 0;

    To try and clear everything but still getting the old results.

    Simon

  6. bcworkz
    Member
    Posted 10 months ago #

    How does your timer trigger your $wpdb->get_results() code to run?

  7. graphicgeek
    Member
    Posted 10 months ago #

    I wonder if you could somehow "redeclare" $wpdb. Maybe if you wrapped it inside a function or something:

    <?php
    function reset_db(){ $global $wpdb; return $wpdb; }
    
    $wpdb = reset_db(); //use this where you need to update the values
    
    ?>

    Not sure if that will work or not, but it seems like the $wpdb variable in the function would get the latest.

  8. adviceit
    Member
    Posted 10 months ago #

    function getSales()
    		{
        		$(".updatingSales", this.DOM_obj).removeClass("hideMe");
    			<?php
    				global $wpdb, $total;
    				$wpdb = new wpdb( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
    				$sales = array();
    				$total = 0;
    				$sales = $wpdb->get_var($wpdb->prepare("SELECT SUM(NET_AMOUNT) FROM wp_dailysales"));
    			?>
    
    			$panel = document.getElementsByClassName("panel-6")[0];
    			$content = $panel.getElementsByTagName("span")[0];
    			$content.innerHTML = "";
    			$content.innerHTML = "<table><tr><td><br/><h2>Total for <?php echo date('F jS'); ?> is: </h2><H1 style='color:blue'>£<?php echo $sales ?></h1></td><td id='sales-update-ticker'>Next Update: 1:00</td></tr></table>";
    			updatemins = 0;
    			updatesecs = 59;
    			setTimeout(function(){$(".updatingSales", this.DOM_obj).addClass("hideMe")}, 500);
    			setTimeout(function(){getSales()}, 1*60*1000);
    			clearTimeout(salesticker);
    			salesticker = setTimeout(function(){updatesalesticker()}, 1000);
    		}

    This is the complete function. it is self calling on the setTimeout after 1 minute.

    Thanks

    Simon

  9. adviceit
    Member
    Posted 10 months ago #

    tried the resetdb() and this had the same result.

    Thanks

    Simon

  10. bcworkz
    Member
    Posted 10 months ago #

    All PHP is executed on your server before sending the jQuery code to the browser. Once at the browser, the PHP portions are static, the PHP is no longer executed on each iteration. You are just getting the original, now static values each time.

    To get update information jQuery needs to send an AJAX request to the browser. It can then receive the updated information and place it in the appropriate HTML element.

    Of course then you also need a PHP AJAX handler to accept the request and return the requested information.

  11. adviceit
    Member
    Posted 10 months ago #

    Hi

    Ok, think i understand what you are saying. The page and the code is static, so the variables will be also.

    So if i perform a GET from a new php page which returns the result that will work...

    Ok cool, I have now added this to a new PHP page but keep getting Error 500 from the new PHP page, when i try and reference the $wpdb variable or its functions I get this error.

    <?php
    include ('wp-load.php');
      global $wpdb;
    
      $results = $wpdb->get_var($wpdb->prepare("SELECT SUM(NET_AMOUNT) FROM wp_dailysales")); 
    
      echo $results;
    
    ?>

    I think it is because it doesn't know what the object $wpdb is. So I tried all the includes I could think of without any joy.

    Any ideas?

  12. adviceit
    Member
    Posted 10 months ago #

    ok sorted the 500 error was wrong reference to the wp-load.php not referencing he right location.

    some : "../" needed.

    Incase anyone else finds this post here are my scripts:

    AJAX call in javascript:

    $.ajax({
    				cache: false,
    				url: "<?php bloginfo('template_url'); ?>/scripts/sales.php",
    				success:function(data) {
    					$("#sales-figure").html("<table><tr><td><br/><h2>Total for <?php echo date('F jS'); ?> is: </h2><H1 style='color:blue'>£"+data+"</h1></td><td id='sales-update-ticker'>Next Update: 1:00</td></tr></table>");
    				}
    			});

    PHP file:

    <?php
    require_once('../../../../wp-load.php');
      global $wpdb;
    
     $results = $wpdb->get_var("SELECT SUM(NET_AMOUNT) FROM wp_dailysales");
     if($results == "")
     {
    	$results = 0;
     }
     echo $results
    ?>

    Thank you for your assistance guys.

  13. bcworkz
    Member
    Posted 10 months ago #

    You're welcome! Glad you got it all sorted.

    For the record, that is not really the suggested way to handle AJAX in WordPress. The script should be enqueued and the request should go through wp-admin/admin-ajax.php. The PHP handler then hooks into the appropriate action initiated by admin-ajax.php.

    @adviceit - while your use of AJAX is not the preferred method, it's such a small thing, I would not bother changing it as long as it's working for you. I only mention the suggested method for anyone else that comes along.

Reply

You must log in to post.

About this Topic

Tags