UNION DISTINCT query PHP MySQL results blank

Question

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();
?>

Try echoing something inside your IF before your while loop, just to make sure it's getting that far. Otherwise, check your server error logs to see if there's any errors there. — aynber
– aynber, Commented Sep 16, 2024 at 14:28
Blank page, seems to be a http/500. So look into the error log file. — Markus Zeller
– Markus Zeller, Commented Sep 16, 2024 at 14:33
Tipp: Do not concat echo arguments. Use comma as delimiter, like: echo $row["sw_category1"], " " , $row["sw_category2"], " "; — Markus Zeller
– Markus Zeller, Commented Sep 16, 2024 at 14:36
3 UNION DISTINCT produces 3 separate sortings and duplicate removings. Use UNION ALL for first 2 unions and UNION DISTINCT as final union only. — Akina
– Akina, Commented Sep 16, 2024 at 14:52

Mahmoud Saleh · Accepted Answer · 2024-09-22 10:26:46Z

0

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

answered Sep 22, 2024 at 10:26

Mahmoud Saleh

324 bronze badges

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

Collectives™ on Stack Overflow

UNION DISTINCT query PHP MySQL results blank

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related