how to call mysql procedures in php???

gptArun · 06-05-2008, 07:21 PM

Hi there ,
did you know, how to call mysql procedures in php?? searched on google then find PDO class library - but don't know how to use.
I m new to PHP, please reply ASAP.

Enfernikus · 06-05-2008, 07:33 PM

PHP: MySQL - Manual

essentially if you just want to get back an array of items from the database

PHP Code:


			
$aInfo = array();

//aInfo is now an associative array with all of 'db' info

$aInfo = mysql_fetch_assoc(mysql_query("SELECT * FROM `db`"));

Personally, I hate having to write functions over and over again so I just use this wrapper I made a fair long while ago - essentially you use it like so

PHP Code:


			


$aDbInfo = array('localhost','root','password','db');

$objDb = new mysql_wrap($aDbInfo);



$objInfo = $objDb->query("SELECT `username` WHERE `email`='%s'",$email);

echo $objInfo->fields['username']

[/php]

Though you might want to look up mysqli - or have someone speak of it, I'm not very learned with it.

The aforementioned class.

PHP Code:


			
<?php







define("NO_FIELDS",'--accept-none--');







class mysql_wrap{



    private $connectionID;



    private $queryID;



    private $fieldCount;



        



        public function __construct($host,$user='',$pass='',$table=''){



            if(is_array($host)){



                $host = $host['host'];



                $user = $host['user'];



                $pass = $host['pass'];



                $table = $host['table'];



            }



            



            $this->connectionID = mysql_connect($host,$user,$pass) or user_error("Connection to MySQL server could not be made");



            mysql_select_db($table);



        }



        



        public function reqs(){



            



        }



        



        public function query(){



            $args = func_get_args();



            $argCount = func_num_args();



            if($argCount == 1){



                $sql = $args[0];



                $this->queryID = mysql_query($sql);



                $this->fields = @mysql_fetch_assoc($this->queryID);



                $this->sqlFieldCount = @mysql_num_rows($this->queryID);



                return;



            }elseif($argCount == 2){



                if($args[1] == '--accept-none--'){



                    $this->queryID = @mysql_query($sql);



                    return;



                }



            }



            if(is_array($args[0])){



                $args = $args[0];



                $argCount = count($args);



            }



            



            if($args[$argCount-1] == '--accept-none--'){



                $input = array_splice($args,0,$argCount-1);



                $sql = array_splice($input,0,1);



                //print_r($input);



                $input = call_user_func_array('mysql_real_escape_string',$input);



                $sql = (is_array($sql)) ? $sql : array($sql);



                $input = (is_array($input)) ? $input : array($input);



                $this->queryID = mysql_query(call_user_func_array('sprintf',array_merge($sql,$input)));



                return new Record($this->queryID,$this->connectionID);



                



            }



            



            $sql = array_splice($args,0,1);



            $arguments = array_map('mysql_real_escape_string',$args);



            $sql = (is_array($sql)) ? $sql : array($sql);



            $arguments = (is_array($arguments)) ? $arguments : array($arguments);



            $this->queryID = mysql_query(call_user_func_array('sprintf',array_merge($sql,$arguments)));



            return new Record($this->queryID,$this->connectionID);



            



        }



        



        public function count(){



            return mysql_num_rows($this->queryID);



        }



        



}







class Record{



    private $count;



    private $curCount;



    private $queryID;



    private $connectionID;



    public $fields;



    public $EOF;



    



        public function __construct($queryID,$connectionID){



            $this->queryID = $queryID;



            $this->connectionID;



            $this->count = @mysql_num_rows($count);



            $this->fields = @mysql_fetch_assoc($queryID);



        }



        



        public function movenext($len=0){



            $this->fields = @mysql_fetch_assoc($this->queryID);



            ++$this->curCount;



            



            if($len > 0 and $this->curCount >= $len){



                $this->EOF = true;



            }



            



            if($this->curCount >= $this->count){



                $this->EOF = true;



            }



        }



}

Wildhoney · 06-05-2008, 07:50 PM

Hmmm. Did you not mean stored procedures?

gptArun · 06-05-2008, 07:53 PM

Yes i mean Stored Procedures in mysql

Wildhoney · 06-05-2008, 07:57 PM

Isn't it something like this? I forget now. It's a while since I used stored procedures.

php Code:

$pResult = mysql_query("CALL('stored_procedure_name')");

gptArun · 06-05-2008, 08:05 PM

i even tried mysqli functions but not working. then i found PDO class but PDO Connection class not working.. don't know where is the problem.
Have a look to the attached zip file, including PDO connection class but not working.

Salathe · 06-05-2008, 08:08 PM

Using PDO you can work along the lines of:

PHP Code:


			
$statement = $pdo->prepare('CALL sp_myprocedure(?)');
$statement->execute();
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

gptArun · 06-05-2008, 08:13 PM

Yes this simple code is working for me, but in PDO Connection class, functions not shows any result.

try {
$dbh = new PDO("mysql:host=localhost;dbname=example", "root", "root");
/*** echo a message saying we have connected ***/
echo 'Connected to database<br />';

/*** The SQL SELECT statement ***/
$sql = "CALL myprocedure(parameters)";

/*** fetch into an PDOStatement object ***/
$stmt = $dbh->query($sql);

/*** echo number of columns ***/
$result = $stmt->fetch(PDO::FETCH_NUM);

/*** loop over the object directly ***/
foreach($result as $key=>$val)
{
echo $key.' - '.$val.'<br />';
}

/*** close the database connection ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}