Timeline for Outputting results from a conditional row count across multiple tables
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 26, 2014 at 23:56 | history | edited | Phrancis | CC BY-SA 3.0 |
added 29 characters in body
|
| Jun 26, 2014 at 23:25 | comment | added | CodeX | Yeah a stored procedure for this is overkill but good to know they exist. There is a reason why I can't merge the tables, although that would be the ideal solution it just isn't viable, in future I need to learn to design my databases and workout any relations that will be needed beforehand, thanks again for your help on this | |
| Jun 26, 2014 at 22:38 | comment | added | 200_success |
I'm not convinced that a stored procedure is justified for such a simple query. If you wanted to optimize the SQL, you would be much better off merging the four tables as I suggested, as well as ensuring that indexes are in place for the active column(s).
|
|
| Jun 26, 2014 at 21:46 | comment | added | CodeX | Haha, indeed, I rely on php too much I think, MySQL can do a lot more than I realised and it's something I need to put more time into, thanks for the inspiration | |
| Jun 26, 2014 at 21:39 | comment | added | Phrancis | Cool. I have a tendency to lean towards letting SQL do as much of the data crunching as possible, since it's what it does best, but I'm biased because I'm a SQL programmer. As they say, when all you have is a hammer, everything looks like a nail lol. | |
| Jun 26, 2014 at 21:37 | comment | added | CodeX |
Oh, wow looking at phpmyadmin i can see it has an add routine button that allows you to create a procedure / function, that is very interesting indeed and something i will experiment with, thankyou
|
|
| Jun 26, 2014 at 21:32 | comment | added | Phrancis |
I don't know much about PHP but from my understanding you could call it from anywhere in your PHP script where you would otherwise write your whole SQL query. As far as storing it, it's stored in the RDBMS itself at this location: yourdatabasename.information_schema.routines
|
|
| Jun 26, 2014 at 21:28 | comment | added | CodeX |
I have never heard of a stored procedure and googling has come back with some very interesting info, list of pros and cons here code.tutsplus.com/articles/… I work a lot with functions / classes and will probably build a function that does the same thing as my above code with the ability to pass in variables making it reusable for other SUM()'s. Just out of interest where would i place this procedure if i wanted to call it in a function within a class, does it need a separate file?
|
|
| Jun 26, 2014 at 21:08 | history | answered | Phrancis | CC BY-SA 3.0 |