badsyntax badsyntax - 1 year ago 44
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 Source

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.