Search code examples
phpmysqlmysql-error-1064

Getting 3 random threads inside 3 random categories


Im making a forum site for my tafe assignment what Im trying to do is get 3 random categories from my site which is this:

$sqlcat = 'SELECT *
 FROM category
 ORDER BY RAND()
 LIMIT 4';
 $resultcat = mysqli_query($con, $sqlcat) or die(mysqli_error($con)); //run the query 
 $rowcat = mysqli_fetch_array($resultcat);

and to echo them out:(using bootstrap)

while ($rowcat = mysqli_fetch_array($resultcat))
{
<div class="panel panel-default">
<div class="panel-heading">
<h3 class="panel-title"><?php echo $rowcat['name'];?></h3>
</div>
<div class="panel-body">
}

now im trying to get 3 random threads (from each of the 3 random categories) to show under each of the 3 categories and I dont know how to write the sql to get that. Also here are the category and thread tables just in case:

1   categoryID  int(10) 
2   name        varchar(40)
3   description varchar(50)`

1   threadID    int(10) 
2   title       varchar(80)     
3   memberID    int(10) 
4   adminID     int(10) 
5   categoryID  int(10) 
6   content     text
7   dateTime    datetime    

please help!


Solution

  • Simple example

     // ...
     // getting categotires
     // ...
     <?php 
      while ($rowcat = mysqli_fetch_array($resultcat)):
    
       $category_id = $rowcat['categoryID'];
    
       $sql_threads = 'SELECT *
                       FROM `thread`
                       WHERE categoryID='.$category_id.' 
                       ORDER BY RAND()
                       LIMIT 3'; 
    
       $result_thread = mysqli_query($con, $sql_thread) or die(mysqli_error($con));   
     ?>
      <div class="panel panel-default">
      <div class="panel-heading">
      <h3 class="panel-title"><?php echo $rowcat['name'];?></h3>
         <?php  while ($row_thread = mysqli_fetch_array($result_thread)):?>
            <div><?php echo $row_thread['title']; ?></div>
         <?php endwhile;?>
      </div>
      <div class="panel-body">
    <?php endwhile; ?>