At the moment I have a project in progress and lots of the SQL query parameters are hard coded, e.g.
$opponentid = "SELECT DISTINCT player2_id AS opponent_id
FROM game
WHERE player1_id = 1
UNION
SELECT DISTINCT player1_id
FROM game
WHERE player2_id = 1";
$sql = "SELECT id, firstname FROM player WHERE id = '$opponentid' ";
$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();
}
Now I am trying to substitute the hard coded values in these queries with the id of the person who has logged in.
This is where I am running into my problem.
So when someone logs in, the two values passed are the email address which is unique and the password.
In my controller I can get hold of the email by using the following:
$sql = "SELECT id, firstname, lastname, email 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();
}
I would like to be able to do something similiar with my first query but substitute the hard coded value for $id, i.e. the id of the player who has logged in.
However I am unsure how to create this $id variable.
Thought I would add some more info in order to hopefully get a response on this.
The id that I am after is in the same table as the email and password. The table creation statement looks like this:
CREATE TABLE player (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
firstname TEXT NOT NULL,
lastname TEXT NOT NULL,
dob DATE NOT NULL,
email TEXT NOT NULL,
password CHAR(32)
) DEFAULT CHARACTER SET utf8;
Please also note that I have updated the email column to be UNIQUE so it is fit for purpose in terms of acting as a logon name.
What I can’t figure out is how to get hold of the id of the player?
The query above is already asking for the id. Somewhere further down in your script it must be retrieving the records from the result array (look for a function beginning with mysqli_fetch_… ).
However I am not sure how to get at that value, i.e. substitute it into the hard coded value in the controller code.
Would it be something like:
$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 = players['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 = players['id']";
Yes your way should get the value. You will notice that in the example below, I have explicitly escaped the value that was originally POSTED as it should not be trusted. You could also do this with a bound parameter if using PDO, I am not sure if you can use bound parameters in the MSQLI?
Also please notice the formating of the SQL it is easier to read, which becomes important as the scope of an application grows.
$sql = null;
$players_id = htmlentities(players['id']);
$sql = "
SELECT DISTINCT
p.firstname
, g.player2_id AS opponent_id
FROM player AS p
INNER JOIN game AS g
ON p.player.id = g.player2_id
WHERE p.player1_id = $players_id
UNION
SELECT DISTINCT
p.firstname
, g.player1_id AS opponent_id
FROM player AS p
INNER JOIN game AS g
ON p.player.id = g.player1_id;
"
My solution as posted doesn’t actually work. I get the following error:
Unable to populate the players dropdown from the database.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 ‘[‘id’] UNION SELECT DISTINCT player.firstname, game.player1_id’ at line 7
My problem is and correct me if you think that I am wrong is that the id which I am after is never actually posted. Upon logging in the only two things that are posted are the email address and the password.
If the id was passed in then that would make my life easier as I would know how to access it in an array.
My question might be better phrased as follows:
If I assign a value to a variable in the controller that stores a player id, how do I then use this variable / value in future sql statements in the same controller.
If the id was passed in then that would make my life easier as I would know how to access it in an array.
Not really, you would likely still have to double check that the value being passed belonged to that email address holder.
If I assign a value to a variable in the controller that stores a player id, how do I then use this variable / value in future sql statements in the same controller.
Most would advocate using a session variable for storing that (maintaining session-level state)
<?php
session_start();
// do login things
$_SESSION['userid'] = $userid;
Then when you need to access that variable:
<?php
session_start();
// coding stuff
// then
"select stuff from table where userid=" . $_SESSION['userid'] ;
Thanks for the reply, please see my comments below.
With regards to your first comment:
If the id was passed in then that would make my life easier as I would know how to access it in an array.
Not really, you would likely still have to double check that the value being passed belonged to that email address holder.
The email address is unique to the user. I know I didn’t say that but hopefully that would solve that issue.
Your second comment is exactly what I am looking for however I am still unsure of how to get that ‘playerid’ into a session variable where it can be used for the duration of the session.
This may sound stupid, but the only two pieces of information being passed in from the login are the email address and the password so how do I then use these to get the player id which can then be used throughout the session.
Would it be:
<?php
session_start();
$playerid = "SELECT id FROM player where email = '$email'";
$_SESSION['playerid'] = $playerid
Then if the above is correct I can as you have said do the following:
<?php
session_start();
// coding stuff
// then
"select stuff from table where userid=" . $_SESSION['userid'] ;
Hopefully that makes sense. What I’m unsure of is how to get that player id into a variable that I can use in other statements.
That seems right, but watch your naming, if you store it as $_SESSION[‘playerid’] = $var then you access it as echo $_SESSION[‘playerid’] – not ‘userid’ as in the last post you made.
Yes, armed with username/password look up playerid, assign it to session variable. When the session ends (because they close their browser window) they have to login again.
$playerid = “SELECT id FROM player where email = ‘$email’”;
Shouldn’t that be?
$playerid = “SELECT id FROM player where email = ‘$email’ AND password = ‘$password’”;
I will presume you are escaping these 2 vars before putting the data into your database like that – I mention it because I do not see you doing it.
Do I need to escape those values? There’s nothing actually going into the database here.
In my registration form, these values use the mysqli_real_escape_string built in function before the information is inserted into the database.
Also the email is unique so I’m not sure if I need to have the password in the SELECT statement too?
Finally with the above, you still have to use mysqli_query don’t you otherwise $playerid will literally equal "“SELECT id FROM player where email = ‘$email’”;