it works, - but is highly inefficient

[dadane]

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)

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);
		}
}