CODEWITHSUNDEEP
phpmysqlsql
koko chidori
koko chidori

Reputation: 11

Fetching two tables with search keyword

I have two tables 'movies' and 'series' I want to fetch in these two tables in the same time with a keyword

So I try to do it but I get an SQL error

$query = $_GET['keyword'];
$query = htmlspecialchars($query);
$stmt = "SELECT series.title, series.pic FROM series UNION SELECT mov_title, mov_pic FROM movies WHERE mov_title LIKE '%$query%' OR title LIKE '%$query%' ";
$result = $pdo->query($stmt);

Upvotes: 0

Views: 98

Answers (4)

Your Common Sense
Your Common Sense

Reputation: 158005

The proper way

$stmt = $pdo->prepare("SELECT title, pic FROM series WHERE title LIKE ?");
$stmt->execute(["%".$_GET['keyword']."%"]);
$result = $stmt->fetchAll();

$stmt = $pdo->prepare("SELECT mov_title, mov_pic FROM movies WHERE mov_title LIKE ?");
$stmt->execute(["%".$_GET['keyword']."%"]);
foreach ($stmt as $row) {
    $result[] = $row;
}

No sql injection, no database overkill

Upvotes: 1

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6098

SELECT series.title, series.pic FROM series WHERE title LIKE '%$query%' 
 UNION
SELECT mov_title, mov_pic FROM movies WHERE mov_title LIKE '%$query%'

Upvotes: 0

Pelin
Pelin

Reputation: 966

The part before union of your query is not finished. Try like this;

SELECT series.title, series.pic 
FROM series where title LIKE '%$query%'
UNION 
SELECT mov_title, mov_pic 
FROM movies WHERE mov_title LIKE '%$query%'

Upvotes: 0

jarlh
jarlh

Reputation: 44805

Wrap your UNION up in a derived table:

select * from
(
    SELECT series.title as mov_title, series.pic FROM series
    UNION
    SELECT mov_title, mov_pic FROM movies
) dt
WHERE mov_title LIKE '%$query%'

BTW, perhaps you should consider UNION ALL instead?

Upvotes: 1

Related Questions