help with php and mysql

quan1114

This is all from a tutorial but its not working for me. I created a database "mydb" and the first piece of code is the creation of the table. The second piece of code is where I'm having trouble. When I run it in a browser and add or delete, it doesn't change my database when I check it in command line. This is an old tutorial so maybe the new php 4.3.2 uses different syntax. Could someone help me? Thanks

CREATE TABLE employees (id tinyint(4) DEFAULT '0' NULL AUTO_INCREMENT, first varchar(20), last varchar(20), address varchar(255), position varchar(50), PRIMARY KEY (id), UNIQUE id (id));

INSERT INTO employees VALUES (1,'Bob','Smith','128 Here St, Cityname','Marketing Manager');

INSERT INTO employees VALUES (2,'John','Roberts','45 There St , Townville','Telephonist');

INSERT INTO employees VALUES (3,'Brad','Johnson','1/34 Nowhere Blvd, Snowston','Doorman');

<?php

$db = mysql_connect("localhost", "root","password");

mysql_select_db("mydb",$db);

if ($submit)
{
// here if no ID then adding else we're editing

if ($id)  
{
		$sql = "UPDATE employees SET first='$first',last='$last',address='$address',position='$position' WHERE id=$id";
} 

else 
{
		$sql = "INSERT INTO employees (first,last,address,position) VALUES ('$first','$last','$address','$position')";

}

// run SQL against the DB

$result = mysql_query($sql);

echo "Record updated/edited!<p>";

}

elseif ($delete)
{
// delete a record

	$sql = "DELETE FROM employees WHERE id=$id";	

	$result = mysql_query($sql);

	echo "$sql Record deleted!<p>";

}

else
{
// this part happens if we don't press submit
if (!$id)
{
// print the list if there is not editing

		$result = mysql_query("SELECT * FROM employees",$db);

		while ($myrow = mysql_fetch_array($result)) 
	{
  			printf("<a href=\"%s?id=%s\">%s %s</a> \n", $PHP_SELF, $myrow["id"], $myrow["first"], $myrow["last"]);

  		printf("<a href=\"%s?id=%s&delete=yes\">(DELETE)</a><br>", $PHP_SELF, $myrow["id"]);
		}
}

?>

<P>

<a href="<?php echo $PHP_SELF?>">ADD A RECORD</a>

<P>

<form method="post" action="<?php echo $PHP_SELF?>">

<?php

if ($id)
{

		// editing so select a record

		$sql = "SELECT * FROM employees WHERE id=$id";

		$result = mysql_query($sql);

		$myrow = mysql_fetch_array($result);

	$id = $myrow["id"];

		$first = $myrow["first"];

		$last = $myrow["last"];

		$address = $myrow["address"];

		$position = $myrow["position"];

		// print the id for editing


?>

	<input type=hidden name="id" value="<?php echo $id ?>">

	<?php

}

?>

First name:<input type="Text" name="first" value="<?php echo $first ?>"><br>

Last name:<input type="Text" name="last" value="<?php echo $last ?>"><br>

Address:<input type="Text" name="address" value="<?php echo $address ?>"><br>

Position:<input type="Text" name="position" value="<?php echo $position ?>"><br>

<input type="Submit" name="submit" value="Enter information">

</form>

<?php
}
?>

</body>
</html>

LordShryku

Try getting some errors messages to see what's going wrong.

$db = mysql_connect("localhost", "root","password") OR die("Cannot Connect: ".mysql_error()); 

$selectdb = mysql_select_db("mydb",$db) OR die("Cannot Bind To DB: ".mysql_error()); 

$result = mysql_query($sql) OR die("Cannot execute SQL: ".mysql_error());

synergypoint

Do you have delete permissions for your db user?

Kind of obvious but worth checking.

Moonglobe

this looks like a register globals problem to me. change $submit to $_POST['submit'] and the other form variables the same way. see if it works. also see [man]register_globals[/man].

quan1114

Thanks everyone for the advice, but its still not working. I tried the error messages and the register globals but when I check the database, all I see is a blank field. It creates a new field but the data I input into the form doesn't go into the database. How do you set delete permissions? I'm new to php so bear the newbie questions, but can anyone please help me? Thx

HalfaBee

Try echoing the sql to see what is happening.
If register_globals is off you can get the POST variables using extract( $_POST );

HalfaBee