[RESOLVED] Trouble executing query and displaying results

[jeffmc21]

I'm pretty new to PHP/MySQL. Assuming I've connected to the database, which I can do, I'm just wanting to retrieve rows from the db that meet the "WHERE" criteria in the code here, and then display them in the table code shown as long as results exist.

What am I doing wrong?

$result = mysql_query("SELECT DATE_FORMAT(date,'%b %d') as audio_date, `title`, `intro`, `link` FROM `tbl_cssAudio` WHERE `interview` = `yes` ORDER BY event_date");


 while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo '<tr>
            <td>
                <div class="audioTitle">'. $row["title"] .'</div>
                <div class="audioDate">'. $row["audio_date"].'</div>
                <div class="audioIntro>'. $row["intro"].'</div>
                <div class="audioLinks><a href="http://www.chrisstewartsports.com/podcasts/audio/'. $row["link"].'" target="_blank">Listen<img src="http:/www.chrisstewartsports.com/images/listen.png" /></a></div>
            </td>
          </tr>';
}

mysql_free_result($result); 

[criterion9]

Are you getting any errors? Is anything being displayed?

[jeffmc21]

Sorry. I should have included that as well.


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/chriss7/public_html/cssaudio.php on line 18

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/chriss7/public_html/cssaudio.php on line 29

[Mindzai]

You are using back ticks around the value you are checking for (yes) - you should be using single quotes. You may also find that date is a reserved word and needs to be enclosed in back ticks, you can check that in the mysql manual.

Incidentally using the mysql_error() function will pass back the error from mysql itself which is useful for debugging and when asking for help.

Just as a general tip, it's usually best to store booleans as some form of int datatype rather than 'yes' or 'no' strings. It makes condition testing much easier since in PHP 1 = true and 0 = false, but 'yes' and 'no' both = true, which means you have to write less straightforward code if you ever come to test the values in such a field. Not applicable in this case, but just a general point.

[Shorts]

$result = mysql_query("SELECT DATE_FORMAT(date,'%b %d') as audio_date, `title`, `intro`, `link` FROM `tbl_cssAudio` WHERE `interview` = `yes` ORDER BY event_date"); 

Should be

$result = mysql_query("SELECT DATE_FORMAT(date,'%b %d') as audio_date, `title`, `intro`, `link` FROM `tbl_cssAudio` WHERE `interview` = 'yes' ORDER BY event_date"); 

`yes` isn't a string, it's looking for a mysql column\variable named yes. Also, those errors you received usually mean the SQL wasn't executed.

[jeffmc21]

Thanks guys. In addition to the back ticks, I figured out that I was using a field/value in the WHERE clause that hadn't been retrieved from the database in the SELECT clause.

[Mindzai]

It is perfectly valid to use a field not included in the select in the WHERE clause. For example:

SELECT `name` FROM `users` WHERE `id` = 1