Is there a way, without changing the structure?

[suttercain]

HI everyone,

 

I have pleaded with the manager that this is a horrible table, but he won't listen. So now I'm left with this layout.

 

There is a form with 5 questions, all of which are radio buttons. Depending on the radio button selected, the value will change.

 

Question 4 = q4 (contains either value A, B, C)

Question 5 = q5 (contains either value D, E)

Question 6 = q6 (contains either value F, G, H, I)

Question 7 = q7 (contains either value J, K)

Question 8 = q8 (contains either value L, M)

 

The MySQL Table is as follows. The left column is the values of the radio buttons from the form, and each number is an answer.

 

SELECTIONS | LINKS

A  5

BD 34

BE 3

CDFJL 1234

CDFJM 34

CDFKL 134

CDFKM 34

CDGL 34

CDGM 34

CDHL 346

CDHM 346

CDIL 5

CDIM 5

CEPJL 123

CEPJM 3

CEPKL 13

CEPKM 3

CEGL 3

CEGK 3

CEHL 36

CEHM 36

CEIL 5

CEIM 5

 

My issue is this. If the user selects Value A and nothing else, the link will come up. If they select AD, the answer won't come up because there is no record for AD... or ADF, or ADFK and so on. Same goes for if they select BD, they'll get 34 but if they select BDF nothing will come up.

 

Is there any logic that would solve this issue without giving all possible configurations of selected values, or without having to change the structure?

 

Thanks.

 

[Mchl]

Well...

You could search for ADFK, then if you find nothing look for ADF (using LEFT() for example) then for AD and finally for A.

[suttercain]

Well...

You could search for ADFK, then if you find nothing look for ADF (using LEFT() for example) then for AD and finally for A.

 

I've never used the MySQL LEFT() function before, so I apologize in advance.

 

Here is a very basic script I write while experimenting with this:

 

$q4 = $_POST['q4'];
$q5 = $_POST['q5'];
$q6 = $_POST['q6'];
$q7 = $_POST['q7'];
$q8 = $_POST['q8'];

$allTogether = $q4.$q5.$q6.$q7.$q8;

$sql = "SELECT * FROM tableA
		  INNER JOIN hhdd_small ON (  tableA.link_id = tanleB.link_id )
		  WHERE tableB.selection = '".$q4.$q5.$q6.$q7.$q8."'";

echo "<ul>";

$reults = mysql_query($sql) or die(mysql_error());
while ($row = mysql_fetch_array($reults)) {
echo "<li>".$row['title']."</li>";
}

echo "</ul>";

 

How would I use LEFT() to accomplish what you suggested? Thanks.

[Mchl]

Something like this

$q4 = $_POST['q4'];
$q5 = $_POST['q5'];
$q6 = $_POST['q6'];
$q7 = $_POST['q7'];
$q8 = $_POST['q8'];

$allTogether = mysql_real_escape_string($q4.$q5.$q6.$q7.$q8);

for($i = strlen($allTogether); $i > 0; $i--) {

  $sql = "SELECT * FROM tableA
           INNER JOIN hhdd_small ON (  tableA.link_id = tableB.link_id )
           WHERE tableB.selection = LEFT('$allTogether',$i)";
  $results = mysql_query($sql) or trigger_error(mysql_error(),E_USER_WARNING);
  if(mysql_num_rows($results) > 0) break;  //break the loop when rows found
}

echo "<ul>";
while ($row = mysql_fetch_array($reults)) {
   echo "<li>".$row['title']."</li>";
}
echo "</ul>";