PHP query not reporting results as expected

[Thorsverd]

Not sure what I am doing wrong. I'm learning 😉

I expect the below code to find one business by id and see if the businesses zip code is found in Florida_Zips column and display the resulting image. It should find one entry, but finds nothing and no errors are being displayed :

URL is called like this: website.com/test.php?id=1234&Business_Zip=56789

Thank you very much to anyone kind enough to help me!
 

    <?php
if (isset($_GET['id'], $_GET['Business_Zip'])) 
{
$con=mysqli_connect("localhost","*****","*****","*****");
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$id = ($_GET['id']);
$Florida_Zips = ($_GET['Florida_Zips']);
$Business_Zip = ($_GET['Business_Zip']);

$result = mysqli_query($con,"SELECT * FROM DATA WHERE '$Business_Zip' LIKE '$Florida_Zips' AND id = " . $_GET['id']);
while($row = mysqli_fetch_array($result))
  {
  echo '<br><img src="images/Florida.png" style="padding-bottom:8px;">';
  }
mysqli_close($con);
}
?>

 

 

[kicken]

Is $Florida_Zips supposed to be a column name, or something like a comma-separated list of zip codes?  Where does the data for it come from? Your code says the query string, but your example URL doesn't mention it.

 

[Thorsverd]

Thank you kicken for the fast reply! $Florida_Zips is a column name that contains a bunch of zip codes. Trying to achieve this: find business with  id in isset. If the Business_Zip is found in Florida_Zips, display image. 

[kicken]

31 minutes ago, Thorsverd said:

$Florida_Zips is a column name that contains a bunch of zip codes.

Given you're trying to compare with LIKE, I'm guessing that means the each row has a list of zipcodes in that column, not a single zipcode?  Given the column name is dynamic, I'm guessing you have different columns for each state too (ie, FLZips, GAZips, etc).  If that's the case, then that is not the proper way to design your database.  A column should only contain a single value (ie, a single zipcode) per row, and you shouldn't have multiple columns for essentially the same info.

You should have a second table that associates the zipcodes and the state with a business, with a single row per zipcode and state combo.  For example:

create table business_zipcodes (
  businessId int not null,
  Zipcode varchar(10) not null,
  State varchar(2) not null
);

You'd then join to that table in your select to search for a particular zip code.

SELECT * 
FROM DATA 
INNER JOIN business_zipcodes on business_zipcodes.businessId=DATA.id
WHERE 
    DATA.id=?
    and business_zipcodes.State='FL'
    and business_zipcodes.zipcode=?

Notice I replaced your variables with ? also.  Sticking variables into your query is also another thing you should not be doing (read up on SQL Injection attacks), you should be using a prepared statement with bound parameters.

Your code would end up looking more like this:

$stmt = mysqli_prepare($con, "
	SELECT * 
	FROM DATA 
	INNER JOIN business_zipcodes on business_zipcodes.businessId=DATA.id
	WHERE 
		DATA.id=?
		and business_zipcodes.State='FL'
		and business_zipcodes.Zipcode=?
");
mysqli_stmt_bind_param($stmt, 'is', $_GET['id'], $_GET['Business_Zip']);
mysqli_stmt_execute($stmt);
while ($row = mysqli_stmt_fetch($stmt)){
	echo '<br><img src="images/Florida.png" style="padding-bottom:8px;">';
}