Hi

Can anyone please help me sort this problem out

I create store procedure in mysql and I want to call this in php. The store procedure is executing ok in mysql but when I call this into php I get fatal error: call to a member function prepare() on a non-object in (path of file) store procedure line. can you tell be with below codes that where I am doing wrong. please help me thanks

Below is my store procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `publication_search`.`searchPublications` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `searchPublications`(IN author VARCHAR(100),

                  IN publication_title VARCHAR(250),

                  IN co_author VARCHAR(100),

                  IN group_name VARCHAR(250),

                  IN fromDate INT,

                  IN toDate INT,

                  IN paper_type VARCHAR(100))

BEGIN

SELECT * from publication p

where

                 author is NULL OR (

                 p.pub_id IN

                 (SELECT sp.pub_id from staff_publication sp

                  where sp.staff_id =

                  (select  s.staff_id from staff s where

                  s.name = author))) AND

       (title is NULL OR

       p.title = title) AND

       (co_author is NULL OR p.co_author = co_author) AND

       (group_name is NULL OR (p.pub_id IN (SELECT gp.pub_id from group_publication gp

                                             where gp.Group_id = (SELECT g.group_id from group1 g

                                                   where g.group_name = group_name)) )) AND

       ((fromDate is NULL OR p.year >= fromDate) AND

        (toDate is NULL OR p.year <= toDate)) AND

        (paper_type is NULL OR p.type = paper_type);

 

 

END $$

 

DELIMITER ;

___________________________________________________________________________

//Here is my pho code where I connect this first to database and then call store procedure in php. I have searching form so when I click on search button that connect to the database but not calling store proceduer

<?php
$submit = $_POST['submit'];

if($submit)
{
$author_keyword = $_POST['author_keyword'];
$pub_title_keyword = $_POST['pub_title_keyword'];
$co_author_keyword = $_POST['co_author_keyword'];
$group_name_keyword = $_POST['group_name_keyword'];
$year1 = $_POST['year1'];
$year2 = $_POST['year2'];
$select = $_POST['select'];

$server = "localhost";
$user = "shahid";
$password = "khan";
$database_name = "publication_search";

//database connection (1. creation)
$connection = @mysql_connect($server,$user,$password); //method to connect with mysql (three parameters server,user,password

//error detection when connecting to mysql

if (!$connection)
{
	die("connection with MySQL is failed".mysql_error()); //die() is for print the message and will show the error 
}//end of if (connection error)
else
echo "Database is connected";

//(2. selecting a database
	$database_select = mysql_select_db($database_name,$connection);

if(!$database_select)
{
	die("database selection is failed".mysql_error()); //die() is for print the message and show the error
}
else
echo"<br> Database is selected Congrate!";
$mysql = 'CALL searchPublications(?,?,?,?,?,?,?)';
$stmt = $connection -> prepare($mysql);
$author_keyword;
$pub_title_keyword;
$con_author_keyword;
$group_name_keyword;
$year1;
$year2;
$select;

$stmt -> bindParam(1, author, PRO:: PARM_STR, 100);
$stmt -> bindParam(2, publication_title, PRO:: PARM_STR, 250);
$stmt -> bindParam(3, co_author, PRO:: PARM_STR, 100);
$stmt -> bindParam(4, group_name, PRO:: PARM_STR, 250);
$stmt -> bindParam(5, fromDate, PDO:: PARAM_INT);
$stmt -> bindParam(6, toDate, PDO:: PARAM_INT);
$stmt -> bindParam(7, paper_type, PRO:: PARM, 100);

$rows = $stmt ->fetchAll(PRO::FETCH_NUM);
if ($rows) {
       print_r($rows);
   }
}
?>

Hi Khanbaba,
the problem is not related to your stored procedure.
mysql_connect doesn't return an object but a resource pointer. Therefore you cannot use

$resource = mysql_connect(...);
$resource->prepare(...);

You have probably confused it with mysqli() set of functions:

$mysqli = new mysqli(...);
$stmt = $mysqli->prepare(...);

btw: if you want to display a value of $author_keyword you have to type echo $author_keyword; not just $author_keyword;

This article has been dead for over six months. Start a new discussion instead.