I have two tables in mysql, one table holds pics been snapped from webcam with image descriptions and another table which holds the user's registration details.
The image below will explain better.
This is the photos table with a track_id of the user
This is the user registration table with a unique id
On the the photos table the user details appeared twice with different description of the image uploaded
here is the result of the fetched details from both tables
My objective is to make the pictures appear side by side but when I fetched from database it was duplicating the information due to the fact that the user appeared twice on the photos table. And when I used group by it only displayed one picture.
Here is my source code
<?php require_once('queries.php'); ?>
<?php include("header.php"); ?>
<?php include("head.php"); ?>
<?php include("sidenavs.php"); ?>
<?php include("topnavs.php"); ?>
<div class="wrapper">
<div class="content">
<div class="container-fluid">
<div class="row">
<div class="col-lg-12 col-md-12">
<h4 class="form-signin-heading">Users Details.</h4>
<hr />
<a href="add_form" class="btn btn-info"> <span class="glyphicon glyphicon-pencil"></span> Add User</a>
<hr />
<div class="content-loader">
<table cellspacing="0" width="100%" id="example" class="table display table-striped table-hover table-responsive">
<thead>
<tr>
<th>#ID</th>
<th>Name</th>
<th>dob</th>
<th>gender</th>
<th>bvn</th>
<th>business name</th>
<th>contact address</th>
<th>Town/City</th>
<th>lga</th>
<th>State</th>
<th>phone 1</th>
<th>phone 2</th>
<th>email</th>
<th>products & services rendered</th>
<th>sub society name</th>
<th>position</th>
<th>mem id no</th>
<th>next kin name</th>
<th>relationship</th>
<th>phone of next kin</th>
<th>payment status</th>
<th>photo</th>
<th>signature</th>
<th>date registered</th>
<th></th>
</tr>
</thead>
<tbody>
<?php
require_once 'db/dbconfig.php';
$i =1;
$stmt = $db_con->prepare("SELECT * FROM users ORDER BY id DESC");
$stmt->execute();
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
{
$idm = $row['id'];
$stmts = $db_con->prepare("SELECT * FROM fotos where track_id ='$idm' group by $idm");
$stmts->execute();
$rows=$stmts->fetch(PDO::FETCH_ASSOC);
?>
<tr>
<td><?php echo $i; $i++;?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['dob'];?></td>
<td><?php echo $row['gender'];?></td>
<td><?php echo $row['bvn'];?></td>
<td><?php echo $row['business_name'];?></td>
<td><?php echo $row['contact_addr'];?></td>
<td><?php echo $row['Town_City'];?></td>
<td><?php echo $row['lga'];?></td>
<td><?php echo $row['State'];?></td>
<td><?php echo $row['phone_1']; ?></td>
<td><?php echo $row['phone_2']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['products_services_rendered'];?></td>
<td><?php echo $row['sub_society_name']; ?></td>
<td><?php echo $row['position']; ?></td>
<td><?php echo $row['mem_id_no'];?></td>
<td><?php echo $row['next_kin_name'];?></td>
<td><?php echo $row['relationship'];?></td>
<td><?php echo $row['phone_of_next_kin'];?></td>
<td><?php if($row['payment_status'] == 'Paid'){echo '<span class="alert-success" style="padding:3px; ">Paid</span>';}else{echo '<span class="alert-warning" style="padding:3px; ">Unpaid</span>';}?></td>
<td><img src="fotos/<?php if($rows['des'] == 'Photo'){echo $rows['id_foto'];}?>" width="50" height="50"></td>
<td><img src="fotos/<?php if($rows['des'] == 'Signature'){echo $rows['id_foto'];} ?>" width="50" height="50"></td>
<td><?php echo $row['date_registered'];?></td>
<td class="td-actions text-right">
<!--<a id="<?php echo $row['id']; ?>" class="edit-link btn btn-primary btn-simple btn-xs" rel="tooltip" href="#" title="Edit User"><i class="material-icons">edit</i></a>-->
<a id="<?php echo $row['id']; ?>" class="delete-link btn-danger btn-simple btn-xs" rel="tooltip" href="#" title="Delete">
<span class="glyphicon glyphicon-trash"></span>
</a>
</td>
</tr>
<?php
}
?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
<?php include("footer2.php"); ?>