...

View Full Version : prechecking checkboxes, ternaries, & insert mult. rows



turpentyne
11-10-2011, 06:03 PM
I have a task that seems a little daunting to me. I'm trying to build an updatable form with numerous checkboxes to update a database. Each checkbox would be an individual entry into the link table. And once I've figured it all out, I have to figure out how to pull results/insert data for three different tables on one page. eek!

I have a table of objects, a table of adjectives, and a table to link them (which is what I want to update). I built a page that populates with checkboxes based on the table of adjectives and their corresponding id. That went fine.

Now I want to query the database to figure out how to mark them "checked" if there's an entry in the link table that corresponds with my object_id and that checkbox's id.

Later I'll have to figure out how to enter each checked box into the link table as a row - unless it already exists, which I assume can be controlled with a key in the database.

I've made it this far, but I'm not sure how to write the ternary to populate each checkbox, or how to run an insert loop for each checkbox. overwhelming! Here's what I got:




// here is the query that I'm trying to write
// for inserting a row for each checkbox into the table

$sql1 = array();
foreach( $data as $row ) {
$sql1[] = '("'.mysql_real_escape_string($_POST['plant_id']).'", '.$_POST['food'].')';
}
mysql_query('INSERT INTO plant_edible_link(plant_id, term_id) VALUES '.implode(',', $sql1));



// below are 2 queries. First is used to create checkboxes
//Second query is to check or uncheck the boxes

$quer2=mysql_query("SELECT `ID`, `usage` FROM `edible_uses` order by `usage`");
$quer2b=mysql_quer("SELECT `plant_id`, `edible_id` FROM `plant_edible_link`");
$q2b = mysql_fetch_array($quer2);
if($q2b)
{

$ch2 = foreach($q2b) ? 'checked="checked"' : NULL;
}

// below are the checkboxes created from adjectives table...

while($row = mysql_fetch_array($quer2)) {
if($row[id]==@$cat){echo "<input type='checkbox' name='option1'". print $ch2 ."value='$row[id]'>$row[usage]<BR>";}
else{echo "<input type='checkbox' name='option1' value='$row[id]'>$row[usage]<br>";}
}

mlseim
11-11-2011, 02:23 AM
Where do the 3 variables shown in red come from?
What are they?

$sql1 = array();
foreach( $data as $row ) {
$sql1[] = '("'.mysql_real_escape_string($_POST['plant_id']).'", '.$_POST['food'].')';
}
mysql_query('INSERT INTO plant_edible_link(plant_id, term_id) VALUES '.implode(',', $sql1));


There has to be some scripting or forms or ??? you're not showing us?


.

turpentyne
11-11-2011, 05:42 PM
Sorry, I was trying to post a simplified example. I've since been trying several different things in an attempt to understand how to do it: This is what I've gotten to, and I'm sure it's a confusing mess :




<?php


if (isset($_POST['submit'])){

require ('db.php');


// first insert. I want to figure out how to insert each checked box as a row, with the plant_id that's in variable $id


if(isset($_POST['option2'])){
$sql1 = array();
foreach( $sql1 as $row ) {

$sql1[] = '("'.mysql_real_escape_string($_POST['plant_id']).'", '.$_POST['food'].'")';
}
mysql_query('INSERT INTO plant_edible_link(plant_id, term_id) VALUES '.implode(',', $sql1));
}

// second one
if(isset($_POST['option3'])){
$sql2 = array();
foreach( $sql2 as $row ) {
$sql2[] = '("'.mysql_real_escape_string($_POST['plant_id']).'", '.$_POST['meds'].'")';
}
mysql_query('INSERT INTO plant_uses_link(plant_id, term_id) VALUES '.implode(',', $sql2));
}


// third one - maybe there's a way to combine these three into one query statement?
if(isset($_POST['option4'])){
$sql3 = array();
foreach( $sql3 as $row ) {
$sql3[] = '("'.mysql_real_escape_string($_POST['plant_id']).'", '.$_POST['uses'].'")';
}
mysql_query('INSERT INTO plant_edible_link(plant_id, term_id) VALUES '.implode(',', $sql3));
}

$result1 = mysql_query ($sql1);
$result2 = mysql_query ($sql2);
$result3 = mysql_query ($sql3);

if (mysql_affected_rows()) {
echo '<div style="position:relative; left:16px; top:266px;"><font color="red">your plant has been updated</font></div>';
}
}
?>


<html>
<body>

<?
require ('db.php');

if ((isset($_GET['id'])) && (is_numeric($_GET['id']))) {
$id = $_GET['id'];
}



