CODEWITHSUNDEEP
php
user3718799
user3718799

Reputation: 55

creating multidimensional array from prepared statement

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?

Upvotes: 0

Views: 87

Answers (1)

Dave O'Dwyer
Dave O'Dwyer

Reputation: 1192

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)

Upvotes: 1

Related Questions