Member Avatar for khanbaba

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);
   }
}
?>
  • 2 Contributors
  • 1 Reply
  • 134 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by petr.pavel

Dani AI

Generated

Short answer for : the fatal error happens because you used the old mysql_connect() resource and then tried to call object methods (->prepare) on it. is correct — switch to an object-based API (PDO or mysqli) and send NULLs for empty form fields so your stored-proc NULL checks work as intended.

A compact PDO example (robust error handling, pass NULLs when inputs are empty):

try {
  $pdo = new PDO('mysql:host=localhost;dbname=publication_search;charset=utf8mb4',
                 'shahid','khan',[
                   PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                   PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                 ]);
  $stmt = $pdo->prepare('CALL searchPublications(?,?,?,?,?,?,?)');
  $params = [
    $author_keyword !== '' ? $author_keyword : null,
    $pub_title_keyword !== '' ? $pub_title_keyword : null,
    $co_author_keyword !== '' ? $co_author_keyword : null,
    $group_name_keyword !== '' ? $group_name_keyword : null,
    $year1 !== '' ? (int)$year1 : null,
    $year2 !== '' ? (int)$year2 : null,
    $select !== '' ? $select : null,
  ];
  $stmt->execute($params);
  $rows = $stmt->fetchAll();
  print_r($rows);
} catch (PDOException $e) {
  echo 'DB error: '.$e->getMessage();
}

If you prefer mysqli (OOP style), prepare the CALL, bind with the correct type string ('ssssiis' for 4 strings, 2 ints, 1 string), then execute and fetch. Note: get_result() requires mysqlnd; otherwise use bind_result() with a fetch loop.

Quick troubleshooting checklist:

  • Replace mysql_* with PDO or mysqli (mysql_* is removed in modern PHP).
  • If prepare() returns false, dump the error ($pdo->errorInfo() or $mysqli->error).
  • Pass actual SQL NULL for empty inputs so IN param IS NULL checks work.
  • Confirm the stored-proc signature matches the number/types of parameters you send.
  • Watch for multiple result sets from procedures (use PDOStatement::nextRowset() or mysqli multi-query handling if needed).

This addresses the fatal error and gives two practical paths (PDO or mysqli) to call your stored procedure safely.

Member Avatar for petr.pavel

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;

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.