PHP - Querying multiple tables from an array

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!

You have implemented a SQL injection security vulnerablity. You are taking input from the user and inserting it into a SQL query without any validation or escaping. A user could craft input to transform their database query into any db operation they choose. You need to learn to use prepared statements before doing any more coding involving web and databases. — Stephen Ostermiller
– Stephen Ostermiller ♦, Commented Dec 11, 2017 at 20:16
I understand this. Thanks. I'll be working on improving this! — code
– code, Commented Dec 11, 2017 at 20:18

Parfait · Accepted Answer · 2017-12-11 21:44:31Z

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();
}

Collectives™ on Stack Overflow

PHP - Querying multiple tables from an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related