[RESOLVED] Checkbox array

[Tezread]

I have developed an approach (see code below) that uses an array to list items which the user can choose. Their choice then executes a query which lists corresponding values to that choice. My question is how do I adapt this approach so it uses an array of checkboxes that can use the $_GET approach.
I will have three seperate checkbox arrays the user chooses from- qualitysystem, expertise and activities. The user checks any number of items they wish from each list and a list of corresponding organisations that meet the criteria selected are shown- here is my current approach

?php 
include("connect.php");
// array of service types  

$array = array('','Facilitating','Signposting','Providing information','Advice and guidance','Resources','Making choices','Training','Support','Mentoring','Model documents','Enabling networks','Peer assessment','Assessing','Accrediting'); 

// loop through the service types: 
foreach($array as $value) 
{ 
    echo '<a href="?page=dictionary&amp;servicetype=' . $value . '">' . strtoupper($value) . '</a> |' . "\n";
} 

// grab servicetype from URL 
if (isset($_GET['servicetype'])) 
{ 
    $servicetype = trim(strtolower($_GET['servicetype'])); 
} 
else { 
    $servicetype = ''; 
} 
?>
            </div>
            <h1><br>
            </h1>
          <div id="layer" style="position:absolute; width:603px; height:49px; z-index:13; left: 240px; top: 339px;">
            <?php

if (isset($_GET['servicetype'])) { 
include("connect.php");

$course = mysql_real_escape_string($_GET['servicetype']); 
$sql = "SELECT qualityadmin.authority_body, activity_participant.activity
FROM qualityadmin, activity_participant
WHERE qualityadmin.qualityadmin_id = activity_participant.qualityadmin_id AND activity_participant.activity ='$servicetype'"; 

$result = mysql_query($sql) or die(mysql_error());  

//echo $result."=result<br>
//";  

while($row=mysql_fetch_array($result))  

{ // NOTE this one ABOVE the echo 
//echo "result found!"; 
//echo $row[0]; 

echo '<a href="infolinks_details.php?authority_body='. urlencode($row[0]) .'">'. $row[0] .'</a><br><br>';  


} 
}
?>

[Tezread]

I was wondering- could one of the mods migrate this thread to the coding forum?

[HeyRay2]

I can probably help you here, but I will need a rundown on your database structure, and answers to the following questions:

• Can each organization be associated with multiple qualitysystem, expertise and activities entries?

• Will the qualitysystem, expertise and activities checkboxes be generated from tables in the database?

[Tezread]

Here is my db structure

qualityadmin

qualityadmin_id
quality_name
address1
address2
etc......

activity_participant

activity_participant_id
activity
qualityadmin_id

expert_participant

expert_participant_id
expert
qualityadmin_id

qualitysystem

qualitysystem_id
qualitysystem
qualityadmin_id

Each organisation can select multiple expertise, activities and quality systems. As far as I know activities/expertise/qualitysystem checkboxes are not generated from the database- though I would be interested to see how this would work too if you think it would make for a more efficient db approach.

Here is my current formhandler for further info:

<?php
// add data from form 1

$sql = "INSERT INTO qualityadmin SET qualityadmin_id =  NULL ";
foreach ($_SESSION['form1_data'] as $col =>$val ){
	if($col <> 'submit'){

	if ($col=='orgtype'){
	//ignore and  process later below for seperate table insert
	}
	elseif ($col=='activity'){
	//ignore and  process later below for seperate table insert
	}
			elseif ($col=='expert'){
	//ignore and  process later below for seperate table insert
	}
			elseif ($col=='qualitysystem'){
	//ignore and  process later below for seperate table insert
	}

	else{
	$sql .=", $col = '$val' ";
	}
	}
}

//////////////////////////////////
mysql_query($sql, $connection) or die(mysql_error()); 

//get last insert id
$sql ="select LAST_INSERT_ID() as id from qualityadmin";
$result = mysql_query($sql, $connection) or die(mysql_error()); 
$row=mysql_fetch_array($result);
$id = $row['id'];
//update orgtype_participant table
if($_SESSION['form1_data']['orgtype']){
foreach($_SESSION['form1_data']['orgtype'] as  $fvalue){
		$orgtypeid_sql ="INSERT INTO orgtype_participant SET orgtype_participant_id = NULL , qualityadmin_id = '$id', orgtype = '$fvalue' ";

	mysql_query($orgtypeid_sql, $connection) or die(mysql_error()); 

	}
	}
