bluedragon Posted May 22, 2009 Share Posted May 22, 2009 All right. I finally had to admit that I am stumped on this one. I have the following query: SELECT author_lastname, author_firstname, title FROM BookTable bt INNER JOIN CategoryTable ct ON bt.category_id = ct.category_id When I execute the query in mysql, I get back the expected 28 rows. In my code, I get back 0 rows. My code is as follows (pretty straight forward): <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HEAD> <TITLE>Book List</TITLE> <link href="tables.css" rel="stylesheet" type="text/css" /> <HEAD> <BODY> <?php $database_host = "localhost"; $database_name = "xxxxxx_db"; $database_read_account = "xxxxxx"; $database_read_passwd = "yyyyyyy"; $getBookResources = "SELECT author_lastname, author_firstname, title FROM BookTable bt INNER JOIN CategoryTable ct ON bt.category_id = ct.category_id "; $conn = mysql_connect( "${database_host}", "${database_read_account}", "${database_read_passwd}" ); mysql_select_db($database_name); mysql_query("SET NAMES 'utf8';", $conn) or die ("Unable to set names."); mysql_query("SET CHARACTER SET 'utf8';", $conn) or die ("Unable to set charset."); $result = mysql_query( $getBookResources, $conn ); if (!$result) { echo("<P>Error performing query: " . mysql_error() . "</P>"); exit(); } if(mysql_num_rows($result) == 0) { print("<P>No Data Entered YET ! </P>\n"); } else { print("<table border='1' class='sofT' cellspacing='0'>\n"); while ( $row = mysql_fetch_array($result) ) { // process the row... $lastname = $row['author_lastname']; $firstname = $row['author_firstname']; $title = $row['title']; print("<tr>\n"); print("<td>$title</td>\n"); print("<td>$lastname</td>\n"); print("<td>$firstname</td>\n"); print("</tr>\n"); } print("</table>\n"); } mysql_free_result($result); ?> </BODY> </HTML> Any help is appreciated on this. My head is cramping -Lynch Link to comment https://forums.phpfreaks.com/topic/159188-solved-mysql_query-returns-0-rows-but-query-works-in-mysql/ Share on other sites More sharing options...
MasterACE14 Posted May 22, 2009 Share Posted May 22, 2009 you sure it's connecting to the database? change this... $conn = mysql_connect( "${database_host}", "${database_read_account}", "${database_read_passwd}" ); to this... $conn = mysql_connect( $database_host, $database_read_account, $database_read_passwd ); Link to comment https://forums.phpfreaks.com/topic/159188-solved-mysql_query-returns-0-rows-but-query-works-in-mysql/#findComment-839529 Share on other sites More sharing options...
Maq Posted May 22, 2009 Share Posted May 22, 2009 you sure it's connecting to the database? change this... $conn = mysql_connect( "${database_host}", "${database_read_account}", "${database_read_passwd}" ); to this... $conn = mysql_connect( $database_host, $database_read_account, $database_read_passwd ); That part is actually fine. Link to comment https://forums.phpfreaks.com/topic/159188-solved-mysql_query-returns-0-rows-but-query-works-in-mysql/#findComment-839533 Share on other sites More sharing options...
bluedragon Posted May 22, 2009 Author Share Posted May 22, 2009 I am absolutely sure the connection, database, etc is valid. If I shorten the sql query: $getBookResources = "SELECT author_lastname, author_firstname, title FROM BookTable"; I actually get a result set from PHP. It doesn't like my join. I have tried the INNER JOIN as stated, and I have tried the more traditional "from TableX x, TableY y where x.id = y.id" with the same results (or lack of results) from php. The next question will certainly be about my tables: CREATE TABLE BookTable ( book_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, title varchar(128), author_lastname varchar(64), author_firstname varchar(64), publish_date date, description varchar(4096), category_id int(10) UNSIGNED, PRIMARY KEY (book_id), FOREIGN KEY (category_id) REFERENCES CategoryTable(category_id) ) AUTO_INCREMENT=200 ENGINE=InnoDB; CREATE TABLE CategoryTable ( category_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, primary_category varchar(64) NOT NULL, secondary_category varchar(64), PRIMARY KEY (category_id) ) AUTO_INCREMENT=200 ENGINE=InnoDB; -Lynch Link to comment https://forums.phpfreaks.com/topic/159188-solved-mysql_query-returns-0-rows-but-query-works-in-mysql/#findComment-839826 Share on other sites More sharing options...
radi8 Posted May 22, 2009 Share Posted May 22, 2009 I do not think that it is a problem with the SQL since you ran it in PHPMyAdmin and got results. just for the heck of it, try removing these lines: mysql_query("SET NAMES 'utf8';", $conn) or die ("Unable to set names."); mysql_query("SET CHARACTER SET 'utf8';", $conn) or die ("Unable to set charset."); and add the following: $count=mysql_num_rows($result); echo 'Results: '.$count.'<br>'; Link to comment https://forums.phpfreaks.com/topic/159188-solved-mysql_query-returns-0-rows-but-query-works-in-mysql/#findComment-839878 Share on other sites More sharing options...
bluedragon Posted May 22, 2009 Author Share Posted May 22, 2009 I already have that check, and I do get the "No Data Entered YET!" as the resulting screen. Sorry, I should I been more clear on what I really did see. if(mysql_num_rows($result) == 0) { print("<P>No Data Entered YET ! </P>\n"); } else { print("<table border='1' class='sofT' cellspacing='0'>\n"); while ( $row = mysql_fetch_array($result) ) { // process the row... } print("</table>\n"); } -Lynch Link to comment https://forums.phpfreaks.com/topic/159188-solved-mysql_query-returns-0-rows-but-query-works-in-mysql/#findComment-839887 Share on other sites More sharing options...
radi8 Posted May 22, 2009 Share Posted May 22, 2009 Change the query and remove the inner join. Do one like: SELECT a . * , b . * FROM tbl1 a, tbl2 b WHERE a.id = b.id LIMIT 0 , 30 Link to comment https://forums.phpfreaks.com/topic/159188-solved-mysql_query-returns-0-rows-but-query-works-in-mysql/#findComment-839932 Share on other sites More sharing options...
bluedragon Posted May 22, 2009 Author Share Posted May 22, 2009 Change the query and remove the inner join. Do one like: SELECT a . * , b . * FROM tbl1 a, tbl2 b WHERE a.id = b.id LIMIT 0 , 30 Hey Radi8, This was my original query, without the INNER JOIN style : $getBookResources = "SELECT author_lastname, author_firstname, title, primary_category, secondary_category FROM BookTable bt, CategoryTable ct WHERE bt.category_id = ct.category_id Same result. I was trying to sniff the traffic, but tcpdump does not give me anything on loopback for this machine. And my mysql won't give me a trace log since my binary was not compiled with debug enabled. I have a few more tricks to try, but not confident they will help. I may have to find/install a new mysql so I can verify mysql sees exactly what I expect. -Lynch Link to comment https://forums.phpfreaks.com/topic/159188-solved-mysql_query-returns-0-rows-but-query-works-in-mysql/#findComment-839971 Share on other sites More sharing options...
bluedragon Posted May 22, 2009 Author Share Posted May 22, 2009 Well, no real answer to the cause except a mysql problem. I dropped all my users, all my tables, and the database, and I recreated from scratch. The join now works. Thanks to all who tried to help. Sorry I didn't find a better answer, but I can move on now... -Lynch Link to comment https://forums.phpfreaks.com/topic/159188-solved-mysql_query-returns-0-rows-but-query-works-in-mysql/#findComment-840295 Share on other sites More sharing options...
Maq Posted May 25, 2009 Share Posted May 25, 2009 Well, no real answer to the cause except a mysql problem. I dropped all my users, all my tables, and the database, and I recreated from scratch. The join now works. Thanks to all who tried to help. Sorry I didn't find a better answer, but I can move on now... -Lynch That's weird... Link to comment https://forums.phpfreaks.com/topic/159188-solved-mysql_query-returns-0-rows-but-query-works-in-mysql/#findComment-841440 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.