Access range of columns' values from one table
Moderator: General Moderators
Access range of columns' values from one table
Dear All,
Everytime I think I am setlled with PHP/MySQL something just pops up and throw me of track.
I am having problem accomplishing this and am asking for your assistance.
I have a table and would like to access the value for one record(row) from a range of columns in this table that has values.
Table Name: Contact
Columns Names: Name/Add/Tele1/Tele2/Tele3/Email
1st Row: Tom/A55 St./1800.231/1800.232/ /Tom@domain.com
2nd Row: Pam/B68 Rd./2900.12/ /732.2500/Pam@domain.com
I would like to loop through this table to capture all the Tele fields that has a value for each record and have the result display as:
Tom
1800.231
1800.232
Pam
2900.12
732.2500
Please note: I do not need the 'Add' or 'Email' values to be in this result.
Is there a loop that can run thorugh a range of columns to achieve this?
Thanks
Dave Evan
Everytime I think I am setlled with PHP/MySQL something just pops up and throw me of track.
I am having problem accomplishing this and am asking for your assistance.
I have a table and would like to access the value for one record(row) from a range of columns in this table that has values.
Table Name: Contact
Columns Names: Name/Add/Tele1/Tele2/Tele3/Email
1st Row: Tom/A55 St./1800.231/1800.232/ /Tom@domain.com
2nd Row: Pam/B68 Rd./2900.12/ /732.2500/Pam@domain.com
I would like to loop through this table to capture all the Tele fields that has a value for each record and have the result display as:
Tom
1800.231
1800.232
Pam
2900.12
732.2500
Please note: I do not need the 'Add' or 'Email' values to be in this result.
Is there a loop that can run thorugh a range of columns to achieve this?
Thanks
Dave Evan
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Access range of columns' values from one table
What do you have for your sql query so far?
Re: Access range of columns' values from one table
Also you Database schema, should have the tel1, tel2, tel3 columns. The telephone data belongs in another table
CONTACTS
ID - Primary Key
NAME
EMAIL
TELEPHONE
ID - Primary Key
CONTACT_ID - Foreign Key-
TELEPHONE_NBR
TELEPHONE_TYPE_ID - Foreign key
TELEPHONE_TYPES
ID - Primary Key
DESCRIPTION - 'home', 'fax', 'cell', work'
Columns Names: Name/Add/Tele1/Tele2/Tele3/Email
CONTACTS
ID - Primary Key
NAME
TELEPHONE
ID - Primary Key
CONTACT_ID - Foreign Key-
TELEPHONE_NBR
TELEPHONE_TYPE_ID - Foreign key
TELEPHONE_TYPES
ID - Primary Key
DESCRIPTION - 'home', 'fax', 'cell', work'
Columns Names: Name/Add/Tele1/Tele2/Tele3/Email
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Access range of columns' values from one table
Instead of having a separate table for telephone types you can just use a ENUM in the telephone table - it would be faster and simplify the sql queries. 
Re: Access range of columns' values from one table
What's an ENUM? I haven't used mySQL in years.jaoudestudios wrote:Instead of having a separate table for telephone types you can just use a ENUM in the telephone table - it would be faster and simplify the sql queries.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Access range of columns' values from one table
Its similar to having a separate table but not. The column will only allow certain values that have been set.
ie. 'small' or 'medium' or 'large'
http://dev.mysql.com/doc/refman/5.0/en/enum.html
ie. 'small' or 'medium' or 'large'
http://dev.mysql.com/doc/refman/5.0/en/enum.html
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Access range of columns' values from one table
Sorry forgot to add, the small/medium/large are actually stored as int so it is still efficient.
If there needs to be a front end page where the user can add to this list of options, then definitely do it the way you suggested as a separate table, but if the user can not change it, and will only ever be changed rarely by the developer then I would recommend to do it as a ENUM, as the queries will be simpler.
If there needs to be a front end page where the user can add to this list of options, then definitely do it the way you suggested as a separate table, but if the user can not change it, and will only ever be changed rarely by the developer then I would recommend to do it as a ENUM, as the queries will be simpler.
Re: Access range of columns' values from one table
That's neat.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Access range of columns' values from one table
Thats nothing, if you want something funky, check out soundex! (mysql)
When you search on google and there are no matches and it makes some suggestions -> Did you mean .... ?
It is achieved by using soundex.
Check it out and let me know what you think
When you search on google and there are no matches and it makes some suggestions -> Did you mean .... ?
It is achieved by using soundex.
Check it out and let me know what you think
Re: Access range of columns' values from one table
Aye, I've not had the opportunity to use soundex yet. I think that functionality also exists in PHP.
- jaoudestudios
- DevNet Resident
- Posts: 1483
- Joined: Wed Jun 18, 2008 8:32 am
- Location: Surrey
Re: Access range of columns' values from one table
Yep you are right, it does
But I only use it for searches which I do all in mysql as it is faster than pulling all the data out into php and then searching it there.
But I only use it for searches which I do all in mysql as it is faster than pulling all the data out into php and then searching it there.