How to select two tables and compare two columns and determine < or =

[jgreen]

I have a form and one db with 2 tables. Db tables are user-info and books.

user-info table - id name book

books table - id title copies

I'm populating the select box with the book titles with no problem.
What I want to do is a comparison - if copies == the number of times the book was selected, remove that book from the select box. If copies are < the number of times the book was selected keep the book title in the select box.

I can't seem to figure out how to compare two tables and see if the books selected and copies available are < or = each other.  Then how to not to display that book if it's ==. Here's what I have to populate the select box.

<select name="books">
<option value="">Select a Book</option>

<?php

mysqli_select_db($con,"books");
$result = mysqli_query($con,"SELECT * FROM books");

while($row = mysqli_fetch_array($result))
{
echo ("<option value='$row[title]'>$row[title]</option>");
}

?>
</select>

Edited August 6, 2015 by jgreen

[jgreen]

Trying to explain it a little better...  

 

The 2 tables have two columns that are the same - title.

What I need is find the number of titles in user-info table and compare it to the copies column in the books table. If user-info table column title is == to books table column copies, do not display that title in the select box.

For Example: This is an example to show what I'm trying to say.
user-info table column - title - the-title-of-a-book - there's 3 of this title. The books table column - copies - there's  3 copies left.  Since 3 == 3 the-title-of-a-book would not display in the select box.

I can't seem to figure this out.  Is there a better way to do this?  

 

 

I have a form and one db with 2 tables. Db tables are user-info and books.

user-info table - id name book

books table - id title copies

I'm populating the select box with the book titles with no problem.
What I want to do is a comparison - if copies == the number of times the book was selected, remove that book from the select box. If copies are < the number of times the book was selected keep the book title in the select box.

I can't seem to figure out how to compare two tables and see if the books selected and copies available are < or = each other.  Then how to not to display that book if it's ==. Here's what I have to populate the select box.

<select name="books">
<option value="">Select a Book</option>

<?php

mysqli_select_db($con,"books");
$result = mysqli_query($con,"SELECT * FROM books");

while($row = mysqli_fetch_array($result))
{
echo ("<option value='$row[title]'>$row[title]</option>");
}

?>
</select>

[Barand]

 

user-info table - id name book

books table - id title copies

 

 

The 2 tables have two columns that are the same - title.

 

I see no title column in user_info table ???

[jgreen]

Sorry I've been changing things to get this to work and above was a typo..... I apologize I'm struggling with this.  The tables and columns are 

 

user-info table - id username title

books table - id title copies

[jgreen]

I'm wondering if when the form inserts the data, if there's a way to update the copies.  Then when it reaches 0 it no longer displays in the select box.  

[Barand]

try this

SELECT b.id
  , b.title
  , b.copies
FROM books b
    LEFT JOIN user_info u USING (title)
GROUP BY b.id
HAVING COUNT(u.title) < b.copies

[jgreen]

I tried what you posted and it does nothing.  I put it after the insert and before the insert.  No errors.  It still displays the book title in the select even if copies selected are above the copies number.  

[Barand]

data

user_info                                books
+--------------+----------+-------+      +----+-------+--------+
| user_info_id | username | title |      | id | title | copies |
+--------------+----------+-------+      +----+-------+--------+
|            1 | qqq      | aaa   |      |  1 | aaa   |      2 |
|            2 | www      | aaa   |      |  2 | bbb   |      3 |
|            3 | eee      | bbb   |      |  3 | ccc   |      2 |
|            4 | fff      | ccc   |      |  4 | ddd   |      3 |
|            5 | ggg      | ccc   |      +----+-------+--------+
|            6 | hhh      | ddd   |
+--------------+----------+-------+

then

$result = mysqli_query($con,"SELECT b.id
                      , b.title
                      , b.copies
                    FROM books b
                        LEFT JOIN user_info u USING (title)
                    GROUP BY b.id
                    HAVING COUNT(u.title) < b.copies");

while($row = mysqli_fetch_array($result))
{
echo ("<option value='$row[title]'>$row[title]</option>");
}

generated HTML

<select name="books">
<option value="">Select a Book</option>
<option value="bbb">bbb</option>
<option value="ddd">ddd</option>
</select>

[jgreen]

Ok previous post you posted 

SELECT b.id
  , b.title
  , b.copies
FROM books b
    LEFT JOIN user_info u USING (title)
GROUP BY b.id
HAVING COUNT(u.title) < b.copies

I thought the sql statement above would go after the sql insert.   Now your example is at the select box level ...  I'll try that and see what happens.  Thanks

[Barand]

Of course it's for the select box - that's what you said your problem was

 

Trying to explain it a little better...  

 

 

What I need is find the number of titles in user-info table and compare it to the copies column in the books table. If user-info table column title is == to books table column copies, do not display that title in the select box.

...

I can't seem to figure this out.  Is there a better way to do this?  

[jgreen]

It works ... Thanks for the help.  

 

Yes you are correct. I did say that and then said this before you posted your code. 

 

I'm wondering if when the form inserts the data, if there's a way to update the copies.  Then when it reaches 0 it no longer displays in the select box.  

 

I thought you agreed and posted the select to work after the insert of the form data.  The only issue with the select is if 2 or more people submit at the same time it will still populate the db with the book selected no matter if there's no more copies.  But this is fine.  

 

Thanks again.  

Edited August 9, 2015 by jgreen