您的位置: 首页  >  技术问答  >  （php，mysql）根据同一db中另一个表中的属性显示一个MySQL表的结果[duplicate]

（php，mysql）根据同一db中另一个表中的属性显示一个MySQL表的结果[duplicate]

分类: 技术问答 • 2022-03-18 17:31:30

（php，mysql）根据同一db中另一个表中的属性显示一个MySQL表的结果[duplicate]

问题描述：

I'm trying to create a search bar in PHP where I would write name of the movie ( full name or just a few first letters of the name) and it would display information about actors in that movie such as actor's firstname, lastname and role. I'm using MySQL and there would be 3 tables name ( column names ):

actors ( a_id, firstname, lastname, role in the movie)
movies ( m_id, name )
actors_movies ( actor_id, movie_id )

i.e. I'd write Lord of the and it would display Elijah Wood, Frodo Baggins etc. I can't get this query to work at all, so any help would be greatly appreciated.

<?php
$connection = @mysql_connect('localhost', 'root', '');
$db = mysql_select_db('db-name', $connection);
$term = strip_tags(substr($_POST['searchit'], 0, 100));

//$term = mysql_escape_string($term); // Attack Prevention
if ($term == "") {
    echo "Enter Something to search";
} else {
    $query = mysql_query("SELECT a.firstname, a.lastname, a.role, m.name 
          FROM actors a 
          INNER JOIN actors_movies am ON a.a_id = am.actor_id 
          INNER JOIN movies m ON am.movie_id = m.m_id 
          ORDER BY a.firstname 
          WHERE m.name like '{$term}%'", $connection);

    $string = '';


    if (mysql_num_rows($query)) {
        while ($row = mysql_fetch_assoc($query)) {
            $string .= $row['firstname'] . "";
            $string .= $row['lastname'] . "</a> , ";
            $string .= $row['role'] . "</a>";
            $string .= "<br/>
";
        }
    } else {
        $string = "No matches found!";
    }

    echo $string;
}
?>

Edit: So after inserting if ( $query === false ) { echo mysql_error(); exit;} and editing the myqsl_query like the first answer below, this is the message that I receive:
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:/location.php on line 18
No matches found!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 actor_id INNER JOIN movies m ON am.movie_id = m.m_id WHERE LOW' at line 3

Name of my columns may include letters that aren't in the English/US vocabulary such as Š,Č etc, could they cause syntax error aswell or that is completely irrelevant? 

</div>

答

You can use % on either side of the search string and use LOWER for case insensitive matching, e.g.:

SELECT a.firstname, a.lastname, a.role, m.name 
FROM actors a INNER JOIN actors_movies am ON a.a_id = am.actor_id 
INNER JOIN movies m ON am.movie_id = m.m_id 
WHERE LOWER(m.name) like '%{$term}%'
ORDER BY a.firstname;

Here's how you can do it with prepared statement:

$stmt = $conn->prepare("SELECT a.firstname, a.lastname, a.role, m.name 
    FROM actors a INNER JOIN actors_movies am ON a.a_id = am.actor_id 
    INNER JOIN movies m ON am.movie_id = m.m_id 
    WHERE LOWER(m.name) like '%:term%'
    ORDER BY a.firstname");
$stmt->bindParam(':term', $term);
$stmt->execute();

相关推荐