Could not check value within two column using MySQL query

Question

I am facing one issue. I need to check value which should present within two column values using PHP and MySQL. I am explaining my table below.

    id      zip_from               zip_to

     1        751001                751030

db_postcode:

$post_code='751010';
$sql="select * from db_postcode where zip_from >='".$post_code."' and zip_to='".$post_code."'";
$sqlpin=mysqli_query($conn,$sql);
if (mysqli_num_rows($sqlpin) > 0) {
    $data=array("status"=>"Success","msg"=>"Product is available for this postcode");
}else{
    $data=array("status"=>"Failed","msg"=>"Product is not available for this postcode");
}
echo json_encode($data);

Here I am getting the message {"status":"Failed","msg":"Product is not available for this postcode"} which is wrong because code 751010 is present within 751001-751030. Here I need to check the user given value should be present within that two column.

Can you share the exact column definitions? Is zip_from a INT column? And why do you think that this is related to PHP? — Nico Haase
– Nico Haase, Commented Jan 9, 2019 at 11:58

Nick · Accepted Answer · 2019-01-09 12:15:17Z

1

Your compare is written the wrong way around, what you want is to check that $post_code is between zip_from and zip_to:

$sql="select * from db_postcode where '$post_code' between zip_from and zip_to";

Note that this will only work if all zip_from, zip_to and $post_code values are the same length, otherwise you will run into issues that in a string compare, 2 > 100000. If they are not the same length, you should cast them to integer to compare them.

answered Jan 9, 2019 at 12:15

Nick

147k23 gold badges67 silver badges106 bronze badges

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

Comments

iamousseni · Accepted Answer · 2019-01-09 19:05:06Z

0

Well, Your query would should be this:

$sql="select * from db_postcode where ".$post_code." BETWEEN zip_from and zip_to";

edited Jan 9, 2019 at 19:05

answered Jan 9, 2019 at 12:03

iamousseni

93 bronze badges

2 Comments

user5443928 Over a year ago

I have already tried this but same issue. Let me to remind you both column has datatype varchar.

Nico Haase Over a year ago

Please explain why it should be like this and which exact changes were needed

Collectives™ on Stack Overflow

Could not check value within two column using MySQL query

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related