+5 votes
213 views
Warehouse procedures with parameters from PHP

in Databases by (552k points)
reopened | 213 views

1 Answer

+3 votes
Best answer

The stored procedures are very useful to encapsulate database operations and save time in development.

When you need to maintain a database with many transactions and some confidentiality, you can have a team of developers who do code without knowing how the database is.

For this we will use stored procedures

Example
We create a database for a real estate and a table for real estate (we use few fields to not generate extensive code and that may confuse)

 CREATE TABLE IF NOT EXISTS `real estate` ( `id` int (11) NOT NULL AUTO_INCREMENT, `typemovable` varchar (100) DEFAULT '0', `price` decimal (10,2) DEFAULT '0.00', `description` text, PRIMARY KEY (`id`) ) ENGINE = MyISAM AUTO_INCREMENT = 195 DEFAULT CHARSET = latin1; 


I create a new home stored procedure to insert real estate into the database. When defining it, I assign it as a parameter the data it must accept, in this case, property, price and description, each with its mysql format, I also write the INSERT action



image


Then we create a html data entry form to enter the values ​​of the new properties.



image


[color = # 000000] Here we send the information to a file save.php that will call the stored procedure and it will record the data. [/ color]


[color = # 000000] To do this we must previously have created the code to connect to the database. [/ color]
[color = # 000000] Create a file config.php [/ color]



<? php

/ / Chain of connection to database

$ link = mysql_connect ('localhost', 'user', 'password');

/ / connection to database

if (! $ link) {

die ('Not connected:'. mysql_error ());

}

// Select database

$ db = 'dbReal Estate';


if (! mysql_select_db ($ db)) {

die ('Error:'. mysql_error ());

}?>



image


In the image we can see how the stored procedure is called by the SQL CALL statement



CALL new house (parametro1, parametro2, ...)



We create another procedure stored in the database to consult by type of property.



CREATE procedure listarinmueble (type varchar (150))

[indent = 1] SELECT * FROM real estate WHERE property LIKE type


In addition to using it in php we can make the query from any software that supports sql and mysql in this case.

We test the stored procedure listmobile by type in this case from the mysql Heidisql administration software

CALL listarinmueble ('house')

The result can be seen below as it has filtered by type of property.


image


We can also create a stored procedure to delete data where we will send as parameter the id

[color = # 000000] CREATE procedure removeinfurniture (idinmueble INT) [/ color]
[color = # 000000] SELECT * FROM real estate WHERE id = idinmueble [/ color]

Then we call the stored procedure as follows

CALL removemovable (172)


The interesting thing about this is that if we were database administrators of a very complex system, we could make available to the programmers, only the list of procedures and not the structure of the database, which provides greater confidentiality.

Allowing them to interact with the data but not with the structure, even if they can only read but not modify. This would be a topic for large database systems with many people interacting where security and confidentiality is important.

The best known databases that support stored procedure are mysql, firebird, ms sql server, oracle, db2 postgresql, sybase (first to create a database transaction language for sql called Transact-SQL).

by (3.5m points)
edited

Related questions

+5 votes
1 answer
+5 votes
1 answer
asked Jun 23, 2019 in Databases by backtothefuture (552k points) | 436 views
+5 votes
1 answer
asked Jun 23, 2019 in PHP by backtothefuture (552k points) | 260 views
+5 votes
1 answer
+5 votes
1 answer
Sponsored articles cost $40 per post. You can contact us via Feedback

Most popular questions within the last 30 days

10,659 questions
10,791 answers
510 comments
3 users