Get values of mySQL rows in PHP and echo multiple values of certain rows

Question

the title is a little vague on what I want to do so I'll do the explaining here. I have two tables that I get information from, the Book table and Inventory table. I get the Book Title, Author Name and ISBN from Book table, and I concatenate the Quantity and Library Location from Inventory into one. What I want is for the information to look like

Title of Book
By Author1 Name and Author2 Name
ISBN: 740-fojsd99
(0) Copies available at Location2 
(3) Copies available at Location5

when I get the echo the information in php it only gets the first row of the Inventory table and echo likes this

22 11 63
By Stephen King
ISBN: 9788401344106
(0) Copies at the location of Sylvania Public Library

It should look like

22 11 63
By Stephen King
ISBN: 9788401344106
(0) Copies at the location of Sylvania Public Library
(2) Copies at the location of Toledo Public Library
(1) Copies at the location of Carson Library

I tried exploding, foreach and using two separate queries to get what I wanted to show but I am a bit of a noob with PHP and still learning how it works. My php code is as follows

$sql = "SELECT Book.Book_Title, 
Book.Author_Name, 
Book.Book_ISBN
FROM Book
GROUP BY Book_Title";


$result = mysqli_query($conn,$sql);


while($row = mysqli_fetch_assoc($result))
{   
    //var_dump($row);
    //had var_dump($result->num_rows); here
    echo "<b><u>". $row["Book_Title"] ."</u></b><br>";
    echo "By ". $row["Author_Name"]. "<br>";
    echo "ISBN: ". $row["Book_ISBN"] . "<br><br>";

    $ISBN = $row['Book_ISBN'];

    $sql2 = "SELECT CONCAT('<DD>(',Inventory.Quantity, ') Copies at the location of ', Inventory.Library_Location,'</DD>') as nCopies
    FROM Inventory
    JOIN Book
    ON Inventory.Book_Id=Book.Book_Id
    WHERE $ISBN = Book.Book_ISBN";

       $result2 = mysqli_query($conn,$sql2);

    while ($row = mysqli_fetch_assoc($result2)) 
    {
                echo "" . $row["numOfCopies"] . "<br>";
    var_dump($row);
        }

    var_dump($row);

}

Doing so in my db

SELECT Book.Book_Title, 
Book.Author_Name, 
Book.Book_ISBN
FROM Book
GROUP BY Book_Title

Gave me the output

