PHP Bind_param fetch data

Question

Guys im using Bind_param in php to retrieve Username and password from Login table. my question is how can i fetch all the info of user, and pass it to variable as an object? please see my code below

  require 'dbc.php';
        require 'security.php';

        $myusername=trim($_POST['strusername']); 
        $mypassword=trim($_POST['strpassword']);
        $myusername =escape($myusername);
        $mypassword=escape($mypassword);

    $sql = "SELECT * FROM login WHERE strusername=? AND strpassword=?";

    $stmt  = $db->prepare($sql);
    $stmt->bind_param('ss',$myusername,$mypassword);
    $stmt->execute();

    $stmt->store_result();

    if($stmt->num_rows){
                echo "user verified, Access Granted.";

    //      while($row=$stmt->fetch_object()){
    //          $strfullname= $row->strfullname;
    //          $strcompany= $row->strcompany;
    //          $strdept= $row->strdept;
    //          $strloc= $row->strloc;
    //          $strposition= $row->strposition;
    //          $strauthorization= $row->strauthorization;
    //          $stremailadd= $row->stremailadd;
    //          $strcostcent= $row->strcostcent;
    //          $strtelephone= $row->strtelephone;
    //      };
//    how to fetch all data in my query using param LIKE THIS

            }else
            {
                echo "Invalid Username or Password";
            }

yah i used that. but im using SELECT * and not by field. is it necessary in bind_param to input all fields in SELECT query. like SELECT strfullname,strcompany,strdep FROM Login WHERE..... or a much easy way like using SELECT * instead? — Nixxx, Mar 23 '15 at 02:31
it can be dynamic, the answer to that lies on the comments below the manual, some of the users already found a way to get around that issue. http://php.net/manual/en/mysqli-stmt.bind-result.php#102179 you don't need to use the whole bit, just inside the if block statement, that'll suffice — Kevin, Mar 23 '15 at 02:36
yap i found it in the comment section! thanks for the reference!! — Nixxx, Mar 23 '15 at 02:37
or if `->get_result()` is available for you to use, use it instead, it'll be less painful — Kevin, Mar 23 '15 at 02:40
@Ghost just a question is `->get_result()` also available to PDO? — Kim Oliveros, Mar 23 '15 at 03:04
@KimOliveros no it does not have, and does not need such function. after `->execute()` in PDO, you could just outright use `->fetchAll()` to fetch all rows, or `fetch()` to get a single row. — Kevin, Mar 23 '15 at 03:09

score 0 · Answer 1 · answered Mar 23 '15 at 02:41

Seems like what you're looking for is extract(), used with fetch_row(). So you'd end up with something like:

while ($row = $stmt->fetch_row()) {
    extract($row, EXTR_OVERWRITE);
    // your logic here...
}

As fair warning, this will overwrite any variables with the same name as your database columns. Since you're pulling from the database rather than a superglobal you at least know what you're getting into, but it's still a risk, particularly if you've got this code in global scope rather than inside a function/method.

As such, you may want to wrap your row-to-be-extracted in array_intersect_key(), like so:

$allowedFields = array_flip(['strfullname', 'strcompany', 'strdept',
    'strloc', 'strposition', 'strauthorization', 'stremailadd',
    'strcostcent', 'strtelephone']);

while ($row = $stmt->fetch_row()) {
    extract(array_intersect_key($row, $allowedFields), EXTR_OVERWRITE);
    // your logic here...
}

so your code documents which fields will suddenly turn into (local or global) variables.

PHP Bind_param fetch data

1 Answers1