// update activity_participant table
if($_SESSION['form1_data']['activity']){
foreach($_SESSION['form1_data']['activity'] as  $fvalue){
		$service_sql ="INSERT INTO activity_participant SET activity_participant_id = NULL , qualityadmin_id = '$id', activity  = '$fvalue' ";
		mysql_query($service_sql, $connection) or die(mysql_error()); 

	}
	}
	// update expert_participant table
if($_SESSION['form1_data']['expert']){
foreach($_SESSION['form1_data']['expert'] as  $fvalue){
		$service_sql ="INSERT INTO expert_participant SET expert_participant_id = NULL , qualityadmin_id = '$id', expert  = '$fvalue' ";
		mysql_query($service_sql, $connection) or die(mysql_error()); 

	}
	}
			// update qualitysystem_participant table
if($_SESSION['form1_data']['qualitysystem']){
foreach($_SESSION['form1_data']['qualitysystem'] as  $fvalue){
		$service_sql ="INSERT INTO qualitysystem_participant SET qualitysystem_participant_id = NULL , qualityadmin_id = '$id', qualitysystem  = '$fvalue' ";
		mysql_query($service_sql, $connection) or die(mysql_error()); 

	}
	}

?>

[Tezread]

so close yet so far- can anyone help me :-)

[HeyRay2]

Sorry about the delay.

Long night dealing with my own coding problems ( coding for a hobby = 🙂 , coding to make a living = :glare: ).

Let me look over your code and I'll drum up some suggestions.

In the meantime, I would suggest normalizing your database structure. This will provide a more efficient method of tracking what expertise, activities and quality systems apply to each company, an easier time of maintaining them, and the ability to use your database to fill in your checkbox entries.

Something like this would work better:

qualityadmin
qualityadmin_id
quality_name
address1
address2
etc......

activity_participant
activity_participant_id
activity

activity_participant_rel
activity_participant_relid
qualityadmin_relid

expert_participant
expert_participant_id
expert

expert_participant_rel
expert_participant_relid
qualityadmin_relid

qualitysystem
qualitysystem_id
qualitysystem

qualitysystem_rel
qualitysystem_relid
qualityadmin_relid

The new tables basically map each qualityadmin to multiple expertise, activities and quality systems, and makes those tables easily maintainable.

I'll go over your code a little later once I have more time, and I'll talk more about normalizing your qualityadmin table as well, but for our current purposes it's not necessary.

[HeyRay2]

FYI, I haven't forgotten about this thread. I've just been busy, and I've got a long post to write for you, so I'm piecing it together throughout the day, and I'll post it later today.

[HeyRay2]

Okay, after a week of work frustration I can now get back to helping you with this. I apologize.

Just for reference, this would be your qualityadmin table:

qualityadmin
qualityadmin_id
quality_name
address1
address2
etc......

Basically, the following tables:

activity_participant
activity_participant_id
activity

expert_participant
expert_participant_id
expert

quality_systems
quality_system_id
quality_system

Will contain the values for each quality system, expertise and activity, each having a title/description and a unique ID. These will be used
for generating our checkboxes dynamically for a user to choose from.

The below tables:

activity_participant_rel
activity_participant_relid
qualityadmin_relid

expert_participant_rel
expert_participant_relid
qualityadmin_relid

quality_system_rel
qualitysystem_relid
qualityadmin_relid

Will contain two values, the ID number of the associated organization and the ID number of the corresponding quality system, expertise and activity.
These values will be compared against for what organizations match the user's search criteria.

Let's now query for all the quality systems, expertise and activities for use in our checkbox arrays:

// Get the quality systems
$sql_qualsys = "SELECT * FROM quality_systems ORDER by quality_system";
$qualsys_result = mysql_query($sql_qualsys) or die("Error fetching quality systems: ".mysql_error()); 
$qualsys_array = mysql_fetch_assoc($qualsys_result);

// Get the expertise
$sql_expert = "SELECT * FROM expert_participant ORDER by expert";
$expert_result = mysql_query($sql_expert) or die("Error fetching experts: ".mysql_error());
$expert_array = mysql_fetch_assoc($expert_result ); 

// Get the activities
$sql_activity = "SELECT * FROM activity_participant ORDER by activity";
$activity_result = mysql_query($sql_activity) or die("Error fetching activities: ".mysql_error()); 
$activity_array = mysql_fetch_assoc($activity_result);

Next, we'll take these arrays and print out a search form:

