Checkbox checked (associated tables) - editing with PHP mySQL

[lasha]

Hello guys

 

I have a small problem and please help to resolve this.

I have associated categories in the database. During the edit, i need, that

categories which were checked when first somebody added his/her info that categories to be selected...

 

In my attached picture is described what i want to do.

 

Thanks for advice...

 

And here is my code

 

<?php
include ("db.php");
if (isset($_GET['id'])) {$id = $_GET['id'];}

if (!isset($id))
{
$result = mysql_query("SELECT id, name, sur FROM info");      
$myrow = mysql_fetch_array($result);
do 
{
printf ("<p style='float:left; margin-left:6px;'>  <a href='edit.php?id=%s'>%s</a>  |</p>",$myrow["id"],$myrow["name"]);
}
while ($myrow = mysql_fetch_array($result));
}
else
{

       
$result2 = mysql_query("SELECT * FROM info WHERE id=$id");      
$myrow2 = mysql_fetch_array($result2);

$sql = "SELECT cat_id, cat_name FROM category";
$res = mysql_query($sql) or die(mysql_error());
    
$checkboxes = '';
while (list($id, $cat) = mysql_fetch_row($res)) {
$checkboxes .= "<input type='checkbox' name='category[]' value='$id' /> $cat<br />";
}

print <<<HERE

<form method = 'post' action = 'update.php' >
    Name: <input type='text' name='name' value="$myrow2[name]" size='50' /><br />
    Surname: <input type='text' name='sur' value="$myrow2[sur]" size='50' /><br />
    Categories:<br />
    $checkboxes
    <input type='submit' name='btnSubmit' value='Submit' />
</form>

HERE;
}
?>

[Christian F.]

You'll need to read up on JOIN statements in MySQL, as that's what you need to fetch the relevant data from the database (in one query). From there it is quite easy to loop through the categories, and match the ID with the ones listed in the user-query.

 

PS: Please indent your code properly, as it makes your code a whole lot easier to read. Which will help prevent lots of issues later on, when you (and others) are trying to understand what the code does.

[lasha]

Thank you Christian,

 

I read MYSQL Join Statements, understend and now i know how to display data with JOIN, but here is a problem anyway... I dont know how to make checkbox checked (in edit panel, which i described on picture) if there is category record on database for certain info ($id)...

 

If you can help me to figure out what i can do with my code to get this program as was in idea, it will be gread

 

Thank you again for attention

[lemmin]

After creating an array containing the user's categories you can check the boxes like so:

 

while (list($id, $cat) = mysql_fetch_row($res)) {
if (in_array($cat, $usercats))
	$checked = ' checked="checked"';
else
	$checked = '';

$checkboxes .= "<input type='checkbox' name='category[]' value='$id'$checked /> $cat<br />";
}

[lasha]

Thanks Lemmin it looks logic )

But it returns error:

 

Warning: in_array() [function.in-array]: Wrong datatype for second argument in C:\Program Files\VertrigoServ\www\test\edit.php on line 86

 

I tryed but i can't understend why...

[lemmin]

In my example, $usercats is an array that you would populate with the relevant categories. I think you might be passing it a mysql resource. If you are still looping through that resource above, you can use that loop to build the $usercats array. Post your updated code if you need more help.

[lasha]

Okey i did it and may be wrong becouse there is no more error but now all checkboxes ar checked...

 

I updated like that

 

while (list($id, $cat) = mysql_fetch_row($res)) {

$usercats = array($id, $cat); //Is this wrong array?

if (in_array($cat, $usercats))
	$checked = ' checked="checked"';
else
	$checked = '';
	$checkboxes .= "<input type='checkbox' name='category[]' value='$id'$checked /> $cat<br />";
}

[lemmin]

Yes, that would be the wrong array. You mentioned previously that you are using a JOIN query to associate your categories table. Could you post that code with any iteration through the result?

[lasha]

Okay, I give up, maybe i missed something...

I just dont understed properly php programming and thats why cant figur code out...

 

Christian said that i must learn JOIN query and i understend this part, but in my code there is no JOIN in use... I posted my code above which is now in use for my "editing"... If you can just say what i must read to do this program like it have to be...?

 

P.S. i'm sorry for my english, may be i just did explain everything wrong way...

[lasha]

????  :'(

[lemmin]

Here is an example query that should return all of the information you are looking for at once.

 

SELECT
cat_name,
cat_id,
name,
id,
sur
FROM
info,
info_cats,
Category
WHERE
info.id = info_cats.info_id AND
Category.cat_id = info_cats.cat_id

 

Try running that query and outputting the results as arrays to see what you are working with:

 

while ($row = mysql_fetch_assoc($result))
    print_r($row);

[lasha]

Okey i stuck with this:

 

<?php
include ("db.php");
if (isset($_GET['id'])) {$id = $_GET['id'];}

