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

By gkornbluth - November 12 - edited: November 12

Hi Daniel,

That worked fine and I thought it would be a relatively simple matter to add the labels from a third table that gets it's values in a similar manner. (Ha...)

IE: The 'genre' field in the 'books' table gets it's values from the 'genre_names' field in the 'genre_categories' editor.

So far I tried a number of permutations based around code similar to this, but I keep getting MySql errors:

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

I had a look at the link you included and some other coding sites, but I keep getting more confused and I’m not sure where to go from here.

I could really use a bit more help...

Thanks,

Jerry

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

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