PHP MySQL INNER JOIN query only returns one row when phpMyAdmin of same query returns four rows

Question

I have two tables 'favorite' and 'image':

'favorite' - contains columns:
- username (varchar) unique identifier for user
- viewkey (varchar) unique identifier for image
- action (tinyint) 1 = image was favorited
- created (datetime) time user favorited image
'image' - contains columns:
- username (varchar) unique identifier for user that uploaded image
- viewkey (varchar) unique identifier for image uploaded
- title (text) title of image
- tags (text) csv formatted tags describing image
- filename (text) path to image file on server
- uploaded (datetime) time user uploaded

I want to output an array with all images favorited by the currently logged in user so they can view a favorited album.

I am doing so using a custom database class with query building functions adapted from CodexWorld. I am new to joining tables but I wrote the function getJoin() to build an INNER JOIN query between the two tables which produced the following MySQL query:

SELECT image.* 
FROM image 
INNER JOIN favorite ON image.viewkey=favorite.viewkey 
WHERE favorite.username = 'Caddy7676' 
GROUP BY image.title, image.tags, image.filename, image.uploaded, image.modified, image.username, image.viewkey

The kicker is when I run this query in phpMyAdmin, 4 correct rows of images are returned but within my php program, only 1 correct row is returned. No others. It also produces an array where all the columns are strings. What am I doing wrong?

function getJoin() - builds and executes query returning an array

class DB {
    private $hostname = DB_HOST;
    private $username = DB_USER; 
    private $password = DB_PASS; 
    private $dbname = DB_NAME; 

    public function __construct() {
        if (!isset($this->db)) {
            // Connect to database
            $conn = new mysqli($this->hostname, $this->username, $this->password, $this->dbname);

            if ($conn->connect_error) {
                die ("Failed to connect with MySQL: " . $conn->connect_error);
            } else {
                $this->db = $conn;
            }
        }
    }

public function getJoin($table1, $table2, $conditions = array()) {
    if (array_key_exists("select", $conditions)) {
        $sql = " SELECT "; 
        $i = 0;
        foreach ($conditions['select'] as $tbl => $value) {
            $pre = ($i > 0)?' , ':'';
            $sql .= $pre . $tbl . "." . $value;
            $i++;
        }
        $sql .= ' FROM ' . $table1; 
    }

    if (array_key_exists("inner_join", $conditions)) {
        $sql .= " INNER JOIN " . $table2 . " ON " . $table1 . "." . $conditions['inner_join'] . "=" . $table2 . "." . $conditions['inner_join'];
        
    }

    if (array_key_exists("where", $conditions)) {
        $sql .= ' WHERE ';
        $i = 0;
        foreach($conditions['where'] as $key => $value) {
            $pre = ($i > 0)?" AND ":'';
            $sql .= $pre.$table2 . "." . $key ." = " . "'$value'" ;
            $i++;
        }
    } 

    if (array_key_exists("group_by", $conditions)) {
        $sql .= ' GROUP BY ';
        $i = 0;
        foreach($conditions['group_by'] as $key => $value) {
            $pre = ($i > 0)?", ":'';
            $sql .= $pre . $table1 . "." . $key;
            $i++;
        }
    }

    if (array_key_exists("order_by", $conditions)) {
        $sql .= ' ORDER BY ' . $conditions['order_by'] . ' DESC ';
    } 

    $result = $this->db->query($sql);

    if (array_key_exists("return_type", $conditions) && $conditions['return_type'] != 'all') {
        switch ($conditions['return_type']) {
            case 'count':
                $data = $result->num_rows;
                break;
            case 'single':
                $data = $result->fetch_assoc();
                break;
            default:
                $data = '';
        }
    } else {
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $data[] = $row;
            }
        }
    }
    echo $sql;
    return !empty($data)?$data:false;
}

view_favorites.php passes all parameters to getJoin() and fleshes out the query

$viewFavoritesDB = new DB();
$images = array ();

// Get all favorited images based on logged in username and favorite action
$conditions['select'] = array (
    'image' => '*'
);
$conditions['inner_join'] = 'viewkey';
$conditions['where'] = array (
    'username' => $_SESSION['username'],
);
$conditions['group_by'] = array (
    'title' => '',
    'tags' => '',
    'filename' => '',
    'uploaded' => '',
    'modified' => '',
    'username' => '',
    'viewkey' => '',
);
$images = $viewFavoritesDB->getJoin('image', 'favorite', $conditions);

var_dump($images)

array (size=8)
  'id' => string '53' (length=2)
  'title' => string 'Green Triangle' (length=14)
  'tags' => string 'green,triangle,2-d' (length=18)
  'filename' => string '33238_1648833408.png' (length=20)
  'uploaded' => string '2022-04-01 13:16:21.790642' (length=26)
  'modified' => string '2022-04-07 01:25:11.000000' (length=26)
  'username' => string 'AgBRAT' (length=6)
  'viewkey' => string '62473379c82ec' (length=13)

before you get the $result, write out the SQL you're about to execute and compare it to that in the PHP admin... is it the same or different? What's it look like.. Should lead you to the issue. — xQbert, Apr 07 '22 at 20:17
**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/32391315) — Dharman, Apr 07 '22 at 20:22
@xQbert `echo $sql;` has the output `SELECT image.* FROM image INNER JOIN favorite ON image.viewkey=favorite.viewkey WHERE favorite.username = 'Caddy7676' GROUP BY image.title, image.tags, image.filename, image.uploaded, image.modified, image.username, image.viewkey` and this is the same as the phpMyAdmin query. — Nyki-Anderson, Apr 07 '22 at 20:26
It looks like you're trying to design your own ORM. If you don't have lots of programming expertise, this is best left to the experts, so you should make use of an existing ORM like Doctrine. — Barmar, Apr 07 '22 at 20:47
And if you insist on doing it yourself, I recommend you use PDO rather than mysqli. It's much easier to implement dynamic prepared statements in PDO. — Barmar, Apr 07 '22 at 20:49
@Barmar I'm just coding this for fun, pushing my limits and learning as I go. I understand that there are easier ways to do things. But thank you for the info about Doctrine. I'll check out converting things to PDO. — Nyki-Anderson, Apr 07 '22 at 20:49
Your code snippet does not show the creation of `$conditions`. Is is POSSIBLE you used it previously, and there's still a `'return_type' => 'single'` left over in there? — Tim Roberts, Apr 07 '22 at 20:51

Nyki-Anderson · Answer 1 · 2022-04-07T21:42:19.573

0

Answered

I had previously used the variable $conditions which had a 'single' row paramater leftover. Reinitializing $conditions with $conditions = array (); cleared everything up. Thank you for the comments.

edited Apr 07 '22 at 21:42

answered Apr 07 '22 at 21:01

Nyki-Anderson

73
1
10

1

Or, just re-initialize it with `$conditions = array();` – Tim Roberts Apr 07 '22 at 21:36

PHP MySQL INNER JOIN query only returns one row when phpMyAdmin of same query returns four rows

1 Answers1