if (!isset($id))
{
$result = mysql_query("SELECT id, name, sur FROM info");      
$myrow = mysql_fetch_array($result);
do 
{
printf ("<p style='float:left; margin-left:6px;'>  <a href='edit.php?id=%s'>%s</a>  |</p>",$myrow["id"],$myrow["name"]);
}
while ($myrow = mysql_fetch_array($result));
}
else
{

$result2 = mysql_query("SELECT * FROM info WHERE id=$id");      
$myrow2 = mysql_fetch_array($result2);


$sql = "SELECT * FROM category, info, info_cats WHERE info.id = info_cats.info_id AND category.cat_id = info_cats.cat_id AND id='$id'";
$res = mysql_query($sql) or die(mysql_error());

while (list($id, $cat) = mysql_fetch_array($res)) {

if (in_array($id, $res))
	$checked = ' checked="checked"';
else
	$checked = '';
	$checkboxes .= "<input type='checkbox' name='category[]' value='$id'$checked /> $cat<br />";

}


print <<<HERE

<form method = 'post' action = 'update.php' >
    Name: <input type='text' name='name' value="$myrow2[name]" size='50' /><br />
    Surname: <input type='text' name='sur' value="$myrow2[sur]" size='50' /><br />
    Categories:<br />
    $checkboxes
    <input type='submit' name='btnSubmit' value='Submit' />
</form>

HERE;
}
?>

 

Output is on picture...

 

Please say to me what part is wrong??? 

[Christian F.]

The best advice I can give you at this point, is to walk through your code line for line, translating what it does to proper English. Doing that should highlight some of the logic errors you have, and help you figure out how to write the script to actually work.

 

I'll give you one hint: You have not fetched the categories for the user, amongst other things.

[lemmin]

$res is a mysql resource, not an array.

 

As you can see by your output, you are now only printing the categories that are associated with the specified user id. I was basing that query on the fact that you had another query returning JUST the list of categories. If you want to include the unassociated categories, you would want to use a LEFT JOIN. Let's stay simple and just use two queries.

 

Let's consider the two sets of information you need; the full list of categories and the list of associated categories. Since the former is bigger, we want to iterate through those. First let's get the latter:

 

$sql = "SELECT cat_id FROM info, info_cats WHERE info.id = info_cats.info_id AND id='$id'";
$res = mysql_query($sql) or die(mysql_error());
$user_cats = array();
while ($id = mysql_fetch_row($res))
{
$user_cats[] = $id[0];
}

 

$user_cats now contains all the cat_id's that are associated with the user specified. (I took out the Category table from the query because we get that information below.)

 

Now let's take your original loop and add the logic to it:

 

$sql = "SELECT cat_id, cat_name FROM category";
$res = mysql_query($sql) or die(mysql_error());
    
$checkboxes = '';
while (list($id, $cat) = mysql_fetch_row($res))
{
if (in_array($id, $user_cats))
	$checked = ' checked="checked"';
else
	$checked = '';
$checkboxes .= "<input type='checkbox' name='category[]' value='$id' /> $cat<br />";
}

 

That should get you your desired output.

[lasha]

Many thanks to you  it works perfectly...

I just start studiing and even dont know what is best book or video to learn php...

I know that this forum is for more educated php programmers, but it was for me a big riddle to write this mini program by someones help...

 

I will be happy if you advice literature for programming...

 

Thank you again

lasha

 

 

P.S. in the end of program was missing variable $checked... 

 

$checkboxes .= "<input type='checkbox' name='category[]' value='$id'$checked   /> $cat<br />";

[Elrohir]

Hi, I searched the net and I went through a lot but I never found what I need so I turn for help to you:
I have done a table viacerimi input / text input and / chcekbox. the checkboxes need to make yourself zaskrtavali based on the value in the database. Thus, if the db find the values ​​for example: when a db Table WAIT and value where I wait so let tick checkbox wait, but if there are defaults does so remains unchecked, further value if I say so in a well-tick the checkbox well and carried.

<?php
include ('../db.php');

$con="SELECT * FROM  db_sn JOIN projekt ON db_sn.IDX=projekt.ID";
$vypis2=mysql_query($con) or die($con."<br/>".mysql_error());

$user_cats = array();
while ($id = mysql_fetch_row($vypis2))
{
   $user_cats[] = $id[0];
}
$con1 = "SELECT IDx, waiting FROM db_sn";
$res = mysql_query($con1) or die($con1."<br/>".mysql_error());
$checkboxes = '';
while (list($id, $waiting) = mysql_fetch_row($res))
{
   if (in_array($id, $user_cats))
      $checked = 'checked="checked"';
   else
      $checked = '';
   $checkboxes .= "<input type='checkbox' name='category[]' value='".$id."' /> ".$waiting."<br />";
}

while ([color=#800000]$data[/color] = mysql_fetch_array([color=#800000]$vypis2[/color])){
?>
<td align="center"><?php echo($data['ok_meno']) ?></td>
<td><input id="wait" type="checkbox" name="wait" value="wait" /></td>
<td><input id="dobre" type="checkbox" name="dobre" value="dobre" /></td>
<td><input id="odniest" type="checkbox" name="odniest" value="odniest" /></td>
<?php } ?>

Edited October 2, 2013 by Elrohir