To search using a stored procedure in PHP, you can use the mysqli_query
function to call the stored procedure and pass any necessary parameters.
Here is an example of calling a stored procedure that searches for a user by their name:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
$mysqli = new mysqli("localhost", "my_user", "my_password", "my_database"); $name = "John"; $query = "CALL searchUser(?)"; $stmt = $mysqli->prepare($query); $stmt->bind_param("s", $name); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // do something with the results echo $row['id'] . " " . $row['name'] . "<br>"; } $stmt->close(); $mysqli->close(); |
This will call the stored procedure searchUser
and pass in the $name
parameter. The results of the stored procedure will be returned and can be accessed in the while
loop.
Make sure to replace "localhost"
, "my_user"
, "my_password"
, and "my_database"
with the correct values for your database connection.
Here is a complete example of using a stored procedure to search for a user by their name in PHP:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
<?php $mysqli = new mysqli("localhost", "my_user", "my_password", "my_database"); // Check connection if ($mysqli->connect_error) { die("Connection failed: " . $mysqli->connect_error); } $name = "John"; $query = "CALL searchUser(?)"; $stmt = $mysqli->prepare($query); $stmt->bind_param("s", $name); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { echo $row['id'] . " " . $row['name'] . "<br>"; } $stmt->close(); $mysqli->close(); |
This code will connect to the database and call the stored procedure searchUser
, passing in the $name
parameter. The results of the stored procedure will be retrieved and displayed in a while
loop.
Make sure to replace "localhost"
, "my_user"
, "my_password"
, and "my_database"
with the correct values for your database connection.
You will also need to have a stored procedure named searchUser
in your database, with a parameter for the search term. The stored procedure should return a result set with the matching rows.
Here is an example of a stored procedure that searches for a user by their name:
1 2 3 4 5 6 7 8 9 10 |
DELIMITER $$ CREATE PROCEDURE searchUser(IN name VARCHAR(255)) BEGIN SELECT * FROM users WHERE name LIKE CONCAT('%', name, '%'); END $$ DELIMITER ; |
This stored procedure takes a single parameter, name
, and searches the users
table for any rows where the name
column contains the search term.
To call this stored procedure from PHP, you can use the following code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
$mysqli = new mysqli("localhost", "my_user", "my_password", "my_database"); $name = "John"; $query = "CALL searchUser(?)"; $stmt = $mysqli->prepare($query); $stmt->bind_param("s", $name); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { echo $row['id'] . " " . $row['name'] . "<br>"; } $stmt->close(); $mysqli->close(); |
This code will call the stored procedure searchUser
and pass in the $name
parameter. The results of the stored procedure will be retrieved and displayed in a while
loop.
Make sure to replace "localhost"
, "my_user"
, "my_password"
, and "my_database"
with the correct values for your database connection.