inserting and deleting in php

Discussion in 'PHP' started by ravi951, Aug 16, 2011.

  1. ravi951

    ravi951 New Member

    Joined:
    Aug 9, 2011
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    web developer
    Location:
    hyderabad
    hi all,
    i have written a code in php for inserting and deleting multiple records using check boxes."test" is the database name. "emp" is the table name
    which contains the following records in my database.

    empno empname desig
    1111 raju pilot
    1112 ram chef
    1113 ramu doctor
    1114 paul engineer
    1115 ajay player

    now i want to delete one or more records and insert the records in to the above table..
    i have written the below code for deleting but the table values are not displaying and i need to delete also using check box.
    below is delete_multiple2.php
    Code:
    <?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="test"; // Database name
$tbl_name="emp"; // Table name
// Connect to server and select databse.
mysql_connect($host, $username, $password)or die("cannot connect");
mysql_select_db($db_name)or die("cannot select DB");
// Check if delete button active, start this
if(isset($_POST['delete'])){
    if(sizeof($_POST['chkbx'])){
        foreach($_POST['chkbx'] AS $val){
            $sql = mysql_query("DELETE FROM $tbl_name WHERE id=".(int)$val)or die(mysql_error());
        }
    }
    // if successful redirect to delete_multiple.php

    if($sql){
        header('Location:delete_multiple2.php');//THIS WAS SAMPLE.PHP - YOU MIGHT WANT TO CHANGE IT BACK
    }
}
 $sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);
 ?>

<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF"> </td>
<td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>EmpNo</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>EmpName</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Desig</strong></td>
</tr>
<?php
$x = 0;
while($rows=mysql_fetch_array($result)){
$x++;
?>
    <tr>
        <td align="center" bgcolor="#FFFFFF"><input name="chkbx[]" type="checkbox" id="checkbox_<? echo $x; // you need a unique id per checkbox ?>" value="<? echo $rows['id']; ?>"></td>
        <td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
        <td bgcolor="#FFFFFF"><? echo $rows['empno']; ?></td>
        <td bgcolor="#FFFFFF"><? echo $rows['emoname']; ?></td>
        <td bgcolor="#FFFFFF"><? echo $rows['desig']; ?></td>
    </tr>
<?php
}
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>
</tr>
<?
mysql_close();
?>
</table>
</form>
</td>
</tr>
</table>
    also i have written for inserting records into database.
    but it is not inserting.....
    below is insert.php
    Code:
    <?php
$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="test"; // Database name 
$tbl_name="emp"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);

// Count table rows 
$count=mysql_num_rows($result);

<?php
while($rows=mysql_fetch_array($result))
{
?>

<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="y" /></td>
<td align="center"><input name="empno[]" type="text" id="name" value="<? echo $rows['empno'];?>"></td>
<td align="center"><input name="empname[]" type="text" id="empname" value="<? echo $rows['empname'];?>"></td>
<td align="center"><input name="desig[]" type="text" id="desig" value="<? echo $rows['desig'];?>"></td>
</tr>

<?php
}
?>
<input type="submit" name="Submit" value="Submit">

<?php

// Get values from form 
$no=$_POST['empno'];
$name=$_POST['empname'];
$desig=$_POST['desig'];

// Check if button name "Submit" is active, do this 
if($checkbox){
for($i=0;$i<$count;$i++)
{
$sql1="INSERT INTO $tbl_name (empno, empname, desig)VALUES('$no[$i]', '$name[$i]', '$desig[$i]')";
$result1=mysql_query($sql1);
}
}
    i want to integrate these two things so that it will be easy for inserting
    and deleting any no of times..
    kindly tell me how to do it....
    where is my code went wrong.......
     
    ravi951, Aug 16, 2011
    SHARE #1
  2. ManzZup

    ManzZup New Member

    Joined:
    May 9, 2009
    Messages:
    278
    Likes Received:
    43
    Trophy Points:
    0
    Occupation:
    Production Manager:Software @ ZONTEK
    Location:
    Sri Lanka
    Home Page:
    http://zontek.zzl.org
    1: please put the code in PHP tags as it would realy help in figuring out bugs :)

    okay your problem,
    what you need is not so clear to me

    as got the part in which you wanna mark the users with checkboxes and then use a single delete to delete the stuff, but you havent mentioned whether you have failed in this or any other place
    but i didnt get what you wanna do with insert/delete combined t gether

    are you trying to make a control panel with add/update/delete function @ one place?
     
    ManzZup, Aug 17, 2011
    SHARE #2
  3. pein87

    pein87 Active Member

    Joined:
    Aug 6, 2010
    Messages:
    173
    Likes Received:
    47
    Trophy Points:
    28
    Occupation:
    Web Dev
    Location:
    Limbo
    I'll help you this is working code I just wrote and tested for you. This is an example of how to get the desired effect your trying to get.

    First create a new database, for the example you'll need to use mine

    Code:
    CREATE DATABASE muu;
    Lets create a table for that database

    Code:
    USE muu;

