phpmysqlechouniondistinct

UNION DISTINCT query PHP MySQL results blank


The query below works fine in phpMyAdmin and displays the correct results. However, when implemented on a php page, it shows nothing (a blank page). Can someone help me to display these results in the echo?

Modified code below to include the "GET". Not sure why, but if I include back ticks like so: $category,, I at least get a result of "0 Results". If stated like so: '$category' I get a blank page.

Error Log reports: "PHP Notice: Trying to get property of non-object in ///test.php on line 38".

<?php
$category = ($_GET['category']);
$servername = "localhost";
$username = "test";
$password = "****";
$dbname = "test";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$sql = "(SELECT 
`sw_category1` as `temp` 
FROM `products` WHERE sw_category1 = `$category` OR sw_category2 = 
`$category` OR sw_category3 = `$category` OR sw_category4 = `$category`
GROUP BY `sw_category1`) 

UNION DISTINCT 
(SELECT 
`sw_category2` as `temp` 
FROM `products` WHERE sw_category1 = `$category` OR sw_category2 = 
`$category` OR sw_category3 = `$category` OR sw_category4 = `$category`
GROUP BY `sw_category2`)

UNION DISTINCT 
(SELECT 
`sw_category3` as `temp` 
FROM `products`  WHERE sw_category1 = `$category` OR sw_category2 = 
`$category` OR sw_category3 = `$category` OR sw_category4 = `$category`
GROUP BY `sw_category3`)

UNION DISTINCT 
(SELECT 
`sw_category4` as `temp` 
FROM `products`  WHERE sw_category1 = `$category` OR sw_category2 = 
`$category` OR sw_category3 = `$category` OR sw_category4 = `$category`
GROUP BY `sw_category4`)";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "" . $row["sw_category1"]. " " . $row["sw_category2"]. " " . 
$row["sw_category3"]. " " .     $row["sw_category4"]. "";
  }
} else {
  echo "0 results";
}
$conn->close();
?>

Solution

  • If it is as you mentioned the query returning data while executing in phpMyAdmin I suspect to remove the back ticks (it's a pure mysql syntax not php), also you need to ensure that in your php script if the result is empty or not (Here if ($result->num_rows > 0) try to print any thing and see).