+-----------------------------------------+-------------------------------------------+---------------+
| Book_Title                              | Author_Name                               | Book_ISBN     |
+-----------------------------------------+-------------------------------------------+---------------+
| 22 11 63                                | Stephen King                              | 9788401344106 |
| A Good Marriage                         | Stephen King                              | 9781401104428 |
| Bag of Bones                            | Stephen King                              | 9780671024239 |
| Carrie                                  | Stephen King                              | 9780307743664 |
| Cell                                    | Stephen King                              | 9781416424419 |
| Christine                               | Stephen King                              | 9780441160447 |
| Cujo                                    | Stephen King                              | 9780441161342 |
| Dark Souls: Design Works                | From Software                             | 9781926778891 |
| Desperation                             | Stephen King                              | 9781101137994 |
| Doctor Sleep                            | Stephen King                              | 9781441698844 |
| Dragon Age Inquisition: Official Guide  | David Knight                              | 9780804162944 |
| Duma Key                                | Stephen King                              | 9788401338090 |
| Everythings Eventual                    | Stephen King                              | 9780743447344 |
| Hearts in Atlantis                      | Stephen King                              | 9780671024246 |
| Heir to the Jedi: Star Wars             | Kevin Hearne                              | 9780344444848 |
| Insomnia                                | Stephen King                              | 9781101138007 |
| IT                                      | Stephen King                              | 9780441169418 |
| Joyland                                 | Stephen King, Hannes Riffel               | 9783641147074 |
| Mile 81                                 | Stephen King                              | 9781441664604 |
| Mr. Mercedes                            | Stephen King                              | 9781476744474 |
| Pet Sematary                            | Stephen King                              | 9780743412278 |
| Prince Caspian                          | C.S. Lewis, Pauline Baynes                | 9780064404003 |
| process control 221 Success Secrets     | David Knight                              | 9781488844672 |
| Secret Window                           | Stephen King                              | 9780441213469 |
| Skeleton Crew                           | Stephen King                              | 9780441168610 |
| Star Wars DarkSaber                     | Kevin J. Anderson                         | 9780307796417 |
| Star Wars I, Jedi                       | Michael A. Stackpole                      | 9780443478737 |
| Star Wars Mad Libs                      | Roger Price, Leonard Stern                | 9780843132717 |
| Star Wars Red Harvest                   | Joe Schreiber                             | 9780344418490 |
| Star Wars The Essential Atlas           | Jason Fry, Daniel Wallace                 | 9780344477644 |
| Star Wars: Choices of One               | Timothy Zahn                              | 9780344411263 |
| Star Wars: Dark Empire Trilogy          | Tom Veitch, Jim Baikie, Cam Kennedy       | 9781302466434 |
| Star Wars: Jedi Academy                 | Jeffery Brown                             | 9780444404178 |
| Star Wars: Rebel Force: Hostage         | Alex Wheeler                              | 9781484720226 |
| Star Wars: Riptide                      | Paul S. Kemp                              | 9780344422467 |
| Star Wars: Rise and Fall of Darth Vader | Ryder Windham                             | 9781484717874 |
| Tarkin: Star Wars                       | James Luceno                              | 9780344411422 |
| The Dead Zone                           | Stephen King                              | 9780441144747 |
| The Horse and His Boy                   | C.S. Lewis, Pauline Baynes                | 9780064471060 |
| The Last Battle                         | C.S. Lewis, Pauline Baynes, David Wiesner | 9780064404034 |
| The Legend of Zelda: Hyrule Historia    | Patrick Thorpe                            | 9781616440417 |
| The Lion, the Witch and the Wardrobe    | C.S. Lewis, Pauline Baynes                | 9780064471046 |
| The Magicians Nephew                    | C.S. Lewis, Pauline Baynes                | 9780064471107 |
| The Mist                                | Stephen King                              | 9780441223296 |
| The Running Man                         | Stephen King, Richard Bachman             | 9780441197962 |
| The Shining                             | Stephen King                              | 9780344806789 |
| The Stand                               | Stephen King                              | 9780307743688 |
| The Tommyknockers                       | Stephen King                              | 9780441146600 |
| Thinner                                 | Stephen King, Richard Bachman             | 9780441161344 |
| Under the Dome                          | Stephen King                              | 9781476734474 |
+-----------------------------------------+-------------------------------------------+---------------+

and doing

SELECT CONCAT('<DD>(',Inventory.Quantity, ') Copies at the location of ', Inventory.Library_Location,'</DD>') as nCopies
            FROM Inventory
        JOIN Book
        ON Inventory.Book_Id=Book.Book_Id

(had to remove GROUP BY Book_Title because it said Column 'Book_Id' in group statement is ambiguous) gave me the results

+----------------------------------------------------------------+
| nCopies                                                        |
+----------------------------------------------------------------+
| <DD>(0) Copies at the location of Sylvania Public Library</DD> |
| <DD>(1) Copies at the location of Carson Library</DD>          |
| <DD>(2) Copies at the location of Commuter Public Library</DD> |
| <DD>(0) Copies at the location of Sylvania Public Library</DD> |
| <DD>(2) Copies at the location of Toledo Public Library</DD>   |
| <DD>(1) Copies at the location of Carson Library</DD>          |
| <DD>(1) Copies at the location of Carson Library</DD>          |
| <DD>(2) Copies at the location of Commuter Public Library</DD> |
| <DD>(2) Copies at the location of Toledo Public Library</DD>   |
| <DD>(2) Copies at the location of Sylvania Public Library</DD> |
+----------------------------------------------------------------+

Edit: Last Edit the while loops worked and results came back as follows

22 11 63
By Stephen King
ISBN: 9788401344106
(0) Copies at the location of Sylvania Public Library
(2) Copies at the location of Toledo Public Library
(1) Copies at the location of Carson Library
A Good Marriage
By Stephen King
ISBN: 9781401104428
(1) Copies at the location of Carson Library
(2) Copies at the location of Commuter Public Library
Bag of Bones
By Stephen King
ISBN: 9780671024239
(2) Copies at the location of Toledo Public Library
(2) Copies at the location of Sylvania Public Library
Cell
By Stephen King
ISBN: 9781416424419
(0) Copies at the location of Sylvania Public Library
(1) Copies at the location of Carson Library
(2) Copies at the location of Commuter Public Library

