Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2016-09-08
6
74 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 109

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need to remove %20 from url in get 17 36
How to set the Tinymce Editor image path 4 22
Row insertion failed. Array 5 48
Log in through ID 5 18
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

838 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