Getting labels to show

Question

I have 3 MySQL tables:

shelf
---------------
id
project_id
label


books
---------------
id
project_id
label


booksOnShelves
---------------
id
shelf_id
book_id
project_id
user_id

I need to display what books are on what shelf in a table format, something like:

History     Fiction
-------     --------
book 1      book 4
book 2      book 5 
book 3

So far I've got only this:

  SELECT 
        shelf.label,
        books.label
    FROM booksOnShelves
    LEFT JOIN shelf ON (shelf.id = booksOnShelves.shelf_id)
    LEFT JOIN books ON (books.id = booksOnShelves.book_id) 
    WHERE booksOnShelves.project_id = ".$_POST['pid']."
    AND booksOnShelves.user_id = ".$_POST['uid']."

Couple things... For some odd reason shelf label is not showing up, but if I remove all other parts in the query, like book labels, it works. Also, I can only display in one long column (via loop). How would I break it into separate coulmns, or if in one column, how do I show shelf label only once per group of books?

**Your code is vulnerable to SQL injection.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of [Bobby Tables](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). — eggyal, Jul 01 '12 at 23:25
Did you mean `shelf.id` or is that a typo for `shelf.label`? — Neil, Jul 01 '12 at 23:36

score 0 · Answer 1 · answered Jul 01 '12 at 23:46

0

SQL is just a (structured) query language, not a reporting language. As you're using PHP I assume you're going to output HTML, so you can create columns via e.g. floats or tables. In order to do your grouping, the easiest way is to read the query result into a nested array, so that the outer array keys could be the shelf labels and the inner array keys could be the book labels, and then output the array in a nested loop.

answered Jul 01 '12 at 23:46

Neil

54,642
8
60
72

Hmm... I think I understand what you mean, although it sound this sort of array manipulation is a bit above my skill level... – santa Jul 02 '12 at 01:55

Ivan Buttinoni · Accepted Answer · 2012-07-02T07:00:02.213

1st: escaping the input vars

2nd: remove LEFT in join, if you start from cross table leftjoin is nonsense (IMHO)

3rd: use GROUP_CONCAT to return a string of set of titles, comma separated, 1 row per shelf

$pid = mysql_real_escape_string( $_POST['pid'] );
$uid = mysql_real_escape_string( $_POST['uid'] );

$sql = "SELECT 
        shelf.label,
        GROUP_CONCAT( books.label separator ',') as titlelist
    FROM booksOnShelves
    JOIN shelf ON (shelf.id = booksOnShelves.shelf_id)
    JOIN books ON (books.id = booksOnShelves.book_id) 
    WHERE booksOnShelves.project_id = ".$pid."
    AND booksOnShelves.user_id = ".$uid."
group by booksOnShelves.shelf_id";

Of course your PHP has to parse/manage titlelist to fill the page

ADD

You can change the separator as your needs (eg: |) then

foreach($results as $row){

echo "<div class='float_left'><p class='bold'>".$row['label']."</p>\n";
// using '|' as separator less common then ',' in the titles
$array_of_titles = explode('|',$row['titlelist']);
foreach($array_of_titles as $title){
echo "<p>".$title."</p>\n";

}
echo "</div>\n";
}

I hypothesized $results is an associative array with all rows of your query.

Getting labels to show

2 Answers2