How to display two or more array on same table-CodePudding

Hey guys I have one mysql table tick where i have some info like user id now to display that data I need to retrieve from user table name of the user based on that id

?php 


$mysql5 = mysqli_query($dbc, "SELECT * FROM tick where status='1' ORDER BY dt DESC LIMIT 6 ");
if($indtbl = mysqli_fetch_array($mysql5))
{

  $title = $indtbl['tickettitle']; 
  $company = $indtbl['companyname'];   
  $companyid =$indtbl['compid'];  
  $trackid = $indtbl['trackid']; 
  $assignto = $indtbl['assignto'];  
  $priority = $indtbl['priority']; 
  

}
?>

and user table query is based on first one

<?php
$findresults2323 = mysqli_query($dbc, "SELECT * FROM users WHERE id= '$assignto'");
if($rest = mysqli_fetch_array($findresults2323))
{

  $userimg = $rest['img']; 
  $fname1 = $rest['fname'];   
  $lname1 = $rest['lname'];  

}
?>

now the problem is when i am fetching the array

<thead>
                        <tr>
                          <th >Title</th>
                          <th>Company</th>
                          <th>Ticket ID</th>
                          <th>Assign To</th>
                          <th>Priority</th>
                          
                        </tr>
                      </thead>
                      <?php

while($rowten = mysqli_fetch_array($mysql5)) {


$retrive11 = mysqli_fetch_array($findresults23);

?>
<tr>
    <td><a href="readit.php?id=<?php echo $rowten["id"];?>"><?php echo $rowten["tickettitle"]; ?></td>
    <td><a href="readit.php?id=<?php echo $rowten["id"];?>"><?php echo $rowten["companyname"];?> <?php echo $rowten["compid"];?></td>
    <td><a href="readit.php?id=<?php echo $rowten["id"];?>"><?php echo $rowten["trackid"]; ?></td>
    <td><a href="readit.php?id=<?php echo $rowten["id"];?>"><?php echo $retrive11['fname'];?></td>
    <td><a href="readit.php?id=<?php echo $rowten["id"];?>"><?php echo $rowten["priority"]; ?></td>
</tr>
<?php

}


?>

i am getting the error

Notice: Trying to access array offset on value of type null  on line 459

line 459 is

<td><a href="readit.php?id=<?php echo $rowten["id"];?>"><?php echo $retrive11['fname'];?></td>

what I am doing wrong

CodePudding user response：

In order to use 2 queries, you need to run the second query inside of the loop so it retrieves the specific user record for the given result from the tick query.

while($rowten = mysqli_fetch_array($mysql5)) {
    $assignto = $rowten['assignto'];
    $findresults2323 = mysqli_query($dbc, "SELECT * FROM users WHERE id= '$assignto'");
    $retrive11 = mysqli_fetch_array($findresults23);

However, this is very inefficient as it requires a query on the user table for every result in the tick query. The proper way of doing this is to use a single query with a join.

SELECT tick.*, users.fname FROM tick LEFT JOIN users ON (tick.assignto=users.id) where tick.status='1' ORDER BY tick.dt DESC LIMIT 6

The result of this query will have all the data from both your queries in one result.