Page 1 of 1
Access range of columns' values from one table
Posted: Fri Sep 12, 2008 7:04 am
by dv_evan
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
Re: Access range of columns' values from one table
Posted: Fri Sep 12, 2008 7:37 am
by jaoudestudios
What do you have for your sql query so far?
Re: Access range of columns' values from one table
Posted: Fri Sep 12, 2008 7:41 am
by marcth
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
Re: Access range of columns' values from one table
Posted: Fri Sep 12, 2008 7:46 am
by jaoudestudios
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
Posted: Fri Sep 12, 2008 7:51 am
by marcth
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.

What's an ENUM? I haven't used mySQL in years.
Re: Access range of columns' values from one table
Posted: Fri Sep 12, 2008 8:00 am
by jaoudestudios
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
Re: Access range of columns' values from one table
Posted: Fri Sep 12, 2008 8:04 am
by jaoudestudios
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.
Re: Access range of columns' values from one table
Posted: Fri Sep 12, 2008 8:06 am
by marcth
That's neat.
Re: Access range of columns' values from one table
Posted: Fri Sep 12, 2008 8:09 am
by jaoudestudios
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

Re: Access range of columns' values from one table
Posted: Fri Sep 12, 2008 8:47 am
by marcth
Aye, I've not had the opportunity to use soundex yet. I think that functionality also exists in PHP.
Re: Access range of columns' values from one table
Posted: Fri Sep 12, 2008 8:57 am
by jaoudestudios
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.