MySQLi Query prepare and retrieving results

Question

I've gone through quite a number of stackoverflow threads and I simply can't get it right to retrieve the results after preparing a query. I've tried a number of different solutions and none seem to be able to fetch the associative array after I execute the query

    $mysqli = new MySQLi('localhost', 'root', '', 'prac2');
    $query = $mysqli-> prepare("SELECT * FROM `user` WHERE username=? and password=?");
    $query-> bind_param('ii', $username, $password);
    if($query-> execute())  {
        $query->store_result();
        if ($query -> num_rows > 0) {
            $result = $mysqli->query($query);
            $r = $result -> fetch_array(MYSQLI_ASSOC)['userid'];
            $_SESSION['userid'] = $r;
        }
    }

I've established that sometimes its a case of result containing a boolean for success but I'm still not certain what exactly I'm doing wrong.

UPDATED:

Okay the bind_param works now, but the fetch_assoc keeps giving me the error "Call to a member function fetch_assoc() on a non-object", I even test the result to ensure that it returns true.

$mysqli = new MySQLi('localhost', 'root', '', 'prac2');
$query = $mysqli->prepare("SELECT * FROM user WHERE username=? and password=?");
echo $mysqli->error;
$query-> bind_param('ss', $username, $password);
if($query->execute())  {
    $result = $query -> store_result();
    if($result) {
        while($row = $result -> fetch_assoc()){
            echo $row['userid'];
            $_SESSION['userid'] = $row['userid'];
        }
    }
}

@Aleksandar as I mentioned I went through quite a number of different articles and for the most part I'm quite confused. 'ss' givs me the same errors: "mysqli::query() expects parameter 1 to be string" and "Call to a member function fetch_array() on a non-object" — Chris Crossman, Sep 01 '14 at 10:44
Call to a member function fetch_array() on a non-object, happens when your query isnt good, change ii to ss, and check what are you getting in your $username and $password variables — Aleksandar Vasić, Sep 01 '14 at 10:46

score 2 · Accepted Answer · answered Sep 01 '14 at 10:41

2

Usernames and password are strings and it should be 's' denoting that corresponding variable has type string. I don't see how usernames and passwords are integers. Bind Param Types

   $query-> bind_param('ss', $username, $password);

answered Sep 01 '14 at 10:41

John Robertson

1,486
13
16

@John Robertson Thank you I was unaware that it stands for integers. – Chris Crossman Sep 01 '14 at 10:47
@ChrisCrossman Glad to help. I know you'll do the same for someone else – John Robertson Sep 01 '14 at 10:50
@JohnRobertson Sorry to bother again, but it seems to almost be working now, I updated the question with the new code, any chance that you could take a look ? Thanks in advance – Chris Crossman Sep 01 '14 at 11:08
@ChrisCrossman try to use $query->bind_result($username, $password); instead of bind_param – John Robertson Sep 01 '14 at 11:14
@ChrisCrossman I'm logging out. http://stackoverflow.com/questions/12769982/reference-what-does-this-error-mean-in-php/12769983#12769983 hope this can help you. – John Robertson Sep 01 '14 at 11:19
@JohnRobertson Thanks a lot, its just about working now, but just small things I think I can handle. Once again thank you very much – Chris Crossman Sep 01 '14 at 11:23

score 0 · Answer 2 · edited Sep 01 '14 at 12:18

0

Inside bind_param for string you should use s. I mean try ss instead of ii.

edited Sep 01 '14 at 12:18

David Bohunek

3,181
1
21
26

answered Sep 01 '14 at 10:42

user2958326

1

MySQLi Query prepare and retrieving results

2 Answers2