PHP MySQL stop loop at next available number

Question

i've got a database with the folowing info:

--------------
id | Book | Names
1  | 1    | Tom
2  | 8    | James
3  | 10   | Tom
4  | 2    | Tom
5  | 17   | James
6  | 2    | James
7  | 9    | James
8  | 7    | Tom
9  | 8    | Tom

This table shows books read by "Tom" and "James".

These are the requirements i need:

to show the next book not read. (eg. Tom's would be '3' and James's '1')
to skip book '1', '10' and '15' as these are no longer available. (so in James's case, the next book would be '3')
if it cannot be sequential, any random book not read will do as well.

here's what i did:

$sql = "Select * FROM books Group By names"; 
    $result = $conn->query($sql);


    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            $sql1 = "SELECT * FROM books WHERE names = '" . $row["names"]. "' ORDER BY book ASC"; 
            $result1 = $conn->query($sql1);
            if ($result1->num_rows > 0) {
                $newbook = '1';
                // output data of each row
                while($row1 = $result1->fetch_assoc()) {

                    if ($row1["book"] == $newbook) {
                        echo "Exist<br><br>";
                        $newbook = $newbook+ 1;
                    } else {
                        if ($row1["book"] == '1' || $row1["book"] == '10' || $row1["book"] == '17') {
                            $newbook= $newbook+ 1;

                        } else {
                            echo "Add".$newbook."<br><br>";
                            break;
                        }
                    }
                }
            }
        }
    }

This is how far i've got. All help appreciated. Thanks

you left out SELECT from the first sql string. what output do you get from that script? — flauntster
– flauntster, Commented Nov 9, 2017 at 5:17

Tim Biegeleisen · Accepted Answer · 2017-11-09 08:11:48Z

2

Off the top of my head, a calendar table approach might be a good solution to handle this problem completely in MySQL. First define a sequence table, containing the values from 1 to the highest book ID (I call this table seq_table). Then, to find the lowest book not read by a given user, a simple left join will do the trick:

SELECT MIN(t1.Book) AS next_book
FROM seq_table t1
LEFT JOIN books t2
    ON t1.Book = t2.Book AND
       t2.Names = 'Tom'
WHERE
    t2.Book IS NULL;

If you instead wanted to choose a random book not read by Tom, then we can use the following query:

SELECT t1.Book AS next_book
FROM seq_table t1
LEFT JOIN books t2
    ON t1.Book = t2.Book AND
       t2.Names = 'Tom'
WHERE
    t2.Book IS NULL
ORDER BY RAND()
LIMIT 1;

edited Nov 9, 2017 at 8:11

answered Nov 9, 2017 at 5:27

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

AndrewTsang Over a year ago

Random doesn't work though. it chooses all books read / not read. if you could amend it that would be great

Tim Biegeleisen Over a year ago

@AndrewTsang My second query absolutely returns a single random record. Perhaps your data has a problem.

am05mhz · Accepted Answer · 2017-11-09 06:07:22Z

0

you will need another table to store available books let say books table

---------------------------------------------
id | Book_Title             | Available
---------------------------------------------
1  | One million promises   | no
2  | Two towers             | yes
3  | Three musketeers       | yes
4  | 4th Avenue Cafe        | yes
5  | Famous Five            | yes
6  | Six million dollar man | yes
7  | Seven Stars            | yes
8  | Eighth Paladin         | yes
9  | Ninth Heaven           | yes

lets say that your read books table is named read_books, you can get the next book to read by Tom with this query:

select min(id) from books where Available='yes' and id not in (select book from read_books where names = 'Tom')

edited Nov 9, 2017 at 6:07

answered Nov 9, 2017 at 5:55

am05mhz

2,9052 gold badges26 silver badges37 bronze badges

1 Comment

AndrewTsang Over a year ago

the 'book' table is the read_books table you are referring to. i've got another table for available books which can be linked via book id

Collectives™ on Stack Overflow

PHP MySQL stop loop at next available number

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related