imkesaurus Posted September 28, 2015 Share Posted September 28, 2015 Hi there! I need some assistance. I'm a PHP noob and have been given a project to build a php 'calculator' for a client. Basically what needs to happen is the following - I have a table with predefined values, they are all numbers. The user will go to a form and add a number, this number then needs to be compared to the first column of the table after which it then needs to display only that row's values. As the numbers are predefined in the table the number the user enters then also needs to be rounded off to the nearest value found in the table. I've searched the internet and found snippets of code but can't quite understand how to put everything together. I understand how it should work, but just can't get there. I hope this all makes sense. If there is anyone who can help I would appreciate it a lot! Thanks Quote Link to comment https://forums.phpfreaks.com/topic/298340-return-single-row-based-on-comparison-of-two-values/ Share on other sites More sharing options...
Jacques1 Posted September 28, 2015 Share Posted September 28, 2015 (edited) I don't think anybody can help you based on this description. It's hard to even make sense of the problem when there's no concrete information whatsoever. What kind of “table” are you talking about? Is this an SQL question? What kind of “numbers” do you deal with? Integers? Floats? Something else? You should show a concrete example with concrete data so that we can actually understand what this is about. Also, what do you expect from us? Obviously you don't care about concrete code, so what's the goal of this thread? Edited September 28, 2015 by Jacques1 Quote Link to comment https://forums.phpfreaks.com/topic/298340-return-single-row-based-on-comparison-of-two-values/#findComment-1521686 Share on other sites More sharing options...
Barand Posted September 28, 2015 Share Posted September 28, 2015 (edited) Can you give us some sample data, an example of the what the user might enter and the expected result? [EDIT] Beaten to the post, again. Edited September 28, 2015 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/298340-return-single-row-based-on-comparison-of-two-values/#findComment-1521687 Share on other sites More sharing options...
imkesaurus Posted September 28, 2015 Author Share Posted September 28, 2015 (edited) Can you give us some sample data, an example of the what the user might enter and the expected result? [EDIT] Beaten to the post, again. I don't think anybody can help you based on this description. It's hard to even make sense of the problem when there's no concrete information whatsoever. What kind of “table” are you talking about? Is this an SQL question? What kind of “numbers” do you deal with? Integers? Floats? Something else? You should show a concrete example with concrete data so that we can actually understand what this is about. Also, what do you expect from us? Obviously you don't care about concrete code, so what's the goal of this thread? Hi Yes sorry that was terrible of me, I probably should have tried harder first before asking! The user inputs a number in the form. The form then goes and checks the first column for the closest value and then returns that row. I've managed to code up until where it only outputs the code if the number matches a value in the table. I'm now stuck on the closest value part. Table: calculator.php <!doctype html> <html> <head> <meta charset="utf-8"> <title>Calculator</title> </head> <body> <h1>Transfer Cost Calculator</h1> <form action="calculate_cost.php" method="post"> Property Purchase Price: <input type="text" name="ppprice"> <input type="submit" value="Calculate"> </form> </body> </html> caclulate_cost.php: <?php $servername = "localhost"; $username = "xxx"; $password = "xxx"; $dbname = "xxx"; // Create connection $con = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } if(isset($_POST['ppprice'])){ $ppp = $_POST['ppprice']; } echo $ppp . "<br>"; $sql = "SELECT purchase_price FROM transfer_cost"; $result=mysqli_query($con,$sql); $pparr = mysqli_fetch_all($result,MYSQLI_NUM); function getClosest($ppp, $pparr) { $closest = null; foreach($pparr as $item) { if($closest == null || abs($ppp - $closest) > abs($item - $ppp)) { $closest = $item; } } return $closest; } $sql = "SELECT * FROM transfer_cost WHERE purchase_price = $ppp"; $result = $con->query($sql); while($row = $result->fetch_assoc()) { echo "Price: " . $row["purchase_price"]. " Duties: " . $row["transfer_duties"]. " Transfer: " . $row["transfer_fee"]. " Deeds: " . $row["deeds_office_fee"]. " VAT:" . $row["vat"]. " Total: " . $row["total_transfer_costs"]. "<br>"; } ?> Edited September 28, 2015 by imkesaurus Quote Link to comment https://forums.phpfreaks.com/topic/298340-return-single-row-based-on-comparison-of-two-values/#findComment-1521706 Share on other sites More sharing options...
Solution Jacques1 Posted September 28, 2015 Solution Share Posted September 28, 2015 (edited) That's a lot clearer. You can actually do this in MySQL itself: Order the rows by distance from the input and then pick the row with the smallest distance. However, you need a special rule when the input is exactly between two rows (e. g. 225,000). Which row do you pick? The one with the smaller price or the one with the bigger price? I'll assume the former: SELECT * -- select explicit columns! FROM transfer_cost ORDER BY ABS(purchase_price - <your input>) ASC, purchase_price ASC -- in case the input is just between two purchase prices LIMIT 1 ; Edited September 28, 2015 by Jacques1 Quote Link to comment https://forums.phpfreaks.com/topic/298340-return-single-row-based-on-comparison-of-two-values/#findComment-1521707 Share on other sites More sharing options...
imkesaurus Posted September 28, 2015 Author Share Posted September 28, 2015 Ok awesome thank you for helping! I really only know the basics off of what I've dealt with while building sites with Joomla or Wordpress Last stupid question though... How exactly do I insert that? I've done it like so: $sql = "SELECT * FROM transfer_cost ORDER BY ABS(purchase_price - <your input>) ASC, purchase_price ASC LIMIT 1 ;"; $result = $con->query($sql); while($row = $result->fetch_assoc()) { echo "Price: " . $row["purchase_price"]. " Duties: " . $row["transfer_duties"]. " Transfer: " . $row["transfer_fee"]. " Deeds: " . $row["deeds_office_fee"]. " VAT:" . $row["vat"]. " Total: " . $row["total_transfer_costs"]. "<br>"; } but then it gives me this error: Fatal error: Call to a member function fetch_assoc() on a non-object Do I need to call it differently? or am I doing the MySQL wrong? Quote Link to comment https://forums.phpfreaks.com/topic/298340-return-single-row-based-on-comparison-of-two-values/#findComment-1521730 Share on other sites More sharing options...
Jacques1 Posted September 28, 2015 Share Posted September 28, 2015 The “<your input>” is of course just a placeholder. Create a prepared statement with one parameter for the input price and then bind $_POST['ppprice'] to that parameter. Quote Link to comment https://forums.phpfreaks.com/topic/298340-return-single-row-based-on-comparison-of-two-values/#findComment-1521733 Share on other sites More sharing options...
imkesaurus Posted September 28, 2015 Author Share Posted September 28, 2015 HAHA! Wow I've been looking at code for too long. Completely missed that It's working perfectly. Thank you so so much for the help! I really appreciate it. Quote Link to comment https://forums.phpfreaks.com/topic/298340-return-single-row-based-on-comparison-of-two-values/#findComment-1521736 Share on other sites More sharing options...
Jacques1 Posted September 28, 2015 Share Posted September 28, 2015 No problem. By the way, never insert user input directly into a query string like you did previously. This allows anybody to manipulate the query and perform an SQL injection attack. For example, any visitor can steal sensitive data like password hashes simply by appending them to the query result. And in the worst case, it's possible to compromise your entire server. Whenever you need to pass dynamic input to a query, use a prepared statement. Quote Link to comment https://forums.phpfreaks.com/topic/298340-return-single-row-based-on-comparison-of-two-values/#findComment-1521740 Share on other sites More sharing options...
imkesaurus Posted September 28, 2015 Author Share Posted September 28, 2015 I will definitely keep that in mind. Thank you Quote Link to comment https://forums.phpfreaks.com/topic/298340-return-single-row-based-on-comparison-of-two-values/#findComment-1521743 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.