Page 1 of 1

Trying to create PHP script to modify tables

Posted: Wed Nov 19, 2003 7:32 pm
by Mikey7047
OK, I just setup some forms today for my school, it is a form which submits to a database (MySQL). Essentially this database is going to be used as a "to-do" list. I have also managed to successfully export the database to a .xml for easy viewing. I later realized, I would need somehow to delete the row from the table once the task was completed...And I have no clue where to start since I have just begun learning PHP...I'm hoping someone around here can help me!
http://mikevarrieur.no-ip.com/example.html
That is an idea of what I want the page to look like, when the submit button is pressed, it should get the id number from the form, then delete the id from the table.

Posted: Wed Nov 19, 2003 7:41 pm
by Weirdan
Are you looking for [mysql_man]DELETE[/mysql_man] sql statement?

hmm.. seems like the mysql_man tag isn't working. ;(

Posted: Wed Nov 19, 2003 7:41 pm
by qads
to delete a record fom the database, you will need to pass a query to it.

soemthing like

Code: Select all

<?php
if(isset($_POST['id']))
{
$id = (int)$_POST['id'];//name your form field 'id'
$query = "DELETE FROM `table_name` WHERE `ID` = '$id' limit 1";
$delete = mysql_query($query);
}
?>
http://www.mysql.com/doc/en/DELETE.html

Posted: Wed Nov 19, 2003 7:41 pm
by qads
mysql_man doest work? 8O

Posted: Wed Nov 19, 2003 8:16 pm
by Mikey7047

Code: Select all

<html>
<head>
</head>

<body>
<?php 
if(isset($_POST&#1111;'id'])) 
&#123; 
$id = (int)$_POST&#1111;'id'];//name your form field 'id' 
$query = "DELETE FROM `formdata` WHERE `ID` = '$id' limit 1"; 
$delete = mysql_query($query); 
&#125; 
?>
<form method="POST" action="--WEBBOT-SELF--" name="id">
Select id to delete from table: 
<input type="text" name="id" size="20">
<br>
<input type="Submit" name="submit" value="Enter information">
</form>
</body>

</html>
Also need to know what to put instead of --WEBBOT-SELF-- (that was the default)
And if anyone can look this code over and tell me if its right...id greatly appreciate it

Posted: Wed Nov 19, 2003 8:26 pm
by infolock
why not do it like this :

Code: Select all

(Name this file bob.htm)
<html> 
<head> 
</head> 
<body> 
<form method="POST" action="bob.php"  name="id"> 
Select id to delete from table: 
<input type="text" name="id" size="20"> 
<br> 
<input type="Submit" name="submit" value="Enter information"> 
</form> 
</body> 
</html>


(Name this file bob.php)
<?php 
if(isset($_POST['id'])) 
{ 
	$id = (int)$_POST['id'];//name your form field 'id' 
	$query = "DELETE FROM `formdata` WHERE `ID` = '".$id."' limit 1"; 
	$delete = mysql_query($query); 
}
?>
as you can see, in place of the WEB BOT thing, you just tell the form to send the data to bob.php..

however, you are also going to need methods that connect to your MySQL Database, along with passing it the correct username/password and database name you wish to use ( you can view all this here : [php_man]mysql[/php_man]

Posted: Wed Nov 19, 2003 8:30 pm
by Mikey7047
define(db_host, "HOST");
define(db_user, "NAME");
define(db_pass, "PASS");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "schoolform");
Is this good enough? (I changed out the host user and pass)

Posted: Wed Nov 19, 2003 8:31 pm
by Weirdan
replace the ---webbot-self--- with <?=$_SERVER['PHP_SELF'];?>. And, as infolock said, you have to connect in your script to mysql server first.

Posted: Wed Nov 19, 2003 8:34 pm
by Weirdan
hmm... unusual syntax. Never thought about such use of constants ;)

Posted: Wed Nov 19, 2003 8:35 pm
by infolock
define(db_host, "HOST");
define(db_user, "NAME");
define(db_pass, "PASS");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "schoolform");

is this good enough?
i guess, but it's not the method i would choose...

instead i would do something like :

Code: Select all

