Not getting expected result - if statements and empty function

[ty11]

I have a relational database with two tables. One table contains titles of musical pieces called ‘works’ (and some other info about those pieces), and the other table contains urls to mp3 tracks of those musical pieces called ‘tracks’. I’ve been trying to get this simple php code to display the url when a url is available for that musical piece. But, all my attempts using if statements and “empty” function to check whether a url is available in the database, turns out always as having a url (even for those pieces that don’t have a url). I also tried !$row2 with the same issue. I’ve simplified the code in order to test if this works (see code below). I appreciate if you could highlight the novice mistakes I made.

$row retrieves an array relating to the first table (work_title, etc.) $row2 retrieves an array relating to the second table (urls, etc.) The SQL is confirmed to be working well since I already got all the details (from the two tables) into the page.

     /*********** Getting 'works' data and displaying *********/
            
            $sql = "select work_title, alternate_titles from works where composer_id='$compid'";
    
            $query2 = mysqli_query($conn, $sql);
            
            $row = mysqli_fetch_array($query2);
            
            
            
            $sql2 = "select track_url from tracks INNER JOIN works ON tracks.work_id=works.work_id";
            
            $query3 = mysqli_query($conn, $sql2);
            
            $row2 = mysqli_fetch_array($query3);

    if(!$row)
                {
                    echo "No musical pieces found.";
                } 
            
    while ($row = mysqli_fetch_array($query2))
                {   
                    if(empty($row2)){
                        echo $row['work_title'] . " - no url" . "<br />";}
                    else{
                        echo $row['work_title'] . " - url available" . "<br />";}
                }

[phdr]

There’s no relationship between the result from the first query and the second one, so, as you loop over the result from the first query, testing anything having to do with the second query has no meaning.

Also, you are executing a fetch statement for the first query before the start of the while(){} loop. This might tell you if that query matched any data, but it also discards the first row of data from that query. To test if the query matched any rows, either use mysqli_num_rows() or fetch all the data into an approximately named array variable, then test if that variable is empty or not. The code looping over the data from the query would be part of an else{} conditional branch, for the test if the query matched any rows.

You should use one query that gets the data that you want, in the order that you want it. Since there may not be track data for any particular work, you would use a LEFT JOIN. See the following -

$sql = "SELECT w.work_title, w.alternate_titles, t.track_url
 FROM works w
 LEFT JOIN tracks t ON w.work_id=t.work_id
 WHERE w.composer_id='$compid'
 ORDER BY w.work_title";
$result = mysqli_query($conn, $sql);

if(mysqli_num_rows($result) == 0)
{
	echo "No musical pieces found.";
}
else
{
	while($row = mysqli_fetch_assoc($result))
	{
		echo $row['work_title'];
		if(empty($row['track_url']))
		{
			echo " - no url<br />";
		}
		else
		{
			echo " - url available<br />";
		}
	}
}

Lastly, don’t put external, unknown, dynamic values directly into the sql query statement, since any sql special characters in a value will break the sql query syntax, which is how sql injection is accomplished. Instead, use a prepared query, with a ? place-holder in the sql query for each value. You would also want to switch to the much simpler and better designed PDO database extension.

[ty11]

Much thanks for your detailed response, which solved the issue and improved my code.

I realized after a short while from posting this that there’s no matching happening between the works and the tracks, which should have been obvious to me while writing this. Anyway, the solution and some of the items you covered here are beyond my little understanding of SQL and PHP, so although I now understand your suggested SQL syntax and the PHP code, I will carefully review your suggestions.

Have a great day!