Member Avatar for PHPExperts

hello,

Please help me to create and using mysql procedures using php scripts.

I searched in web for the solution but i didn't get a perfect support from anywhere .

Thanks
Anoop Ravi

  • 3 Contributors
  • 2 Replies
  • 96 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by twiss

Dani AI

Generated

Short practical guide for that complements 's pointer to the docs and answers 's "what's wrong" question with common causes and fixes.

MySQL side: create routines with CREATE PROCEDURE and remember to change the client delimiter when defining body in the mysql CLI or scripts:

DELIMITER //
CREATE PROCEDURE AddUser(IN p_name VARCHAR(50), OUT p_id INT)
BEGIN
  INSERT INTO users(name) VALUES (p_name);
  SET p_id = LAST_INSERT_ID();
END //
DELIMITER ;

PHP side: avoid the old mysql extension (removed in PHP 7). Use mysqli or PDO. A simple pattern for an OUT parameter is to call the procedure with a user variable and then select it:

$mysqli = new mysqli('host','user','pass','db');
$stmt = $mysqli->prepare("CALL AddUser(?, @new_id)");
$stmt->bind_param('s', $name);
$stmt->execute();
$stmt->close();

$res = $mysqli->query("SELECT @new_id AS id");
$id = $res->fetch_assoc()['id'];

If the procedure returns result sets, ensure all result sets are consumed before issuing further queries (use mysqli->next_result() or mysqli->multi_query()), otherwise subsequent SELECT @var may hang or return nothing.

Troubleshooting checklist: verify CREATE ROUTINE privilege; use proper DELIMITER when creating procedures; confirm server supports stored routines; prefer mysqli/PDO for prepared calls; flush remaining result sets when mixing result sets and OUT parameters.

Recommended Answers

All 2 Replies

Member Avatar for twiss

What solutions don't you find "perfect"? What's wrong with them?

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.