MySQL IF THEN ELSE doubts

[marcnyc]

Hello, I never used IF THEN ELSE in a MySQL query so I am struggling with this.
My query is as follows:

	$sql = "SELECT artistId,
					artistName,
					artistNationality,
					artistPicture,
					labelName,
					collaborationDiscYear,
					collaborationDiscName,
					collaborationCover,
					collaborationId
			FROM dL_artist, dL_collaboration
		IF (dL_collaboration.collaborationLabelId = dL_label.labelId) THEN
			LEFT JOIN dL_label ON (dL_collaboration.collaborationLabelId = dL_label.labelId)
			WHERE dL_artist.artistId = dL_collaboration.collaborationArtistId 
		ELSE
			FROM dL_artist
			WHERE dL_artist.artistId
		END IF;
			";

Basically, I have a bunch of artists and collaborations in a database. My script shows the artists when they have a collaboration but I need to create a list of all the artists (even if they are not linked/joined to a collaboration) so I am trying to get a simple list back that includes all the artists (with and without collaboration) but that will still show the artist's collaboration if and when there is one.

Thanks

[bpat1434]

Well, if you want everyone no matter what, then you want something like this:

SELECT a.artistId, a.artistName, a.artistNationality, a.artistPicture, a.labelName,
			c.collaborationDiscYear, c.collaborationDiscName, c.collaborationCover, c.collaborationId
FROM dL_artists a, dL_collaboration c
LEFT JOIN dL_label l
    ON c.collaborationLabelId = l.labelId

That will give you a complete list of everything in the dL_artists and dL_collaboration table with the dL_label table joined to it. From that point, since it's a left-join, we know that if it's not in the dL_label table, then those row values will be null.

[marcnyc]

I've never seen this syntax but I tried it and it doesn't work. I get a "Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home4/marcurse/public_html/dl/library.inc.php on line 32" even though line 32 is 150 lines before the query itself and reads:
while ( $res = mysql_fetch_object($resource) )

[bpat1434]

So the query I gave you, is it executed?

How do you define $resource? Is it really the output of a call to mysql_query()?

[marcnyc]

Let me post you the functions:

function
getArtistListByLetter($text)
{
	global $dl_lang;

$mysql_stream =	mysql_connect(SERVER_NAME.":".SERVER_PORT, MYSQL_USER, MYSQL_PW);
mysql_select_db(SERVER_DB_NAME, $mysql_stream);

$sql = "SELECT a.artistId, a.artistName, a.artistNationality, a.artistPicture, a.labelName,
		c.collaborationDiscYear, c.collaborationDiscName, c.collaborationCover, c.collaborationId
FROM dL_artists a, dL_collaboration c
LEFT JOIN dL_label l
    ON c.collaborationLabelId = l.labelId
			";

if ( strlen($text) == 1 )	// it's not simply a letter!!!
$sql .= "&& LEFT(artistName, 1) = '".$text."'";

$sql .= "ORDER BY artistPriority DESC, artistId DESC";


$result = mysql_query ($sql, $mysql_stream);

makeTableList($result);

mysql_close($mysql_stream);
}

function
makeTableList($resource)
{
	global $dl_lang;

echo '<table align="center" width="100%">
		<tr bgcolor="#BBBBBB">
			<td align="left"><b>'.$dl_lang["artistName"].'</b></td>
			<td align="center"><b>'.$dl_lang["colDiscyear"].'</b></td>
			<td align="center"><b>'.$dl_lang["genre"].'</b></td>
			<td align="center"><b>'.$dl_lang["artistNationality"].'</b></td>
			<td align="center"><b>'.$dl_lang["artistPicture"].'</b></td>
			<td align="center"><b>'.$dl_lang["colPicture"].'</b></td>
		</tr>';
$TableListRow = 0;
while ( $res = mysql_fetch_object($resource) )
{
	$TableListRow++;
	echo ('<tr '.( is_int($TableListRow/2) ? 'bgcolor="#EEEEEE"' : '' ).'>
			<td valign="top" style="vertical-align:middle" align="left"><b><a href="'.$_SERVER['PHP_SELF'].'?artistid='.$res->artistId.'">'.$res->artistName.'</a></b></td>
			<td valign="top" style="vertical-align:middle" align="center">'.returnIfSet($res->collaborationDiscYear).'</td>
			<td valign="top" style="vertical-align:middle" align="center">'.getCollaborationGenreById($res->collaborationId).'</td>
			<td valign="top" style="vertical-align:middle" align="center">'.$res->artistNationality.'</td>
			<td valign="top" style="vertical-align:middle" align="center">'.imgCtrl($res->artistPicture, IMG_ARTIST).'</td>
			<td valign="top" style="vertical-align:middle" align="center">'.imgCtrl($res->collaborationCover, IMG_COVER).'</td>
		</tr>');
}
echo '</table>';
}

The second is the function that generates the output table and that has that error I told you about.

[bpat1434]

The issue is that after the initial SQL statement, you deviate from SQL standards. You specify no "WHERE" keyword, so you're not limiting your results, and furthermore, the syntax becomes broken. What you would need to do is something like this:

$sql = "SELECT a.artistId, a.artistName, a.artistNationality, a.artistPicture, a.labelName,
            c.collaborationDiscYear, c.collaborationDiscName, c.collaborationCover, c.collaborationId
FROM dL_artists a, dL_collaboration c
LEFT JOIN dL_label l
    ON c.collaborationLabelId = l.labelId
            "; 
  if(!empty($text))
  {
    if(strlen($text) > 1) // Longer than 1 character long
      $sql .= "WHERE artistName = '".substr($text, 0,1)."%'";
    else // String is 1 character long
      $sql .= "WHERE artistName = '".$text."%'";
  }
  $sql .= "ORDER BY artistPriority DESC, artistId DESC";