Go4Expert - inserting and deleting in php

Go4Expert (http://www.go4expert.com/)

- PHP (http://www.go4expert.com/forumdisplay.php?f=26)

- - inserting and deleting in php (http://www.go4expert.com/showthread.php?t=26509)

inserting and deleting in php

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

Re: inserting and deleting in php

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?

Re: inserting and deleting in php

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 Code:


		
			
<?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 Code:


		
			
<?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 Code:


		
			
$b = "Kami";

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

its

PHP Code:


		
			
' " . $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.

Re: inserting and deleting in php

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

Re: inserting and deleting in php

Try these

multi.php

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[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 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 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.

Re: inserting and deleting in php

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

Re: inserting and deleting in php

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 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[empno]\" />" . $dat['empno'] . ": ". $dat['empname'] . ": " . $dat['desig'];

    }  

    echo "<input type=\"submit\" value=\"delete\" />";  

    echo "</form>";  

}  

?>