asked on

How do I delete row when prompted?

Hello all,

I am trying to use PHP to pull information from a mySQL database, as well as offer an "Add More" button through the use of JavaScript. I'm able to put the new information into the database and I am able to pull the information out of the database to show. However, I am unable to figure out how to delete the information that's pulled from the database when I hit the delete button beside it (Line 137 below). Any thoughts or ideas?

Thank you in advance!
Paula

<?php
include ("dbinfo.php");
?>
<HTML>
<head>
<title>Add test</title>
<php
$rnumber="0";
?>
<script language="javascript">
rnumber=0;
function addRow(tbl,row){

<?php
//row count
$rnumber++;
 
if ($rnumber<20){
 ?>
 
var textbox = '<center><input type="file" size="10" name=upload[]></center>';

var textbox2 ='<center><select name=toy[]><option 

value="select">Select</option><option value="doll">Doll</option><option 

value="truck">Truck</option><option value="train">Train</option><option 

value="ball">Ball</option></select></center>';

var textbox3 = '<center><select name=color[]><option 

value="select">Select</option><option value="blue">Blue</option><option 

value="red">Red</option><option value="yellow">Yellow</option><option 

value="Green">Green</option></select></center>';

var textbox4 = '<center><input type="text" size = "5" maxlength= "10" name=weight[]

></center>';
var textbox5 = '<center><input type="text" size = "5" maxlength= "10" name=price[]

></center>';
var textbox6 = '<center><input type="checkbox" name=accept[]></center>';

var stop = '<input type="button" value="delete" onclick="deleteRow(this)" >';
 
var tbl = document.getElementById(tbl);
var rowIndex = document.getElementById(row).value;
var newRow = tbl.insertRow(rnumber);
var newCell = newRow.insertCell(0);
newCell.innerHTML = textbox;
var newCell = newRow.insertCell(1);
newCell.innerHTML = textbox2;
var newCell = newRow.insertCell(2);
newCell.innerHTML = textbox3;
var newCell = newRow.insertCell(3);
newCell.innerHTML = textbox4;
var newCell = newRow.insertCell(4);
newCell.innerHTML = textbox5;
var newCell = newRow.insertCell(5);
newCell.innerHTML = textbox6;
var newCell = newRow.insertCell(6);
newCell.innerHTML = stop;
 <?php
}
?>
 
}
    function deleteRow(r)
    {
    var i=r.parentNode.parentNode.rowIndex;
    document.getElementById('TableMain').deleteRow(i);
     
<?php
    $rnumber--;
?>
    }
 
</script>
</head>
<body>

<table width="75%" >
<tr>
<td width = 100%>

<form name="toyentry" method="post"> 
<table width="100%" border="0" cellspacing="0" cellpadding="2" id="TableMain">
<th>Upload Picture</th>
<th><center>Toy</th>
<th>Color</th>
<th>Weight</th>
<th>Price</th>
<th>Accept</th>

$id=$_SESSION['id'];
$id=htmlspecialchars($id);
$id_sq=mysql_real_escape_string($id);

<?php
$sql="SELECT * FROM table WHERE id='$id_sq'";
$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result))
{
$toydb=$row["toy"];
$colordb=$row["color"];
$weightdb=$row["Weight"];
$picdb=$row["ImagePath"];
$pricedb=$row["Price"];

echo "<tr><td>";
echo "<center><img src=\"$picdb_sq\" height=\"75\" width=\"100\"></center>";
echo "</td><td>";
echo "<center>";
echo $toydb_sq;
echo "</center>";
echo "</td><td>";
echo "<center>";
echo $colordb_sq;
echo "</center>";
echo "</td><td>";
echo "<center>";
echo $weightdb_sq;
echo "</center>";
echo "</td><td>";
echo "<center>$";
echo $pricedb_sq;
echo "</center>";
echo "</td><td>";
echo "<center><input type=\"checkbox\"></center>";
echo "</td><td>";
echo "<center><input type=\"checkbox\"></center>";
echo "</td><td>";
echo "<input type=\"button\" value=\"Delete\" onclick=\"deleteRow(this)\" >";
echo "</td></tr>";
}
?>

<tr id="row1">
</tr>
</table>
</td>
<td valign="top" width = 20%>
<input type="button" name="Button" value="Add more" onClick="addRow

('TableMain','row1')"></td>
</tr>
</table>
<table>
<tbody>
<tr><td>
<input type="submit" class="button" name="submit" value="Submit">
</td></tr>
</tbody>
</table>
</form>
</body>
</html>

Open in new window

re-searcher

It's work as well on firefox, but if you test it on IE (Internet Explorer) you should change line 74 to following code:

document.all.getElementById('myTable').deleteRow(i);

Open in new window

re-searcher

