mardi 26 février 2019

Is php-mysql: update column with different values depending on condition involving PHP variable

I have a webform with the string variables $participant1, participant2, and $winner in php.When a form is posted it updates the columns participant and winner for two rows in my_table. The column type for winner is BOOLEAN.

Here is what my table currently looks like:

ID |Game |Participant | Winner
-------------------------------
1     1     John          NULL
2     1     Frank         NULL

The submitted variable $winner will be the name of either participant ('John' or 'Frank'). When it is submitted, I'd like for 1 to be set for which ever participant the string $winner matches. And 0 for the other participant.

i.e. if $participant1 =='John' and $winner=='John' Then the table should look like:

ID |Game |Participant | Winner
-------------------------------
1     1     John          1
2     1     Frank         0

I can't seem to figure this part out.

I tried:

$participant1= mysqli_escape_string( $con, $params['participant1']);
$participant2= mysqli_escape_string( $con, $params['participant2']);
$Winner= mysqli_escape_string( $con, $params['Winner']);    

mysqli_query($con, "UPDATE my_table SET Winner = IF('$Winner'=='$participant1',1,0) WHERE Participant ='$participant1');

mysqli_query($con, "UPDATE my_table SET Winner = IF('$Winner'=='$participant2',1,0) WHERE Participant ='$participant2'");

Winner still shows NULL afterwards. Is there a primarily MySQL way to do this?

Aucun commentaire:

Enregistrer un commentaire