echo ('<form name="searchform" method="GET" action="search.php">
 <table>
 <tr>
 <td>Select the quality systems:</td>
 </tr>
 <tr> 
 <td>');

foreach($qualsys_array as $qualsys){
 echo ('<input type="checkbox" name="qualsys_checkbox[]" value="".$qualsys['quality_system_id']."">".$qualsys['quality_system']."'); 
}

echo ('</td>
 </tr>
 <tr>
 <td>Select the expertise:</td>
 </tr>
 <tr>
 <td>');

foreach($expert_array as $expert){
 echo ('<input type="checkbox" name="expert_checkbox[]" value="".$expert['expert_participant_id']."">".$expert['expert']."'); 
}

echo ('</td>
 </tr>
 <tr>
 <td>Select the activities:</td>
 </tr>
 <tr>
 <td>'); 


foreach($activity_array as $activity){
 echo ('<input type="checkbox" name="activity_checkbox[]" value="".$activity['activity_participant_id']."">".$activity['activity']."'); 
}

echo ('</td>
 </tr>
 <tr>
 <td>
  <input type="submit" name="search_submit" value="Search!">
 </td>
 </tr>
 </table>');

This will get you the basic elements for your search form, dynamically generated from the database. Next, you'll simply need to parse through
the $_GET array for the checkbox values the user chose, and add them to a SQL query:

if($_GET['search_submit']){
// Start the basic query
$sql_admin = "SELECT qa.* FROM quality_admin qa, activity_participant atc, activity_participant_rel act_rel, expert_participant ex,
			      expert_participant_rel ex_rel, quality_systems qs, quality_system_rel qs_rel
	      WHERE act_rel.activity_participant_relid = act.activity_participant_id AND act_rel.qualityadmin_relid = qa.quality_admin_id
	      AND ex_rel.expert_participant_relid = ex.expert_participant_id AND ex_rel.qualityadmin_relid = qa.quality_admin_id
	      AND qs_rel.quality_system_relid = qs.quality_system_id AND ex_rel.qualityadmin_relid = qa.quality_admin_id
	      ";

// Check the activity checkbox array for passed values, build a query line with them if there are any
if(count($_GET['activity_checkbox']) > 0){
	$act_sql_append = "AND act.activity_participant_id IN ('" . implode("','", $_GET['activity_checkbox']) . "')";

$sql_admin .= $act_sql_append;
}

// Check the expert checkbox array for passed values, build a query line with them if there are any
if(count($_GET['expert_checkbox']) > 0){
	$ex_sql_append = "AND ex.expert_participant_id IN ('" . implode("','", $_GET['expert_checkbox']) . "')";

$sql_admin .= $ex_sql_append;
}

// Check the quality system checkbox array for passed values, build a query line with them if there are any
if(count($_GET['qualsys_checkbox']) > 0){
	$qs_sql_append = "AND qs.quality_system_id IN ('" . implode("','", $_GET['qualsys_checkboxx']) . "')";

$sql_admin .= $qs_sql_append;
}	     

// Add ordering and grouping to the query, and append them to the query
$sql_group = "GROUP BY qa.qualityadmin_id";
$sql_order = "ORDER BY qa.quality_name";

$sql_admin .= $sql_group . $sql_order;


// Run the query, and fetch the results
$sql_result = mysql_query($sql_admin) or die("Error in search query: ".mysql_error());
$sql_array = mysql_fetch_assoc($sql_result);

//
// Print your results here
//

}

This code is far from perfect, and I'm sure you'll have to do some variable and syntax renaming to ensure that it works for your situation
and SQL tables. Basically this will give you a rundown of a general method of turning form values into search criteria.

Let me know if you have questions on this code, or need further help. I apologize again for the time I took to post, and I hope you find this helpful.

[Tezread]

thankyou for this - it is really helping the learning curve!!

[Tezread]

im getting an error message on line 109:

foreach($qualsys_array as $qualsys){ 
echo ('<input type="checkbox" name="qualsys_checkbox[]" value="".$qualsys['quality_system_id']."">".$qualsys['quality_system']."'); 
} 

I cannot see what is wrong here. looks fine

the error message is "unexpected t_string"

[HeyRay2]

Probably my sloppy typing... :queasy:

Try changing this:

echo ('<input type="checkbox" name="qualsys_checkbox[]" value="".$qualsys['quality_system_id']."">".$qualsys['quality_system']."');

to this:

echo '<input type="checkbox" name="qualsys_checkbox[]" value="'.$qualsys['quality_system_id'].'">'.$qualsys['quality_system'].'';