CREATE TABLE info(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(80), title VARCHAR(80), PRIMARY KEY(id));
    Now lets set auto increments value to 1111 so you don't have to manually enter it.

    Code:
    ALTER TABLE info AUTO_INCREMENT=1111;
    Lets add some default data

    Code:
    INSERT INTO info (name,title) VALUES ("pein","Kami");
INSERT INTO info (name,title) VALUES ("konan","Tenshi");
INSERT INTO info (name,title) VALUES ("amaterasu","Megami");
INSERT INTO info (name,title) VALUES ("izanumo","Megami");
INSERT INTO info (name,title) VALUES ("izanagi","Kami");
INSERT INTO info (name,title) VALUES ("sakura","Tenshi");
    You will need to change the database password and username on both files before you start.

    Multi.php
    PHP:
    <?php
    $host     = "localhost";
    $user = "pein87";
    $pass = "daking";
    $con = 0;
    $dbs = 0;
    $re = 0;
    $se = 0;

    $C = mysql_connect($host,$user,$pass);

    if(    $C)
    {
            $con = 1;
    }
    $D = mysql_select_db("muu",$C);
    if(    $D)
    {
            $dbs = 1;
    }

    $Q = mysql_query("SELECT * FROM info");
    if(    $Q)
    {
            $re = 1;
    }
    if(    mysql_num_rows($Q) > 0)
    {
            $se = 1;
    }

    if(    $con == 1 && $dbs == 1 && $re == 1 && $se == 1)
    {
        echo     "<form method=\"post\" action=\"p.php\">";
        while((    $dat = mysql_fetch_assoc($Q)) !== false)
        {
            echo     "<input type=\"checkbox\" name=\"id[]\" value=\"$dat[id]\" />" . $dat['name'] . ": " . $dat['title'] . "<br />";
        }
        echo     "<input type=\"submit\" value=\"delete\" />";
        echo     "</form>";
    }
    ?>
    multi.php gets the data from the database and displays that data as checkboxes with each name and title being displayed to the side. The value of the input elements is the id of each row. Because each one is unique they serve as an excellent way to delete them. Take note that the name I used for each checkbox tag is id[]. Each checkbox value attribute is set to the value of $dat['id'], which is the id of each row from the database.

    p.php
    PHP:
    <?php
    $uv     = $_POST['id'];
    $cs = 0;
    $host = "localhost";
    $user = "pein87";
    $pass = "daking";
    $con = 0;
    $dbs = 0;
    $ct = 0;
    $ac = 0;

    $C = mysql_connect($host,$user,$pass);

    if(    $C)
    {
            $con = 1;
    }
    $D = mysql_select_db("muu",$C);
    if(    $D)
    {
            $dbs = 1;
    }


    if(    $uv)
    {
            $cs = 1;
    }
    if(    $cs == 1)
    {
        if(    is_array($uv))
        {
                $ac = count($uv);
            
            if(    $con == 1 && $dbs == 1)
            {
                foreach(    $uv as $k)
                {
                        $Q = mysql_query("DELETE FROM info WHERE id = '".$k."'");
                    
                    if(    $Q)
                    {
                            $ct++;
                    }
                    
                }
                echo     "You sent " . $ac . " items to be deleted. " . $ct . " items where deleted!";
            }
            else
            {
                echo     "not connected to server and no database selected";
            }
            
        }
        else
        {
            echo     "not an array";
        }
    }
    ?>
    p.php processes the data being sent by the multi.php file. It does all the work and uses flags instead of output data to tell if something is set or not.

    I've tested this several times and it works. This is only an example but this should help you understand how to get what you want done. Also when using variables in your sql statements they need to be done like so:

    PHP:
    $b = "Kami";
    $Q = mysql_query("SELECT * FROM info WHERE title = '".$b."'");
    its

    PHP:
    ' " . $b . " '
    As far as adding the data I've never seen a multi insert before so I can't help you there but you'd use this in your query

    Code:
    INSERT INTO table_name (column_name_1,column_name_2) VALUES ("value_1","value_2")
    good luck with your work.
     
    pein87, Aug 17, 2011
    SHARE #3
    shabbir likes this.
  4. ravi951

    ravi951 New Member

    Joined:
    Aug 9, 2011
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    web developer
    Location:
    hyderabad
    can u check for my database...
    not getting executed.
    i am doing exactly as you told above......

    my database name is "test" and my table name is "emp"
    my emp table contains the following values....

    empno empname desig
    1111 raju pilot
    1112 ram chef
    1113 ramu doctor
    1114 paul engineer
    1115 ajay player

    below is my multi.php

    Code:
    <?php 
