[PHP] Re: Storing indefinite arrays in database
On 05/10/2011 03:16 PM, Benedikt Voigt wrote: Hi, I'am very new to PHP, so please any comment is welcome. I want to write a function in PHP, which takes X arguments and outputs a value. The functioning of this function should be stored in a db (mydb? or better alternatives?) The function would look up the result in the db based on the X arguments. But how can I store X arguments and the corresponding output value? If I limit the X arguments to a specific number like N=10, then I could create N=10 +1 columns in a table. But how should I do it if I don't want to limit myself to a fix number? Thanks for any comment! Ben Two ways that come to mind: 1. If you don't need to search, join, etc. on any of the arguments in the DB (*and never will need to*), then serialize the array and store it in one column and the result in another. 2. Use two tables: results id result 1 50 2 99 arguments id results_id argument 1 1 800 2 1 999 3 1 3.14 Then you just join results.id on arguments.results_id in your query. If you actually need to store the argument name then just add another column called variable and change the argument column's name to value. id results_id variable value -- Thanks! -Shawn http://www.spidean.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Storing indefinite arrays in database
Thanks Shawn, yes, your second idea works for me. The first one not, as I need to search and join on it. To continue your second idea with your example: Arguments: id results_id variablevalue 1 1 1 800 2 1 2 999 3 1 3 3.14 Results: id result 1 50 2 99 The Arguments and Results table would be filled dynamically by user content. In order to run a function, I have to do N times a join, whereas N is the number of arguments: select result from Results join Arguments as A1 join Arguments as A2 join Arguments as A3 on Results.id=A1.results_id and on Results.id=A2.results_id and on Results.id=A3.results_id and where A1.variable=1 and A1.value=800 and A2.variable=2 and A2.value=999 and A3.variable=3 and A3.value=3.14 and A1.results_id=Results.results_id and A2.results_id=Results.results_id and A3.results_id=Results.results_id Theoretically this works, but how good will be the performance if there are Thousands of entries? Anyway, I will try out. Thanks again, Ben Shawn McKenzie schrieb: On 05/10/2011 03:16 PM, Benedikt Voigt wrote: Hi, I'am very new to PHP, so please any comment is welcome. I want to write a function in PHP, which takes X arguments and outputs a value. The functioning of this function should be stored in a db (mydb? or better alternatives?) The function would look up the result in the db based on the X arguments. But how can I store X arguments and the corresponding output value? If I limit the X arguments to a specific number like N=10, then I could create N=10 +1 columns in a table. But how should I do it if I don't want to limit myself to a fix number? Thanks for any comment! Ben Two ways that come to mind: 1. If you don't need to search, join, etc. on any of the arguments in the DB (*and never will need to*), then serialize the array and store it in one column and the result in another. 2. Use two tables: results id result 1 50 2 99 arguments id results_id argument 1 1 800 2 1 999 3 1 3.14 Then you just join results.id on arguments.results_id in your query. If you actually need to store the argument name then just add another column called variable and change the argument column's name to value. id results_id variable value -- Benedikt Voigt Tucholskystrasse 33 10117 Berlin Mobile: 0049/1775902210 GERMANY 25 Carter House Brune Street E1 7NN London Mobile: 0044 7800744839 GREAT BRITAIN -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Re: Storing indefinite arrays in database
Date: Wed, 11 May 2011 22:15:21 +0200 From: benedikt.vo...@web.de To: nos...@mckenzies.net CC: php-general@lists.php.net Subject: Re: [PHP] Re: Storing indefinite arrays in database Thanks Shawn, yes, your second idea works for me. The first one not, as I need to search and join on it. To continue your second idea with your example: Arguments: id results_id variable value 1 1 1 800 2 1 2 999 3 1 3 3.14 Results: id result 1 50 2 99 The Arguments and Results table would be filled dynamically by user content. In order to run a function, I have to do N times a join, whereas N is the number of arguments: select result from Results join Arguments as A1 join Arguments as A2 join Arguments as A3 on Results.id=A1.results_id and on Results.id=A2.results_id and on Results.id=A3.results_id and where A1.variable=1 and A1.value=800 and A2.variable=2 and A2.value=999 and A3.variable=3 and A3.value=3.14 and A1.results_id=Results.results_id and A2.results_id=Results.results_id and A3.results_id=Results.results_id Theoretically this works, but how good will be the performance if there are Thousands of entries? Anyway, I will try out. Thanks again, Ben Dear Ben, Firstly, as this is my first post to this list I apologize for any etiquette mistakes. I would like to suggest to you a different approach, which would be more dynamical: First, you would have a table which stores the number of arguments of a certain entry, something like record_id num_of_arg You would store the num_of_arg entry in a PHP variable, say $num. Then you would proceed to use CREATE TABLE IF NOT EXISTS \'entries_.$num.\' ...some more code... to create a table which can store precisely $num arguments per record. Then you add it to that table using standard MySQL. Effectively this groups all records into tables according to $num. The only thing here is that you probably need to call the database two times: - first to get num_of_arg to be able to call onto the right table - second to get the data But as the number of arguments would go into the thousands, no huge join would be necessary. Only thing is, that you would have very wide tables (I don't know how wide MySQL can go). Creating tables on-the-fly as necessary seems to be something you could consider, but again I stress that I don't know performance details. There might be something quicker. So far for my 2c. Hopefully, it is of some help. Best regards, Jasper Mulder -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Storing indefinite arrays in database
Thank you Jasper, this also sounds like an interesting approach. But creating tables on the fly brings me to the idea, that I can also enlarge one table on the fly. Then I could consider the function as a matrice and store it in only one table, which can be enlarged dynamically. I am very new to PHP and havn't programmed for a decade now. But thank you all (Bastien, Shawn, Jasper) for your comments. Probably I will implement all and run then some articifial performance tests agains the different versions. But this will take me weeks or months until I am so far. And I hoped some new non-relational DB technology could solve the challenges I described, but probably not. Cheers, Ben Jasper Mulder schrieb: Date: Wed, 11 May 2011 22:15:21 +0200 From: benedikt.vo...@web.de To: nos...@mckenzies.net CC: php-general@lists.php.net Subject: Re: [PHP] Re: Storing indefinite arrays in database Thanks Shawn, yes, your second idea works for me. The first one not, as I need to search and join on it. To continue your second idea with your example: Arguments: id results_id variable value 1 1 1 800 2 1 2 999 3 1 3 3.14 Results: id result 1 50 2 99 The Arguments and Results table would be filled dynamically by user content. In order to run a function, I have to do N times a join, whereas N is the number of arguments: select result from Results join Arguments as A1 join Arguments as A2 join Arguments as A3 on Results.id=A1.results_id and on Results.id=A2.results_id and on Results.id=A3.results_id and where A1.variable=1 and A1.value=800 and A2.variable=2 and A2.value=999 and A3.variable=3 and A3.value=3.14 and A1.results_id=Results.results_id and A2.results_id=Results.results_id and A3.results_id=Results.results_id Theoretically this works, but how good will be the performance if there are Thousands of entries? Anyway, I will try out. Thanks again, Ben Dear Ben, Firstly, as this is my first post to this list I apologize for any etiquette mistakes. I would like to suggest to you a different approach, which would be more dynamical: First, you would have a table which stores the number of arguments of a certain entry, something like record_id num_of_arg You would store the num_of_arg entry in a PHP variable, say $num. Then you would proceed to use CREATE TABLE IF NOT EXISTS \'entries_.$num.\' ...some more code... to create a table which can store precisely $num arguments per record. Then you add it to that table using standard MySQL. Effectively this groups all records into tables according to $num. The only thing here is that you probably need to call the database two times: - first to get num_of_arg to be able to call onto the right table - second to get the data But as the number of arguments would go into the thousands, no huge join would be necessary. Only thing is, that you would have very wide tables (I don't know how wide MySQL can go). Creating tables on-the-fly as necessary seems to be something you could consider, but again I stress that I don't know performance details. There might be something quicker. So far for my 2c. Hopefully, it is of some help. Best regards, Jasper Mulder -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Re: Storing indefinite arrays in database
On 05/11/2011 03:15 PM, Benedikt Voigt wrote: Thanks Shawn, yes, your second idea works for me. The first one not, as I need to search and join on it. To continue your second idea with your example: Arguments: idresults_idvariablevalue 111800 212999 3133.14 Results: idresult 150 299 The Arguments and Results table would be filled dynamically by user content. In order to run a function, I have to do N times a join, whereas N is the number of arguments: select result from Results join Arguments as A1 join Arguments as A2 join Arguments as A3 on Results.id=A1.results_id and on Results.id=A2.results_id and on Results.id=A3.results_id and where A1.variable=1 and A1.value=800 and A2.variable=2 and A2.value=999 and A3.variable=3 and A3.value=3.14 and A1.results_id=Results.results_id and A2.results_id=Results.results_id and A3.results_id=Results.results_id Theoretically this works, but how good will be the performance if there are Thousands of entries? Anyway, I will try out. Thanks again, Ben Shawn McKenzie schrieb: On 05/10/2011 03:16 PM, Benedikt Voigt wrote: Hi, I'am very new to PHP, so please any comment is welcome. I want to write a function in PHP, which takes X arguments and outputs a value. The functioning of this function should be stored in a db (mydb? or better alternatives?) The function would look up the result in the db based on the X arguments. But how can I store X arguments and the corresponding output value? If I limit the X arguments to a specific number like N=10, then I could create N=10 +1 columns in a table. But how should I do it if I don't want to limit myself to a fix number? Thanks for any comment! Ben Two ways that come to mind: 1. If you don't need to search, join, etc. on any of the arguments in the DB (*and never will need to*), then serialize the array and store it in one column and the result in another. 2. Use two tables: results id result 1 50 2 99 arguments id results_id argument 1 1 800 2 1 999 3 1 3.14 Then you just join results.id on arguments.results_id in your query. If you actually need to store the argument name then just add another column called variable and change the argument column's name to value. id results_id variable value I just read your post quickly so maybe I'm off, but your query makes no sense. What is known (to use in the where clause to limit by) and what do you want to retrieve? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
