Database Design

flinginarrows

I have a pretty simple form at:
http://www.flinginarrows.com/Ranges/AddRanges/addranges.php

Part of the insert statement:

$select = $_POST['services'];
$services=implode(',',$services);

$sql = "INSERT INTO ranges (rangename,website,claddress,claddressb,clcity,clstate,clzip,clphone,clfax,coname,coaddress,coaddressb,cocity,costate,cozip,cophone,coemail,cofax,services,userfile_name)
VALUES ('$rangename','$website','$claddress','$claddressb','$clcity','$clstate','$clzip','$clphone','$clfax','$coname','$coaddress','$coaddressb','$cocity','$costate','$cozip','$cophone','$coemail','$cofax','$services','$userfile_name')";

$result = mysql_query($sql);

}
echo "Thank you! Information entered. \n";

} else{

// display form

Currently as you can see I have everything going into their own field except for the services, which began as an array.

Would it make more sense to combine the range information and the contact information into their own array or does it make more sense to keep them as independent fields?

The reason I ask is that any one of the fields can be left blank and the way I am displaying them now:

<?PHP printf("%s \n", $myrow["claddress"]);?>
<?PHP printf("%s \n", $myrow["claddressb"]);?>
<?PHP printf("%s %s \n", $myrow["clcity"], $myrow"[clstate"]);?>
<?PHP printf("%s \n", $myrow["clzip"]);?>
<?PHP printf("%s \n", $myrow["clphone"]);?>
<?PHP printf("%s \n", $myrow["clfax"]);?>

You can see that I will print a regardless if there is anything there or not.

Would it be easier to just do an "if its not empty check" before each print or is my design way out in center field somewhere?

superwormy

You should almost NEVER be storing something like serialize()'d arrays in a database. Each piece of data should pretty much have its own field. In your case, I would probably use a few tables...

RANGE
ID, name, URL, address_ID, email_ID, phone_ID, fax_ID

ADDRESS
ID, street, city, state_ID, zip

EMAIL
ID, email, name

PHONE_OR_FAX
ID, area_code, three_digit, four_digit

SERVICES
ID, name

LINK_SERVICES_RANGE
ID, range_ID, service_ID

flinginarrows

So It seems I should keep the services as an array?

SERVICES
ID, name

I should probably also create tables for the contact information?

CONTACT_ADDRESS
ID, street, city, state_ID, zip

CONTACT_PHONE_OR_FAX
ID, area_code, three_digit, four_digit

Any other suggestions?

superwormy

No, you misunderstand me.

Services SHOULD NOT be stored as an array. Heres an example, say we have this range:

Keiths Archery Range

Contact Address: 56 Cowles Road
Address: 71 Birch Road

Services include:
Arrow sharpening
Arrow sales
Bow sales

Here are our records:

RANGE
ID, name, URL, address_ID, contact_address_ID
1, Keith's Archery Range, www.keithsrange.com, 2, 1

ADDRESS
ID, street
1, 71 Birch Road
2, 56 Cowles Road

SERVICES
ID, name
1, Arrow Sharpening
2, Arrow Sales
3, Bow Sales

LINK_SERVICES_RANGE
range_ID, service_ID
1, 1
1, 2
1, 3

Thus, SERVICES does not store an array. Instead it stores several records, and those several records are linked together with the RANGE record by the LINK_SERVICES_RANGE table. So for instance, what service does Keith's Range have?

Keith's Range is RANGE.ID = 1

SELECT range_ID FROM link_services_range WHERE range_ID = 1
while ($array = fetch_array)
{

SELECT * FROM services WHERE ID = $array['service_ID']

}

Oh, and one other thing, why do you need to have a diferent table for contract_address than address? They both store the same things right? street, city, state_ID, zip, etc. so why not just use the same table? Same thing with phone and fax numbers, they're both just 10 digit numbers right ( 3 + 3 + 4 ) so just use the same table for both phone and fax numbers.

flinginarrows

I think I am getting the idea. Even though contact information and range information (Phone Numbers for example) is different I can still link them with the range_ID to pull the correct info from all the relevent tables in the database. I should then change my multiple checkboxes from

name="services[]"
to
name="rentals" (for example)

So out of the 17 possible "check marks" how would I go about displaying only the fields (boxes) that have been checked and ignore the empty ones without printing out the space where they might have been?

superwormy

Yeah, you're getting the idea.

If you do your query my way, and build your HTML dynamically, you won't need to worry about whitespace. So if you did this:

SELECT range_ID FROM link_services_range WHERE range_ID = 1
while ($array = fetch_array)
{

$result = query ("SELECT * FROM services WHERE ID = " . $array['service_ID']);
$service = fetch_array ($result);

print ("<input type=\"checkbox\" value=\"1\" name=\"" . $service['name'] . "\" >");

}

Then you'd be printing a checkbox for each related service, and each related service only.