PHP to mqsql not working

[debow]

I have an issue where my button call to php does not work correctly but if I run the sql manually it works just fine. Can someone please suggest what may be wrong.

My file buttons.php has the following code, there are 9 other form call's but I cleaned it up for viewing reasons. All the updates are similar and work manually but when I click the button to call the updates they don't run at all, depending on how many times I click it it might but the results are off etc. But if I just copy/paste the sql into phpadmin it works as expected.

<?php 
require_once ('includes/functions.php');
require_once ('includes/config.inc.php'); 
include ('includes/header.html');
?>

<div id="leftcolumn">
<?php include "sidebar.php"; ?>
<br/>	
</div>	
      
<div id="main">
<?php

					score_swim_form();
?>
</div>

<div id="footer">
<?php include ('includes/footer.php'); ?>
</div>

SCORE_SWIM_FORM(); is in rank.buttons.inc.php which has the following code.

<?php
#### Display Functions ####
include_once 'config.inc.php'; 

function addSwimRank(){
   
	$query = "SET @rank = 0, @prev_val = NULL;
			UPDATE er2 
			JOIN(
			SELECT 
				@rank := IF(@prev_val=result,@rank,@rank+1) AS rank,
				event,
				id,
				@prev_val := result AS result
				FROM er2 where result !='DNC' and event='100m Swim' ORDER BY result)
				ranks ON (ranks.id = er2.id)
				SET eventrank = ranks.rank";
	$res = mysql_query($query);
	
	$query = "UPDATE er2 e
			LEFT JOIN ranktable rt ON e.eventrank = rt.rank
			SET e.eventscore = rt.score
			WHERE e.event='100m Swim'";

	$res = mysql_query($query); 
	
	$query = "UPDATE er2 set eventrank='n/a' where result='DNC' and event='100m Swim'";
	$res = mysql_query($query); 
	
	
}

function score_swim_form(){
	
	if(isset($_POST['updateswim'])) {
		addSwimRank();
		}
	
echo '<form action="./ranktestbutton.php" method="post"> 
	<fieldset><legend>Score Swim</legend>
		<p> 
			<input name="updateswim" type="submit" value="Update"> 
		</p> 
		
	</fieldset>
</form>';

}
?>

Thanks for any help or suggestions.

[omol]

If this takes a while to complete as a query on your database you could be timing out on the mysql connection.

Where abouts to do you open the mysql connection in your code? I can't see a call to any functions or class at the begining of your query.

Try using mysql_ping or add mysql_connnect to the begining of the function.

Edit,

Also, maybe worth adding some error checking after you send the query.

$res = mysql_query($query);
if(!$res) {
    die('Error with query: ' . mysql_error());
}

[rajarajan2017]

Formation of query in php may be wrong

[debow]

I added there query error checking and this is what I got after hitting the button.

Error with query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE er1 JOIN( SELECT @rank := IF(@prev_val=result,@rank,@rank+' at line 2

So the issue is with php as it works just fine manually.

[debow]

Think I found a fix/workaround. Broke the query into 2 and it seems to be working. Will start testing the other 10 events.

$query = "SET @rank = 0, @prev_val = NULL";
	$res = mysql_query($query);
	
	$query = "
			UPDATE er1 
			JOIN(
			SELECT 
				@rank := IF(@prev_val=result,@rank,@rank+1) AS rank,
				event,
				id,
				@prev_val := CAST(result AS SIGNED) AS result
				FROM er1 where result !='DNC' and event='Push Ups' ORDER BY result DESC)
				ranks ON (ranks.id = er1.id)
				SET eventrank = ranks.rank";
	$res = mysql_query($query);
	if (!$res){
		die('Error with query: ' . mysql_error());
	}