badsyntax badsyntax - 1 month ago 5
MySQL Question

How to handle data received from unioned select statements

Hello I'm trying to create a search function for my website. I have this method that has the query

public function search_all($data)
{
$query = [];

$result = $this->db->query('
SELECT blog_title FROM blog WHERE blog_title LIKE "%'. $data . '%"
UNION
SELECT body FROM blog WHERE body LIKE "%'. $data . '%"
UNION
SELECT username FROM users WHERE username LIKE "%'. $data . '%"
');

while ($row = $result->fetch_assoc())
{
$query[] = $row;
}

$result->free();

return $query;

$mysqli->close();
}


Then I have the method that handles the user input

public function search()
{
if (isset($_POST['search']))
{
$search_term = trim($_POST['search_term']);

$terms = $this->search->search_all($search_term);

foreach ($terms as $term)
{
var_dump($term);
}
}
}


The search works sort of. If I search a username or a blog title it then I can echo it back like this

echo $term['blog_title'];


if I var_dump then I get something like

array (size=1)
'blog_title' => string 'BillyBob' (length=8)


My question is why does it always retrun blog_title as the key. I thought I would have to do something like

If I want to echo the blog title

echo $term['blog_title'];


If I want to echo the username

echo $term['username'];

Answer

In the union of two tables either the fields of both tables should be same or the alias should be same for both the tables.

Please try this query.

$result = $this->db->query('
    SELECT body,blog_title,'' as username FROM blog WHERE blog_title LIKE "%'. $data . '%" OR body  LIKE "%'. $data . '%"  OR username  LIKE "%'. $data . '%"
    UNION 
    SELECT '' as body,'' as blog_title,username FROM users WHERE username LIKE "%'. $data . '%"
');

This query will select records when search will match with blog_title or body or username. And you will able to fetch all three fields' value for fetched records.

Comments