为什么我的php脚本没有返回任何变量？

Question

I've been trying for a while to add a 'load more' button to a php script.

I've got a rather complicated MYSQL query here that does work but I am now trying to use with prepared statements and just output two variables.

Whilst the script does return the expected number of results it's not actually echoing out any variables. I suspect the problem lies in the foreach loops... In the console response I can see this:

<li>-</li><li>-</li><li>-</li>

The script

//get current starting point of records
$position = (($page_number-1) * $item_per_page);

//fetch records 
$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 LIMIT ?, ?");

//bind parameters for markers
$results->bind_param("dd", $position, $item_per_page); 
$results->execute(); //Execute prepared Query
$results->bind_result($title, $file); //bind variables to prepared statement

//output results from database

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

$titles =  explode (",", $row['title']);
$files = explode (",", $row['file']);

foreach($titles as $title) {
foreach($files as $file) {
    echo '<li>'.$title.'-'.$file.'</li>';   
}
}
}

Here's an example of the out put from phpadmin.

Answer 1

I think you are misunderstanding how bind_result() works.

When mysqli_stmt_fetch() is called to fetch data, the MySQL client/server protocol places the data for the bound columns into the specified variables var1, ....

This means that the columns listed in your select statement get mapped to the variables listed in your call to bind_result()

For example, if you had SELECT first_name, last_name FROM names and you called:

$results->bind_result($first, $last);

When you loop over the results, for each row, the value from first_name will be available via the variable $first and the value from last_name will be available via $last

That means you could do:

while($results->fetch()){ //fetch values
    echo 'Hi '.$first.' '.$last;
}

Try your code like this:

        //fetch records
        // change your select to only select the columns you need and the order needs to be the same as bind_result below
        $results = $mysqli->prepare("SELECT up.file,up.title
                                     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 LIMIT ?, ?");




        //bind parameters for markers
        $results->bind_param("dd", $position, $item_per_page);
        $results->execute(); //Execute prepared Query
        $results->bind_result($title, $file); //bind variables to prepared statement
        // this binds the selected columns to the variable names given for use below
        // $title below is equivalent to $result['title] inside a for loop 



        while($results->fetch()){ //fetch values 
            echo '<li>'.$title.'-'.$file.'</li>';
        }