Report Builder and displaying labels

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

By gkornbluth - November 12, 2019

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

By gkornbluth - November 12, 2019 - edited: November 12, 2019

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

By daniel - November 12, 2019

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
Technical Lead
interactivetools.com

By gkornbluth - November 13, 2019

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