$host = "localhost"; 
$user = "root"; 
$pass = ""; 
$con = 0; 
$dbs = 0; 
$re = 0; 
$se = 0; 
$C = mysql_connect($host,$user,$pass); 
if($C) 
{ 
    $con = 1; 
} 
$D = mysql_select_db("test",$C); 
if($D) 
{ 
    $dbs = 1; 
} 
$Q = mysql_query("SELECT * FROM emp"); 
if($Q) 
{ 
    $re = 1; 
} 
if(mysql_num_rows($Q) > 0) 
{ 
    $se = 1; 
} 
if($con == 1 && $dbs == 1 && $re == 1 && $se == 1) 
{ 
    echo "<form method=\"post\" action=\"p.php\">"; 
    while(($dat = mysql_fetch_assoc($Q)) !== false) 
    { 
        echo "<input type=\"checkbox\" name=\"id[]\" value=\"$dat[id]\" />" . $dat['empno'] . ": " . $dat['empname'] . ": " .$dat['desig'] "<br />"; 
    } 
    echo "<input type=\"submit\" value=\"delete\" />"; 
    echo "</form>"; 
} 
?>
    below is the p.php

    Code:
    <?php 
$uv = $_POST['id']; 
$cs = 0; 
$host = "localhost"; 
$user = "root"; 
$pass = ""; 
$con = 0; 
$dbs = 0; 
$ct = 0; 
$ac = 0; 
$C = mysql_connect($host,$user,$pass); 
if($C) 
{ 
    $con = 1; 
} 
$D = mysql_select_db("test",$C); 
if($D) 
{ 
    $dbs = 1; 
} 

