Need help with checkboxes

n_baswa

Hi friends,

I need ur help with checkboxes. I've a form in which there are multiple checkboxes. I'm able to insert the checkbox values to a single field in MySQL database.

The problem is that when i go to the edit page it should display the checkbox values as checked for those that r checked and then we can change the checkboxes and update the values in the database. I hope u understood my problem and wud help me.

Here r my codes.....

Code of the form for inserting the data to the database:

<?php
include('dbconn.php');

if( !empty($_POST) )
{
	$sql = "INSERT INTO attorney_profiles (firstname,lastname,email,biography,practice_areas,username,password)
VALUES 
('$_POST[firstname]','$_POST[lastname]','$_POST[email]','$_POST[biography]','{$_POST[checkbox][0]} {$_POST[checkbox][1]} {$_POST[checkbox][2]} {$_POST[checkbox][3]} {$_POST[checkbox][4]} {$_POST[checkbox][5]} {$_POST[checkbox][6]} {$_POST[checkbox][7]} {$_POST[checkbox][8]} {$_POST[checkbox][9]} {$_POST[checkbox][10]}','$_POST[username]','$_POST[password]')";

if (!mysql_query($sql,$con))
		{
			 die('Error: ' . mysql_error());
		}
else
		{
			header("Location: index.php");
			echo "1 record added";
		}

if(!$sql)
{
	echo "No data.";
}
}

?>


<html>
<head>
</head>
<body>

<script language="Javascript">
function checkForm(form)
{

if(form.username.value=="")
{
alert("Please enter a User Name!!");
form.username.focus();
return(false);
}
if(form.password.value=="")
{
alert("Please enter a Password!!");
form.password.focus();
return(false);
}
if(form.firstname.value=="")
{
alert("Please enter a First Name!!");
form.firstname.focus();
return(false);
}
if(form.lastname.value=="")
{
alert("Please enter a Last Name!!");
form.lastname.focus();
return(false);
}

}
</script>

<form name="form" action="profile_add.php" method="POST" onSubmit="return checkForm(this)">

<table border='0'>

<tr>
<td>
<b>User Name*:</b> <br>
<input type="text" name="username" value="<?php print($username);?>" />
</td>
</tr>

<tr>
<td>
<b>Password*:</b> <br>
<input type="password" name="password" value="<?php print($password);?>" />
</td>
</tr>

<tr>
<td>
<b>First Name* :</b> <br>
<input type="text" name="firstname" value="<?php print($firstname);?>" />
</td>
</tr>

<tr>
<td>
<b>Last Name* :</b> <br>
<input type="text" name="lastname" value="<?php print($lastname);?>" />
</td>
</tr>

<tr>
<td>
<b>E-Mail Address:</b> <br>
<input type="text" name="email" value="<?php print($email);?>" />
<i>(e.g., abc@xyz.com)</i>
</td>
</tr>

<tr>
<td>
<b>Biography:</b> <br>
<TEXTAREA name="biography" class="biography"><?php print($biography);?></TEXTAREA></td>
</tr>

<tr>
<td>
<b>Practice Areas:</b> <br>
<input type="checkbox" name="checkbox[]" value="Business" />Business<br>
<input type="checkbox" name="checkbox[]" value="Litigation"/>Litigation<br>
<input type="checkbox" name="checkbox[]" value="Landuse"/>Land Use<br>
<input type="checkbox" name="checkbox[]" value="Real Estate"/>Real Estate<br>
<input type="checkbox" name="checkbox[]" value="Seniorlaw"/>Senior Law<br>
<input type="checkbox" name="checkbox[]" value="Estates"/>Estates<br>
<input type="checkbox" name="checkbox[]" value="P.I."/>P.I.<br>
<input type="checkbox" name="checkbox[]" value="Employment"/>Employment<br>
<input type="checkbox" name="checkbox[]" value="Family"/>Family<br>
<input type="checkbox" name="checkbox[]" value="Criminal"/>Criminal<br>
<input type="checkbox" name="checkbox[]" value="LL/T"/>LL/T<br>
</tr>

</table>

<br>
<input type="submit" value="Add" id="submit" name="submit" />
<input name="hidSubmit" type="hidden" id="hidSubmit2" value="true">
</form>

