+- UserSpice (https://userspice.com/forums)
+-- Forum: General (https://userspice.com/forums/forumdisplay.php?fid=20)
+--- Forum: New to UserSpice? (https://userspice.com/forums/forumdisplay.php?fid=22)
+--- Thread: mysql table relationship (/showthread.php?tid=1448)
mysql table relationship - Nuelnike - 04-20-2019
Hello Everyone,
am nuelnike and am very very new to userspice actually I stumbled on it on my quest for a secured, flexible, and easy php framework. but I really want to know how MySQL tables are connected using user spice DB function.
example: I have a table named projects, which is related to table 'categories' by 'id' and also related to table 'users' by userid so am wondering how possible it is to connect the tables all together so that when I list all data in table 'projects' all projects displayed will have a category name and an ownername attached to it.
RE: mysql table relationship - mudmin - 04-20-2019
(04-20-2019, 10:36 AM)Nuelnike Wrote: Hello Everyone,
am nuelnike and am very very new to userspice actually I stumbled on it on my quest for a secured, flexible, and easy php framework. but I really want to know how MySQL tables are connected using user spice DB function.
example: I have a table named projects, which is related to table 'categories' by 'id' and also related to table 'users' by userid so am wondering how possible it is to connect the tables all together so that when I list all data in table 'projects' all projects displayed will have a category name and an ownername attached to it.
We basically do the same thing...connect ids usually on another table. I think the best way to see this in actions is to look at the users table (id) and the permissions table(id) and then go look at user_permission_matches and you will see it is just a 3rd table that links the ids of the first two. As you add/remove permissions (and level) those tables are modified.
RE: mysql table relationship - Nuelnike - 04-20-2019
This is a sample code of what I mean
<?php
$res = mysqli_query($conn, "SELECT * FROM project_info ORDER BY id DESC $limit");
while ($row = mysqli_fetch_array($res)) {
$pid = $row['id'];
$projectname = $row['project_name'];
$category = $row['category'];
$ownerid = $row['owner'];
$stmt = $db->prepare("SELECT * FROM category WHERE id='$category'");
$stmt->execute();
$row = $stmt->fetch();
$categoryname = $row ['title'];
$stmt = $db->prepare("SELECT * FROM user_info WHERE user_id='$ownerid'");
$stmt->execute();
$row = $stmt->fetch();
$ownername = $row ['full_name'];
?>
<p>
Project name : <?php echo $projectname; ?>
Project category : <?php echo $categoryname; ?>
Project owner : <?php echo $ownername; ?>
</p>
<?php } ?>
Now the above will display projects together with their respective category names and owner names.
So what I need is how to actualize the above using userspice DB function.