CODEWITHSUNDEEP
phpmysql
user457666
user457666

Reputation: 73

SQL query not returning a value

My sql script is not returning a value 'sid' when I use it with php, but when I run it against the actual database it returns the value. I cannot figure out where my error is. My tables are as follows:

Song
aid (1)
sid (999)
title (Moonlight Sonata)
surl

Album
aid (1)
artist (Beethoven)
genre (Classic)
album

Here is my code:

$sql="SELECT S.title, S.surl, S.sid, A.aid, A.album, A.artist FROM challenge C, 
    song S, album A WHERE C.fid = '$uid' AND uid = '$user' AND date = '$date' 
    AND C.sid = S.sid AND S.aid = A.aid";

$result=mysql_query($sql);

if (mysql_num_rows($result) != 0){
    while($row=mysql_fetch_array($result)){
    $title = $row[title];
    $album = $row[album];
    $surl = $row[surl];
    $artist = $row[artist];
    $aid = $row[aid];
    $sid = $row[sid];

echo "Album: $album Artist: $artist Title: $title Song: $surl SongID: $sid";
    }
}

Everything is printing except the value 'sid' - it should be printing 999. Any help would be greatly appreciated!!

Upvotes: 0

Views: 834

Answers (2)

Marc B
Marc B

Reputation: 360592

You're not escaping your values in the query ($uid, $user, $date) and one or more of them contains SQL metacharacters (probably a quote ') which is breaking the query's syntax.

You're also not checking the return value of the mysql_query() call and assuming it succeeded. At bare minimum, you should have

$result = mysql_query($sql) or die(mysql_error());

or

$result = mysql_query($sql);
if ($result === FALSE) {
    die(mysql_error());
}

And you've also got an invisible syntax error with your fetch_row loop:

$title = $row[title];

What you want is

$title = $row['title']

You're trying to fetch an array index using the constant "title" which doesn't exist. This also holds for all the other values you're trying to retrieve from $row.

Upvotes: 0

deceze
deceze

Reputation: 522025

$row[title] is not the syntax you're looking for. If you'd enable error reporting you'd see warnings about undefined constants. Just writing title makes PHP look for a constant called "title", which does not exist. Only then does it fall back onto strings and tries if you maybe meant the string 'title'. SID happens to be a predefined constant, so that's the only time it's using the value of the constant, and there's no index in the array with the value of that constant.

Quote your strings! Write $row['title'] and $row['sid'].

Please read "Why is $foo[bar] wrong?".
Furthermore read the article about SQL injection and escape your input!

Upvotes: 3

Related Questions