We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

PHP 5 - calling MYSQL 5.5 stored procedure

brillox
brillox asked
on
Medium Priority
829 Views
Last Modified: 2012-05-11
Hi experts,

first of all let me say that I am a DOT NET programmer who for the FIRST time has to code in PHP. I am poirting a web application from DOT NET to PHP. this mean that I am an absolute newbie in PHP with only 2 weeks of experience.

I am trying to get a resultset from PHP via  astored procedure in MYSQL 5.5

I tested the stored procedure in the database and it works, however when I try to get the  records via PHP I always get a NULL recordset.

below my code. I would appreciate your help with this.


public function authenticateUser($username, $password)
  {
     $dbMan = new dbConnect();
     $dbName = NULL;
     $mySqlConn = NULL;
     $resultSet = NULL;
     $totalRows = 0;
     $sqlQuery = 'call sp_user_authentication(' .$username .',' .$password .')';

     if($dbMan->Connect())
       {
         
          $dbName = $dbMan->getDbName();
          $mySqlConn = $dbMan->getmySqlConn();
          mysqli_select_db($mySqlConn, $dbName);
          $resultSet = mysqli_query($mySqlConn,$sqlQuery);

         if($resultSet != NULL)
           {
              $totalRows = mysqli_num_rows($resultSet);
              return $resultSet;
           }
         else
           {
             return false;
           }
       }
    else
       {
         return false;
       }
  }
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
Run, don't walk, to buy this book right now:
http://www.sitepoint.com/books/phpmysql4/

Add error_reporting(E_ALL); to the top of all your PHP scripts.

Next let's look at the PHP man page for mysqli.
http://php.net/manual/en/mysqli.query.php

There you will see that the function returns a "mixed" result into the $resultSet variable.  It will be FALSE, a result object or TRUE.  So a test for != NULL does not really make sense.

In PHP you may find that good data visualization is very helpful for things like this problem.  You can use this function to print out the contents of your variables:
http://us2.php.net/manual/en/function.var-dump.php

Hope that helps, ~Ray

Author

Commented:
Thanks Ray,
Run, don't walk...

I wish I could... I ahve to complete this in 2 weeks time is work related I am afraid.

I  changed from mysqli to mysql, same result... nothing

I'll have a look at your links....

Author

Commented:
Add error_reporting(E_ALL); to the top of all your PHP scripts.
I have this set into my php.ini; I am using x_debug and I see the error info

You can use this function to print out the contents of your variables:
I am using netbeans IDE and while debugging I see my variables.. although I did not had a look at them yet :-)

Author

Commented:
after this line
$resultSet = mysqli_query($mySqlConn,$sqlQuery);

$resultSet is null
CERTIFIED EXPERT

Commented:
Where you have

         if($resultSet != NULL)
           {
              $totalRows = mysqli_num_rows($resultSet);
              return $resultSet;
           }


Change to

         if( $resultSet )
              if ( mysqli_num_rows($resultSet) == 1 )
                  return true;

To access data in the resultSet see this page http://uk.php.net/manual/en/mysqli-result.fetch-assoc.php for examples. The resultSet is NOT the data returned from running the query, it is an intermediate form of data used to pull the result data one record at a time from the database.

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
bportlock you are right is returning false; I am running your code ina  minute

aarontomosky: I am afraid that mYSQL is a constraint for this project, apologies for my ignorance if adodb library can work with mysql; I am not aware of it
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
Adodb can work with many databases.  It's an abstraction layer library. I use it for all my php projects whether using mysql or mssql. It also makes it easier to change the db if you need to.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
adodb may be a really good idea.

Here is how you might print out a variable:
var_dump($resultSet);

Most of the PHP functions have some kind of return value.  For example, see this:
http://php.net/manual/en/mysqli.select-db.php

So if you write a line of code like this:
mysqli_select_db($mySqlConn, $dbName);

... You have no way of knowing whether it worked or not.  The script should test for FALSE and react as shown in the examples on the man page.

Author

Commented:
Thanks aarontomosky; I will research on this; unfortunately when I cannot do something I do not give up until I do it. In this case I would like firts to try witth these libraries before doing it with adodb. However I will certainly take your suggestion in consideration.

bportlock
I am still trying to figure out how to display the mysql error; I had to add a parameter
mysqli_error($mySqlConn)
but how can I echo froma  class file ? there is any debug print option in PHP ?
Most Valuable Expert 2011
Author of the Year 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2011
Author of the Year 2014

Commented:
there is any debug print option in PHP ?

Please read the man pages!
http://php.net/manual/en/function.var-dump.php
CERTIFIED EXPERT

Commented:
In PHP "debug print" is usually done via var_dump or print_r

http://www.php.net/print_r
http://www.php.net/var_dump

Author

Commented:
damn guys you're fast in replying...

:-) :-)
CERTIFIED EXPERT

Commented:
Who? Us?

;-)

Author

Commented:
ok.. pause :-)

I am suing netbeans also for the first time (normally I use Visual Studio)

by doing this:
  $resultSet = mysqli_query($mySqlConn,$sqlQuery);
       print_r(mysqli_error($mySqlConn));where the heck I can see the print_r result ? output window is empty

Author

Commented:
otherwise I have to create a session and get it from the login page

Author

Commented:
GOT IT !!!!!!

Author

Commented:
basically in my query I am passing this:

call sp_user_authentication(guest,guest)

rather than
call sp_user_authentication('guest','guest')

so in mysqli_error($mySqlConn) I get "Unknown column guest"


I guess that I need firts to realise how properly debug and get variables values !!!

so the problem is in this line
$sqlQuery = 'call sp_user_authentication(' .$username .',' .$password .')'

can someone suggest me how to add '  '   to each sp parameter ?

Author

Commented:
got it

$sqlQuery = 'call sp_user_authentication(' ."'" .$username ."'," ."'" .$password ."'" .')';

Author

Commented:
SUCCESS !!!!

thanks to all of you guys

I guess I am splitting the points with all of you because each of you gave me very good advices and links to follow ?

Author

Commented:
these guys replied at an incredible speed.. we were more like chatting rather than waiting for answers and all of them gave me an invaluable amount of tips and good suggestions
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
A good rule to follow is build your query, echo your query, run your query. I do most of my php without an IDE since I don't get along with netbeans or eclipse very well. It will be wierd for you being used to visual studio, hands down the best IDE.

Author

Commented:
Hi aarontomosky

I have to admit that Vs is very good, but I guess that maybe is also true that I feel weird with this new IDE because I did not used before

It is true though that what I am trying to do since this morning, it would have accomplished in DOT NET in less than 1 hour; but again.. I am a toal newbie in PHP so. cannot blame anything else than myself :-)
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
Just try to take small steps of code and we are here to help. It's the "something in these 500 lines is broke" that's hard to help with.
CERTIFIED EXPERT

Commented:
Glad you've got it sorted. PHP, like any programming language, has its own odd little ways but once you are used to them you can fly along.

I would be just as lost in .net

Author

Commented:
Thanks guys... Appreciated

if only i could have a bit more time than the mid of may :-)

anyway rest assured that in the following weeks you will hear about me a lot :-)

after this little success (thanks to all of you ) and considering that it is 17|:24 I think I cna now get off my office and relax at the gym.. but this evening... I will be back on PHP

How sad I am :-)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.