| Dynamic field names in recordset [message #169673] |
Wed, 22 September 2010 13:01  |
Pedro M. Leite
Messages: 5
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
good afternoon
sorry if i not make my self clear
current situation
one table with 50 fields - appx
id - name - entrydate - remarks - width .... locked
one page with :
select from table where id = " - from $_GET
read the recordset and output pdf
rst_table['id']
rst_table['name']
rst_table['entrydate']
.....
rst_table['locked']
50 something times repeated
now, i want to have a table with
fieldname
position
so that i can :
open a recordset from table with one row
browse the position table, ordered by index, from 1 to 50 something
in the loop, make
(pseudocode) echo_to_pdf rst_table(position_table(fieldname))
loop
so that, to rst_table, the field i want is passed bu the content of the
position_table field content, instead of literally :
'position_table(fieldname)'
simple example
main table
id
name
remarks
position table
id,1
name,2
remarks,3
so the output is : id - name - remarks
OR
position table
id,1
name,3
remarks,2
so the output is : id - remarks - name
s, what i am looking to do is to reference the field by another
reference and not literally
thank you in advance
Pedro Leite
|
|
|
|
| Re: Dynamic field names in recordset [message #169676 is a reply to message #169673] |
Wed, 22 September 2010 14:33  |
Robert Hairgrove
Messages: 19
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
Pedro M. Leite wrote:
> good afternoon
>
> sorry if i not make my self clear
> current situation
> one table with 50 fields - appx
> id - name - entrydate - remarks - width .... locked
> one page with :
> select from table where id = " - from $_GET
> read the recordset and output pdf
> rst_table['id']
> rst_table['name']
> rst_table['entrydate']
> ....
> rst_table['locked']
>
> 50 something times repeated
>
> now, i want to have a table with
> fieldname
> position
> so that i can :
> open a recordset from table with one row
> browse the position table, ordered by index, from 1 to 50 something
> in the loop, make
> (pseudocode) echo_to_pdf rst_table(position_table(fieldname))
> loop
>
> so that, to rst_table, the field i want is passed bu the content of the
> position_table field content, instead of literally :
> 'position_table(fieldname)'
>
> simple example
>
> main table
> id
> name
> remarks
>
> position table
> id,1
> name,2
> remarks,3
>
> so the output is : id - name - remarks
>
> OR
>
> position table
> id,1
> name,3
> remarks,2
>
> so the output is : id - remarks - name
>
> s, what i am looking to do is to reference the field by another
> reference and not literally
>
> thank you in advance
> Pedro Leite
You didn't specify what database you are using ... with most relational
databases (MySQL, Oracle, etc.) you can (and should!) refer to the field
by name and not by position.
You should not depend on the position of a column within a table! Most
of the time, the DBA is NOT the same person who is programming the
client application. So, what happens if someone adds a column to the
table, and doesn't tell you about it? Your code breaks, of course.
|
|
|
|
| Re: Dynamic field names in recordset [message #169679 is a reply to message #169676] |
Wed, 22 September 2010 15:52 |
Pedro M. Leite
Messages: 5
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
On 09/22/2010 03:33 PM, Robert Hairgrove wrote:
> Pedro M. Leite wrote:
>> good afternoon
>>
>> sorry if i not make my self clear
>> current situation
>> one table with 50 fields - appx
>> id - name - entrydate - remarks - width .... locked
>> one page with :
>> select from table where id = " - from $_GET
>> read the recordset and output pdf
>> rst_table['id']
>> rst_table['name']
>> rst_table['entrydate']
>> ....
>> rst_table['locked']
>>
>> 50 something times repeated
>>
>> now, i want to have a table with
>> fieldname
>> position
>> so that i can :
>> open a recordset from table with one row
>> browse the position table, ordered by index, from 1 to 50 something
>> in the loop, make
>> (pseudocode) echo_to_pdf rst_table(position_table(fieldname))
>> loop
>>
>> so that, to rst_table, the field i want is passed bu the content of the
>> position_table field content, instead of literally :
>> 'position_table(fieldname)'
>>
>> simple example
>>
>> main table
>> id
>> name
>> remarks
>>
>> position table
>> id,1
>> name,2
>> remarks,3
>>
>> so the output is : id - name - remarks
>>
>> OR
>>
>> position table
>> id,1
>> name,3
>> remarks,2
>>
>> so the output is : id - remarks - name
>>
>> s, what i am looking to do is to reference the field by another
>> reference and not literally
>>
>> thank you in advance
>> Pedro Leite
>
> You didn't specify what database you are using ... with most relational
> databases (MySQL, Oracle, etc.) you can (and should!) refer to the field
> by name and not by position.
>
> You should not depend on the position of a column within a table! Most
> of the time, the DBA is NOT the same person who is programming the
> client application. So, what happens if someone adds a column to the
> table, and doesn't tell you about it? Your code breaks, of course.
Hi
good point
i am the dba and the programmer.
anyway, this is to allow users to customize their pdf on the fly, by
selecting whether the fields are sent to the pdf and their position
think of the fields as a vertical stack, and users can click on "up" and
"down" arrow to move the field position up or down.
there is also an "active" check box.
out of the context, you are right of course.
thank you
Pedro Leite
|
|
|
|
| Re: Dynamic field names in recordset [message #169680 is a reply to message #169676] |
Wed, 22 September 2010 15:53 |
Pedro M. Leite
Messages: 5
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
forgot : php + mysql
On 09/22/2010 03:33 PM, Robert Hairgrove wrote:
> Pedro M. Leite wrote:
>> good afternoon
>>
>> sorry if i not make my self clear
>> current situation
>> one table with 50 fields - appx
>> id - name - entrydate - remarks - width .... locked
>> one page with :
>> select from table where id = " - from $_GET
>> read the recordset and output pdf
>> rst_table['id']
>> rst_table['name']
>> rst_table['entrydate']
>> ....
>> rst_table['locked']
>>
>> 50 something times repeated
>>
>> now, i want to have a table with
>> fieldname
>> position
>> so that i can :
>> open a recordset from table with one row
>> browse the position table, ordered by index, from 1 to 50 something
>> in the loop, make
>> (pseudocode) echo_to_pdf rst_table(position_table(fieldname))
>> loop
>>
>> so that, to rst_table, the field i want is passed bu the content of the
>> position_table field content, instead of literally :
>> 'position_table(fieldname)'
>>
>> simple example
>>
>> main table
>> id
>> name
>> remarks
>>
>> position table
>> id,1
>> name,2
>> remarks,3
>>
>> so the output is : id - name - remarks
>>
>> OR
>>
>> position table
>> id,1
>> name,3
>> remarks,2
>>
>> so the output is : id - remarks - name
>>
>> s, what i am looking to do is to reference the field by another
>> reference and not literally
>>
>> thank you in advance
>> Pedro Leite
>
> You didn't specify what database you are using ... with most relational
> databases (MySQL, Oracle, etc.) you can (and should!) refer to the field
> by name and not by position.
>
> You should not depend on the position of a column within a table! Most
> of the time, the DBA is NOT the same person who is programming the
> client application. So, what happens if someone adds a column to the
> table, and doesn't tell you about it? Your code breaks, of course.
|
|
|
|
| Re: Dynamic field names in recordset [message #169682 is a reply to message #169673] |
Wed, 22 September 2010 17:13 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 9/22/2010 9:01 AM, Pedro M. Leite wrote:
> good afternoon
>
> sorry if i not make my self clear
> current situation
> one table with 50 fields - appx
> id - name - entrydate - remarks - width .... locked
> one page with :
> select from table where id = " - from $_GET
> read the recordset and output pdf
> rst_table['id']
> rst_table['name']
> rst_table['entrydate']
> ....
> rst_table['locked']
>
> 50 something times repeated
>
> now, i want to have a table with
> fieldname
> position
> so that i can :
> open a recordset from table with one row
> browse the position table, ordered by index, from 1 to 50 something
> in the loop, make
> (pseudocode) echo_to_pdf rst_table(position_table(fieldname))
> loop
>
> so that, to rst_table, the field i want is passed bu the content of the
> position_table field content, instead of literally :
> 'position_table(fieldname)'
>
> simple example
>
> main table
> id
> name
> remarks
>
> position table
> id,1
> name,2
> remarks,3
>
> so the output is : id - name - remarks
>
> OR
>
> position table
> id,1
> name,3
> remarks,2
>
> so the output is : id - remarks - name
>
> s, what i am looking to do is to reference the field by another
> reference and not literally
>
> thank you in advance
> Pedro Leite
What you need to do is join the tables and order by the position. For
more information, please follow up in comp.databases.mysql. They will
want the output of your CREATE TABLE statements and some sample data
plus exactly what you want output.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Dynamic field names in recordset [message #169683 is a reply to message #169682] |
Wed, 22 September 2010 17:30 |
Pedro M. Leite
Messages: 5
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
On 09/22/2010 06:13 PM, Jerry Stuckle wrote:
> On 9/22/2010 9:01 AM, Pedro M. Leite wrote:
>> good afternoon
>>
>> sorry if i not make my self clear
>> current situation
>> one table with 50 fields - appx
>> id - name - entrydate - remarks - width .... locked
>> one page with :
>> select from table where id = " - from $_GET
>> read the recordset and output pdf
>> rst_table['id']
>> rst_table['name']
>> rst_table['entrydate']
>> ....
>> rst_table['locked']
>>
>> 50 something times repeated
>>
>> now, i want to have a table with
>> fieldname
>> position
>> so that i can :
>> open a recordset from table with one row
>> browse the position table, ordered by index, from 1 to 50 something
>> in the loop, make
>> (pseudocode) echo_to_pdf rst_table(position_table(fieldname))
>> loop
>>
>> so that, to rst_table, the field i want is passed bu the content of the
>> position_table field content, instead of literally :
>> 'position_table(fieldname)'
>>
>> simple example
>>
>> main table
>> id
>> name
>> remarks
>>
>> position table
>> id,1
>> name,2
>> remarks,3
>>
>> so the output is : id - name - remarks
>>
>> OR
>>
>> position table
>> id,1
>> name,3
>> remarks,2
>>
>> so the output is : id - remarks - name
>>
>> s, what i am looking to do is to reference the field by another
>> reference and not literally
>>
>> thank you in advance
>> Pedro Leite
>
> What you need to do is join the tables and order by the position. For
> more information, please follow up in comp.databases.mysql. They will
> want the output of your CREATE TABLE statements and some sample data
> plus exactly what you want output.
>
thanks, but that's not quite it
my question is :
<short version>
this is correct in php
--> $row_customers['id'];
being 'id' the name of the field
question
--> can this be done ??
---> $row_customers[$a_variable_that_contains_'id'_or_something_else]
the $a_variable .. is collected from another recordset that contains
field names and their position, ordered by position
thank you and sorry for not making myself clear
Best Regards
PLeite
|
|
|
|
| Re: Dynamic field names in recordset [message #169684 is a reply to message #169683] |
Wed, 22 September 2010 19:12 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 9/22/2010 1:30 PM, Pedro M. Leite wrote:
>
>
> On 09/22/2010 06:13 PM, Jerry Stuckle wrote:
>> On 9/22/2010 9:01 AM, Pedro M. Leite wrote:
>>> good afternoon
>>>
>>> sorry if i not make my self clear
>>> current situation
>>> one table with 50 fields - appx
>>> id - name - entrydate - remarks - width .... locked
>>> one page with :
>>> select from table where id = " - from $_GET
>>> read the recordset and output pdf
>>> rst_table['id']
>>> rst_table['name']
>>> rst_table['entrydate']
>>> ....
>>> rst_table['locked']
>>>
>>> 50 something times repeated
>>>
>>> now, i want to have a table with
>>> fieldname
>>> position
>>> so that i can :
>>> open a recordset from table with one row
>>> browse the position table, ordered by index, from 1 to 50 something
>>> in the loop, make
>>> (pseudocode) echo_to_pdf rst_table(position_table(fieldname))
>>> loop
>>>
>>> so that, to rst_table, the field i want is passed bu the content of the
>>> position_table field content, instead of literally :
>>> 'position_table(fieldname)'
>>>
>>> simple example
>>>
>>> main table
>>> id
>>> name
>>> remarks
>>>
>>> position table
>>> id,1
>>> name,2
>>> remarks,3
>>>
>>> so the output is : id - name - remarks
>>>
>>> OR
>>>
>>> position table
>>> id,1
>>> name,3
>>> remarks,2
>>>
>>> so the output is : id - remarks - name
>>>
>>> s, what i am looking to do is to reference the field by another
>>> reference and not literally
>>>
>>> thank you in advance
>>> Pedro Leite
>>
>> What you need to do is join the tables and order by the position. For
>> more information, please follow up in comp.databases.mysql. They will
>> want the output of your CREATE TABLE statements and some sample data
>> plus exactly what you want output.
>>
>
> thanks, but that's not quite it
> my question is :
> <short version>
>
> this is correct in php
> --> $row_customers['id'];
>
> being 'id' the name of the field
>
> question
> --> can this be done ??
> ---> $row_customers[$a_variable_that_contains_'id'_or_something_else]
>
> the $a_variable .. is collected from another recordset that contains
> field names and their position, ordered by position
>
> thank you and sorry for not making myself clear
>
> Best Regards
> PLeite
Ah, sorry, I misunderstood your question - I thought you were trying to
figure out how to get values in order from the database. My mistake,
not yours.
Yes, you can use a variable as an index, i.e. $my_row[$my_var]. It
works for either numeric or associative arrays (although in php,
strictly speaking all arrays are associative).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
| Re: Dynamic field names in recordset [message #169685 is a reply to message #169683] |
Wed, 22 September 2010 20:14 |
Robert Hairgrove
Messages: 19
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
Pedro M. Leite wrote:
>
> On 09/22/2010 06:13 PM, Jerry Stuckle wrote:
>> On 9/22/2010 9:01 AM, Pedro M. Leite wrote:
>>> good afternoon
>>>
>>> sorry if i not make my self clear
>>> current situation
>>> one table with 50 fields - appx
>>> id - name - entrydate - remarks - width .... locked
>>> one page with :
>>> select from table where id = " - from $_GET
>>> read the recordset and output pdf
>>> rst_table['id']
>>> rst_table['name']
>>> rst_table['entrydate']
>>> ....
>>> rst_table['locked']
>>>
>>> 50 something times repeated
>>>
>>> now, i want to have a table with
>>> fieldname
>>> position
>>> so that i can :
>>> open a recordset from table with one row
>>> browse the position table, ordered by index, from 1 to 50 something
>>> in the loop, make
>>> (pseudocode) echo_to_pdf rst_table(position_table(fieldname))
>>> loop
>>>
>>> so that, to rst_table, the field i want is passed bu the content of the
>>> position_table field content, instead of literally :
>>> 'position_table(fieldname)'
>>>
>>> simple example
>>>
>>> main table
>>> id
>>> name
>>> remarks
>>>
>>> position table
>>> id,1
>>> name,2
>>> remarks,3
>>>
>>> so the output is : id - name - remarks
>>>
>>> OR
>>>
>>> position table
>>> id,1
>>> name,3
>>> remarks,2
>>>
>>> so the output is : id - remarks - name
>>>
>>> s, what i am looking to do is to reference the field by another
>>> reference and not literally
>>>
>>> thank you in advance
>>> Pedro Leite
>> What you need to do is join the tables and order by the position. For
>> more information, please follow up in comp.databases.mysql. They will
>> want the output of your CREATE TABLE statements and some sample data
>> plus exactly what you want output.
>>
>
> thanks, but that's not quite it
> my question is :
> <short version>
>
> this is correct in php
> --> $row_customers['id'];
>
> being 'id' the name of the field
>
> question
> --> can this be done ??
> ---> $row_customers[$a_variable_that_contains_'id'_or_something_else]
>
> the $a_variable .. is collected from another recordset that contains
> field names and their position, ordered by position
>
> thank you and sorry for not making myself clear
>
> Best Regards
> PLeite
This is possible. The trick is that the array representing one row which
is returned by the PHP mysql_fetch_array() function can be treated
either as an associative array or as a numerically indexed array.
For example, since you mention the scenario of a user setting up their
own preferred order in the application -- presumably using JavaScript
code or something else running on the client -- when you send off the
form data, presumably using a POST request, send along the array sort
order values as a strings (use implode() with an appropriate delimiter
and explode() them again in the page evaluating the form data). Then you
could fetch your row, i.e.:
// $aOrder[] contains the order of your fields...
// for example:
$aOrder['a'] = 1;
$aOrder['b'] = 2;
$aOrder['c'] = 0;
// ...
$sql1 = "SELECT a,b,c FROM the_table";
$res = mysql_connect(...etc. ...);
if ($res && ($q = mysql_query($sql, $res)) {
while ($row = mysql_fetch_array($q)) {
foreach ($aOrder as $o) {
$val[] = $row[$o];
}
// display the row data in $val[]...
} // fetch the next row
}
Of course, this is just one way of doing it. You could store the
ordering data in the database, but I would at least keep it independent
of the other data.
|
|
|
|
| Re: Dynamic field names in recordset [message #169690 is a reply to message #169685] |
Wed, 22 September 2010 22:31 |
Pedro M. Leite
Messages: 5
Registered: September 2010
Karma: 0
|
Junior Member |
|
|
On 22-09-2010 21:14, Robert Hairgrove wrote:
> Pedro M. Leite wrote:
>>
>> On 09/22/2010 06:13 PM, Jerry Stuckle wrote:
>>> On 9/22/2010 9:01 AM, Pedro M. Leite wrote:
>>>> good afternoon
>>>>
>>>> sorry if i not make my self clear
>>>> current situation
>>>> one table with 50 fields - appx
>>>> id - name - entrydate - remarks - width .... locked
>>>> one page with :
>>>> select from table where id = " - from $_GET
>>>> read the recordset and output pdf
>>>> rst_table['id']
>>>> rst_table['name']
>>>> rst_table['entrydate']
>>>> ....
>>>> rst_table['locked']
>>>>
>>>> 50 something times repeated
>>>>
>>>> now, i want to have a table with
>>>> fieldname
>>>> position
>>>> so that i can :
>>>> open a recordset from table with one row
>>>> browse the position table, ordered by index, from 1 to 50 something
>>>> in the loop, make
>>>> (pseudocode) echo_to_pdf rst_table(position_table(fieldname))
>>>> loop
>>>>
>>>> so that, to rst_table, the field i want is passed bu the content of the
>>>> position_table field content, instead of literally :
>>>> 'position_table(fieldname)'
>>>>
>>>> simple example
>>>>
>>>> main table
>>>> id
>>>> name
>>>> remarks
>>>>
>>>> position table
>>>> id,1
>>>> name,2
>>>> remarks,3
>>>>
>>>> so the output is : id - name - remarks
>>>>
>>>> OR
>>>>
>>>> position table
>>>> id,1
>>>> name,3
>>>> remarks,2
>>>>
>>>> so the output is : id - remarks - name
>>>>
>>>> s, what i am looking to do is to reference the field by another
>>>> reference and not literally
>>>>
>>>> thank you in advance
>>>> Pedro Leite
>>> What you need to do is join the tables and order by the position. For
>>> more information, please follow up in comp.databases.mysql. They will
>>> want the output of your CREATE TABLE statements and some sample data
>>> plus exactly what you want output.
>>>
>>
>> thanks, but that's not quite it
>> my question is :
>> <short version>
>>
>> this is correct in php
>> --> $row_customers['id'];
>>
>> being 'id' the name of the field
>>
>> question
>> --> can this be done ??
>> ---> $row_customers[$a_variable_that_contains_'id'_or_something_else]
>>
>> the $a_variable .. is collected from another recordset that contains
>> field names and their position, ordered by position
>>
>> thank you and sorry for not making myself clear
>>
>> Best Regards
>> PLeite
>
> This is possible. The trick is that the array representing one row which
> is returned by the PHP mysql_fetch_array() function can be treated
> either as an associative array or as a numerically indexed array.
>
> For example, since you mention the scenario of a user setting up their
> own preferred order in the application -- presumably using JavaScript
> code or something else running on the client -- when you send off the
> form data, presumably using a POST request, send along the array sort
> order values as a strings (use implode() with an appropriate delimiter
> and explode() them again in the page evaluating the form data). Then you
> could fetch your row, i.e.:
>
> // $aOrder[] contains the order of your fields...
> // for example:
> $aOrder['a'] = 1;
> $aOrder['b'] = 2;
> $aOrder['c'] = 0;
> // ...
> $sql1 = "SELECT a,b,c FROM the_table";
> $res = mysql_connect(...etc. ...);
> if ($res && ($q = mysql_query($sql, $res)) {
> while ($row = mysql_fetch_array($q)) {
> foreach ($aOrder as $o) {
> $val[] = $row[$o];
> }
> // display the row data in $val[]...
> } // fetch the next row
> }
>
> Of course, this is just one way of doing it. You could store the
> ordering data in the database, but I would at least keep it independent
> of the other data.
thats about it
i plan to store in a table : id, user, field, position
so, each user can have its preferences.
that catch is, to fill for each user, once, the table with all the
fields, and position { 1.. 50 something }
there will be a page with the sorted list and up and down arrows,
linking to a process page, that receives field id and delta ( +1 or -1 )
and returns to the page.
i think i got the idea.
thank you all
Pedro Leite
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]