prepared statement select fetch

[sqlnoob]

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

[Barand]

$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 December 27, 2014 by Barand

[mac_gyver]

example #1 on the php.net documentation for the mysqli prepare statement shows a select query - http://php.net/manual/en/mysqli.prepare.php

[Jacques1]

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.

[sqlnoob]

thanks for the replies guys. That was what I needed.