SitePoint Sponsor

User Tag List

Results 1 to 1 of 1
  1. #1
    SitePoint Member
    Join Date
    May 2008
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    it works, - but is highly inefficient

    Hi there,

    I have made a function that can update a result but it uses a lot of SQL commands and a tiny bit of PHP to glue it all together.
    I am pretty sure it can be done entirely in MySQL (and will be much faster).

    Can someone help me do this please?

    The (working) PHP code is below, - it just needs to be called with the given word-id (vorto).

    (In the end it creates a Result (resultoj) row if it's not already there. If this is tricky to make in pure MySQL it's not important. Basically I do already know if it exists or not, - so if it doesn't I can just create a row and call the update function afterwards)


    Code:
    function updateResultatoj($vorto) {
    		global $uzanto;
    	
    		$sql = "SELECT dato FROM atingoj WHERE atingo = 1 AND uzanto = 1 AND vortonumero = '" . $_REQUEST['vorto'] . "' ORDER BY dato DESC";
    		$res = mysql_query($sql);
    		$row=mysql_fetch_array($res);
    
    		$k2=7;
    		$k1=50;
    
    		$p = $k1-log10(strtotime("tomorrow")-strtotime($row['dato']))*$k2;
    		$p*=4;
    		if ($p<-10) $p=-10;
    
    		$sql = "SELECT SUM(g.atingo) AS s0 FROM (SELECT atingo FROM atingoj WHERE vortonumero = " . $vorto . " ORDER BY dato DESC LIMIT 0,1) g";
    		$res = mysql_query($sql);
    		
    		$row = mysql_fetch_array($res);
    		$p += $row['s0']*50;
    
    		$sql = "SELECT SUM(g.atingo)-0.2*COUNT(g.atingo) AS s1 FROM (SELECT atingo FROM atingoj WHERE vortonumero = '" . $vorto . "' AND uzanto = " . $uzanto . " ORDER BY dato DESC LIMIT 1,4) g";
    		$res = mysql_query($sql);
    		
    		$row = mysql_fetch_array($res);
    		$p += $row['s1']*20;
    
    		$sql = "SELECT SUM(g.atingo)-0.2*COUNT(g.atingo) AS s2 FROM (SELECT atingo FROM atingoj WHERE vortonumero = '" . $vorto . "' AND uzanto = " . $uzanto . " ORDER BY dato DESC LIMIT 5,10) g";
    		$res = mysql_query($sql);
    		
    		$row = mysql_fetch_array($res);
    		$p += $row['s2']*5;
    
    		$sql = "SELECT SUM(g.atingo)-0.2*COUNT(g.atingo) AS s3 FROM (SELECT atingo FROM atingoj WHERE vortonumero = '" . $vorto . "' AND uzanto = " . $uzanto . " ORDER BY dato DESC LIMIT 15,30) g";
    		$res = mysql_query($sql);
    		
    		$row = mysql_fetch_array($res);
    		$p += $row['s3']*1;
    	
    		if ($p<0) $p=0;
    
    		$sql = "UPDATE resultoj SET xpoint = '" . $p . "' WHERE vorto = '" . $vorto . "' AND uzanto = " . $uzanto;
    		mysql_query($sql);
    		if (mysql_affected_rows()<1) {
    			$sql2 = "INSERT INTO resultoj (vorto, uzanto, xpoint) VALUES ('" . $vorto . "', " . $uzanto . ", '" . $p . "')";
    		    mysql_query($sql2);
    		}
    }
    Last edited by dadane; Jul 22, 2008 at 11:27. Reason: Putting my code in a codebox


Tags for this Thread

Bookmarks

Posting Permissions

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