John Nichel wrote:
Hi, I'm trying to select data from a couple of tables with the same query. Easy right? Well it's driving me crazy. I'm passing an integer via an html form, using php to grab it, and insert it into the query (albumSongs.albumID = 1), to where I want to grab all the data from the first table where albumID equals the number I pass, and the data from songNames for each row that matches in albumSongs (songNamesID = songNamesID) The query executes without error, but no data is returned (the data is there, and should return if my query is right). I'm lost trying to find my mistake.
mysql> show columns from albumSongs; +-------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------+------+-----+---------+-------+ | albumID | int(3) | | | 0 | | | songNamesID | int(4) | | | 0 | | | discNumber | tinyint(1) | | | 0 | | | subSong | int(4) | | | 0 | | | trackLength | varchar(5) | | | | | | orderBy | int(3) | | | 0 | | +-------------+------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
mysql> show columns from songNames; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | songNamesID | int(4) | | | 0 | | | songNamesName | varchar(196) | | | | | +---------------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql> SELECT albumSongs.*, songNames.* FROM bytor.albumSongs, bytor.songNames WHERE albumSongs.albumID = 1 && albumSongs.SongNamesID = songNames.songNamesID;
Empty set (0.01 sec)
I assume bytor is the name of the db. I don't understand exactly what you're doing (1 song has multiple names?), but that shouldn't matter.
I would have written that query like this:
SELECT * FROM bytor.albumSongs s, bytor.songNames n WHERE s.SongNamesID = n.SongNamesID AND s.albumID = 1;
but that's just style. Your query is equivalent. That is, there's nothing wrong with your query that I can see. Many times (if not most), this sort of problem is because php isn't sending precisely what you think it is to mysql. That can be difficult to see in the coded query with variable names, but often jumps out at you when you see the string the way mysql does. Maybe you already did this, but my first suggestion would be to load the query into a string and print it, before using the string in mysql_query().
If that doesn't help, the only other thing I can think of is to verify that there are rows in albumSongs with albumID = 1, and verify that there is at least one row in songNames with songNamesID = a value you found in albumSongs with albumID = 1.
Here's a thought. Try
SELECT * FROM bytor.albumSongs s LEFT JOIN bytor.songNames n ON s.SongNamesID = n.SongNamesID WHERE s.albumID = 1;
The LEFT JOIN will give you results for any row in albumSongs where albumID=1, regardless of whether or not there are matching rows in songNames.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
