Jump to content

prepared statement select fetch


Go to solution Solved by sqlnoob,

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.

Link to comment
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'];


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

Join the conversation

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

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.