Jump to content

Archived

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

sqlnoob

prepared statement select fetch

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.

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);
$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'];

 

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
');
$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.

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.