<?
$dbhost = 'HOST'; 
$dbuser = 'NAME'; 
$dbpass = 'PASS'; 
$dblink = mysql_connect($dbhost,$dbuser,$dbpass); 
$dbname = 'schoolform';
mysql_select_db($dbname) or die(Mysql_Error());
?>

Posted: Wed Nov 19, 2003 8:42 pm
by Mikey7047

Code: Select all

<html> 
<head> 
</head> 
<body> 
<form method="POST" action="forms.php" name="id"> 
Select id to delete from table: 
<input type="text" name="id" size="20"> 
<br> 
<input type="Submit" name="submit" value="Enter information"> 
</form> 
</body> 
</html>
^^forms.html

Code: Select all

<?php 
if(isset($_POST&#1111;'id'])) 
&#123; 
$id = (int)$_POST&#1111;'id'];//name your form field 'id' 
$query = "DELETE FROM `formdata` WHERE `ID` = '".$id."' limit 1"; 
$delete = mysql_query($query); 
&#125; 
$dbhost = 'COMPAQ'; 
$dbuser = 'root'; 
$dbpass = ''; 
$dblink = mysql_connect($dbhost,$dbuser,$dbpass); 
$dbname = 'schoolform'; 
mysql_select_db($dbname) or die(Mysql_Error()); 
?>
^^forms.php

Doesn't seem to be working?

Posted: Wed Nov 19, 2003 8:58 pm
by infolock
it's because you aren't putting it in the correct order in yoru php file...

you should have it like this :

Code: Select all

<?php 
if(!isset($_POST['id'])) 
{ 
     echo 'please insert an ID';
     exit;
}
$id = (int)$_POST['id'];//name your form field 'id' 
$dbhost = 'COMPAQ'; 
$dbuser = 'root'; 
$dbpass = ''; 
$dbname = 'schoolform'; 
$dblink = mysql_connect($dbhost,$dbuser,$dbpass); 
mysql_select_db($dbname) or die(Mysql_Error()); 
$query = "DELETE FROM formdata WHERE ID = '".$id."' limit 1"; 
$delete = mysql_query($query) or die(mysql_error()); 
echo 'ID '.$id.' successfully deleted.';
?>

Posted: Thu Nov 20, 2003 9:25 pm
by Mikey7047
Thanks everyone so much for the help, its working great!

Posted: Sun Dec 21, 2003 1:52 am
by concatenate_man
so what if your using names gathered from the mysql database as in this file that calls itself:

Code: Select all

<?php 
$connection = mysql_connect('localhost');

$db = @mysql_select_db(marcat, $connection);
?>

<html>
<head>
	<title>MarCat Buyer Deletion</title> 
</head>
<body bgcolor="#000000" text="#FFFFFF" link="#0000FF"
     vlink="#000080" alink="#FF0000">
    <p align="center"><img src="images/logo.gif" width="850" height="89"></p>
<p>
<center>
<h2>MarCat Buyer Deletion Page</h2>
<p></center>
<form action="delete_buyer.php" method="GET">
<table border="1" >
  <tr>
      <td>BUYERS</td>
      <td>
      <?php
      echo '<form><select name=Name>';
      $names = mysql_query("SELECT Name FROM BUYERS ORDER BY Name");
      while ($row = mysql_fetch_array($names))
		&#123;
		  $name_entry=$row&#1111;"Name"];
		  echo "<option value=$name_entry>$name_entry\n";
		&#125;
      echo "</form>";
      ?>
      </td>
      <td>
      <input type="Submit" value="Delete Buyer">
      </td>
  </tr>
</table>
<p>

<hr>
<?php
 
$connection = mysql_connect('localhost');

$db = @mysql_select_db(marcat, $connection);

	$buyers_name = $_REQUEST&#1111;'Name'];
	

	$query = " DELETE *
		   FROM BUYERS
		   WHERE Name = '$buyers_name'";
				

	$result = mysql_query($query);

if ($result)
	echo mysql_affected_rows().' buyer deleted from database.';

?>

</body>
</html>
The first part works connecting to the database and then displaying the name field in the database but then the second part doesn't work (in the fact it doesn't delete the affected row.

Any Ideas