PHP/MYSQL Check record in other database

Question

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;
    }
}
?>

No, this is not how you do it - both in terms of design and combining datasets in queries. 1) Artists should have their own table (not database!). 2) Use foreign keys to formalise relationships between various tables and ensure consistency of data#. 3) Use sql joins to combine data from multiple tables, instead of using nested for loops in application code. — Shadow, Nov 15 '21 at 13:14
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Nov 15 '21 at 13:21
why dody you have 2 databases? but as long as they are on one server you canuse them like tbale dtabase2.music JOIN databse1.artists — nbk, Nov 15 '21 at 13:23

Slava Rozhnev · Accepted Answer · 2021-11-15T13:44:34.020

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

Many tracks have multiple authors and playlists - track relationship is also many-to-many. But we are getting into the realm of business requirements, as opposed to programming.... — Shadow, Nov 15 '21 at 13:56

PHP/MYSQL Check record in other database

1 Answers1