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.