techker Posted December 6, 2011 Share Posted December 6, 2011 Hey guys im confused on something..lol so what im doing is searching my databse for specific terms.. no that part is good. the part were i need guidance is i need to list the results but put a checkbox beside it to select multiple results to insert in a database example select * from students were grade = 3 results: matthew d checkbox luis m checkbox mike a checkbox .... so once they press submit it inserts the selected studenst in a class database. Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/ Share on other sites More sharing options...
techker Posted December 6, 2011 Author Share Posted December 6, 2011 i got this $conn = mysql_connect($db_host, $db_user, $db_pass) or die("Could not connect to database!"); mysql_select_db($db, $conn); if(isset($_POST['checkbox'])){$checkbox = $_POST['checkbox']; if(isset($_POST['activate'])?$activate = $_POST["activate"]:$deactivate = $_POST["deactivate"]) $id = "('" . implode( "','", $checkbox ) . "');" ; $sql="INSERT INTO `Classes` (`Etudiant` ) values ('$id')" ; $result = mysql_query($sql) or die(mysql_error()); } $sql="SELECT * FROM Etudiant"; $result=mysql_query($sql); $count=mysql_num_rows($result); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset="utf-8"" /> <title>Update multiple rows in mysql with checkbox</title> <script type="text/javascript"> <!-- function un_check(){ for (var i = 0; i < document.frmactive.elements.length; i++) { var e = document.frmactive.elements[i]; if ((e.name != 'allbox') && (e.type == 'checkbox')) { e.checked = document.frmactive.allbox.checked; }}} //--> </script> </head> <body> <table width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td><form name="frmactive" method="post" action=""> <table width="400" border="0" cellpadding="3" cellspacing="1"> <tr> <td colspan="5"><input name="activate" type="submit" id="activate" value="Select" /> </td> </tr> <tr> <td> </td> <td colspan="4"><strong>Update multiple rows in mysql with checkbox</strong> </td> </tr><tr> <td align="center"><input type="checkbox" name="allbox" title="Select or Deselct ALL" style="background-color:#ccc;"/></td> <td align="center"><strong>Id</strong></td> <td align="center"><strong>Firstname</strong></td> <td align="center"><strong>Lastname</strong></td> <td align="center"><strong>Status</strong></td> </tr> <?php while($rows=mysql_fetch_array($result)){ ?> <tr> <td align="center"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['Code_P']; ?>"></td> <td><? echo $rows['Code_P']; ?></td> <td><? echo $rows['Nom']; ?></td> <td><? echo $rows['Prenom']; ?></td> <td><? echo $rows['Foyer']; ?></td> </tr> <?php } ?> <tr> <td colspan="5" align="center"> </td> </tr> </table> </form> but i get this You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ABDA16059609','ABOD20609508','ABOA06599807');')' at line 2 i think it's because it is inserting all in on column. in need it to instert in but seperatly.. Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295095 Share on other sites More sharing options...
fenway Posted December 7, 2011 Share Posted December 7, 2011 Oh, so many things are wrong. First, you shouldn't be storing multiple values in a single field. Second, you're trying to insert 3 strings, not one. You have quoting problems, amongst others. Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295160 Share on other sites More sharing options...
techker Posted December 7, 2011 Author Share Posted December 7, 2011 but how to do it to insert multiple values in database? Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295299 Share on other sites More sharing options...
kickstart Posted December 7, 2011 Share Posted December 7, 2011 Hi Problem is your table design. You are best off having a table linking students to classes, with a separate line per student and class. So if the student has 3 classes there will be 3 rows. If you want to keep your current design (which will cause problems later on when trying to do more complex SQL) then assuming Etudiant is a string field change $id = "('" . implode( "','", $checkbox ) . "');" ; to $id = "('" . implode( ",", $checkbox ) . "');" ; All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295351 Share on other sites More sharing options...
techker Posted December 7, 2011 Author Share Posted December 7, 2011 Hi Problem is your table design. You are best off having a table linking students to classes, with a separate line per student and class. So if the student has 3 classes there will be 3 rows. If you want to keep your current design (which will cause problems later on when trying to do more complex SQL) then assuming Etudiant is a string field change $id = "('" . implode( "','", $checkbox ) . "');" ; to $id = "('" . implode( ",", $checkbox ) . "');" ; All the best Keith true.how would you make the table? Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295353 Share on other sites More sharing options...
mikosiko Posted December 7, 2011 Share Posted December 7, 2011 Only to answer your specific question and address the error that you shown: This line in your code: $id = "('" . implode( "','", $checkbox ) . "');" ; should be: $id = implode("'),('",$checkbox); after that if you echo your $sql you should see something like this: INSERT INTO <your-table-here> ( <your-field-here> ) VALUES ('value1'), ('value2'), ('value3'),..... ('value-n') ; Agree with Keith that you have design issues in your DB Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295354 Share on other sites More sharing options...
techker Posted December 7, 2011 Author Share Posted December 7, 2011 so the table issue would be to make a table for every class and add to students in it.. so the teacher would creat her class then add the students in her class table.. as fo the insert part.how can i insert the values?were do the values come from? this:?$id = implode("'),('",$checkbox); Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295357 Share on other sites More sharing options...
kickstart Posted December 7, 2011 Share Posted December 7, 2011 Hi It is difficult to know without seeing how other things are already laid out. However I would assume you have a table of students and another table containing classes. You would then have a 3rd table linking the 2 together. Maybe called StudentClass with the following fields. Id StudentId ClassId So if student with StudentId 5 had 3 classes with ClassId of 1,4 and 6 you could do an insert like:- INSERT INTO StudentClass (Id, StudentId, ClassId) VALUES (NULL, 5, 1), (NULL, 5, 4), (NULL, 5, 6) All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295358 Share on other sites More sharing options...
techker Posted December 7, 2011 Author Share Posted December 7, 2011 ok i get it.so after i can pull the information faster and more efficient. So my question is .i made the script that populates the page by Grade(1-2-3-4-5-6) so the teacher selects the students that are in her class and submits to insert in DB.. simple said...but not done.this is an issue for me.. so i have the form set up as: // get value of id that sent from address bar $Sec = $_GET['Sec']; // Retrieve data from database $sql="SELECT * FROM Etudiant WHERE Secondaire='$Sec'"; $result=mysql_query($sql); echo '<form name="Edit" method="post" action="update.php"> <table width="400" border="0" cellspacing="1" cellpadding="0"> <tr> <td align="center"><strong>Name:</strong></td> <td align="center"><strong>Contact:</strong></td> <td align="center"><strong>Periode:</strong></td> <td align="center"><strong>Select:</strong></td> </tr>'; while ($row = mysql_fetch_assoc($result)) { //we will echo these into the proper fields echo '<tr> <td align="center"><input name="Name" type="text" id="Name" value="'.$row['Code_P'].'"></td> <td align="center"><input name="Contact" type="text" id="Contact" value="'.$row["Nom"].'" size="15"></td> <td> <select name="Periode"> <option value="1">1</option> <option value="2">2</option> <option value="3">3</option> <option value="4">4</option> </select> </td> <td> <input name="Name" type="checkbox" id="Select" value="'.$row['Code_P'].'"> </td> </tr>'; } echo '<tr> <td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td> </tr> </table> </form>'; // close connection mysql_close(); so its the insert part that confuses me.. Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295360 Share on other sites More sharing options...
kickstart Posted December 7, 2011 Share Posted December 7, 2011 Hi On that page it is probably easiest to loop through building up a long insert string. Note that in your code you have 2 fields called Name which will cause problems. I would specifically set the array numbers for the fields:- $fieldCnt = 0; while ($row = mysql_fetch_assoc($result)) { //we will echo these into the proper fields echo '<tr> <td align="center"><input name="Name[$fieldCnt]" type="text" id="Name" value="'.$row['Code_P'].'"></td> <td align="center"><input name="Contact[$fieldCnt]" type="text" id="Contact" value="'.$row["Nom"].'" size="15"></td> <td> <select name="Periode[$fieldCnt]"> <option value="1">1</option> <option value="2">2</option> <option value="3">3</option> <option value="4">4</option> </select> </td> <td> <input name="NameSelect[$fieldCnt]" type="checkbox" id="Select" value="'.$row['Code_P'].'"> </td> </tr>'; $fieldCnt++; } Then I would add a hidden id field for each name. When the page is returned use a foreach on one of the fields, check the values returned and if required add them to the required insert. Things get a touch more difficult when you want to remove items from the database when they have been unselected. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295362 Share on other sites More sharing options...
techker Posted December 7, 2011 Author Share Posted December 7, 2011 ouff..getting heavy for me..lol thx for the help by the way.. so $fieldCnt = 0; returns in input name="Name[$fieldCnt]-Contact[$fieldCnt]-Periode[$fieldCnt] So how does it know witch field is what? shouldnt i do $fieldCnt = 0 $fieldCnt2 = 1.... Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295369 Share on other sites More sharing options...
fenway Posted December 7, 2011 Share Posted December 7, 2011 Again, we're pushing the boundaries of this forum -- naming input fields/ Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295486 Share on other sites More sharing options...
techker Posted December 7, 2011 Author Share Posted December 7, 2011 sorry..i will try to find tutorials on this.. Quote Link to comment https://forums.phpfreaks.com/topic/252618-confused-on-list-results/#findComment-1295509 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.