EricRueter Posted September 27, 2012 Share Posted September 27, 2012 I have a simple SQL Query that when run in PHPMyAdmin, or MySQL Workbench it produces the desired results. However when run via a PHP page, it returns nothing and does not produce an error. The problem -seems- to be the WHERE clause. If I remove the WHERE, the PHP version returns rows (obviously many more than desired). Any thoughts? Below is the SQL from the PHP page with the variable hard coded. Note - I have an extensive background with Oracle, but have only "played" with PHP and MySQL, so please do not laugh at my code... // get the chosen value $strEntry = "SELECT lbentries.LBEntryID , lbentries.FirstName , lbentries.LastName , CONCAT_WS(', ',lbentries.LastName,lbentries.FirstName) AS RiderName , lbentries.EntryHorseName , lbentries.RideName , lbentries.HorseAERCNumber , lbentries.HorseAERCID , lbentries.HorseAHANumber , lbentries.RiderAERCNumber , lbentries.RiderAERCID , lbentries.RiderAHANumber , lbentries.EntryNumber , lbentries.EntryDivision , lbentries.RideID , DATE_FORMAT(lbrides.RideStartTime,'%H:%i') RideStartTime FROM lbentries JOIN lbrides ON lbrides.RideID = lbentries.RideID WHERE lbentries.LBEntryID = 333"; $dbconnection = mysqli_connect($host,$user,$password,$database) or die ("Could not sign on database - LeaderBoardDetailResults.php - strEntry - $strEntry"); $EntryResult = mysqli_query($dbconnection, $strEntry) or die ("Could not execute query - LeaderBoardDetailResults.php - strEntry - $strEntry"); echo $strEntry; echo ("Num Rows = ").mysqli_num_rows($EntryResult); Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/ Share on other sites More sharing options...
Jessica Posted September 27, 2012 Share Posted September 27, 2012 (edited) Is error reporting enabled? And is the "variable hardcoded" in the actual page or just in your example? Edited September 27, 2012 by Jessica Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381295 Share on other sites More sharing options...
EricRueter Posted September 27, 2012 Author Share Posted September 27, 2012 To test this the variable is/was/has been hardcoded in the PHP page. I also intentionally changed a column name at one point to verify I was actually capturing errors. So yes, error reporting is turned on. Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381300 Share on other sites More sharing options...
Jessica Posted September 27, 2012 Share Posted September 27, 2012 do a print_r($EntryResult); What does it output? Are you sure you're connecting to the same database? Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381301 Share on other sites More sharing options...
EricRueter Posted September 27, 2012 Author Share Posted September 27, 2012 SELECT lbentries.LBEntryID , lbentries.FirstName , lbentries.LastName , CONCAT_WS(', ',lbentries.LastName,lbentries.FirstName) AS RiderName , lbentries.EntryHorseName , lbentries.RideName , lbentries.HorseAERCNumber , lbentries.HorseAERCID , lbentries.HorseAHANumber , lbentries.RiderAERCNumber , lbentries.RiderAERCID , lbentries.RiderAHANumber , lbentries.EntryNumber , lbentries.EntryDivision , lbentries.RideID , DATE_FORMAT(lbrides.RideStartTime,'%H:%i') RideStartTime FROM lbentries JOIN lbrides ON lbrides.RideID = lbentries.RideID WHERE lbentries.LBEntryID = 333 Num Rows = 0 mysqli_result Object ( [current_field] => 0 [field_count] => 16 [lengths] => [num_rows] => 0 [type] => 0 ) This PHP page is called from another PHP page, which passes in the LBEntryID. The calling page is attached to the same DB (via an included connect) so I am fairly sure I am in the correct (or at least the same DB). Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381302 Share on other sites More sharing options...
Jessica Posted September 27, 2012 Share Posted September 27, 2012 DATE_FORMAT(lbrides.RideStartTime,'%H:%i') RideStartTime should be DATE_FORMAT(lbrides.RideStartTime,'%H:%i') AS RideStartTime If you run that exact query in phpmyadmin it doesn't give you an error for that? Because it should. Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381303 Share on other sites More sharing options...
EricRueter Posted September 27, 2012 Author Share Posted September 27, 2012 Cut and paste -- That *may* be a new typo from when I removed the join to see if the join was wrong. I just corrected it and still have the problem... been scratching my head and fiddling with this for two days... Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381305 Share on other sites More sharing options...
Christian F. Posted September 27, 2012 Share Posted September 27, 2012 (edited) Jessica: The "AS" keyword is optional, I'm afraid. So that shouldn't give any errors. Though, I agree that it should be added none the less, for no other reason than clarity. Eric: Is that result from the page with the error condition, or the one that works? If the latter, please try the same from the page that has the problem, and post the results here. Preferably use the [code][/code] tags around the paste as well, as it'll help make it easier to read. Never mind, just re-read the post and realized it was from the erroneous page. Tried echoing out the database connection variables, or executed a test query against the table. To verify that there aren't anything going on with the DB connection details, or some strangeness about the data/query. Only thing I can think of now is that the DB user that you're using might not have the proper privileges to the database/table, and since you're not checking with mysqli_error () it's hard to say for sure. Error message might be hidden there, after all. Edited September 27, 2012 by Christian F. Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381306 Share on other sites More sharing options...
EricRueter Posted September 27, 2012 Author Share Posted September 27, 2012 The result listed is a cut and paste straight from the page. There is no error showing. I have echos in the code to show the exact SQL, the number of rows returned (Num Rows = 0) and the print_r that Jessica asked for. That seems to be the root problem is the SQL returns no rows in the PHP page - yet the same SQL returns one row elsewhere. I will be happy to include the entire PHP page if that will help. I am old and would like to save what little hair I have left.. Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381308 Share on other sites More sharing options...
EricRueter Posted September 27, 2012 Author Share Posted September 27, 2012 // get variable after selecting something from the dropdown with name 'chooser' if (!empty($EntrySelected)) { // get the chosen value $strEntry = "SELECT lbentries.LBEntryID , lbentries.FirstName , lbentries.LastName , CONCAT_WS(', ',lbentries.LastName,lbentries.FirstName) AS RiderName , lbentries.EntryHorseName , lbentries.RideName , lbentries.HorseAERCNumber , lbentries.HorseAERCID , lbentries.HorseAHANumber , lbentries.RiderAERCNumber , lbentries.RiderAERCID , lbentries.RiderAHANumber , lbentries.EntryNumber , lbentries.EntryDivision , lbentries.RideID , DATE_FORMAT(lbrides.RideStartTime,'%H:%i') AS RideStartTime FROM lbentries JOIN lbrides ON lbrides.RideID = lbentries.RideID WHERE lbentries.LBEntryID = 333"; $dbconnection = mysqli_connect($host,$user,$password,$database) or die ("Could not sign on database - LeaderBoardDetailResults.php - strEntry - $strEntry"); $EntryResult = mysqli_query($dbconnection, $strEntry) or die ("Could not execute query - LeaderBoardDetailResults.php - strEntry - $strEntry"); echo $strEntry; echo ("Num Rows = ").mysqli_num_rows($EntryResult); print_r($EntryResult); // keeps getting the next row until there are no more to get while ($row = mysqli_fetch_assoc($EntryResult)) { extract($row); echo "<center>"; echo "<b>RIDER -> </b>".$RiderName."   <b> AERC NUMBER -> </b>".$RiderAERCNumber; echo "<br><b>HORSE -> </b>".$EntryHorseName."   <b> AERC NUMBER -> </b>".$HorseAERCNumber; echo "<br><b>DIVISION -> </b>".$EntryDivision."   <b> ENTRY NUMBER -> </b>".$EntryNumber; echo "<br><b>RIDE START TIME -> </b>".$RideStartTime; echo "<hr></center>"; } Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381309 Share on other sites More sharing options...
Christian F. Posted September 27, 2012 Share Posted September 27, 2012 Yeah, noticed that on a second read through. Updated my post above while you were replying. Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381310 Share on other sites More sharing options...
Jessica Posted September 27, 2012 Share Posted September 27, 2012 You're sure it's the same database? IE, you don't have a prod/dev database either on same or different servers, with similar names? I had the same problem last week and after 2 hours of working on it figured out someone had made a copy of the DB with a misspelled name with one letter off, and in phpMyAdmin I was looking at the misspelled one (simply because it was first in the list!) Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381311 Share on other sites More sharing options...
EricRueter Posted September 27, 2012 Author Share Posted September 27, 2012 This is from the calling page - it is a straight listing (table) of the LBEntries MySQL table (with a couple of other tables joined) <?php // Common code and routines include("../SERAMySQL.php"); require_once("../PHPUtility/PHPMySQLUtilities.php"); // Make Database Connections setDBconnections("leaderboard"); ?> and this is from the failing page [code] <?php // Common code and routines include("../SERAMySQL.php"); require_once("../PHPUtility/PHPMySQLUtilities.php"); // Make Database Connections setDBconnections("leaderboard"); ?> Both pages are in the same directory... In the setDBconnections I set the user/host/pwd/db to global variables Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381312 Share on other sites More sharing options...
Pikachu2000 Posted September 27, 2012 Share Posted September 27, 2012 What exactly does happen when you run the script that's failing? Is there anything at all? Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381313 Share on other sites More sharing options...
EricRueter Posted September 27, 2012 Author Share Posted September 27, 2012 Correct - I get no rows returned ... I am fairly confident that it is some stupid error on my part ... I just cannot find it. Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381314 Share on other sites More sharing options...
PFMaBiSmAd Posted September 27, 2012 Share Posted September 27, 2012 (edited) This PHP page is called from another PHP page, which passes in the LBEntryID. STOP or at least slow down. Your actual php code is putting an external value into the query. You probably have some white-space/non-printing character in front of the value that is evaluating to a zero value. When you copy/paste the query, only the printing characters are copied. Post the code for the page that is calling this page, showing the links/form that submits to this page. Edited September 27, 2012 by PFMaBiSmAd Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381315 Share on other sites More sharing options...
Jessica Posted September 27, 2012 Share Posted September 27, 2012 Your actual php code is putting an external value into the query. You probably have some white-space/non-printing character in front of the value that is evaluating to a zero value. When you copy/paste the query, only the printing characters are copied. I think he said he hardcoded the 333 value in, but do you think if the 333 was copied/pasted from something it could have the non printing character still? Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381316 Share on other sites More sharing options...
EricRueter Posted September 27, 2012 Author Share Posted September 27, 2012 $Parm1 = $row['LBEntryID']; $Parm2 = $row['EntryNumber']; echo "<tr><td>"; echo "<a href='http://localhost/LeaderBoard/LeaderBoardDetailResults.php?LBEntryID=$Parm1' target='_blank'>$Parm2</a>"; Ouch!!!! Did anyone else hear that loud noise? That was me removing my head from my ... well let's just say THANK YOU! notice where I am loading the new page from??? Not where it should come from THANK YOU --- fixed my problem ... Quote Link to comment https://forums.phpfreaks.com/topic/268848-query-runs-in-native-mysql-but-not-through-php/#findComment-1381317 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.