Thank you @nomistic for the help, appreciate it!

could you var_dump($result->num_rows) ? Are you sure there is more than one? — sitilge
– sitilge, Commented Apr 26, 2015 at 21:14
@sitilge when I var_dump($result->num_rows) it displayed int(4) after the each echo. — Jaybro90
– Jaybro90, Commented Apr 26, 2015 at 21:18
The html syntax is wrong, see the first <b> and <u>. Could you var_dump($row) and see if the resutlts are correct there? — sitilge
– sitilge, Commented Apr 26, 2015 at 21:28
array(4) { ["Book_Title"]=> string(8) "22 11 63" ["Author_Name"]=> string(12) "Stephen King" ["Book_ISBN"]=> string(13) "9788401344106" ["numOfCopies"]=> string(62) " (0) Copies at the location of Sylvania Public Library " } Not sure if this is what you wanted but I replaced the $row["Book_Title"] with the var_dump($row) and the above is what I got. — Jaybro90
– Jaybro90, Commented Apr 26, 2015 at 21:32
Okay, so it's displaying an array of 4 items, which is the Book title, Author Name, ISBN and the Concat. So its not actually get each row in the Inventory table, its just getting 1 row associated to the Book_Title. — Jaybro90
– Jaybro90, Commented Apr 26, 2015 at 21:33

nomistic · Accepted Answer · 2015-04-26 22:45:44Z

2

You could try something like this. Basically grabbing a from the results you got for a specific book and then looping through the locations should work. (I know it's a little clunky but it should do the job). The problem you are having above is because you are only kicking out the first row; PHP doesn't know to loop through a sub-section of your initial query unless you tell it to.

while($row = mysqli_fetch_assoc($result))
    {   
        //had var_dump($row); here
        //had var_dump($result->num_rows); here
        echo "<b><u>". $row["Book_Title"] ."</u></b><br>";
        echo "By ". $row["Author_Name"]. "<br>";
        echo "ISBN: ". $row["Book_ISBN"] . "<br>";


        $ISBN = $row['Book_ISBN'];


        $sql2 = "SELECT CONCAT('<DD>(',Inventory.Quantity, ') Copies at the location of ', Inventory.Library_Location,'</DD>') as numOfCopies
        FROM Book
        JOIN Inventory
        ON Book.Book_Id=Inventory.Book_Id 
        WHERE Book.Book_ISBN = '$ISBN'
        GROUP BY Book_Title";
        $result2 = mysqli_query($conn,$sql2);

        while ($row = mysqli_fetch_assoc($result2)) {
            echo "" . $row["numOfCopies"] . "<br>";

        }

    }

Note, you could also clean up your first query to only bring forward the results that you need for the first round.

Edit: just enclosed $ISBN in single quotes, because I see you are treating it as a string. (Though I have to say that's a very odd ISBN in your example; typically these are numeric, and often stored with hyphens, but not alphanumeric)

edited Apr 26, 2015 at 22:45

answered Apr 26, 2015 at 22:15

nomistic

2,9624 gold badges22 silver badges37 bronze badges

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

8 Comments

Jaybro90 Over a year ago

I edited my code with what you gave and tried it out. For some reason it is never actually going into the second while loop. I put var_dump($row) both inside the second while and outside after it and I get back 'NULL' from only the var_dump($row) outside of the second while loop. Also in your code the ; is missing after the $ISBN = $row['Book_ISBN'].

nomistic Over a year ago

good catch on the semicolon, fixed. Hmmm, what results are you getting when you run your query directly in your database? try both queries.

Jaybro90 Over a year ago

I edited my code to show what I got from using just the queries directly in my db. Had to remove the GROUP BY because it was ambiguous.

nomistic Over a year ago

Are the results on the second query narrowed to a specific ISBN? They don't appear to be. Just give the results for just one title. What we need to know is how many locations are coming up for each title. I think you over-edited your original code which is causing some confusion.

Jaybro90 Over a year ago

Thank you, edited the code to show that it works and had to edit the sql2 also to get it right. Now it works, thank you and accepted it as an answer and upvote

|

Collectives™ on Stack Overflow

Get values of mySQL rows in PHP and echo multiple values of certain rows

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related