Jump to content


This topic is now archived and is closed to further replies.


prepared statement select fetch

Recommended Posts

$myserver ="localhost";
$myname = "myname";
$mypassword = "mypassword";
$mydb ="mygamedb";

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

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

$username = $_POST["username"];
$password = $_POST["password"];

//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

$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.

Share this post

Link to post
Share on other sites

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

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


Share this post

Link to post
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
    '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.

Share this post

Link to post
Share on other sites

  • 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.