jgreen Posted August 6, 2015 Share Posted August 6, 2015 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> Link to comment https://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/ Share on other sites More sharing options...
jgreen Posted August 6, 2015 Author Share Posted August 6, 2015 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> Link to comment https://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/#findComment-1518127 Share on other sites More sharing options...
Barand Posted August 6, 2015 Share Posted August 6, 2015 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 ??? Link to comment https://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/#findComment-1518131 Share on other sites More sharing options...
jgreen Posted August 6, 2015 Author Share Posted August 6, 2015 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 Link to comment https://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/#findComment-1518143 Share on other sites More sharing options...
jgreen Posted August 6, 2015 Author Share Posted August 6, 2015 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. Link to comment https://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/#findComment-1518144 Share on other sites More sharing options...
Barand Posted August 6, 2015 Share Posted August 6, 2015 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 Link to comment https://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/#findComment-1518147 Share on other sites More sharing options...
jgreen Posted August 9, 2015 Author Share Posted August 9, 2015 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. Link to comment https://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/#findComment-1518344 Share on other sites More sharing options...
Barand Posted August 9, 2015 Share Posted August 9, 2015 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> Link to comment https://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/#findComment-1518346 Share on other sites More sharing options...
jgreen Posted August 9, 2015 Author Share Posted August 9, 2015 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 Link to comment https://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/#findComment-1518366 Share on other sites More sharing options...
Barand Posted August 9, 2015 Share Posted August 9, 2015 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? Link to comment https://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/#findComment-1518367 Share on other sites More sharing options...
jgreen Posted August 9, 2015 Author Share Posted August 9, 2015 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. Link to comment https://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/#findComment-1518373 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.