PHP - 从数组中查询多个表

Question

I'm really struggling trying to get these two queries (below) together with INNER JOIN. I'm passing an array through in _GET. I wanna select from two different mysql tables.

URL array _GET example: www.mysite.com/users_slideshows[]=one&users_slideshows[]=two&users_slideshows[]=three

Here are my two queries that i'm trying to combine:

$mysqli = new mysqli('localhost','root','1234','root');
$records = array();

foreach ($_GET["users_slideshows"] as $djahjkdsh) {
    $result = $mysqli->query("SELECT * FROM playlist_builder WHERE playlist_builder.volume IN ('".$djahjkdsh."') ");
    while($row = $result->fetch_array(MYSQL_ASSOC)) {
            $records[] = array('tags' => array($row));
    }
    $json = json_encode($records, JSON_PRETTY_PRINT);

}

foreach ($_GET["users_slideshows"] as $djahjkdsh) {
    $result = $mysqli->query("SELECT * FROM custom_slideshow WHERE custom_slideshow.volume IN ('".$djahjkdsh."') ");
    while($row = $result->fetch_array(MYSQL_ASSOC)) {
            $records[] = array('tags' => array($row));
    }
    $json = json_encode($records, JSON_PRETTY_PRINT);

}

If I just choose from one table, it works perfect but it's only when I combine both together.

I've tried the following below... not happening

SELECT * FROM playlist_builder INNER JOIN custom_slideshow ON custom_slideshow.volume = playlist_builder.volume IN ('".$djahjkdsh."')

If anyone could help me on what I'm doing wrong, I would extremely appreciate it. Thanks!

Answer 1

Your SQL syntax is incorrect since you run two expressions in same logical ON clause. Simply separate the ON clauses into multiple expressions. Also, with one value, use equality over IN.

SELECT * FROM playlist_builder 
INNER JOIN custom_slideshow ON custom_slideshow.volume = playlist_builder.volume 
AND playlist_builder.volume = ?)

Alternatively, with a WHERE clause:

SELECT * FROM playlist_builder 
INNER JOIN custom_slideshow ON custom_slideshow.volume = playlist_builder.volume 
WHERE playlist_builder.volume = ?)

PHP (using parameterization and table aliases)

$sql = 'SELECT * FROM playlist_builder p 
        INNER JOIN custom_slideshow c ON c.volume = p.volume AND p.volume = ?';

foreach ($_GET["users_slideshows"] as $djahjkdsh) {

    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param("s", $djahjkdsh);
    $stmt->execute();

    $result = $stmt->get_result();
    while($row =  $result->fetch_assoc()) {
         $records[] = array('tags' => array($row));
    }
    $json = json_encode($records, JSON_PRETTY_PRINT);    

    $stmt->close();
}