2表MYSQL PHP查询和应用程序用PHP填充HTML网格显示

Question

Hello and thank you for viewing my question, I'll get into it.

I'm looking to pull a bunch of data stored in two different tables within a MySQL database, assign values from each row into arrays (if this is the best approach), then use this data to populate html grid elements (so that each row is used to populate one box in the grid).

This will end up as a grid preview for threads within a php-mysql webapp, a user will have threads displayed as 'preview items', which they may click to open the thread in full. So far this is how I'm approaching the task, do you have any tips or recommendations on how you would do the same?

idea_thread contains information about the threads, and thumb contains the images used for thumbnails and grid box side.

    $result = $stmt = $mysqli->query("SELECT i.idea_id, i.user_id, i.time, i.title, i.Brief, t.location, t.width, t.height, t.colour 
FROM idea_thread i, thumb t
    WHERE t.idea_id = i.idea_id 
        ORDER BY i.idea_id DESC LIMIT 150,5");  
while ($row = $stmt->fetch_array(MYSQLI_ASSOC)){

        $id = $row[0];
        $user = $row[1];
        $date = $row[2];
        $title = $row[3];
        $briefdescrip = $row[4];
        $photolocation = $row[5];
        $width = $row[6];
        $height = $row[7];
        $color = $row[8];
};

?>

Thank you for reading and for any tips you may offer.

For somereason this code however returns nothing but a white screen? Any thoughts as to why?

Edits applied as a result of suggestions, reflected in my code **

Answer 1

You are using both mysql (mysql_fetch_array) and mysqli and are you sure you want an outer join? A simple way to join is just specifying multiple tables with an identifier like you have (i and t) and then specifying where they are joined like so:

SELECT i.idea_id, i.user_id, i.time, i.title, i.Brief, t.location, t.width, t.height, t.colour FROM idea_thread i, thumb t WHERE t.idea_id = i.idea_id ORDER BY i.idea_id DESC

A left outer join will include all threads even without a correlated id in thumb which may be what you are after:

SELECT i.idea_id, i.user_id, i.time, i.title, i.Brief, t.location, t.width, t.height, t.colour FROM idea_thread i LEFT OUTER JOIN thumb t ON t.idea_id = i.idea_id ORDER BY i.idea_id DESC

Since you aren't providing any values, you don't need to use prepare and can just use query with mysqli.

Update: Noticed you had * in the SELECT as well. This will select all the columns then the ones you specified throwing off your query even more.

So for mysqli, you will want:

$stmt = $mysqli->query(...);
while ($row = $stmt->fetch_array(MYSQLI_NUM)) {

 ... logic here ...

}

I used MYSQLI_NUM in fetch_array since you are referencing the columns by number, you can also use MYSQLI_ASSOC: http://us2.php.net/manual/en/mysqli-result.fetch-array.php