Jump to content

prepared statement select fetch


sqlnoob
Go to solution Solved by sqlnoob,

Recommended Posts

//DATABASE CONNECTION VARIABLES
$myserver ="localhost";
$myname = "myname";
$mypassword = "mypassword";
$mydb ="mygamedb";


/*SQL CONNECTION*/
// Create connection
$conn = new mysqli($myserver, $myname, $mypassword, $mydb);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
else {

//variables
$username = $_POST["username"];
$password = $_POST["password"];
$ip = $_SERVER['REMOTE_ADDR'];

//INSERT USER
//prepare and bind
$stmt = $conn->prepare("INSERT INTO Players (Username, Password, IP) VALUES (?, ?, ?)");
//bind parameters
$stmt->bind_param("sss", $username, $password, $ip);
//set parameters and execute
$stmt->execute();
//close
$stmt->close();

//FETCH ID
$resultnews = mysql_query("SELECT * FROM Players WHERE Username ='$username'");
$rownews = mysql_fetch_array($resultnews);
$user_id = $rownews["ID"];


}

After having suffered an SQL injection into one of my sites, I figured it was time to overhaul it and use prepared statements. I am new to this. I figured out how to an INSERT with an example, but now I need to fetch an ID and cannot get it to work. Any help much obliged. All I need is just one good example. Looked all over the place, but all I get are insert examples, which is NOT what i need. Really need one with a select and fetch.

Link to comment
Share on other sites


$sql = "SELECT firstname, lastname FROM Players WHERE Username = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $username);
$stmt->execute();

// either
$stmt->bind_result($firstname, $lastname);
$stmt->fetch();
echo $firstname . ' ' . $lastname;
// or
$res = $stmt->get_result();
$row = $res->fetch_assoc();
echo $res['firstname'] . ' ' . $row['lastname'];

 

Edited by Barand
  • Like 1
Link to comment
Share on other sites

Don't use MySQLi, it's incredibly cumbersome (as you just saw).

 

Go with PDO. It's a universal database interface which works with all mainstream systems (not just MySQL), and has a very nice API. You'll find it much easier to use:

$player_id_stmt = $database->prepare('
    SELECT id
    FROM players
    WHERE username = :username
');
$player_id_stmt->execute([
    'username' => $_POST['username'],
]);
        
$player_id = $player_id_stmt->fetchColumn();

It's even more obvious when you have to fetch multiple rows. With PDO, you simply use a foreach loop. With MySQLi, you need bind_result(), fetch() and a while loop.

 

Also note that PDO supports named parameters.

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.