How to call data from another table inside of a $row

PHP
1

Evening All

I’m a bit stuck, i’m looking to call data (the company name) from the users table inside of a row. Currently the table in question is the file table which lists all of the details from the a form however i’m looking to add the company name inside the row. I’m trying to avoid using SQL Joins as reading up they don’t allow me to have certain features which i currently use in the table. Here is a sample of the current code.

Please can you advise on the best way to go about this?


 $sql = "SELECT * FROM mytable 
WHERE stage = 'Pending";
   if($result = mysqli_query($con, $sql)){
   if(mysqli_num_rows($result) > 0){
      echo "<table id='datatable' class='text-center table table-hover table-sm mb-0'>";
      echo "<thead class='thead-dark'>";
      echo "<tr>";
      echo "<th>Company (From another table)</th>";
      echo "<th>Comments</th>";
      echo "<th>Stage</th>";
      echo "<th>Date Submitted</th>";
      echo "<th>Orig File Name</th>";
      echo "<th>Update</th>";
    echo "</tr>";
  echo "</thead>";
  echo "<tbody>";
  while($row = mysqli_fetch_array($result)){
    echo "<tr>";
    echo "<td>" . $row['#'] . "</td>"; //this is the row i'm looking to target
    echo "<td>" . $row['extra_comments'] . "</td>";
    echo "<td>" . $row['stage'] . "</td>";
    echo "<td>" . $row['dt_created'] . "</td>";
    echo "<td>" . $row['file_name'] . "</td>";
    echo "<td>";
    echo "<a href='updatefilereq.php?id=". $row['id'] ."' title='Update Record' data-toggle='tooltip'><span 
          class='fas fa-pencil-alt'></span></a>";
    echo "</td>";

Thanks in advance all.

2

What might those be? I don’t see anything special in the code you posted.

1 Like
3

I’ve purposely left some columns out but the columns I’m talking about have Hrefs which take you to a read page.

4

What does that have to do with an SQL JOIN?

5

Looking at SQL Joins on various sites, they appear to just output a table. For instance on W3 Schools, they give this example:-

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

But in their code, it doesn’t display all of the rows as I have them which suggested to me that based on the join, it naturally outputs a standard table based on the Join alone? I could be wrong though.

6

When you SELECT more rows you can display more rows

7

SQL Joins don’t output anything, they just link multiple tables together. For example, you might use an inner join to retrieve a customer name from the customer-details table when your transactions table (correctly) only stores the customer id, so that your output can include both. If your database is properly normalised, they’re pretty much essential unless you want to do things in a more complex fashion.

I’m a bit confused in your code, you have “Company (from another table)” in your output header, but then you say "this is the row… " in each of the lines. If the company is in the header, shouldn’t it also be a parameter in the query to limit results to that company?

If so, one way would be to use a “first” flag to be able to use the data in the header

first = true
query = "select * from table where company = 45 inner join companydetails on id = companydetails.id"
for each result {
  if first == true {
    output header
    first = false
    }
  output result
  }

(There are several different types of join, several of which I’ve never needed).

1 Like
8

Thanks droopsnoot. That is exactly the description I was looking for :+1:t2: I’ll have a play tonight.

9

Side note: if I wanted a notification system when a record had been inserted/updated into the db tb what is the best way to go about this?

I get that I would need JS to fire the notification but I’m not sure on how to code the other part which is for PHP to check if the record has been inserted or updated and to then trigger the JS notification accordingly.

Unless PHP wouldn’t even come into it and AJAX would handle it all? :tired_face:. So confused.

Please can you advise as after a lot of googling I can’t seem to find the right answer.

Thanks in advance.

10

The only way I could think of would be to have some JS check something periodically on the server. Maybe you’d have a “last updated” date somewhere that can easily be accessed, and if it is different than the previous date, then re-read the data and update the display.

Probably a question for others, and not in PHP - while you’ll probably call some PHP to retrieve and re-display the data, that’s not the thing that will trigger the update, IMO.

1 Like
11

No worries, thanks anyways.

I thought it would have been the PHP that would have handled the check to see if the record had been updated. Of which i could then create a js script that says something like if X = true, display notification else display error notification.

12

Well, it probably will be PHP that does the check, but something has to trigger it. Keep in mind that by the time your page is drawn, PHP has pretty much finished with things. So while you could create a cron job to check periodically, I’m not sure how that could be “pushed” back to your page. So your page has to initiate the check.

I have to say it may well be possible that your cron-induced PHP could push back to the web page, but I don’t know how you’d do that.

1 Like
13

Nope. Not possible.

1 Like
14

Thanks gents.

I’ll post under JS to see if they can confirm how best To handle it. :+1:t2:

15

You could achieve this using web sockets.

I’ve never used web sockets in php only c# but this looks promising.

16

Isn’t it just a case of something like:-

if($db->query($queryString)){ // Just did an insert or update...
    // Do notification!
}

What’s the need for any cron type check?
PHP knows when its just done a successful query, so when it has done, do what you need it to do.
Or am I missing something?

17

Notifications would be displayed without the page refreshing which requires either long polling or a web socket. You could also use a service provider like PubNub but that might be an over kill for something this small.

18

I presumed they meant any insert or update, not just ones done by that user. So you’d have a status screen that would update any time a change is made. Otherwise, as you say, if it’s only concerned with that users updates, it will know.

19

That’s what’s unclear, who is to be notified.
But either way, and however the notification is sent, it doesn’t need a cron check to trigger it, just the script having completed an insert/update can trigger it.

20

But what makes the status screen (if that’s what the OP intends) know that the update has happened, so that it can display the updated information?