</body>
</html>

The above code perfectly adds the checked values to the database.

Now the edit form code:

<?php
include('dbconn.php');

$attorney_id=$_GET['attorney_id'];

$query="SELECT * FROM attorney_profiles WHERE attorney_id='$attorney_id'";

$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();

$i=0;
while ($i < $num) {
$attorney_id=mysql_result($result,$i,"attorney_id");
$firstname=mysql_result($result,$i,"firstname");
$lastname=mysql_result($result,$i,"lastname");
$email=mysql_result($result,$i,"email");
$biography=mysql_result($result,$i,"biography");
$practice_areas=mysql_result($result,$i,"practice_areas");
$username=mysql_result($result,$i,"username");
$password=mysql_result($result,$i,"password");
$i++;
}

?>

<script language="Javascript">
function checkForm(form)
{

if(form.username.value=="")
{
alert("Please enter a User Name!!");
form.username.focus();
return(false);
}
if(form.password.value=="")
{
alert("Please enter a Password!!");
form.password.focus();
return(false);
}
if(form.firstname.value=="")
{
alert("Please enter a First Name!!");
form.firstname.focus();
return(false);
}
if(form.lastname.value=="")
{
alert("Please enter a Last Name!!");
form.lastname.focus();
return(false);
}
}
</script>

<html>
<body>

<form name="form1" action="profile_editdb.php" method="POST" onSubmit="return checkForm(this)">

<table border='0'>

<tr>
<input type="hidden" name="attorney_id" value="<?php print($attorney_id);?>" /></p>
</tr>

<tr>
<td>
<b>User Name*:</b> <br>
<input type="text" name="username" value="<?php print($username);?>" />
</td>
</tr>

<tr>
<td>
<b>Password*:</b> <br>
<input type="text" name="password" value="<?php print($password);?>" />
</td>
</tr>

<tr>
<td>
<b>First Name* :</b> <br>
<input type="text" name="firstname" value="<?php print($firstname);?>" />
</td>
</tr>

<tr>
<td>
<b>Last Name* :</b> <br>
<input type="text" name="lastname" value="<?php print($lastname);?>" />
</td>
</tr>

<tr>
<td>
<b>E-Mail Address:</b> <br>
<input type="text" name="email" value="<?php print($email);?>" />
</td>
</tr>

<tr>
<td>
<b>Biography:</b> <br>
<TEXTAREA name="biography" class="biography"><?php print($biography);?></TEXTAREA></td>
</tr>

<tr>
<td>
<b>Practice Areas:</b> <br>
<input type="checkbox" name="checkbox[]" value="Business"/>Business<br>
<input type="checkbox" name="checkbox[]" value="Litigation"/>Litigation<br>
<input type="checkbox" name="checkbox[]" value="Landuse"/>Land Use<br>
<input type="checkbox" name="checkbox[]" value="Real Estate"/>Real Estate<br>
<input type="checkbox" name="checkbox[]" value="Senior Law"/>Senior Law<br>
<input type="checkbox" name="checkbox[]" value="Estates"/>Estates<br>
<input type="checkbox" name="checkbox[]" value="P.I."/>P.I.<br>
<input type="checkbox" name="checkbox[]" value="Employment"/>Employment<br>
<input type="checkbox" name="checkbox[]" value="Family"/>Family<br>
<input type="checkbox" name="checkbox[]" value="Criminal"/>Criminal<br>
<input type="checkbox" name="checkbox[]" value="LL/T"/>LL/T<br>
</tr>

</table>

<br>

<input type="submit" value="Update" id="update" name="update" />
<input type="hidden" name="ud_attorney_id" value="<?php print($attorney_id); ?>" />
<input type="button" value="Delete" onClick="if (confirm('Are you sure you want to delete this profile?')) window.location='profile_delete.php?attorney_id=<?php print($attorney_id) ?>';">

</form>

</body>
</html>

The above code works fine with the textboxes by displaying the values from the database, but it doesn't display the checked values for the checkboxes. Where do i change what code?

and the code for updating:

<?php
include('dbconn.php');

$sql = "UPDATE attorney_profiles SET username='". $username ."', password='". $password ."', firstname='". $firstname ."', lastname='". $lastname ."', email='". $email ."', biography='". $biography ."', practice_areas='". $practice_areas ."' WHERE attorney_id='".$attorney_id."'";

if (!mysql_query($sql,$con))
		{
			 die('Error: ' . mysql_error());
		}
else
        {
			header("Location: index.php");
		}
?>

This code updates all the textbox values, but not the checkbox values....

Please help.

bradgrafelman

Your code for inserting data into the DB is vulnerable to SQL injection attacks. User-supplied data should never be placed directly into a SQL query! Instead, sanitize it with a function such as [man]mysql_real_escape_string/man.
Same as above for the edit script - don't trust the id given by the user. Cast it to an integer first:
```
$attorney_id = (isset($_GET['attorney_id']) ? (int)$_GET['attorney_id'] : 0);
```
Since you're only wanting 1 row back from the server, I would suggest adding a "LIMIT 1" onto the end of your query, simply for optimization purposes.
You close the connection to your MySQL server before the while() loop. How is PHP supposed to retrieve data from MySQL when you've already disconnected from it?

Your entire code to retrieve the data could be rewritten like so:

$query="SELECT * FROM attorney_profiles WHERE attorney_id=$attorney_id LIMIT 1";
$result=mysql_query($query);
$data = mysql_fetch_assoc($result);
extract($data, EXTR_SKIP);

Even without using [man]extract/man, retrieving rows is normally done like so:

$query="SELECT * FROM attorney_profiles WHERE attorney_id=$attorney_id LIMIT 1";
$result=mysql_query($query);

while($data = mysql_fetch_assoc($result)) {
    // do stuff with this row
}

Now, having said all of that, you might want to re-think how you're doing this "practice areas" bit. Here's what I would do:

Make a separate table in your DB... call it practice_areas. It would consist of two columns like so:

CREATE TABLE `practice_areas` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB

Then, you would display the areas on the intial form like so:

<td> 
<b>Practice Areas:</b> <br>
<?php
$query = 'SELECT `id`, `name` FROM `practice_areas` ORDER BY `id` DESC';
$exec = mysql_query($query);

while($data = mysql_fetch_assoc($exec))
    echo '<input type="checkbox" name="checkbox[]" value="'.$data['id'].'" />'.$data['name']."<br>\n";
?>
</td>

Then, you'd modify your INSERT query only slightly; instead of naming all of the $POST['checkbox'] elements in your query, you would do something like this:

$practice_ids = implode(',', $_POST['checkbox']);

and just use $practice_ids in your INSERT query.

To display this data (e.g. on the frontend of the site), you would have a query like so:

SELECT a.attorney_id, a.firstname, a.lastname, a.email, a.biography,
    GROUP_CONCAT(p.name ORDER BY p.id SEPARATOR ', ') AS practice_areas
FROM attorney_profiles AS a
LEFT JOIN practice_areas AS p ON FIND_IN_SET(p.id, p.id) > 0 GROUP BY p.name;

Now, after doing all of that, we can address the problem of updating w/ multiple checkboxes. First, for your update script, you'll have a simpler query:

SELECT * FROM attorney_profiles WHERE attorney_id=$attorney_id LIMIT 1

Now, here's how you would handle the checkboxes; just as in the original form above that INSERT's new attorney records, the update form should dynamically create the checkboxes and see if they should be checked or not, like so:

<td> 
<b>Practice Areas:</b> <br>
<?php
$practice_query = 'SELECT `id`, `name` FROM `practice_areas` ORDER BY `id` DESC';
$practice_exec = mysql_query($practice_query);

while($practice_data = mysql_fetch_assoc($practice_exec)) {
    if(strpos($data['practice_areas'], $practice_data['id']) !== FALSE)
        $checked = 'checked="checked" ';
    else
        $checked = '';
    echo '<input type="checkbox" name="checkbox[]" value="'.$practice_data['id'].'" ' . $checked . '/>'.$practice_data['name']."<br>\n";
}
?>
</td>

