Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

PHP 5 - calling MYSQL 5.5 stored procedure

Posted on 2011-04-22
28
Medium Priority
?
807 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;
       }
  }
0
Comment
Question by:brillox
  • 15
  • 5
  • 4
  • +1
28 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35448061
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
0
 

Author Comment

by:brillox
ID: 35448165
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....
0
 

Author Comment

by:brillox
ID: 35448189
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 :-)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:brillox
ID: 35448193
after this line
$resultSet = mysqli_query($mySqlConn,$sqlQuery);

$resultSet is null
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35448196
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.

0
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 800 total points
ID: 35448224
@brillox - it is probably returning FALSE rather than NULL to indicate a failure with the query.

Try running mysqli_error() to see if the database returned an error message.

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

         echo mysqli_error();

http://uk3.php.net/manual/en/mysqli.error.php
0
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 400 total points
ID: 35448227
I recommend using the adodb library. It will make more sense coming from .net. Also, setting fetch_assoc will return arrays which are similar to how the entity framework returns objects.
0
 

Author Comment

by:brillox
ID: 35448251
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
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35448266
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.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35448305
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.
0
 

Author Comment

by:brillox
ID: 35448318
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 ?
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 800 total points
ID: 35448326
Here is a teaching example showing how to connect, select and query a data base table.  It is mysql and not mysqli but the principles of testing for success or failure and visualizing the results of functions, especially if there is a failure, are pretty well shown.  HTH, ~Ray
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// ESCAPING A DATA FIELD FOR USE IN MYSQL QUERIES
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATING AND SENDING A SELECT QUERY AND TESTING THE RESULTS
// MAN PAGE:http://php.net/manual/en/function.mysql-query.php
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
$num = mysql_num_rows($res);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/>$sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $num ROWS OF DATA ";
    echo "<br/>$sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
echo "<pre>\n"; // MAKE IT EASY TO READ
while ($row = mysql_fetch_assoc($res))
{
    // MAN PAGE: http://php.net/manual/en/function.var-dump.php
    var_dump($row);
}




// A WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
// MAN PAGE: http://php.net/mysql_fetch_array
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35448332
there is any debug print option in PHP ?

Please read the man pages!
http://php.net/manual/en/function.var-dump.php
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35448345
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

0
 

Author Comment

by:brillox
ID: 35448353
damn guys you're fast in replying...

:-) :-)
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35448378
Who? Us?

;-)

0
 

Author Comment

by:brillox
ID: 35448393
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
0
 

Author Comment

by:brillox
ID: 35448399
otherwise I have to create a session and get it from the login page
0
 

Author Comment

by:brillox
ID: 35448436
GOT IT !!!!!!
0
 

Author Comment

by:brillox
ID: 35448469
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 ?

0
 

Author Comment

by:brillox
ID: 35448487
got it

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

Author Comment

by:brillox
ID: 35448498
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 ?
0
 

Author Closing Comment

by:brillox
ID: 35448541
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
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35448543
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.
0
 

Author Comment

by:brillox
ID: 35448589
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 :-)
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35448616
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.
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 35448617
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

0
 

Author Comment

by:brillox
ID: 35448702
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 :-)
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question