D4mations.com

update & delete multiple records PHP mysql tutorial [ENG]



update & delete multiple records PHP mysql tutorial [ENG]

source

Link to this post!

d4mer

,
, , ,

40 responses to “update & delete multiple records PHP mysql tutorial [ENG]”

  4. In case you're getting "Notice: Undefined offset: 2":

    foreach ($_POST['id'] as $row => $id) {

    $field1 = $_POST['field1'][$row];
    $field2 = $_POST['field2'][$row];

    $update = "UPDATE test SET field1 = '$field1', field2 = '$field2' WHERE id = '$id'";

  5. thank you so much for this, it works great and helped me a lot . I have a date field which can be null. When I update records if there is nothing entered in the form it doesn't leave the field null, it enters a date of 0000-00-00.
    Here's the code (minimal modifications from what you use. COMPLETED is the date field):
    while ($row=mysql_fetch_assoc($res) )
    {
    echo 'LIST_NUM : ' . $row["LIST_NUM"]. '<br/>';
    echo 'Item name: ' . $row["ITEM_NAME"]. '<br/>';
    echo 'Completed on : <input type="text" name="COMPLETED['. $row["LIST_NUM"] .']" value="'.$row["COMPLETED"].'"><br/>'."n";
    echo '<input type="hidden" name="LIST_NUM[]" value="'. $row["LIST_NUM"] .'">'."n";
    echo '<input type="checkbox" name="delete[]" value="'. $row["LIST_NUM"] .'">'."n";
    echo "<hr>n";

  6. Thanks again for the help. It is working well now, but only when I use internet explorer. It's not working in Safari and Firefox. But that may be a result of the website I embedded the code. Your code was a tremendous help.

  8. Hello,
    My name is Stacy Hines with http://www.softwarevideo.com. We are inviting you to become an author to create training video tutorials for us as your videos are very informative and well done. What we would do is have you create courses for our site where you will make money every time one of our members clicks on your course. You can create training video tutorials wherever you are located. Additionally, you will have your own author page where you can showcase your business, portfolio, experience, etc. Once you become an author, you will make money and drive business back to you. To become an author go to: http://www.softwarevideo.com/author . For additional information, please contact me: Stacy Hines phone 951.230.1842 or stacyhines@softwarevideo.com

  11. Thank you so much Jozsi, I am working on School Project, I have issue on how to select course_ID from Course Table and see Students in that specific Course on the form.
    Now I will explain more about what I am trying to do:

    1: I created Tables : Student table, Courses Table, Attendance Table:
    I joined tables using Student ID and Course ID in attendance table as FK keys.

    Now I need to select students by coureid, I can easily select the student by using sql query like (" Select * from attendance where course_ID=''anycoursename");

    In this case, I will see only students who are taking that specific course…
    but I want to create dropbox and select the course_ID from the dropbox and after I select the course_ID from dropobx, I want to click on submit button and then see students who currently enrolled that course.

    I can create dropbox in php like:

    $sql = "SELECT courseid FROM Courses";
    $rs = mysql_query($sql) or die(mysql_error());
    echo "<select>";
    while($row = mysql_fetch_array($rs)){
    echo "<option value='".$row["courseid"]."'>".$row["courseid"]."</option>";
    }mysql_free_result($rs);
    echo "</select>";

    now I can see all course_IDs
    but the I want but I want select the course_ID and than pull the students from the table… Please help….

    if anyone can help, Please create small tables
    like student_table ID, Name.
    course_table ID, cours_ID
    attendance_table ID, name,course_ID
    do not worry about joining …. just show me how to select value from drop box and populate data on the form which …

    Please send me answer at readytech@live.com

  12. Man… Oh… God… You're like god… Really, I'm from Mexico and… I'd been looking for a update multiple 'cause I'm working on a dynamic form… There's no useful information in spanish about this, I could kiss you… Really… Ah… I will make a tutorial to explain this in spanish and I'll mention your video… Thank you so much!

    Saludos y mucha suerte, eres genial!

  26. can i ask question about the problem that shows everytime i test the codes i follow in this tutorial? this is what my browser shows  

    ID is 1

    ( ! ) Notice: Undefined index: 1 in C:wampwwwdatabase1multiple_edit.php on line 14
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0
    Field1 is 

    ( ! ) Notice: Undefined index: 1 in C:wampwwwdatabase1multiple_edit.php on line 15
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0
    lname is 

    ( ! ) Notice: Undefined index: 1 in C:wampwwwdatabase1multiple_edit.php on line 18
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0

    ( ! ) Notice: Undefined index: 1 in C:wampwwwdatabase1multiple_edit.php on line 19
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0
    ID is 2

    ( ! ) Notice: Undefined index: 2 in C:wampwwwdatabase1multiple_edit.php on line 14
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0
    Field1 is 

    ( ! ) Notice: Undefined index: 2 in C:wampwwwdatabase1multiple_edit.php on line 15
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0
    lname is 

    ( ! ) Notice: Undefined index: 2 in C:wampwwwdatabase1multiple_edit.php on line 18
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0

    ( ! ) Notice: Undefined index: 2 in C:wampwwwdatabase1multiple_edit.php on line 19
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0
    ID is 3

    ( ! ) Notice: Undefined index: 3 in C:wampwwwdatabase1multiple_edit.php on line 14
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0
    Field1 is 

    ( ! ) Notice: Undefined index: 3 in C:wampwwwdatabase1multiple_edit.php on line 15
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0
    lname is 

    ( ! ) Notice: Undefined index: 3 in C:wampwwwdatabase1multiple_edit.php on line 18
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0

    ( ! ) Notice: Undefined index: 3 in C:wampwwwdatabase1multiple_edit.php on line 19
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0
    ID is 4

    ( ! ) Notice: Undefined index: 4 in C:wampwwwdatabase1multiple_edit.php on line 14
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0
    Field1 is 

    ( ! ) Notice: Undefined index: 4 in C:wampwwwdatabase1multiple_edit.php on line 15
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0
    lname is 

    ( ! ) Notice: Undefined index: 4 in C:wampwwwdatabase1multiple_edit.php on line 18
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0

    ( ! ) Notice: Undefined index: 4 in C:wampwwwdatabase1multiple_edit.php on line 19
    Call Stack
    # Time Memory Function Location
    1 0.0007 389992 {main}( ) ..multiple_edit.php:0

    and my code is this

    <?php
    $con =mysql_connect( 'localhost', 'root','');
    $db =mysql_select_db('test' , $con );
    if ($db ==false)
    die( mysql_error() );

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

    echo '<pre>';
    print_r($_POST);
    echo '</pre>';
    foreach( $_POST["id"] AS $id)   {
    echo 'ID is ' . $id .'<br/>'; 
    echo 'Field1 is ' . $_POST["fname"][$id] ."<br>";
    echo 'lname is ' . $_POST["lname"][$id]."<br />";

    $fname =mysql_real_escape_string($_POST["fname"] [$id]);
    $lname =mysql_real_escape_string($_POST["lname"] [$id]);
    $update=" UPDATE `bb` SET `fname` ='$fname', `lname` = '$lname' WHERE 
          `id` =$id LIMIT 1; ";
     mysql_query($update) or die (mysql_error());

    }

    }

    $sql ="select * from bb";
    $res =mysql_query( $sql ) or die ( mysql_error() );
    if (mysql_num_rows( $res ) > 0) {
    echo '<form method="post">';
    while ( $row=mysql_fetch_assoc($res) ) {
    echo ' id : ' . $row["id"]. '<br/>';
    echo ' fname : <input type="text" name="fname['.$row["id"].']" value="'.$row["fname"].'"> <br/>'; 
    echo ' lname : <input type="text" name="lname['.$row["id"].']" value="'.$row["lname"].'"> <br/>'; 
    echo '<input type="hidden" name="id[]" value=" '.$row["id"].'"> '."n";

    echo "<hr/>n";

    }

    echo '<input type="submit" name="submit" value="Multiple Change">';
    echo '</form>';
    }

    ?>

    what is wrong about this?

  27. I did get to 15:46 but when I run the script i only get this:

    Array
    (
        [field1] => sffsf
        [field2] => sfdsdfddd
        [id] => 4
        [submit] => Opslaan
    )

    My code is:

    <?php
    $con=mysql_connect('localhost','root','otsjki80');
    $db = mysql_select_db('test' , $con);
    if( $db=false )
    die( mysql_error() );

    if (isset($_POST["submit"]))
    {
    echo '<pre>';
    print_r ($_POST);
    echo '</pre>';
    }

    $sql = " select * from test ";
    $res = mysql_query( $sql ) or die ( mysql_error() );

    if ( mysql_num_rows( $res ) > 0 ) {
    echo '<form method="post">';
    while( $row=mysql_fetch_assoc( $res ) ) {
    echo ' ID : ' . $row["id"] . '<br />';
    echo ' field1 : <input type="text" name="field1"['.$row["id"].']" value="'.$row["field1"].'"> <br />'."n";
    echo ' field2 : <input type="text" name="field2"['.$row["id"].']" value="'.$row["field2"].'"> <br />'."n";
    echo '<input type="hidden" name="id"[]" value="'.$row["id"].'">  '."n";
    echo "<hr>n";
    }
    echo '<input type="submit" name="submit" value="Opslaan">';
    echo '</form>';
    } 

    ?>

    I hope you can tell me what i do wrong.

    Thanks,

    Sylvester 

  29. <?php
    $con = mysql_connect( 'localhost', 'root', '123457');
    $db = mysql_select_db( 'bb' , $con );
    if ( $db == false )
    die( mysql_error() );

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

    if ( isset( $_POST["delete"] )) {
    $list = implode( "," , $_POST["delete"] );
    $sql = " delete from test Where id in ($list)";
    mysql_query( $sql ) or die( mysql_error() );
    }

    echo '<pre>';
    print_r( $_POST );
    echo '</pre>';
    foreach( $_POST["id"] AS $id ) {
    echo 'ID is ' . $id . '<br />';
    echo 'Field1 is ' . $_POST["field1"][$id]."<br />";
    echo 'Field2 is ' . $_POST["field2"][$id]."<br />";

    $field1 = mysql_real_escape_string($_POST["field1"][$id]);
    $field2 = mysql_real_escape_string($_POST["field2"][$id]);
    $update ="UPDATE test SET field1 = '$field1', field2 = '$field2' WHERE id = $id LIMIT 1";

    mysql_query( $update ) or die( mysql_error() );

    }

    }

    $sql = "select * from test ";
    $res = mysql_query( $sql ) or die ( mysql_error() );

    if ( mysql_num_rows( $res ) > 0 ) {
    echo '<form method="post">';
    while ( $row = mysql_fetch_assoc( $res ) ) {
    echo ' ID : ' . $row["id"] . '<br />';
    echo ' fieldl : <input type="text" name="field1['.$row["id"].']" value="'.$row["field1"].'"> <br />'."n";
    echo ' field2 : <input type="text" name="field2['.$row["id"].']" value="'.$row["field2"].'"> <br />'."n";
    echo '<input type="hidden" name="id[]" value="'.$row["id"].'"> '."n";
    echo '<input type="checkbox" name="delete[]" value="'.$row["id"].'"> '."n";
    echo "<hr>n";
    }
    echo '<input type="submit" name="submit" value="mutiple change">';
    echo '</form>';

    }

    ?>

  31.  
    sir;
    could you help me about this problem? The pagination links- pages 1 2 3 4 Next -is not working. Please help me sir, the pages number were not working. Can you just spot the errors for me ?. And thank you very much for the helpful tutorial of yours.
    <?php
    mysql_connect("localhost","root","") or die("could not connect");
    mysql_select_db("inventory") or die ("could not find database");
    $output = '';
    //collect
     $per_page = 5;
     
     $page = (isset($_GET['page'])) ? (int) $_GET['page'] : 1;
     $start = (($page – 1) * $per_page);
    ?>
    <html>
    <head>
    <title>Property/Equipment Database Records</title>
    </head>
    <body bgcolor="#CCCCCC" text="#000000" box-shadow: 0 0 10px #000>
    <form enctype = "multipart/form-data" method ="POST">
     <input type="text" name="search" placeholder="search for endusers…"/>
     <input type="submit" value=">>" />
    </form>
    <table width="800" border="1" cellpadding="1" cellspacing="1" bgcolor="eccc00">
    <tr>
    <th>PropID</th>
    <th>Name</th>
    <th>Desc</th>
    <th>ClassNo</th>
    <th>PropNo</th>
    <th>Dacqd</th>
    <th>UnitCost</th>
    <th>Enduser</th>
    <th>EmpoyeeNo</th>
    <th>College/Dept.</th>
    <th>Image</th>
    </tr>
    <?php
     if (isset ($_POST['search'])) {
     $searchq= $_POST['search'];
     $earchq= preg_replace("#[^0-9a-z]#i","",$searchq);
     
     $sql= "SELECT * FROM sup WHERE enduser LIKE '%$searchq%' limit $start, $per_page" or die("no records on file..");
    // $query= mysql_query("SELECT * FROM sup WHERE enduser LIKE '%$searchq%'") or die("no records on file..");
     mysql_query($sql);
     $records=mysql_query($sql);
     
     $pages_query = mysql_query("SELECT COUNT('propid') FROM sup");
     $pages = ceil(mysql_result($pages_query, 0) / $per_page);
     $count= mysql_num_rows($records);
     
    //  if($count ==0){
     // $output = 'There was no search results!';
    //  } else{
      
       while ($employee = mysql_fetch_assoc($records)) {
       $propid = $employee['propid'];
       $name = $employee['name'];
       $desc = $employee['desc'];
       $classno = $employee['classno'];
       $propno = $employee['propno'];
       $dacqd = $employee['dacqd'];
       $ucost = $employee['ucost']; 
       $enduser = $employee['enduser'];
       $empno = $employee['empno'];
       $unitname = $employee['unitname'];
      
      
       echo "<tr>";
       echo "<td>".$employee['propid']."</td>";
       echo "<td>".$employee['name']."</td>";
       echo "<td>".$employee['desc']."</td>";
       echo "<td>".$employee['classno']."</td>";
       echo "<td>".$employee['propno']."</td>";
       echo "<td>".$employee['dacqd']."</td>";
       echo "<td>".$employee['ucost']."</td>";
       echo "<td>".$employee['enduser']."</td>";
       echo "<td>".$employee['empno']."</td>";
       echo "<td>".$employee['unitname']."</td>";
       echo "<td>"."<img src=image.php?propid=".$employee['propid']." width=200 height=100>"."</td>";
     
       echo "</tr>";
       }
        $prev = $page – 1;
        $next = $page + 1;
        echo "<center>";
        if(!($page<=1)){
        echo "<a href='search.php?page=$prev'>Prev</a> ";
        }
        if($pages >= 1){
         for($x=1;$x<=$pages;$x++){
         echo ($x == $page) ? '<b><a href="?page='.$x.'">'.$x.'</a></b> ':'<a href="?page='.$x.'">'.$x.'</a> ';
         }
        }
       if(!($page>=$pages)){
       echo "<a href='search.php?page=$next'>Next</a> ";
       }
       echo "</center>";  
      
    //  }              
    }
    ?>
    </table>
    <form action="search.php" method="post">
     <input type="text" name="search" placeholder="search for endusers…"/>
     <input type="submit" value=">>" />
    </form>

    </body>
    </html>

  32. I perfectly copied your samples. When i run the script all field value were blank, there should ba a values on it as what it seen in your tutorial. What is this means? I check my database and the field value are intact. Is there a possiblity that there a malware virus on the script?  The worst thing is that when i click Multiple Change all data are gone and what is left is the one ive edited.

  34. Sir I am wondering where is the problem lies in this error?—-
    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
    ''field1' = 'qrwe', 'field2' = '' WHERE 'id'=4 limit 1' at line 1

    The field2 is a blank how come since the field2 has a value "qrewsedss" ?

  39. Great job, it works a treat. Can you do a tutorial on individual updates. Rather than updating the whole table each time, you could update individual records. An individual update button per record, hope that makes sense. Thanks again Dec

Leave a Reply