SQL query not returning a value

Question

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!!

deceze · Accepted Answer · 2010-12-20 02:27:24Z

3

$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!

answered Dec 20, 2010 at 2:27

deceze♦

524k88 gold badges804 silver badges953 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user457666 Over a year ago

Thanks so much!! I will read the articles - I am still really new to php so I am still learning. I appreciate your help!

deceze Over a year ago

@user You're welcome. To help yourself in the future: enable error reporting! Set it to error_reporting(-1) to see all errors. Look in the manual for any error that comes up. It'll help you a lot!

Marc B · Accepted Answer · 2010-12-20 02:27:59Z

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.

Collectives™ on Stack Overflow

SQL query not returning a value

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related