Solved

Can Create single (dynamic)  store procedure for Insert,Update, Delete of 100 of tables.

Posted on 2016-09-08
6
77 Views
Last Modified: 2016-09-26
Hello,
 I want to create single store procedure in php mysql for all operations. Where I want pass all parameters including table name and column name. I don't want to create 100 procedure for my 100 table. Insted of creating 100 procedure want to create single procedure where i can perform my 100 tables operations.
0
Comment
Question by:Pravin Bnakar
6 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41790769
And what's hindering you? You create an sql string and send it to MySQL, you can compose it as needed.
Have a look at ActiveRecord (as used by Laravel, for example): http://www.phpactiverecord.org/

Bye, Olaf.
1
 

Author Comment

by:Pravin Bnakar
ID: 41790778
I am not getting what you want to say.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41790781
ActiveRecord does what you want to do. Take a look at its source code. Or simpler: Take it and use it.

Bye, Olaf.
1
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41790855
If you do not understand the meaning of the Active Record design pattern and you're not willing to invest the time to learn it, you might want to consider using E-E Gigs.  You can get a professional programmer to help you with the details of the implementation!  

Your required solution may not have to be the classic AR design pattern; it may be possible to use any of a number of CRUD-type abstraction layers.  But I think Olaf has you on the right track.
0
 
LVL 31

Accepted Solution

by:
Marco Gasi earned 500 total points
ID: 41790861
AFAIK a stored procedure can't include more than one single op: you can have a stored procedure for SELECT, one for INSERT and so on.
So you can use ActiveRecord and use its abstraction to do the same thing.
Alternatively, you could write your own class with methods to do exactly what you want. You could modify a class like this one: http://code.tutsplus.com/tutorials/create-a-php5-framework-part-2--net-805
or just keep it as it is and use it in a loop for all 100 tables.

Maybe you can be more specific in your question to help us to give you better answers :)
1
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41791047
In fact ActiveRecord does not work with Stored Procs, but prepared PDO Statements. I still recommend this, because you don't really profit from 100 of stored procs nor 1 stored proc. I can reuse this for the third time now, to spare my time: http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/

Prepared statements give you all the advantages you may get from stored procs "only" anyway. And a single stored proc can only do the same as ActiveRecord does, build up an sql command string and prepare/execute it.

Bye, Olaf.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question