Fetch data from two SQL tables with INNER JOIN, display HTML table

Question

I am trying to display a table which will print out a list of themes I am creating for a forum software (there are several dozen), and display their version number from another table, by using an INNER JOIN statement.

Here's the HTML table I want to print:

Theme Name     Version Number
-------------------------------
Elegance       1.7.0
Smarty         1.7.4
Aria           1.8.1
etc etc

The themes and their IDs are stored in xf_style table:

--------------------------------
style_id   |  title
--------------------------------
1          |  Elegance
2          |  Smarty
3          |  Aria

The theme version numbers are stored in the options table xf_style_property. There's hundreds of options in the backend system, each with an option ID (style_property_id). The "Theme Version" option I'm looking for has ID of "5145".

xf_style_property table

---------------------------------------------------------------------
style_id  |  style_property_id  |  property_label  |  property_value
---------------------------------------------------------------------
1         |  5144               |  Logo Size       |  110px
2         |  5144               |  Logo Size       |  145px
3         |  5144               |  Logo Size       |  120px
1         |  5145               |  Theme Version   |  1.7.0
2         |  5145               |  Theme Version   |  1.7.4
3         |  5145               |  Theme Version   |  1.8.1

There are many repeating values in this table. Basically I want to fetch the property_value for each theme where the style_property_id equals 5145, and inner join this with the xf_style table.

My full script:

<?php
$servername = "localhost";
$username = "***";
$password = "***";
$dbname = "***";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";

$sql = "SELECT xf_style.title, xf_style_property.property_value FROM     xf_style_property WHERE property_definition_id = 5145, INNER JOIN xf_style ON xf_style_property.style_id=xf_style.style_id";
$result = $conn->query($sql) or die($conn->error);
?>

<table border="2" style= "background-color: #84ed86; color: #761a9b; margin: 0 auto;" >
  <thead>
    <tr>
      <th>Theme Name</th>
      <th>Theme Version</th>
    </tr>
  </thead>
  <tbody>
    <?php
        while ($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row['title'] . "</td><td>" . $row['property_value'] . "</td></tr>";
        }
      ?>
  </tbody>
</table>

I've been trying a dozen different tweaks including this guide: https://www.w3schools.com/sql/sql_join.asp and other guides here at SE and can't seem to make it work. Any help would be appreciated from a SQL newbie.

Disclaimer: the property_label column doesn't actually exist.. I only wrote it in for reader understanding. It's already known from another table which ID represents what option label.

user7794434 · Accepted Answer · 2018-05-18 21:40:02Z

1

The where conditions are after the join

This should fix it

$sql = "SELECT xf_style.title, xf_style_property.property_value FROM xf_style_property INNER JOIN xf_style ON xf_style_property.style_id=xf_style.style_id" WHERE property_definition_id = 5145,;

Otherwise if you want to avoid repeated themes (even if they ahve diferent propety value) you can use Group By

answered May 18, 2018 at 21:40

user7794434

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

1 Comment

Whitesky Over a year ago

Thank you this was perfect fix! Working great now

Shawn · Accepted Answer · 2018-05-18 21:54:47Z

1

Your query should simply be

SELECT xfs.title, xfsp.property_value 
FROM xf_style_property xfsp
INNER JOIN xf_style xfs ON xfsp.style_id = xfs.style_id
WHERE xfsp.style_property_id = 5145

answered May 18, 2018 at 21:54

Shawn

4,7861 gold badge22 silver badges29 bronze badges

1 Comment

Whitesky Over a year ago

Thank you for the input, the comment above solved but this helps me see the syntax difference for different approaches

Collectives™ on Stack Overflow

Fetch data from two SQL tables with INNER JOIN, display HTML table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related