creating multidimensional array from prepared statement

Question

I have php code that uses general queries but I want to convert it to a prepared statement. I keep receiving errors when trying to create an array with the prepared queries.

Here is the array that was created with a general query that works perfectly.

public function tickets () {
    $this->db_connection = new mysqli('', '', '', '');
    $sql = "SELECT * FROM tickets WHERE member_id = '1'"; 
    $query = $this->db_connection->query($sql); 
    $me2 = array();

    while ($row = $query->fetch_object()) { 
        $me2[$row->ticket_id]['ticket_result'] = $row->ticket_result;
        $me2[$row->ticket_id]['member_id'] = $row->member_id;
    } 

    return $me2;
}

This is the new code I tried changing into a prepared statement that doesn't work. When I try echoing with a foreach loop a var_dump returns "NULL" values.

public function tickets() {
    $this->db_connection = new mysqli('', '', '', '');

    $sql = "SELECT * FROM tickets
            WHERE member_id = ? ";

    $stmt = $this->db_connection->prepare($sql);
    $id = "1";
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $stmt->store_result();   

    $me2 = array();

    while ($row = $stmt->fetch()) { 
        $me2[$row->ticket_id]['ticket_result'] = $row->ticket_result;
        $me2[$row->ticket_id]['member_id'] = $row->member_id;
    }  
    return $me2;
 }

Is it necessary to use prepared statements with a query like this since the query doesn't involve any user input?

Dave O'Dwyer · Accepted Answer · 2014-08-12 11:18:30Z

1

To answer your last question

Is it necessary to use prepared statements with a query like this since the query doesn't involve any user input?

Not really. Prepared statements are designed to fight against SQL injection attacks, that can only happen from badly sanitized user input. If you are creating the query without user input, you would get away with just using your original implementation.

Regarding the NULL values within you while loop, this would suggest that your query is failing.

$stmt->bind_param('i', $id);

The above function is incorrect and is most likey why the query is not working. "1" is not an integer, which the i suggests from the PHP Manual. Use $i = 1 instead (notice no quotes wrapped around the value)

answered Aug 12, 2014 at 11:18

Dave O'Dwyer

1,20210 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user3718799 Over a year ago

could anything bad happen from me using the general query i posted in the question? (e.g. sql injection)

Dave O'Dwyer Over a year ago

By general query, if you mean the first query you posted, I don't think so as it is just a SQL SELECT that isn't using any variables obtained from a user. However, for the purposes of future development, I would suggest that you use the prepared statement approach. It is good to get in the habit of always using prepared statements because eventually you will be doing a query based on user input.

user3718799 Over a year ago

I changed it to $i = 1 but still I receive the null. When I do a var_dump on the variable $me2 this is what I receive. array(1) { [""]=> array(2) { ["ticket_result"]=> NULL ["member_id"]=> NULL } }

Dave O'Dwyer Over a year ago

Try this: printf("Error: %s.\n", $stmt->error); Make sure you add it AFTER $stmt->execute. It should give you a clearer understanding of the error.

user3718799 Over a year ago

I get "Error:." returned. when I changed it to $stmt->errno it returned "Error:0.".

|

Collectives™ on Stack Overflow

creating multidimensional array from prepared statement

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related