Search in two tables [duplicate]

Question

Search in two tables [duplicate]

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I have a media table with the fields id_midia , nome , tipo , and another synonymous table that has the id_midia , sinônimo

I need to make a query where it checks the two tables whether the term you typed is contained in the names of the media or the synonym.

I'm doing it this way:

$sql = "
  SELECT ayzac_midia_name,ayzac_midia_id 
  FROM 'ayzac_midia' 
  WHERE 'ayzac_midia'.'ayzac_midia_name' 
  LIKE '%".$searchTerm."%' 
  UNION SELECT ayzac_tag,ayzac_midia_id 
    FROM 'ayzac_tags' 
    WHERE 'ayzac_tags'.'ayzac_tag' 
    LIKE '%".$searchTerm."%'
";

I just need information from the media table, and when the term found is a synonym it brings the sinônimo instead of nome . And I also need the type in the media table and so it only returns me nome and id . Also, if the fetched term exists in both tables with the same id it displays both tables and this can not happen.

If anyone can help me with this I would be grateful.

@edit Explaining the context:

What I'm doing is a search of movies and series registered in my database, it happens that the user does not necessarily have to search for the full name of the media he can search for a synonym or a tag, for example Game of Thrones can be fetched by GoT, but even though it searches using the term GoT, I want it to be returned Game of Thrones.

PS: GoT is in a table only for synonyms that only contain the id_midia, which is a FK of the media table and the synonym.

php mysql sql select

asked by anonymous 20.09.2017 / 14:23

2 answers

0

I think you only want 1 result according to the search term.

Follows:

$sql = "SELECT DISTINCT
  ayzac_midia_name,
  a.ayzac_midia_id 
FROM
  ayzac_midia a
JOIN
  ayzac_tags b on a.ayzac_midia_id = b.ayzac_midia_id
WHERE
  ayzac_midia_name LIKE '%".$searchTerm."%'
  OR ayzac_tag LIKE '%".$searchTerm."%'";

SQLFiddle

20.09.2017 / 19:13

Trouble activating Vue.Js Execute method that is inside the event "onclick"

score 2 · Accepted Answer

I think you need to make a JOIN with the ayzac_midia table in the second SELECT and in this way you can return the midia name.

Something like this:

$sql = "SELECT ayzac_midia_name,ayzac_midia_id 
  FROM 'ayzac_midia' 
  WHERE 'ayzac_midia'.'ayzac_midia_name' LIKE '%".$searchTerm."%'    
  UNION 
    SELECT ayzac_tag,ayzac_midia_id, ayzac_midia.ayzac_midia_name 
    FROM 'ayzac_tags'
    INNER JOIN 'ayzac_midia' ON 'ayzac_midia'.'ayzac_midia_id' = 'ayzac_tags'.'ayzac_midia_id'
    WHERE 'ayzac_tags'.'ayzac_tag' LIKE '%".$searchTerm."%'";