PHP / MY SQL design problem...

Calvin256

Hello,
I'm kinda newbie on all this and I hate databases (lol)... but I'm trying to figure out the easiest way to save on a database the following form:

-----------------------------------------------
| Country | Service#1 | Service#2 | Service#3 |
-----------------------------------------------
|Canada | Yes | Yes | No |
-----------------------------------------------
|USA | Yes | No | No |
-----------------------------------------------
|Spain | No | Yes | No |
-----------------------------------------------
|Brazil | Yes | Yes | Yes |
-----------------------------------------------

The thing here is that the user is gonna be able to mark the countries in which it provides services and it has to indicate which services provides there...
The options of services is the same for all the users as well as the countries, the user just have to select the country and the services, therefore user 1 and user 2 may o may not have the same services on the same country.
Don't forget that all the countries of the world are listed on the page, therefore we're talking about 150 check boxes for all the countries plus 4 or 5 more per country for the services....
The idea is taht later on we I can serch either by service, by country or by user, or a combination of one or more values, but I have not idea how to make it easy to save all this on a database without writing a book full of if's lol...
Thanks in advance.,..

TheServant

Databases are your friend.
One option is to have about 750 columns
---------------------------------------------------------------------------------
| User | AU1 | AU2 | AU3 | AU4 | AU5 | US1 | US2 | US3 | US4 ...
---------------------------------------------------------------------------------

Which is probably inefficient, but will get you one table with all values.

Another option is to store choices as text and process later (NULL if country not selected, or the services separated by commas if country selected):
---------------------------------------------------------------------------------
| User | AU | US | ...
| 1 | 1,0,0,1,0 | 0,1,0,1,1 | ...
| 2 | NULL | 0,1,0,1,0 | ...
---------------------------------------------------------------------------------
That will require you to separate the strings pulled from the DB when you do want to find some particular choice which could be too much effort, making the former choice more attractive.

How many users are you expecting?

And welcome to Bytes ;)

Calvin256

Thanks TheServant,
I understand what you saying in both cases, but I'm expecting (hopefully) around 500 / 750 users if not more... now, I can probably go with option 2, after some tweaking...
Now the question is pretty much how do I pass those values from my form to a database... the code is gonna be a book pretty much if we take into considetaion that we have 30+ countries per form and 4 or 5 services per country.... you know what I mean ?...

How do I tell the data base that the user click or check US / AU / NZ / GB / CA / BR / UY / AR and PY and some fo the services for each individual country and not MX / CL / SP / CH that were in between those other countries in the form ?....
Am I complicating things ?... I guess the database part I ahve it clear, but is just the logic in between my form and the database that is killing me at this point

TheServant

Well I think that you need to use arrays with loops.
So to make the form, you need your array with all your countries:

Expand|Select|Wrap|Line Numbers

$countries = array ( 'US', 'AU', 'NZ', ... );

Then you need to use a loop in a table like:

Expand|Select|Wrap|Line Numbers

 <form name="myform" method="post" action"...">

<table>

<?php

foreach( $contries as $country ) {

$co = $country; // eg. AU

$s1 = $country . "1"; // eg. AU1

$s2 = $country . "2"; // eg. AU2

$s3 = $country . "3";

$s4 = $country . "4";

echo("

<tr>

<td><input type="checkbox" name="$co" /></td>

<td><input type="checkbox" name="$s1" /></td>

<td><input type="checkbox" name="$s2" /></td>

<td><input type="checkbox" name="$s3" /></td>

<td><input type="checkbox" name="$s4" /></td>

</tr>

");

}

?>

</table>

</form>

Now when that sends it will send it all in a $_POST[] array which you can cycle through in a similar fashion to make a nice long insert command.

Expand|Select|Wrap|Line Numbers

 $query_string = "INSERT INTO mytable(";

foreach($countries as $country) {

$co = $country; // eg. AU

$s1 = $country . "1"; // eg. AU1

$s2 = $country . "2"; // eg. AU2

$s3 = $country . "3";

$s4 = $country . "4";
 
$query_string .= "$co, $s1, $s2, $s3, $s4,";

}

/* Need to remove last comma from string */
 
$query_string .= " VALUES ";

foreach($countries as $country) {

$co = $_POST[$country]; // eg. AU

$s1 = $_POST[$country . "1"]; // eg. AU1

$s2 = $_POST[$country . "2"]; // eg. AU2

$s3 = $_POST[$country . "3"];

$s4 = $_POST[$country . "4"];
 
$query_string .= "$co, $s1, $s2, $s3, $s4,";

}

/* Need to remove last comma from string */
 
mysql_query($query_string);

Now that is *extremely messy and there is probably a better way to do it, but it will give you some ideas on the logic atleast. I don't know if that works, but look at the logic of building a statement using loops to save you coding! This way, if you ever wanted to add a country, you need to add a column to your database, and change the $countries variable only. Check this thread as others might have quicker/neater ideas.

PHP / MY SQL design problem...

re: PHP / MY SQL design problem...

re: PHP / MY SQL design problem...

re: PHP / MY SQL design problem...

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about