CODEWITHSUNDEEP
phpmysql
pr0digy
pr0digy

Reputation: 72

How to store a selected value from a MySQL stored procedure into a variable in PHP?

Basically, I want to take a value from running a stored procedure that randomly generates a number, and use it later on in the website. Eventually, I'm going to need to pass that randomly generated number back to the MySQL database depending upon the actions that the user takes.

Stored Procedure:

BEGIN
    SET @leftplayerone = 0;
    SET @rightplayerone = 0;

    SET @leftplayerone = (SELECT FLOOR(RAND()*((SELECT COUNT(*) FROM 
Players)-1+1))+1);

   WHILE @rightplayerone = 0 OR @rightplayerone = @leftplayerone
    DO
        SET @rightplayerone = (SELECT FLOOR(RAND()*((SELECT COUNT(*) FROM 
Players)-1+1))+1);
    END WHILE;

   SELECT @leftplayerone;
   SELECT @rightplayerone;
END

I have a separate table that includes a number of players. I want to randomly pull 1 player from that table and then display that player on my website. Then, I want to randomly display a different player from that same table. The user should then be presented with these two randomly generated players and then choose an option presented to them with 5 different buttons. I want to then send that response, along with the two players that were randomly generated back to the database to store that information.

I'm just looking for some help on how to pull the two randomly generated players from the stored proc and display that on the site - I just put the rest of the details for context.

Update:

My PHP code is:

<?php
  $sql = "CALL FantasyUnited.GetRandomPlayersForTradeProposal();";
  $result = mysqli_query($conn, $sql);
  $resultCheck = mysqli_num_rows($result);

if ($resultCheck > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
      echo "<tr><td>". $row["PlayerOne"] ."</td><td>". $row["PlayerTwo"] ." 
  </td></tr>";
    }
  echo "</table>";
  }
  else {
    echo "0 result";
  }


  $conn-> close();

?>

I am now able to display the random Player IDs - now I need to figure out how to display the player's name that correlates to the player ID instead of just displaying the ID number. Then save that information and pass it back to the database when a user clicks on a button regarding those two players.

http://pr0digy.me/trades.php

The user will select one of the options (buttons) below the names of the players. I want the button they pushed and the names of the players stored and sent back to the database.

Upvotes: 1

Views: 720

Answers (1)

Barmar
Barmar

Reputation: 781592

Change the last line of the procedure to:

SELECT @leftplayerone AS leftplayer, @rightplayerone AS rightplayer;

so that you can fetch both results at once in PHP.

Then in PHP you can do:

$result = $pdo->query("CALL yourProcedure");
$row = $result->fetch(PDO::FETCH_ASSOC);
$leftplayer = $row['leftplayer'];
$rightplayer = $row['rightplayer'];

This is PDO syntax, the corresponding mysqli syntax is not very different.

Upvotes: 2

Related Questions