Trying to create PHP script to modify tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Mikey7047
Forum Newbie
Posts: 5
Joined: Wed Nov 19, 2003 7:32 pm

Trying to create PHP script to modify tables

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Are you looking for [mysql_man]DELETE[/mysql_man] sql statement?

hmm.. seems like the mysql_man tag isn't working. ;(
Last edited by Weirdan on Wed Nov 19, 2003 7:42 pm, edited 1 time in total.
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post 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
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

mysql_man doest work? 8O
Mikey7047
Forum Newbie
Posts: 5
Joined: Wed Nov 19, 2003 7:32 pm

Post 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
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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]
Mikey7047
Forum Newbie
Posts: 5
Joined: Wed Nov 19, 2003 7:32 pm

Post 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)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

hmm... unusual syntax. Never thought about such use of constants ;)
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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());
?>
Mikey7047
Forum Newbie
Posts: 5
Joined: Wed Nov 19, 2003 7:32 pm

Post 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?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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.';
?>
Last edited by infolock on Thu Nov 20, 2003 10:27 pm, edited 1 time in total.
Mikey7047
Forum Newbie
Posts: 5
Joined: Wed Nov 19, 2003 7:32 pm

Post by Mikey7047 »

Thanks everyone so much for the help, its working great!
concatenate_man
Forum Newbie
Posts: 13
Joined: Tue Dec 09, 2003 1:22 am
Location: Australia

Post 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
Post Reply