PHP/MYSQL Check record in other database-CodePudding

I'm building an Audio playlist system. The system contains two separate databases.

Database 1: Records with with Artist - Title Database 2: Records with Artist - Title - file path

What I want: Check if Artist Title from database 1 exists in database 2 and get the file path.

if EXISTS add to output and check next from database 1.

if NOT exists, skip and check next from database 1.

I made something like this, but I get more results then I expect.

<?php
include("config.php");

$query = $con->query("SELECT * FROM database1 WHERE scheduled = 0 ORDER BY added ASC");

foreach($query as $row) {
    $artist= $row['artist'];
    $title= $row['title'];
                                                                     
    $query2 = $con->query("SELECT * FROM database2 WHERE artist = '$artist' AND title = '$title' AND active = 1");
    while($data2 = $query2->fetch(PDO::FETCH_ASSOC)) {
        $path = $data2['path'];
        echo $path;
    }
}
?>

CodePudding user response：

I mean you can use single query for this:

SELECT Musics.artist, Musics.title, Musics.path
FROM database1 Playlist
JOIN database2 Musics ON 
    Playlist.artist = Musics.artist AND 
    Playlist.title =  Musics.title AND 
    Musics.active = 1
WHERE 
    Playlist.scheduled = 0;

Actually you need to implement next tables structure:

CREATE TABLE Artists (
    id int auto_increment primary key,
    name varchar(255)
);

CREATE TABLE Tracks (
    id int auto_increment primary key,
    artist_id int,
    title varchar(255),
    path varchar(255),
    INDEX(artist_id),
    FOREIGN KEY (artist_id) REFERENCES Artists(id)
);

CREATE TABLE Playlist (
    id int auto_increment primary key,
    track_id int,
    scheduled tinyint,
    INDEX(track_id),
    FOREIGN KEY (track_id) REFERENCES Tracks(id)
);

code.php

<?php
$query = <<<SQL
SELECT Artists.name, Tracks.title, Tracks.path
FROM Playlist
JOIN Tracks ON Tracks.id = Playlist.track_id
JOIN Artists ON Artists.id = Tracks.artist_id
WHERE 
    Playlist.scheduled = 0;
SQL;

$stmt = $pdo->prepare($query);
$stmt->execute();
$playlist = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($playlist);

MySQL fiddle