Cannot use a scalar value as an array using mysqli_stmt_bind_param

Question

I am creating ablog using php and mysqli in the blog I can display posts under specific topic

below is the code where display posts under topic:

/* * * * * * * * * * * * * * * *
* Returns all posts under a topic
* * * * * * * * * * * * * * * * */
function getPublishedPostsByTopic($topic_id) {
    global $conn;
    $sql = "SELECT * FROM posts ps WHERE ps.id IN (SELECT pt.post_id FROM post_topic pt WHERE pt.topic_id=? GROUP BY pt.post_id HAVING COUNT(1) = 1)";
    $stmt= mysqli_stmt_init($conn);
    mysqli_stmt_prepare($stmt,$sql);
    mysqli_stmt_bind_param($stmt,"i",$topic_id);
    mysqli_stmt_execute($stmt);
    $result=mysqli_stmt_get_result($stmt);
    $posts=mysqli_fetch_assoc($result);

    $final_posts = array();
    foreach ($posts as $post) {
        $post['topic'] = getPostTopic($post['id']); 
        array_push($final_posts, $post);
    }
    return $final_posts;
}

but when I run it I got these errors:

Warning: Cannot use a scalar value as an array
Warning: Illegal string offset 'id' 
Warning: Cannot assign an empty string to a string offset

the point I'm using mysqli_stmt_bind_param to avoid MySQL injection the SQL INJECTION occur when i used the following code instead of one mentioned above:

function getPublishedPostsByTopic($topic_id) {
    global $conn;
    $sql = "SELECT * FROM posts ps WHERE ps.id IN (SELECT pt.post_id FROM post_topic pt WHERE pt.topic_id=$topic_id GROUP BY pt.post_id     HAVING COUNT(1) = 1)";
    $result = mysqli_query($conn, $sql);
    // fetch all posts as an associative array called $posts
    $posts = mysqli_fetch_all($result, MYSQLI_ASSOC);

    $final_posts = array();
    foreach ($posts as $post) {
        $post['topic'] = getPostTopic($post['id']); 
        array_push($final_posts, $post);
    }
    return $final_posts;
}

I'm beginner with PHP and hope someone explain to me how to solve this issue

thank you

score 0 · Answer 1 · answered Aug 02 '20 at 20:26

mysqli_fetch_assoc fetches single record from resultset. So, $posts in $posts=mysqli_fetch_assoc($result); is not array of arrays, but just a single-dimension array. You still can use mysqli_fetch_all and change your code to:

mysqli_stmt_prepare($stmt,$sql);
mysqli_stmt_bind_param($stmt,"i",$topic_id);
mysqli_stmt_execute($stmt);
$result=mysqli_stmt_get_result($stmt);
$posts=mysqli_fetch_all($result);   // change here

Other code doesn't need any changes.

score 0 · Answer 2 · answered Aug 02 '20 at 21:03

There is no need to complicate the code so much. Stick to using object-oriented style and loop on the results of get_result() directly.

function getPublishedPostsByTopic($topic_id) {
    global $conn;
    // prepare/bind/execute
    $sql = "SELECT * FROM posts ps WHERE ps.id IN (SELECT pt.post_id FROM post_topic pt WHERE pt.topic_id=? GROUP BY pt.post_id HAVING COUNT(1) = 1)";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('i', $topic_id);
    $stmt->execute();

    $final_posts = array();
    // get result and iterate over it
    foreach ($stmt->get_result() as $post) {
        $post['topic'] = getPostTopic($post['id']);
        $final_posts[] = $post;
    }
    return $final_posts;
}

Cannot use a scalar value as an array using mysqli_stmt_bind_param

2 Answers2