Creating and using varables within a controller

PHP
1

Hi all,

If I create a variable within a controller and then want to use it again as a parameter within another sql statement in that same controller, how is this done?

I am not talking about anything being available as part of an array here, please see my example below.

So in the controller index.php, I would have the following:

$id = "SELECT id FROM customer WHERE id = 1"

Then directly underneath that in the same controller there is another SQL statement.

$sql = "SELECT * FROM customer where id = '$id';

Now if this then becomes an array so I have something like:

 while($row = mysqli_fetch_array($result))
 {
  $customers[] = array('id' => $row['id'], 'name' => $row['name']);
 }

How do I then use the value in the array in my SQL statement:

"SELECT * FROM customer where id = [B]UNSURE OF THE VALUE THAT SHOULD GO HERE?[/B]

Please note that I am assuming there is only one value in the $customers array.

Thanks for your time and help in advance.

2

Sorry but I’m totally confused as to what your trying to accomplish here as your code is already redundant just by looking at the above examples, what your doing above is redundant because:

  1. Your running a MySQL query to select an id for a customer which you already know the id of
  2. Your running a second query using the first queries return value (assumed) which you don’t need to do if you already know the id == ‘1’
  3. Your running a third query which is VERY redundant as you already have the customer information you need in the $customers[] array

Sorry if this sounds rough but I’m just very confused as to what your trying to accomplish without a properly explained post.

3

No it doesn’t sound rough at all.

So my problem is that I have created some log on functionality.

A player logs in using their email address and password.

What I am then trying to do in the controller (index.php) is to obtain the unique id for that player to use in other sql queries.

if(isset($_REQUEST['email']))
{
 include $_SERVER['DOCUMENT_ROOT'] . '/includes/db.inc.php';

 $email = mysqli_real_escape_string($link, $_REQUEST['email']);
 $sql = 'SELECT id, firstname FROM player WHERE email = "' . $email . '"';
 $result = mysqli_query($link, $sql);
 if(!$result)
 {
  $error = 'Unable to select players firstname from the database.' . mysqli_error($link);
  include 'error.html.php';
  exit();
 }

 while($row = mysqli_fetch_array($result))
 {
  $players[] = array('id' => $row['id'], 'firstname' => $row['firstname']);
 }

So now I have that id which I want to use in the $players array.

However I do not know how to make use of it in subsequent queries.

So for instance if I want to select all of the games that this player was involved in, I would want something like

SELECT * FROM games WHERE player_id = ?????????

I’m not sure how to use that id that I now have in the $players array in the sql statement.

This is where I am stuck.

4

In your while loop simply add a third index key after firstname called games and run the SQL query you need to inside the while loop, see the below example:

$players = array();

while ($row = mysqli_fetch_array($result))
{
    $players[] = array('id' => $row['id'], 'firstname' => $row['firstname'], 'games' => array());

    // Retrieve all the games for the current user
    if ($games = mysqli_query('SELECT name, genre FROM games WHERE player_id = ' . $row['id']))
    {
        if (mysqli_num_rows($games) > 0)
        {
            while ($game = mysqli_fetch_array($games, MYSQLI_BOTH))
            {
                $players[count($players) - 1]['games'][] = array(
                    'name'  => $game['name'],
                    'genre' => $game['genre']
                );
            }
        }
    }
}

Also i would recommend you try and steer clear of using a wildcard queries as they take longer unless you have table INDEXES which still can lead to slow results.

5

Thanks for your reply.

Firstly the question marks were not meant to represent a wild card search. The question marks where me wondering what I had to put in at that point in order to use the id of the player.

I’m still not sure what that value should be. Are you able to help with that?

If it helps I am trying to populate some drop downs such as ‘show all opponents that player has played’ and ‘show all leagues that player belongs to’.

I tried this but it does’t work:

 $sql = "SELECT DISTINCT player.firstname, game.player2_id AS opponent_id

				FROM player INNER JOIN game

                ON player.id = game.player2_id
		
				WHERE player1_id = '$row['id']'
		
				UNION
		
				SELECT DISTINCT player.firstname, game.player1_id AS opponent_id
		
				FROM player INNER JOIN game

                ON player.id = game.player1_id
		
				WHERE player2_id = '$row['id']'";
				
 $result = mysqli_query($link, $sql);
 if(!$result)
 {
  $error = 'Unable to populate the players dropdown from the database.' . mysqli_error($link);
  include 'error.html.php';
  exit();
 }