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 Quote 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 ); Quote 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. Quote 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 Quote 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>'; Quote 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 Quote 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 Quote 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 Quote 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 Quote 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... Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.