Prepared statement with a relational database

I could use some help on how to change this query

$query = mysql_query("SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username FROM messages M, users U  WHERE M.uid_fk=U.uid and M.uid_fk='$uid' order by M.msg_id desc ")

into a prepared statement. I am not sure what would go in the mysqli_stmt_bind_param(). This is what I have so far:

$stmt = mysqli_prepare($link, "SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username FROM messages M, users U  W开发者_Python百科HERE M.uid_fk=? and M.uid_fk=? order by M.msg_id desc")) {


mysqli_stmt_bind_param($stmt, "s,s", $uid,$uid); 

I know that the $uid,$uid is not right, on how to change M.uid_fk=U.uid and M.uid_fk='$uid to work in the bind_para.

Thanks

You only need to bind what you actually plan on passing in so your query stays pretty much the same:

$stmt = mysqli_prepare("SELECT M.msg_id, M.uid_fk, M.message, M.created, U.username FROM messages M, users U  WHERE M.uid_fk=U.uid and M.uid_fk= ? order by M.msg_id desc");

mysqli_stmt_bind_param($stmt, "s", $uid);

Also not when you have multiple params the bind types are not comma separated so if it would look like this:

 mysqli_stmt_bind_param($stmt, "sss", $uid, $someString, $someOtherString);

Finally if you should really use PDO. Mysqli is much harder to work with, especially with prepared statements. For example this is the Prepared statement example from the php.net manual:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT Name, CountryCode FROM City WHERE CountryCode = ? ORDER by ID DESC LIMIT 150,5";

// you can only bind by reference so we have to do this... and it gets really annoying!
$code = 'US'; 

if ($stmt = $mysqli->prepare($query)) {

    $stmt->bind_param($stmt, 's', $code);

    /* execute statement */
    $stmt->execute();

    /* bind result variables  - we have to do this as well with is really annoying! */
    $stmt->bind_result($name, $code);

    /* fetch values */
    while ($stmt->fetch()) {
        printf ("%s (%s)\n", $name, $code);
    }

    /* close statement */
    $stmt->close();
}

And doing the same with PDO:

try {

  $pdo = new PDO("mysql:host=localhost;dbname=mydb", "my_user", "my_password");
} catch(PDOException $e) {
  printf("Connect failed: %s\n", $e->getCode());
  exit();
}

$query = "SELECT Name, CountryCode FROM City WHERE CountryCode = ? ORDER by ID DESC LIMIT 150,5";

    // when you call prepare you can bind all the vairables immediately
    // or you can do it ehn you call PDOStatement::execute()

    if ($stmt = $pdo->prepare($query, array('US')) {


        /* execute statement */
        // if we wanted to bind params at execution time we could use
        // $pdo->execute(array('US'));
        $stmt->execute();


        /* fetch values */
        while (false !== ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            printf ("%s (%s)\n", $row['Name'], $row['Code']);
        }

        /* close statement */
        $stmt->close();
    }

Now if you like binding to referenced variables for results or parameters, you can still do that with PDO, but I find it a lot easier and more flexible to not do so. So really it's about the flexibility. You can use the simple procedure for easy things, or the more complex procedure when needed.