× This forum is read only. As of July 23, 2019, the UserSpice forums have been closed. To receive support, please join our Discord by clicking here. Thank you!

  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
mysql table relationship
#1
Lightbulb 
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.
  Reply
#2
(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.
  Reply
#3
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.
  Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)