bluedragon
-
Posts
5 -
Joined
-
Last visited
Never
Everything posted by bluedragon
-
[SOLVED] mysql_query returns 0 rows but query works in mysql
bluedragon replied to bluedragon's topic in PHP Coding Help
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 -
[SOLVED] mysql_query returns 0 rows but query works in mysql
bluedragon replied to bluedragon's topic in PHP Coding Help
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 -
[SOLVED] mysql_query returns 0 rows but query works in mysql
bluedragon replied to bluedragon's topic in PHP Coding Help
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. -Lynch -
[SOLVED] mysql_query returns 0 rows but query works in mysql
bluedragon replied to bluedragon's topic in PHP Coding Help
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 -
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