Searching multiple tables

Question

I'm trying to create a search for my site and I'm having lots of trouble doing it. In my movies database I have two tables: movieinfo and movie_genres. Under movieinfo, there is Movie_Name, actor1, actor2, actor3, actor4. Under the movie_genres table, there is Genre_Id. (There are more columns in each of the tables, but I only want to search those columns.)

My current code is:

<?php
if ($_GET) {
    $search = $_GET['search'];
    $connect = mysql_connect("localhost","root","spencer");
    if($connect) {
        mysql_select_db("movies", $connect);

        $query = "SELECT Movie_Name, actor1, actor1, actor1, actor1, Genre_Id  FROM movieinfo, movie_genres WHERE * LIKE '%:search%' OR actor1 LIKE '%:search%' OR  actor2 LIKE '%:search%' OR actor3 LIKE '%:search%' OR  actor4 LIKE '%:search%' OR Genre_Id = ? ";

        $results = mysql_query($query);
        while($row = mysql_fetch_array($results)) {
            echo $row['Poster'] . "<br/>" . $row['Movie_Name'];
        }
    } else {
        die(mysql_error());
    }
}
?>

Everything in movieinfo I'm searching for is VARCHAR, and movie_genres is an INT (incase that makes much of a difference). I think I'm close to what the code should be, but I don't know.

As of right now, I get the following error while trying to search:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\movies\get_search.php on line 12

New code:

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'movies';
$userdb = 'root';
$passdb = 'spencer';

try {
    // Connect and create the PDO object
    $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
    $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8
}
catch (PDOException $e) {
    $v_errormsg = $e->getMessage();
    $error_str = <<<END_HTML
            <h2> Error connecting to database: Message: $v_errormsg  <h2>
    END_HTML;
    echo $error_str;
    die;
}

$sql = <<<END_SQL
    SELECT Movie_Name FROM movieinfo WHERE Movie_Name LIKE '%":search"%' ";
END_SQL;
try {
    $sth = $conn->prepare($sql);
    $sth->execute();
}
catch (PDOException $e) {
    $v_errormsg = $e->getMessage();
    $error_str = <<<END_HTML
        <h2> Error selecting data: Message: $v_errormsg  <h2>
    END_HTML;
    echo $error_str;
    die;
}

$num_rows = 0;
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
    $Movie_Name = $row['Movie_Name'];
}

echo $row['Movie_Name'];        
?>

In which table is `Poster` ? You didn't include that in your question but you did make a mention about `Movie_Name` — Funk Forty Niner, Oct 28 '13 at 03:09
I'm suspecting that you left out `Poster` in your `SELECT` and you're querying an item that doesn't exist in the query. — Funk Forty Niner, Oct 28 '13 at 03:12
I'm also suspecting/questioning `'%:search%'` - the colon in there may or may not be valid. Have you tried using `'%search%'` ? — Funk Forty Niner, Oct 28 '13 at 03:17
Poster is from movieinfo, I left it out because I didn't want it to be searched for as it's an image. For now I have removed it from the echo, and I have also removed the colon from %:search% I'm still getting the same error Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\movies\get_search.php on line 12 — Mike, Oct 28 '13 at 03:32
Step 1: test mysql_query as in example #1 from http://php.net/manual/en/function.mysql-query.php — Ryan, Oct 28 '13 at 03:35
@Mike Then try `$query = ($connect, "SELECT Movie_Name, actor1, actor1, actor1, actor1, Genre_Id FROM movieinfo, movie_genres WHERE * LIKE '%:search%' OR actor1 LIKE '%:search%' OR actor2 LIKE '%:search%' OR actor3 LIKE '%:search%' OR actor4 LIKE '%:search%' OR Genre_Id = ? ");` — Funk Forty Niner, Oct 28 '13 at 03:35
@Mike Yet why are you repeating `actor1, actor1, actor1, actor1` ? — Funk Forty Niner, Oct 28 '13 at 03:35
@Fred sorry it should say actor1, actor2, acto3, actor4 my code had it right — Mike, Oct 28 '13 at 03:42
@Ryan Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1=1' at line 1 — Mike, Oct 28 '13 at 03:47
The rest of my code that uploads data to my database is done with PDO, maybe that's why? — Mike, Oct 28 '13 at 03:49
absolutely, use PDO - read this: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php and this: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Ryan, Oct 28 '13 at 03:52
amazed no one has suggested this: consider putting actors in a separate table and joining, so you don't have to query four different columns. (or, later, five or six or seven or...) — Eevee, Oct 28 '13 at 04:18

score 1 · Answer 1 · answered Oct 28 '13 at 04:35

1

You can't use part of a string as a placeholder; placeholders can only take the place of a single entire value. Use LIKE :search and bind '%' . $search . '%' as the value.
You aren't binding any values. :)

answered Oct 28 '13 at 04:35

Eevee

47,412
11
95
127

Ryan · Answer 2 · 2013-10-28T04:13:29.213

0

You are getting an invalid query because you're using bind paramter syntax from PDO

start with something very simple like

$search= mysql_escape_string($search);
$query = "SELECT Movie_Name FROM movie_info WHERE name LIKE '%".$search."%';

Once you get that working, move on to the more complex logic...

Better yet, use PDO to do the query:

<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

example from: http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

edited Oct 28 '13 at 04:13

answered Oct 28 '13 at 03:46

Ryan

5,959
2
25
24

Good idea, Getting Notice: Array to string conversion in C:\xampp\htdocs\movies\get_search.php on line 9 now – Mike Oct 28 '13 at 03:54
I've updared my code, not getting any errors, just a blank page now. – Mike Oct 28 '13 at 04:21

Searching multiple tables

2 Answers2