getting the feedback in php

[shadd]

i have a stored procedure as :

Create Procedure Update_studentinfo ( IN p_id INT, IN p_address varchar(20))
    BEGIN
    UPDATE student_info
    SET
    address = p_address WHERE id = p_id;
select "successful as result;"
 END //

And works fine on the server mysql.

how can I get the confirmation in Php

$mSuccessMessage = Update_studentinfo(125, 'Shimla');

 

[gw1500se]

$mSuccessMessage=mysql_query("Update_studentinfo(125, 'Shimla')");

 

[Barand]

TABLE: student_info
+-----+---------+
| id  | address |
+-----+---------+
| 125 | xyz     |
| 126 | bbb     |
+-----+---------+

Procedure

DELIMITER $$

CREATE PROCEDURE `Update_studentinfo`( IN p_id INT, IN p_address varchar(20))
BEGIN
    UPDATE student_info
    SET
    address = p_address WHERE id = p_id;
    select "successful as result";
END$$

DELIMITER ;
;

PHP

$res = $pdo->query("CALL update_studentinfo(125, 'Shimla')");
echo $res->fetchColumn();

Output

successful as result

TABLE student_info

+-----+---------+
| id  | address |
+-----+---------+
| 125 | Shimla  |
| 126 | bbb     |
+-----+---------+

 

[shadd]

On 7/14/2024 at 2:46 AM, Barand said:

TABLE: student_info
+-----+---------+
| id  | address |
+-----+---------+
| 125 | xyz     |
| 126 | bbb     |
+-----+---------+

Procedure

DELIMITER $$

CREATE PROCEDURE `Update_studentinfo`( IN p_id INT, IN p_address varchar(20))
BEGIN
    UPDATE student_info
    SET
    address = p_address WHERE id = p_id;
    select "successful as result";
END$$

DELIMITER ;
;

PHP

$res = $pdo->query("CALL update_studentinfo(125, 'Shimla')");
echo $res->fetchColumn();

Output

successful as result

TABLE student_info

+-----+---------+
| id  | address |
+-----+---------+
| 125 | Shimla  |
| 126 | bbb     |
+-----+---------+

 

what if i used prepared statement inside procedure will it affect the output in php

set @Updateanswer_sql=CONCAT('UPDATE student_info SET
    address =', p_address,'  WHERE id =', p_id);

           PREPARE sUpdateanswer FROM @Updateanswer_sql;
            EXECUTE sUpdateanswer;
            DEALLOCATE PREPARE sUpdateanswer;

select "successful as result";

 

 

[Barand]

The purpose of a prepared statement is to avoid placing variable contents directly into the query (and thus avoid SQL injection attacks) by using placeholders and parameters.

Using a stored procedure does the same thing.

Therefore using a prepared statement inside a stored procedure is a "belt and braces" approach and unnecessary overkill.

You should note that your use of prepare is incorrect as you are not using placeholders, but placing the values into the query via concatenation, and is therefore a waste of time anyway.