if you want to remove this row from database,too.
you should post delete command to another php page (or current page with specified variables) with ajax.

happysunny

ASKER

Thank you for the IE tip!

Yes, I am trying to use the delete command, but I'm unsure how to do it correctly in this case. This is the code I have for when they press submit.

<?php

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

include ("dbinfo.php");

$weight = $_POST["weight"];

$cnt=count($weight);

for ($counter=0; $counter < $cnt; $counter++)
{
$weight = $_POST["weight"][$counter];
$file = $_POST["upload"][$counter];
$price = $_POST["price"][$counter];
$toy = $_POST["toy"][$counter];
$color = $_POST["color"][$counter];

mysql_query("INSERT INTO table (Weight, File, Price, Toy, Color) VALUES('$weight', '$file', '$price', '$toy', '$color')") or die(mysql_error());  
}

}
?>

Open in new window

Ray Paseur

Generally a delete-from-database concept would work something like this. You need to be sure that you do not do a delete (or any other change to the data model) on the basis of the URL parameters alone. HTH, ~Ray

<?php // RAY_delete_example.php
error_reporting(E_ALL);


// DEMONSTRATE HOW TO HAVE A SAFE DELETE SCRIPT
// IF THE GET AND POST KEYS MATCH, IT IS OK TO DELETE


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES



// GET THE KEYS FROM GET AND POST ARRAYS
$pk = (!empty($_POST["key"])) ? (int)$_POST["key"] : 0;
$gk = (!empty($_GET["key"]))  ? (int)$_GET["key"]  : 0;

// IF BOTH KEYS ARE PRESENT, DELETE THE ROW (NOTE THERE ARE NO ROWS WITH KEY=0)
if ($pk == $gk)
{
    $sql = "DELETE FROM mytable WHERE mykey = $pk LIMIT 1";
    $res = mysql_query($sql) or die( mysl_error() );
    die("KEY $pk HAS BEEN DELETED");
}



// IF ONLY THE GET KEY IS PRESENT, PUT UP A MESSAGE AND A FORM
if ( (empty($pk)) && ($gk) )
{
	$sql = "SELECT myname FROM mytable WHERE mykey = $gk LIMIT 1";
	$res = mysql_query($sql) or die( mysql_error() );
	$num = mysql_num_rows($res);
	if ($num)
	{
	    // THE ROW EXISTS, GENERATE THE FORM FOR POST-METHOD SIGNAL TO DELETE
	    $row = mysql_fetch_assoc($res);
	    $nom = $row["myname"];
	    $uri = $_SERVER["REQUEST_URI"];

	    // CREATE THE FORM USING HEREDOC SYNTAX
	    $form = <<<ENDFORM

<form method="post" action="$uri">
CONFIRM REQUEST TO DELETE THE ROW WITH MYNAME=$nom
<input type="hidden" name="key" value="$gk" />
<input type="submit" value="DELETE RECORD NUMBER $gk" />
</form>
ENDFORM;

        echo $form;
        die();
    }

    // NO RECORD WAS FOUND
    else
    {
        die("NO RECORD FOUND FOR KEY=$gk");
    }
}

Open in new window

re-searcher

you want delete a row in database, right?

SOLUTION

re-searcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

happysunny

ASKER

re-searcher...what does the 'id: "21"' stand for?

happysunny

ASKER

Ray_Paseur...I see that the value of the submit button isn't onClick. I'd like for it to both take it away with JavaScript, and delete it out of the database with PHP. I know it's possible, but I'm still not seeing how it will all work.

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

happysunny

ASKER

I've never used jQuery before, so this will be a wonderful learning experience :) Thanks, Ray!

re-searcher, will you help with this?

happysunny

ASKER

I will look into both ajax and jQuery for this. Either that, or try to use something like http://www.phpmyedit.org/ Thank you both very much :)

re-searcher

@happysunny
you should change you primary key field name with "id".

please, let me know if you need any more helps.

happysunny

ASKER

@re-searcher...sorry, I thought you gave up on me! Yes, I don't understand where the 21 came from in the js function.

re-searcher

# happysunny
Would you mind tell me what's the table's primary key name?

happysunny

ASKER

It is toyid. Is this something that should be added and pulled out of the database along with the other information?

re-searcher

so you should change to following code:

$.post("smth.php", { toyid: "21" },
   function(data) {
     alert("Row, Deleting Status: " + data);
   });

Open in new window

replace toyid value with 21. so each time you post it will send correct toyid for deletion.
for example:

$.post("smth.php", { toyid: "<?php echo $row['toyid']; ?>" },
   function(data) {
     alert("Row, Deleting Status: " + data);
   });

Open in new window

happysunny

ASKER

Oh wow! You are a genius! It works :) Sorry about the points, I would have given you much more!

re-searcher

# happysunny
No problem.

Enjoy ;)