@$cat=$_GET['cat'];
if(strlen($cat) > 0 and !is_numeric($cat)){
echo "Data Error";
exit;
}


///////// Getting the data to make a box checked or not - this part is totally confusing me on how to to //////////

$quer2=mysql_query("SELECT `ID`, `usage` FROM `edible_uses` order by `usage`");
$quer2b=mysql_query("SELECT `plant_id`, `edible_id` FROM `plant_edible_link` WHERE `plant_id` = $id ");

$q2b = mysql_fetch_array($quer2);
$q2c = mysql_fetch_array($quer2b);

echo explode(string ',' $q2b); // need to figure this out
while($q2b = mysql_fetch_array($quer2)) {

$ch2 = array_intersect($q2b, $q2c);
}
///////////// End of query for first list box////////////


///////// Getting the data from Mysql table for first list box - works fine//////////

$quer3=mysql_query("SELECT `ID`, `usage` FROM `edible_uses` order by `usage`");
$quer3b=mysql_query("SELECT `plant_id`, `edible_id` FROM `plant_edible_link` WHERE `plant_id` = $id ");
$q3b = mysql_fetch_array($quer3);
echo explode(string ',' $q3b); // need to figure this out
while($q3b = mysql_fetch_array($quer3)) {

$ch3 = 'variable that will tell me whether to check a box';
}
///////////// End of query for first list box////////////

///////// Getting the data from Mysql table for first list box - works fine//////////
$quer4=mysql_query("SELECT `ID`, `usage` FROM `edible_uses` order by `usage`");
$quer4b=mysql_query("SELECT `plant_id`, `edible_id` FROM `plant_edible_link` WHERE `plant_id` = $id ");
$q4b = mysql_fetch_array($quer4);
echo explode(string ',' $q4b); // need to figure this out
while($q4b = mysql_fetch_array($quer4)) {

$ch4 = 'variable that will tell me whether to check a box';
}
///////////// End of query for first list box////////////


echo "<br><br><div style='position:relative; left:20px;'><form method=post name=f1 action=''>";

////////// Starting of first drop downlist I was thinking I could use a ternary to add 'checked' /////////
echo $num_rows;
echo "<table><tr><td valign=top>EDIBLE USES<br><br><font size=1>";
while($row = mysql_fetch_array($quer2)) {
if($row[id]==@$cat){echo "<input type='checkbox' name='option2'". ($ch2 ? 'checked': NULL) ."value='$row[id]'>$row[usage]<BR>";}
else{echo "<input type='checkbox' name='option1' value='$row[id]'>$row[usage]<br>";}
}
echo "</font></td>";
////////////////// This will end the first drop down list ///////////


echo "<td valign=top width=150>MEDICINES<br><br><font size=1>";
while($row = mysql_fetch_array($quer3)) {
if($row[id]==@$cat){echo "<input type='checkbox' name='option3'" . ($ch3 ? 'checked': NULL) . "value='$row[id]'>$row[Name]<BR>";}
else{echo "<input type='checkbox' name='option1' value='$row[id]'>$row[Name]<br>";}
}
echo "</font></td>";
////////////////// This will end the second drop down list ///////////


echo "<td valign=top width=190>OTHER USES<br><br><font size=1>";
while($row = mysql_fetch_array($quer4)) {
if($row[id]==@$cat){echo "<input type='checkbox' name='option4'" . ($ch4 ? 'checked': NULL) ."value='$row[id]'>$row[Name] <BR>";}
else{echo "<input type='checkbox' name='option1' value='$row[id]'> $row[Name] <br>";}
}
echo "</font></td></tr></table>";
////////////////// This will end the third drop down list ///////////


echo "<input type='hidden' name='plant_id' value=".$id.">

<input type=submit name=submit value=submit>";
echo "</form><br><br></div>";

?>

</div>

</body>

</html>

mlseim
11-11-2011, 05:54 PM
Can I ask what the purpose of the script is?

Do you give a user a list of things and they click on the
options they want? I don't quite understand what is
supposed to happen here. Does it return a result from
searching their checkboxes?

Do you have a picture of the form screen?

turpentyne
11-11-2011, 06:15 PM
It's a page with three columns of checkboxes. Each column is from a separate database of adjectives. one for food, one for medical uses, and one for general uses.

The plant id is a _get in the url at top. I want to search three Link tables between the plant table and those adjectives. If there's a record in that link table that matches the plant id and the adjective id, then I want it's corresponding checkbox to be preselected.

users can check or uncheck the boxes, hit submit, and it deletes or adds the records in each of the three link tables.

I'm heading in to work, where I can do a screenshot to show, unless this helps.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum