为什么我的MySQL准备语句不起作用？

Question

I am trying to convert a MySQL query to a prepared statement and having trouble, for some reason I am not getting any results. The MySQL query definitely works but I just can't see what I am doing wrong. I'd really appreciate any help. In my error log I get the error Invalid argument supplied for foreach()

$results = $mysqli->prepare("SELECT up.id,up.file,up.title,p.user_name,p.user_id, GROUP_CONCAT(CONCAT(cp.user_id,'~',cp.user_name) SEPARATOR '|') AS tagGroup
    FROM tbl_uploads up
    LEFT JOIN tbl_users p ON up.user_id = p.user_id
    LEFT JOIN tbl_collab c ON up.file = c.file
    LEFT JOIN tbl_users cp ON cp.user_id = c.collab_userid
    GROUP BY up.file ORDER BY up.id DESC");

$results->execute(); //Execute prepared Query

$results->bind_result($page_id,$user_id,$file); //bind variables to prepared statement

//output results from database

while($results->fetch()){ //fetch values

    $page_id = explode (",", $row['id'] );
    $user_id = explode (",", $row['user_id'] );
    $files = explode (",", $row['file']);

    foreach($user_id as $user_id) {
        foreach($files as $file) {
            foreach($page_id as $page_id) {
                echo '<li>'.$page_id.' - '. $user_id.' - '.$file.'</li>';    
            }
        }
    }
}
?>

Answer 1

Change your code accordingly:

$stmt = $mysqli->prepare("SELECT up.id,up.file,up.title,p.user_name,p.user_id, GROUP_CONCAT(CONCAT(cp.user_id,'~',cp.user_name) SEPARATOR '|') AS tagGroup
    FROM tbl_uploads up
    LEFT JOIN tbl_users p ON up.user_id = p.user_id
    LEFT JOIN tbl_collab c ON up.file = c.file
    LEFT JOIN tbl_users cp ON cp.user_id = c.collab_userid
    GROUP BY up.file ORDER BY up.id DESC");

$stmt->bind_param( 'iis', $page_id, $user_id, $file );

$stmt->execute();

$result = $stmt->get_result();

//output results from database

while( $row = $result->fetch_assoc() ){

    $page_ids = explode( ",", $row['id'] );
    $user_ids = explode( ",", $row['user_id'] );
    $files = explode( ",", $row['file'] );

    foreach( $user_ids as $user_id ) {
        foreach( $files as $file ) {
            foreach( $page_ids as $page_id ) {


                echo '<li>'.$page_id.' - '. $user_id.' - '.$file.'</li>';

            }
        }
    }
}

Take a look at the example. Also bind_param method.

And one more thing: Storing page_ids, user_ids, files in one field in the database separating with commas is not the best db structure!

Update:
In case you are not passing any parameters to SQL statement, use the following:

$query = "SELECT up.id,up.file,up.title,p.user_name,p.user_id, GROUP_CONCAT(CONCAT(cp.user_id,'~',cp.user_name) SEPARATOR '|') AS tagGroup
    FROM tbl_uploads up
    LEFT JOIN tbl_users p ON up.user_id = p.user_id
    LEFT JOIN tbl_collab c ON up.file = c.file
    LEFT JOIN tbl_users cp ON cp.user_id = c.collab_userid
    GROUP BY up.file ORDER BY up.id DESC";

if ($result = $mysqli->query($query)) {
    while ($row = $result->fetch_assoc()) {
        $page_ids = explode (",", $row['id'] );
        $user_ids = explode (",", $row['user_id'] );
        $files = explode (",", $row['file']);

        foreach($user_ids as $user_id) {
            foreach($files as $file) {
                foreach($page_ids as $page_id) {


                    echo '<li>'.$page_id.' - '. $user_id.' - '.$file.'</li>';

                }
            }
        }
    }

    $result->close();
}