Query Runs In Native Mysql But Not Through Php

[EricRueter]

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);

[Jessica]

Is error reporting enabled? And is the "variable hardcoded" in the actual page or just in your example?

Edited September 27, 2012 by Jessica

[EricRueter]

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.

[Jessica]

do a print_r($EntryResult);

What does it output?

Are you sure you're connecting to the same database?

[EricRueter]

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).

[Jessica]

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.

[EricRueter]

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...

[Christian F.]

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.

[EricRueter]

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..

[EricRueter]

 // 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."&nbsp&nbsp&nbsp<b>  AERC NUMBER -> </b>".$RiderAERCNumber;
     echo "<br><b>HORSE -> </b>".$EntryHorseName."&nbsp&nbsp&nbsp<b>  AERC NUMBER -> </b>".$HorseAERCNumber;
     echo "<br><b>DIVISION -> </b>".$EntryDivision."&nbsp&nbsp&nbsp<b>  ENTRY NUMBER -> </b>".$EntryNumber;
     echo "<br><b>RIDE START TIME -> </b>".$RideStartTime;
     echo "<hr></center>";
   }

[Christian F.]

Yeah, noticed that on a second read through. Updated my post above while you were replying.

[Jessica]

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!)

 

 

[EricRueter]

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

[Pikachu2000]

What exactly does happen when you run the script that's failing? Is there anything at all?

[EricRueter]

Correct - I get no rows returned ... I am fairly confident that it is some stupid error on my part ... I just cannot find it.

[PFMaBiSmAd]

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

[Jessica]

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?

[EricRueter]

         $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 ...