5 posts by 2 authors in: Forums > CMS Builder
Last Post: November 13   (RSS)

Hi All,

I know this should be relatively simple, but I can’t seem to get to an answer that works.

I’m using Report Builder with the following query:

SELECT title as 'Title',
genre as 'Genre',
location as 'Shelf Location'
FROM `<?php echo $TABLE_PREFIX ?>books`
WHERE hidden = 0 OR hidden = 1

The problem is that the ‘location’ field in the ‘books’ table gets it’s values from a field called ‘shelf_location’ in a table called ‘book_location’, and the current query returns record numbers and not shelf location text values for ‘location’.

Any thoughts?

Thanks,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!

Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Hi Jerry,

When you need to pull reference data from other tables like this it requires the use of "joins". For this purpose, I'll suggest using a LEFT JOIN, which would look something like this:

SELECT b.title as 'Title',
b.genre as 'Genre',
bl.shelf_location as 'Shelf Location'
FROM `<?php echo $TABLE_PREFIX ?>books` b
LEFT JOIN `<?php echo $TABLE_PREFIX ?>book_location` bl ON b.location = bl.num
WHERE b.hidden = 0 OR b.hidden = 1

I've aliased the "books" table as "b" and the "books_location" table as "bl" and prepended these aliases to the column names (e.g. "b.title"); when you perform a join, it makes all columns from both tables available to the query. If the tables have any of the same column names the query won't know which you refer to, so it needs to be made explicit like this. If a column only exists in one table it can be left as-is, but to be safe I've done this for every column name. (It's also possible to use the full table name instead of an alias, but that can get cumbersome)

Here's a page with a good overview of different joins and how they work: https://www.guru99.com/joins.html (Note that an INNER join may also have worked here; they function similarly to LEFT joins but with some difference in how they return data)

The above should probably do the trick, but let me know if you run into any issues or have any further questions!

Thanks,

Daniel
PHP Programmer
interactivetools.com

Hey Jerry,

It looks like you were most of the way there - the main issue is that a MySQL query only needs (and should only have) one "FROM" statement. You can simply continue to add more JOIN statements underneath, like this:

SELECT b.title as 'Title',
b.genre as 'Genre',
bl.shelf_location as 'Shelf Location'
gc.genre_names as 'Genre Names'
FROM `<?php echo $TABLE_PREFIX ?>books` b
LEFT JOIN `<?php echo $TABLE_PREFIX ?>book_location` bl ON b.location = bl.num
LEFT JOIN `<?php echo $TABLE_PREFIX ?>genre_categories` gc ON b.genre = gc.num
WHERE b.hidden = 0 OR b.hidden = 1

I also cleaned up a few of the column names and table aliases based on what I think is correct, but you'll need to confirm these still.

  • gc.genre_names should be the column in the "genre_categories" table that contains the genre text label
  • b.genre should be the column in the "books" table that contains the genre num

Let me know if this does the trick, or if you have any more questions!

Thanks,

Daniel
PHP Programmer
interactivetools.com

Thanks Daniel,

I added a missed comma after 'bl.shelf_location as 'Shelf Location',' and removed the 'b.genre as 'Genre',' line since it's now superfluous, and all works as planned.

Here's my final working code:

SELECT b.title as 'Title',
bl.shelf_location as 'Shelf Location',
gc.genre_names as 'Genre Names'
FROM `<?php echo $TABLE_PREFIX ?>books` b
LEFT JOIN `<?php echo $TABLE_PREFIX ?>book_location` bl ON b.location = bl.num
LEFT JOIN `<?php echo $TABLE_PREFIX ?>genre_categories` gc ON b.genre = gc.num
WHERE b.hidden = 0 OR b.hidden = 1

Appreciate the help (as always).

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!

Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php