confused on list results

techker · 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.

techker · 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..

fenway · 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.

techker · December 7, 2011

but how to do it to insert multiple values in database?

kickstart · 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

techker · 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?

mikosiko · 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

techker · 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);

kickstart · 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

techker · 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..

kickstart · 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

techker · 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....

fenway · December 7, 2011

Again, we're pushing the boundaries of this forum -- naming input fields/

techker · December 7, 2011

sorry..i will try to find tutorials on this..

Sign In

confused on list results

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information