Obviously, you'll also modify your UPDATE query in the same way you modified the INSERT query above; use implode() to combine the ids from the array of checkboxes into a single, comma-delimited string, and store that in the DB.

n_baswa

Thank you bradgrafelman. You have given a lot of good information and explanation. I really appreciate it whole-heartedly.

Thank u so much for ur help.

I might need ur help in the future.

n_baswa

hi bradgrafelman,

i'm referring to the thread :

http://www.phpbuilder.com/board/showthread.php?t=10343268&highlight=checkboxes

You had provided me with much explanation on this, but i'm having a little problem. I hope you can help me in fixing this.

i did not understand the following part of the code:

<td>
<b>Practice Areas:</b> <br>
<?php
$practice_query = 'SELECT `id`, `name` FROM `practice_areas` ORDER BY `id` DESC';
$practice_exec = mysql_query($practice_query);

while($practice_data = mysql_fetch_assoc($practice_exec)) {
    if(strpos($data['practice_areas'], $practice_data['id']) !== FALSE)
        $checked = 'checked="checked" ';
    else
        $checked = '';
    echo '<input type="checkbox" name="checkbox[]" value="'.$practice_data['id'].'" ' . $checked . '/>'.$practice_data['name']."<br>\n";
}
?>
</td>

I know that it has to do with showing the checked boxes as checked, but i'm afraid it is not doing so. But the update is working fine. How do i fix it so that it shows the checked boxes as checked.

and can u also please explain the following code also:

SELECT a.attorney_id, a.firstname, a.lastname, a.email, a.biography,
    GROUP_CONCAT(p.name ORDER BY p.id SEPARATOR ', ') AS practice_areas
FROM attorney_profiles AS a
LEFT JOIN practice_areas AS p ON FIND_IN_SET(p.id, p.id) > 0 GROUP BY p.name;

looking forward to hear from you.

bpat1434

Why start a new thread? It's dealing under the same general issue, just reply to your other thread 🙂 I'm merging them.

n_baswa

anyone please help me out........

bpat1434

I'll bite.. where is $data populated at?

n_baswa

are you talking abt this....

	<?php
	$practice_query = mysql_query("SELECT areaID, area_name FROM practice_areas ORDER BY area_name");

while($data = mysql_fetch_assoc($practice_query)) 
{
	$areaID = $row['areaID'];
	$area_name = $row['area_name'];

	if(strpos($data['area_name'], $data['areaID']) !== FALSE)
	{
    	$checked = 'checked="checked" '; 
	}
	else
	{	
		$checked = '';  
	}
	echo '<input type="checkbox" name="practice_areas[]" value="'.$data['areaID'].'"'. $checked .' />'.$data['area_name']."<br>\n";
}

?>

It is still not working.

bpat1434

Is that in the same exact file as where you have

<td>
<b>Practice Areas:</b> <br>
<?php
$practice_query = 'SELECT `id`, `name` FROM `practice_areas` ORDER BY `id` DESC';
$practice_exec = mysql_query($practice_query);

while($practice_data = mysql_fetch_assoc($practice_exec)) {
    if(strpos($data['practice_areas'], $practice_data['id']) !== FALSE)
        $checked = 'checked="checked" ';
    else
        $checked = '';
    echo '<input type="checkbox" name="checkbox[]" value="'.$practice_data['id'].'" ' . $checked . '/>'.$practice_data['name']."<br>\n";
}
?>
</td>

n_baswa

yes, the one that i gave is the original i'm using the other one which is in the thread is an example given by bradgrafelman.

can u pls help me in figuring this out. I need to complete this project by the end of this week.

Pls help me out.

bpat1434

Okay, but where is the variable $data actually populated? I get your code and Brad's code, but if $data isn't populated, then no checkboxes would be checked. The issue here is that $data isn't populated anywhere. I'm guessing that $data is supposed to relate to the list of areas stored in the database for each lawyer.

But that's just a guess...

n_baswa

actually, that's what my problem is also. i don't know how to do it. i'm not able to find the difference between $data and $practice_data.

Is thr anyway u can help me out or atleast any way to contact bradgrafelman.

bradgrafelman

I skimmed through this thread earlier, but haven't had time to dive in and re-read the code and my suggested solution (it's finals week for me) - I'll try to get back to you when I can.

n_baswa

good to hear from you finally. I'll be waiting for u to look at it and pls get back to me as soon as possible.

and yes....All the very best for ur finals.

I hope u didn't mind me contacting u over Yahoo IM.

bradgrafelman

Can you show me your script in entirety, that way I can see where you're at? So many separate code snippets have been posted, I'm not sure exactly which part to explain first.

Also, can you tell me what MySQL tables you have created (if you could show me the 'SHOW CREATE TABLE tbl_name' output too that'd be helpful)?

n_baswa

I've two tables:
1. practices_areas(areaID, area_name, description).
2. attorney_profiles(attorneyID, firstname, lastname, email, password, biography, areas_servicing, photo).

The attorneys fill in a form to save their individual profiles. The area_name from practice_areas makes checkboxes for the filed areas_servicing in attorney_profiles table.

The code to do this is as follows:

<b>Practice Areas:</b> <br>
<?php
$sql1 = mysql_query("SELECT areaID, area_name FROM practice_areas ORDER BY area_name"); 
while($row1 = mysql_fetch_array($sql1))
{
    echo '<input type="checkbox" name="areas_servicing[]" value="'.$row1['area_name'].'" />'.$row1['area_name']."<br />";
}
?> 


The above code makes checkboxes for each area_name in the practice_areas table.

Now, when the attorneys check the areas and click submit, it inserts the checkbox values into the areas_servicing field with a comma separator. The code for inserting is as follows:
PHP Code:
 $practice_ids = implode(',', $_POST['areas_servicing']); 

if( !empty($_POST) )
    {

    $sql = "INSERT INTO attorney_profiles (firstname,lastname,email,password,biography,practice_areas,photo) VALUES ('$firstname','$lastname','$email','$password','$biography','$practice_ids','{$_FILES['photo']['name']}')";
}

Suppose i check appeals and criminal law with ids 2 and 5 then then above code adds 2, 5 into the areas_servicing in the attorney_profiles table. and these ids come from the practice_areas table.

Then for updating i've a query which updates the checkbox values, but when the form is first seen it doesn't show the checkbox values as checked.

Here is the code:

<b>Practice Areas:</b> <br>
    <?php
    $practice_query = mysql_query("SELECT areaID, area_name FROM practice_areas ORDER BY area_name");

while($data = mysql_fetch_assoc($practice_query)) 
{
    $areaID = $data['areaID'];
    $area_name = $data['area_name'];

    if(strpos($data['area_name'], $data['areaID']) !== FALSE)
    {
        $checked = 'checked="checked" '; 
    }
    else
    {    
        $checked = '';  
    }
    echo '<input type="checkbox" name="practice_areas[]" value="'.$data['area_name'].'"'. $checked .' />'.$data['area_name']."<br>\n";
}

?>

The above code should be showing the checked boxes as checked, but it doesn't work.

The code for updating is as follows:

 $practice_ids = implode(', ', $_POST['practice_areas']); 

$sql = "UPDATE attorney_profiles SET firstname='". $firstname ."', lastname='". $lastname ."', email='". $email ."', biography='". $biography ."', practice_areas='". $practice_ids ."', photo='". $_FILES['photo']['name'] ."' WHERE attorneyID='".$_POST[attorneyID]."'";

it updates the checkboxes. for e.g, if i update from appeals to suppose business law, it will update the 2 to 4, which r again the ids of those area_name.

How do i retrieve the actual area_name instead of those ids which are stored corresponding to the area_names.

For e.g., if my profile has areas_servicing column as 2, 5. My profile on the public pages should show up the actual names corresponding to these ids from the practice_areas table.

Also, if someone clicks on an area_name, it should show up all the attorneys servicing that area.

n_baswa

finally I got it working using three tables like this.
The first table has the practice_areas, the second has the attorney_profiles and the third has the attorney_practices with just the attorneyID and areaID.

But now, i'm having trouble with retrieving data from the three tables. I'm able to write the wuery using JOIN which also is giving the correct results on the MySQL CommandLineClient, but it is giving me error when i use the PHP

Here is my query:

if (isset($_GET['areaID'])) {
$query=mysql_query("SELECT attorney_practices.*, attorney_profiles.*, practice_areas.* FROM attorney_practices LEFT JOIN attorney_profiles ON (attorney_practices.attorneyID=attorney_profiles.attorneyID) LEFT JOIN practice_areas ON (attorney_practices.areaID=practice_areas.areaID) WHERE areaID='".$_GET['areaID']."'");
}

while ($row = mysql_fetch_array($query)) 
{
	$areaID = $row['areaID'];
	$area_name = $row['area_name'];
	$description = $row['description'];
	$attorneyID = $row['attorneyID'];
	$firstname = $row['firstname'];
	$lastname = $row['lastname'];
}
	echo "<h3><u>$area_name</u></h3>";
	echo "$description<br /><br />";
	echo "<b>Attorneys Servicing this area include:</b><br />
			 $firstname $lastname<br /><br />";

The above query should retrieve the attorney names which practice the area with the selected areaID.

The following is the error i'm getting:
PHP Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\www\html\ThorpPurdy\view_details.php on line 12, referer: http://localhost/ThorpPurdy/index.php

Pls let me know how do i fix it and get it working.

bradgrafelman

Can you show us the SQL error message as well as the query being sent?

n_baswa

thr is no error with the query 'coz it's giving the correct data when i run it in the MySQL Command LineClient.

Tha above query is not working when i use the mysql_fetch_array and retrieve the data.

But of course, this code works without any error, but it shows up only the last record:

if (isset($_GET['areaID'])) {
$query=mysql_query("SELECT attorney_practices.*, attorney_profiles.* FROM attorney_practices LEFT JOIN attorney_profiles ON attorney_practices.attorneyID=attorney_profiles.attorneyID WHERE areaID='".$_GET['areaID']."'");
}

mausom_t

I am having a problem I have made a scrap book the values are dispalyed with two buttons delete and reply when we press the delete button the id (i.e the primary key) related to the button that particular msg gets deleted but when I sent a reply it goes to the first person in the scrap book when I used the same primary id to relate.
Plz help me the codes are here.

<pre>
//get the viewer details
$q2 = "select * from classmates_members where MemberID = '$_SESSION[MemberID]' ";
$r2 = mysql_query($q2) or die(mysql_error());
$a2 = mysql_fetch_array($r2);

if($GET[id] == $SESSION[MemberID] || $a2[price2] > '0')
{
if(!empty($a1[email]))
{
$contacts[] = "Click <a href=\"mailto:$a1[email]\" class=RedLink>here</a> to email";
}

$contact_info = implode("<br>", $contacts);

}
else
{
$contact_info = "<br>Click <a href=\"upgrade.php\" class=RedLink>here</a> to upgrade your account and access the member contact info!";
}

if($GET[id] == $SESSION[MemberID])
{
$edit_delete = "<a class=GreenLink href=\"YourAccount.php\">Edit your profile</a> | <a class=RedLink href=\"delete.php\" OnClick=\"return ConfirmDelete('your profile');\">Delete your profile</a>";
}
if ($POST[message]!==null)
{
/$sql="INSERT INTO classmates_scrap (scrap_by,scrap_from,scrap,date_time) VALUES
('$a2[CurrentName]','$a1[CurrentName]','$POST[message]',now())";/
$sql="INSERT INTO classmates_scrap (scrap_by,scrap_from,scrap,date_time) VALUES
('$a2[MemberID]','$GET[id]','$POST[message]',now())";
if (!mysql_query($sql,$connection))
{ die('ERROR: '.mysql_error()); }
}</pre>

and the action page

<pre>
q3="Select * from classmates_scrap where scrap_id='$_GET[scrap_id] LIMIT 1'";
$result= mysql_query($q3) or die(mysql_error());
$a3=mysql_fetch_array($result);

$sql="INSERT INTO classmates_scrap (scrap_by,scrap_from,scrap,date_time) VALUES
('$a3[scrap_from]','$a3[scrap_by]','$POST[message]',now())";
if (!mysql_query($sql,$connection))
{ die('ERROR: '.mysql_error()); }
header("location:profile.php?id=$GET[id]");
</pre>