if($uv) 
{ 
    $cs = 1; 
} 
if($cs == 1) 
{ 
    if(is_array($uv)) 
    { 
        $ac = count($uv); 
         
        if($con == 1 && $dbs == 1) 
        { 
            foreach($uv as $k) 
            { 
                $Q = mysql_query("DELETE FROM emp WHERE id = '".$k."'"); 
                 
                if($Q) 
                { 
                    $ct++; 
                } 
                 
            } 
            echo "You sent " . $ac . " items to be deleted. " . $ct . " items where deleted!"; 
        } 
        else 
        { 
            echo "not connected to server and no database selected"; 
        } 
         
    } 
    else 
    { 
        echo "not an array"; 
    } 
} 
?>
    i need to insert using php code only so that it gets updated in database.
    it must ask fields in separate box as
    empno:
    empname:
    desig:
    after clicking the submit button it must updated in the database....

    but my multi.php i also not getting deleted
    kindly tell me how to do it...
     
    ravi951, Aug 17, 2011
    SHARE #4
  5. pein87

    pein87 Active Member

    Joined:
    Aug 6, 2010
    Messages:
    173
    Likes Received:
    47
    Trophy Points:
    28
    Occupation:
    Web Dev
    Location:
    Limbo
    Try these

    multi.php
    PHP:
    <?php 
    $host     = "localhost"; 
    $user = "root"; 
    $pass = ""; 
    $con = 0; 
    $dbs = 0; 
    $re = 0; 
    $se = 0; 
    $C = mysql_connect($host,$user,$pass); 
    if(    $C) 
    { 
            $con = 1; 
    } 
    $D = mysql_select_db("test",$C); 
    if(    $D) 
    { 
            $dbs = 1; 
    } 
    $Q = mysql_query("SELECT * FROM emp"); 
    if(    $Q) 
    { 
            $re = 1; 
    } 
    if(    mysql_num_rows($Q) > 0) 
    { 
            $se = 1; 
    } 
    if(    $con == 1 && $dbs == 1 && $re == 1 && $se == 1) 
    { 
        echo     "<form method=\"post\" action=\"p.php\">"; 
        while((    $dat = mysql_fetch_assoc($Q)) !== false) 
        { 
            echo     "<input type=\"checkbox\" name=\"id[]\" value=\"$dat[empno]\" />" . $dat['empno'] . ": " . $dat['empname'] . ": " .$dat['desig'] "<br />"; 
        } 
        echo     "<input type=\"submit\" value=\"delete\" />"; 
        echo     "</form>"; 
    } 
    ?>
    I changed the value of the value attribute from $dat[id] to $dat[empno]. Since id is not a field in your table nothing would get deleted.

    p.php
    PHP:
    <?php 
    $uv     = $_POST['id']; 
    $cs = 0; 
    $host = "localhost"; 
    $user = "root"; 
    $pass = ""; 
    $con = 0; 
    $dbs = 0; 
    $ct = 0; 
    $ac = 0; 
    $C = mysql_connect($host,$user,$pass); 
    if(    $C) 
    { 
            $con = 1; 
    } 
    $D = mysql_select_db("test",$C); 
    if(    $D) 
    { 
            $dbs = 1; 
    } 

    if(    $uv) 
    { 
            $cs = 1; 
    } 
    if(    $cs == 1) 
    { 
        if(    is_array($uv)) 
        { 
                $ac = count($uv); 
             
            if(    $con == 1 && $dbs == 1) 
            { 
                foreach(    $uv as $k) 
                { 
                        $Q = mysql_query("DELETE FROM emp WHERE empno = '".$k."'"); 
                     
                    if(    $Q) 
                    { 
                            $ct++; 
                    } 
                     
                } 
                echo     "You sent " . $ac . " items to be deleted. " . $ct . " items where deleted!"; 
            } 
            else 
            { 
                echo     "not connected to server and no database selected"; 
            } 
             
        } 
        else 
        { 
            echo     "not an array"; 
        } 
    } 
    ?>
    I changed the delete statement to use empno instead of id because you don't have the field id but the value being passed to the array is the empno field. This should work. When you ran the test did it not show you that none of the values were deleted? You should have gotten the info saying 0 out of NUM items where deleted. You can add more info or even style it but make sure the value attribute stays at $dat[empno]. That is the data that is sent to the script for processing. You can create a div and make it look nice.
     
    pein87, Aug 18, 2011
    SHARE #5
    shabbir likes this.
  6. ravi951

    ravi951 New Member

    Joined:
    Aug 9, 2011
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    web developer
    Location:
    hyderabad
    i am getting the error as
    Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING, expecting ',' or ';' in C:\xampp\htdocs\multi.php on line 33
    what may be went wrong...
     
    ravi951, Aug 18, 2011
    SHARE #6
  7. pein87

    pein87 Active Member

    Joined:
    Aug 6, 2010
    Messages:
    173
    Likes Received:
    47
    Trophy Points:
    28
    Occupation:
    Web Dev
    Location:
    Limbo
    Use this one. I couldn't test if it worked because I don't have a database set up like yours but no errors where shown aside from the mysql_num_rows() error being triggered because the value wasn't a resource. Other wise no errors.

    PHP:
    <?php  
    $host     = "localhost";  
    $user = "root";  
    $pass = "";  
    $con = 0;  
    $dbs = 0;  
    $re = 0;  
    $se = 0;  
    $C = mysql_connect($host,$user,$pass);  
    if(    $C)  
    {  
            $con = 1;  
    }  
    $D = mysql_select_db("test",$C);  
    if(    $D)  
    {  
            $dbs = 1;  
    }  
    $Q = mysql_query("SELECT * FROM emp");  
    if(    $Q)  
    {  
            $re = 1;  
    }  
    if(    mysql_num_rows($Q) > 0)  
    {  
            $se = 1;  
    }  
    if(    $con == 1 && $dbs == 1 && $re == 1 && $se == 1)  
    {  
        echo     "<form method=\"post\" action=\"p.php\">";  
        while((    $dat = mysql_fetch_assoc($Q)) !== false)  
        {  
            echo     "<input type=\"checkbox\" name=\"id[]\" value=\"$dat[empno]\" />" . $dat['empno'] . ": ". $dat['empname'] . ": " . $dat['desig'];
        }  
        echo     "<input type=\"submit\" value=\"delete\" />";  
        echo     "</form>";  
    }  
    ?>
     
    pein87, Aug 20, 2011
    SHARE #7

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice