Fetch data from different tables using while loop and if statement

Question

I am attempting to fetch data from my database using 8 different tables named:

inventory descriptorid typeid conditionid statusid locationid stationid users

I believe my query is working correctly the problem is using my $row variable. I am only getting the results from data in the inventory table. Can Someone explain how I can fetch my data from each table?

The code is below:

<?php
    // get the records from the database
    if ($result = $con->query("
    SELECT inventory.InventoryID, descriptorid.dename, typeid.tyname, 
           inventory.Serial, inventory.ServiceTag, inventory.CityTag, 
           conditioncid.conname, statusid.statusname, locationid.locname, 
           stationid.stationname, users.Fname, users.Lname, 
           inventory.PurchaseDate, inventory.POnumber 
    FROM inventory 
    LEFT JOIN descriptorid 
      ON inventory.Descriptor = descriptorid.dename 
    LEFT JOIN typeid 
      ON inventory.Type = typeid.tyname 
    LEFT JOIN conditioncid 
      ON inventory.ConditionC = conditioncid.conname 
    LEFT JOIN statusid 
      ON inventory.Status = statusid.statusname 
    LEFT JOIN locationid 
      ON inventory.Location = locationid.locname 
    LEFT JOIN stationid 
      ON inventory.Station = stationid.stationname 
    LEFT JOIN users 
      ON inventory.cuserFname = users.Fname 
     AND inventory.cuserLname = users.Lname "))
    {
    // display records if there are records to display
        if ($result->num_rows > 0)
        {
            // display records in a table
            echo "<table border id='myTable' class='tablesorter' >";

            // set table headers
            echo "<thead><th>ID</th><th>Descriptor</th><th>Type</th><th>Serial</th><th>ServiceTag</th><th>CityTag</th><th>Condition</th><th>Status</th><th>Location</th><th>Station</th><th>CurrentUser</th><th>Lastname</th><th>PurchaseDate</th><th>POnumber</th><th></th><th></th></thead>";


            echo "<tbody";

            while ($row = $result->fetch_object())
            {
                // dump whats returned
                // print_r($row);

                echo "<tr>";
                echo "<td>" . $row->InventoryID . "</td>";
                echo "<td>" . $row->Descriptor . "</td>";
                echo "<td>" . $row->Type . "</td>";
                echo "<td>" . $row->Serial . "</td>";
                echo "<td>" . $row->ServiceTag . "</td>";
                echo "<td>" . $row->CityTag . "</td>";
                echo "<td>" . $row->ConditionC . "</td>";
                echo "<td>" . $row->Status . "</td>";
                echo "<td>" . $row->Location . "</td>";
                echo "<td>" . $row->Station . "</td>";
                echo "<td>" . $row->cUserFname . "</td>";
                echo "<td>" . $row->cUserLname . "</td>";
                echo "<td>" . $row->PurchaseDate . "</td>";
                echo "<td>" . $row->POnumber . "</td>";
                echo "<td><a href='invrecords.php?InventoryID=" . $row->InventoryID . "'><img src='img/default/button_mini_ticket_edit.gif'></a></td>";
                echo '<td><a href="javascript:void(0);" onclick="confirmation(' . $row->InventoryID . ');"><img src="img/default/button_mini_delete.gif"></a>';
                echo "</tr>";

            }
            echo "</tbody>";
            echo "</table>";

hellcode · Accepted Answer · 2015-08-17 18:56:49Z

1

You should name your row vars as you select them (as you did it with the columns of the inventory table):

Wrong:

        echo "<td>" . $row->Descriptor . "</td>";
        echo "<td>" . $row->Type . "</td>";
        echo "<td>" . $row->ConditionC . "</td>";
        echo "<td>" . $row->Status . "</td>";
        echo "<td>" . $row->Location . "</td>";
        echo "<td>" . $row->Station . "</td>";
        echo "<td>" . $row->cUserFname . "</td>";
        echo "<td>" . $row->cUserLname . "</td>";

Use instead:

        echo "<td>" . $row->dename . "</td>";
        echo "<td>" . $row->tyname. "</td>";
        echo "<td>" . $row->conname. "</td>";
        echo "<td>" . $row->statusname. "</td>";
        echo "<td>" . $row->locname. "</td>";
        echo "<td>" . $row->stationname. "</td>";
        echo "<td>" . $row->Fname. "</td>";
        echo "<td>" . $row->Lname. "</td>";

edited Aug 17, 2015 at 18:56

answered Aug 17, 2015 at 18:47

hellcode

2,6881 gold badge19 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Drudge · Accepted Answer · 2015-08-18 14:06:53Z

1

Are you sure that you want use LEFT JOIN instead of INNER JOIN? That means that if you have table A and B you only take elements which are located in A but not necessarily in B. That means if there is not a matching partner for an element of the left table, you would have a row containg an element of table A in one column and null in the other column. This might be one reason for having a resultset only containing an element of the inventory table. As far as I understand you want a result containing the elements joined on equal attributes, so try to replace LEFT JOIN with INNER JOIN.

Image source: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

edited Aug 18, 2015 at 14:06

answered Aug 17, 2015 at 18:26

Drudge

5281 gold badge6 silver badges21 bronze badges

7 Comments

hellcode Over a year ago

That is not correct. LEFT JOIN means that it takes rows of table A even if there is no corresponding row in table B, but if there is, you get the result of table B, too (except you exclude them with "WHERE B.Key IS NULL" which is one line below in your excellent graph).

Drudge Over a year ago

Sorry for my misknowledge, as far as I understood it, it joins both tables based on what you have specified in the ON clause and then takes only the elements located on the right side. Does that mean that you have the full table A in the result?

hellcode Over a year ago

In this case with no WHERE clause: yes

Drudge Over a year ago

You're right, I recognized it on my own image now. Shame on me. But I think in his case an INNER JOIN would work, wouldn't it? A LEFT JOIN would produce a result only containing informatik from inventory if there is nothing on the right side, is it right? I'll update my answer.

alstonan25 Over a year ago

I am attempting to use an id that is in the inventory table to connect to the data in the other tables. I don't wish to keep data fetched from both tables just the ID from descriptorid,typeid, etc. How can I replace that ID in inventory to match the id and name of the table I am joining it to?

|

alstonan25 · Accepted Answer · 2015-08-18 12:16:42Z

0

Ok so the issue was instead of matching the third option (dename, tyname,etc.) on my secondary tables with the inventory I should've been matching it with the ID of that said table. Now when I query the third option with my row variable I can select any option from my secondary table (deid,tyid). It now outputs the desired results and hopefully I can create a drop down list using a similar query.Thanks for all the ideas.

answered Aug 18, 2015 at 12:16

alstonan25

239 bronze badges

Collectives™ on Stack Overflow

Fetch data from different tables using while loop and if statement

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related