Jump to content

Return single row based on comparison of two values


imkesaurus
Go to solution Solved by Jacques1,

Recommended Posts

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 :)

Link to comment
Share on other sites

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 by Jacques1
Link to comment
Share on other sites

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:

f70d074.png

 

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 by imkesaurus
Link to comment
Share on other sites

  • Solution

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 by Jacques1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.