Multiple pieces of information in an SQL cell

jollyollie

Hi,

I'm looking for help in formatting my SQL database to best meet the PHP interface.

My goal is to make 2 tables and then match them on a certain criteria. Basically, a table of service providers and a table of people needing service, matched by service area (I was going to use Zip code as the area signifier.)

This leaves me with a number of questions.

Is it possible to include multiple zip codes in the providers area cell?
Is the search query a union query, or what is the best way to match the areas?
Maybe I'm being over ambitious here, but is it possible to have multiple areas in the service seekers search? (i.e. I am looking for service in one of these 3 areas.) Do those searchs need to be seperated into 1, 2 and 3 or is there a 1 step search?

I'm trying to learn this all and am excited about the potential, but feeling a bit lost in the implementation. Even some help in finding online resources to handle this type of thing would be sincerely appreciated.

Thanks,
Ollie

rachel2004

Originally posted by jollyollie
1. Is it possible to include multiple zip codes in the providers area cell?

I'm not sure what you mean by "cell"... however, it's possible to match records in one table to records in another table if that's what you mean.

2. Is the search query a union query, or what is the best way to match the areas?

This depends on how you've designed your tables and what you want to receive in your result set. For now, if you're just starting, think about your tables and work on just matching one customer with one zip code. Once you are comfortable with that, then expand your skills where you are matching one customer with several zip codes. The matching is usually done with a WHERE clause.

3. Maybe I'm being over ambitious here, but is it possible to have multiple areas in the service seekers search? (i.e. I am looking for service in one of these 3 areas.) Do those searchs need to be seperated into 1, 2 and 3 or is there a 1 step search?

Again, depending on how you're structured you tables and with the use of the WHERE claues, you can probably do what you're asking about.

Even some help in finding online resources to handle this type of thing would be sincerely appreciated.

Try these:
www.devshed.com
http://www.sitepoint.com/article/driven-website-php-amp-mysql

jollyollie

Originally posted by rachel2004
I'm not sure what you mean by "cell"... however, it's possible to match records in one table to records in another table if that's what you mean.[/i]

Yes, I meant records. Sorry for the poor choice of words. Was thinking in terms of a spreadsheet, not database.

This depends on how you've designed your tables and what you want to receive in your result set.

The tables are designed to have the following fields:

Customer:
Name, Address, Phone, Email, Area of need (in zip code form)

Provider:
Name, Address, Phone, Email, Areas served (in zip code form)

What I am trying to do is set something up that would return the list of providers when a customer fills out a form.

For now, if you're just starting, think about your tables and work on just matching one customer with one zip code.

I've got that part down.

select * from xxx where 'zip' = $zip

Again, depending on how you're structured you tables and with the use of the WHERE claues, you can probably do what you're asking about.

Ok, I guess this gets to the heart of what I'm asking. How do you structure the table to include more than 1 zip code per record? Should they be seperated by commas? How does a form add them if they are set up as checkboxes? (i.e. a form listing all zips in an area with a checkbox beside each 1. The Provider could then check all applicable zips.)

Maybe I'm biting off more than I can chew.

Ollie

The_Chancer

You could store multiple zips in the same "cell" and use

$query="SELECT * FROM companies WHERE zip LIKE '".%$required%."'";

And store multiple zips in it separated by a space....

As to adding multiple - you will know how many zips they can check yes ? so..

for ($i=0;$i<$numzips;$i++)
{
    @$addzip .=" ".$_POST['zip'.$i];
}
$addzip=substr($addzip,1);

Leaving you with $addzip, holding all those that they can do...