Problems with deleting from mysql table

[chrisdee]

Hello. I'm struggling to make a php/mysql based information website so I can add and edit my pages directly online. So far I'v set up a mysql table for each of the categories windows, unix, mysql, encyclopedia.
That went ok. Next I'v created several php files for each of the categories above that list out the content of each mysql table. I'v also created pages to add and edit the tables for each of the categories. Also this went ok.

To prevent my website from having to many files (since I'm trying to transfer my old flat file based website into mysql tables) I created only one file that deletes entries from the different tables windows, unix, mysql and encylopedia. Here comes the problem. When I choose a post to delete from one of the tables, unfortunately all other tables gets a entry deleted also. Not so funny.

I wonder if somebody smarter than me can take a look at what I'm doing wrong here. I'm trying to make it delete only the post I click on , and not posts from the other tables. Below is the code.

First is the db connecting file :

<?php
#connects to the MySQL server
$username               = "username";
$password               = "password";
$host                   = "localhost";
$database               = "database_name";

$table_windows          = "windows";
$table_unix             = "unix";
$table_encyclopedia     = "encyclopedia";
$table_mysql            = "mysql";

mysql_connect($host,$username,$password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());
?>

Code for the windows_admin page (the other categories look the same besides wich table they refer to, so I only post this one) :

<?php
include("connect.php");
echo "<a href=windows_add.php>Add Info</a></p>";

echo "<table cellpadding=4 cellspacing=0 border=1>";
echo "<tr>";

echo "<td bgcolor=lightgrey>Nr</td>";
echo "<td bgcolor=lightgrey>ID</td>";
echo "<td bgcolor=lightgrey>Tittel</td>";
echo "<td bgcolor=lightgrey>Edit/Delete</td>";
echo "</tr>";

$order=$_GET['order'];
if(!$order) {
 $order="tittel";
}

# Connecting to mysql table
$result = mysql_query("SELECT * FROM $table_windows order by $order asc");
$loop = mysql_num_rows($result);

for ($i=0; $i<$loop; $i++) {
 $myrow = mysql_fetch_array($result);
 $tittel = $myrow["tittel"];
 $innhold = $myrow["innhold"];
 $id = $myrow["id"];
 $nr++;

print "<tr>";
print "<td>$nr</td>";
print "<td>$id</td>";
print "<td><a href='windows_allinfo.php?id=$id'>$tittel</a></td>";
print "<td><a href='windows_edit.php?id=$id'>Edit</a> | <a href='del.php?id=$id'>Delete</a></td>";
print "</tr>";
}
echo "</table>";
?>

Finally the delete file :

<?php
include("connect.php");
$id=$_GET['id'];

if($table_windows) {
 $sql = "DELETE FROM $table_windows WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Entry is deleted from $table_windows";
 echo "<br><a href='windows_admin.php'>Go back.</a>";
}
elseif($table_unix) {
 $sql = "DELETE FROM $table_unix WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Entry is deleted from $table_unix";
 echo "<br><a href='unix_admin.php'>Go back.</a>";
}
elseif($table_mysql) {
 $sql = "DELETE FROM $table_mysql WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Entry is deleted from $table_mysql";
 echo "<br><a href='mysql_admin.php'>Go back.</a>";
}
elseif($table_encyclopedia) {
 $sql = "DELETE FROM $table_encyclopedia WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Entry is deleted from $table_encyclopedia";
 echo "<br><a href='encyclopedia_admin.php'>Go back.</a>";
}
else { #Nothing deleted }
?>

[SyWill]

Hey chrisdee,

your problem comes in on your delete php page in your if statement. Once you include the dbConnection file the variables $table_windows, unix, encyclopedia, and mysql are set, so your if statement reads if table_windows exists then erase where id = $x else if unix is set then erase where id = $x so essentially all your if statements are true, the simplest fix i can think of is to simply add a hidden input with the page or table your wanting to erase from so would go something like this.

\\Windows Php Page Somewhere between <form> and </form> of course ;)
<Input type="hidden" name="pageFrom" value="windows">

and then in your delete php page

  $id = $_GET['id'];
  $table = $_GET['pageFrom'];

  switch($table){
      case ("windows"): {
       $eraseTable = $table_windows;
       $location = "windows_admin.php";
       break;
      }
      case ("unix"): {
       $eraseTable = $table_unix;
       $location = "unix_admin.php";
       break;
      }
      case ("encyclopedia"): {
       $eraseTable = $table_encyclopedia;
       $location = "encyclopedia_admin.php";
        break;
      }
      case ("mysql"): {
       $eraseTable = $table_mysql;
       $location = "mysql_admin.php";
       break;
      }
      default:
       $eraseTable = false;
       break;   

  }

  if (!$eraseTable==false){
  $sql = "DELETE FROM $eraseTable WHERE id='$id'";
  $query = mysql_quer($sql) or die(mysql_error());
  echo "Entry is deleted from $eraseTable";
  echo "<br><a href='$location'>Go Back.</a>";
  }

hope it helps 🙂

[chrisdee]

Sorry. I posted the wrong delete file. Below is the original one (uses if instead of elseif):

<?php
include("connect.php");
$id=$_GET['id'];

if($table_windows) {
$sql = "DELETE FROM $table_windows WHERE id='$id'";
$query = mysql_query($sql) or die(mysql_error());
echo "Entry is deleted from $table_windows";
echo "<br><a href='windows_admin.php'>Go back.</a>";
}
if($table_unix) {
$sql = "DELETE FROM $table_unix WHERE id='$id'";
$query = mysql_query($sql) or die(mysql_error());
echo "Entry is deleted from $table_unix";
echo "<br><a href='unix_admin.php'>Go back.</a>";
}
if($table_mysql) {
$sql = "DELETE FROM $table_mysql WHERE id='$id'";
$query = mysql_query($sql) or die(mysql_error());
echo "Entry is deleted from $table_mysql";
echo "<br><a href='mysql_admin.php'>Go back.</a>";
}
if($table_encyclopedia) {
$sql = "DELETE FROM $table_encyclopedia WHERE id='$id'";
$query = mysql_query($sql) or die(mysql_error());
echo "Entry is deleted from $table_encyclopedia";
echo "<br><a href='encyclopedia_admin.php'>Go back.</a>";
}
?>

[chrisdee]

SyWill;10909247 wrote:

Hey chrisdee,

your problem comes in on your delete php page in your if statement. Once you include the dbConnection file the variables $table_windows, unix, encyclopedia, and mysql are set, so your if statement reads if table_windows exists then erase where id = $x else if unix is set then erase where id = $x so essentially all your if statements are true, the simplest fix i can think of is to simply add a hidden input with the page or table your wanting to erase from so would go something like this.

\\Windows Php Page Somewhere between <form> and </form> of course ;)
<Input type="hidden" name="pageFrom" value="windows">

and then in your delete php page

  $id = $_GET['id'];
  $table = $_GET['pageFrom'];

  switch($table){
      case ("windows"): {
       $eraseTable = $table_windows;
       $location = "windows_admin.php";
       break;
      }
      case ("unix"): {
       $eraseTable = $table_unix;
       $location = "unix_admin.php";
       break;
      }
      case ("encyclopedia"): {
       $eraseTable = $table_encyclopedia;
       $location = "encyclopedia_admin.php";
        break;
      }
      case ("mysql"): {
       $eraseTable = $table_mysql;
       $location = "mysql_admin.php";
       break;
      }
      default:
       $eraseTable = false;
       break;   

  }

  if (!$eraseTable==false){
  $sql = "DELETE FROM $eraseTable WHERE id='$id'";
  $query = mysql_quer($sql) or die(mysql_error());
  echo "Entry is deleted from $eraseTable";
  echo "<br><a href='$location'>Go Back.</a>";
  }

hope it helps 🙂

Thank you for your reply. I'll try this and se if it works🙂

[chrisdee]

Hi again. I actually found another way to do it.

In my windows_admin.php file I have :

<?php
include("connect.php");
echo "<a href=windows_add.php>Add Info</a></p>";

echo "<table cellpadding=4 cellspacing=0 border=1>";
echo "<tr>";
$title_array = array('Nr','ID','Tittel','Edit/Delete');
foreach($title_array as $ta) {
 echo "<td bgcolor=lightgrey>$ta</td>";
}
echo "</tr>";

$order=$_GET['order'];
if(!$order) {
 $order="tittel";
}
# Connecting to mysql table
$result = mysql_query("SELECT * FROM $table_windows order by $order asc");
$loop = mysql_num_rows($result);

for ($i=0; $i<$loop; $i++) {
 $myrow = mysql_fetch_array($result);
 $tittel = $myrow["tittel"];
 $innhold = $myrow["innhold"];
 $id = $myrow["id"];
 $nr++;

print "<tr>";
print "<td>$nr</td>";
print "<td>$id</td>";
print "<td><a href='windows_allinfo.php?id=$id'>$tittel</a></td>";
print "<td><a href='windows_edit.php?id=$id'>Edit</a> | <a href='del.php?id=$id&table=windows'>Delete</a></td>";
print "</tr>";
}
echo "</table>";
?>

And in my delete file I have :

<?php
include("connect.php");
$id=$_GET['id'];
$table = $_GET['table'];

if($table == 'windows') {
 $sql = "DELETE FROM $table_windows WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Entry is deleted from $table_windows";
 echo "<br><a href='windows_admin.php'>Go back.</a>";
}
elseif($table == 'unix') {
 $sql = "DELETE FROM $table_unix WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Entry is deleted from $table_unix";
 echo "<br><a href='unix_admin.php'>Go back.</a>";
}
elseif($table == 'mysql') {
 $sql = "DELETE FROM $table_mysql WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Entry is deleted from $table_mysql";
 echo "<br><a href='mysql_admin.php'>Go back.</a>";
}
elseif($table == 'encyclopedia') {
 $sql = "DELETE FROM $table_encyclopedia WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Entry is deleted from $table_encyclopedia";
 echo "<br><a href='encyclopedia_admin.php'>Go back.</a>";
}
elseif($table == 'category') {
 $sql = "DELETE FROM $table_category WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Entry is deleted from $table_encyclopedia";
 echo "<br><a href='category_admin.php'>Go back.</a>";
}
?>

It works perfect. However, as Im trying to keep static files to a minumum, I would also like to create a single edit.php file for editing all the tables, based on the same priciple as the del.php file I now have fully working. But I try the following, and it does'nt work :

The new windows_admin.php file :

<?php
include("connect.php");
echo "<a href=windows_add.php>Add Info</a></p>";

echo "<table cellpadding=4 cellspacing=0 border=1>";
echo "<tr>";
$title_array = array('Nr','ID','Tittel','Edit/Delete');
foreach($title_array as $ta) {
 echo "<td bgcolor=lightgrey>$ta</td>";
}
echo "</tr>";

$order=$_GET['order'];
if(!$order) {
 $order="tittel";
}
# Connecting to mysql table
$result = mysql_query("SELECT * FROM $table_windows order by $order asc");
$loop = mysql_num_rows($result);

for ($i=0; $i<$loop; $i++) {
 $myrow = mysql_fetch_array($result);
 $tittel = $myrow["tittel"];
 $innhold = $myrow["innhold"];
 $id = $myrow["id"];
 $nr++;

print "<tr>";
print "<td>$nr</td>";
print "<td>$id</td>";
print "<td><a href='windows_allinfo.php?id=$id'>$tittel</a></td>";
print "<td><a href='edit.php?id=$id&table=windows'>Edit</a> | <a href='del.php?id=$id&table=windows'>Delete</a></td>";
print "</tr>";
}
echo "</table>";
?>

And the single edit.php file is like this :

<?php
include("connect.php");
$id=$_GET['id'];
$table=$_GET['table'];

$tittel = $_POST['tittel'];
$innhold = $_POST['innhold'];

if(!$_POST) {
 $sql = "SELECT * FROM $table WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 $result = mysql_fetch_array($query);

$id= $result["id"];
$tittel = $result["tittel"];
$innhold = $result["innhold"];

echo "<form method='post' action='edit.php'>";
echo "Tittel :<br><input type='text' size='70' name='tittel' value='$tittel'><br>";
echo "Innhold : <br><textarea rows='28' cols='100' name='innhold'>$innhold</textarea><br>";
echo "<input type='hidden' name='id' value='$id'>";
echo "<input type='submit' value='Update'>";
echo "<input type='button' onclick='history.go(-1)' value='Cancel'>";
echo "</form>";
}
elseif($table == 'windows') {
 $id=$_POST['id'];
 $sql = "UPDATE $table_windows SET tittel='$tittel', innhold='$innhold' WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Table updated.......<br><a href='windows_admin.php'>To mainpage</a>";
}
elseif($table == 'unix') {
 $id=$_POST['id'];
 $sql = "UPDATE $table_unix SET tittel='$tittel', innhold='$innhold' WHERE id='$id'";
 $query = mysql_query($sql) or die(mysql_error());
 echo "Table updated.......<br><a href='unix_admin.php'>To mainpage</a>";
}
?>

Any ideas ?

[SyWill]

hey chrisdee,

well the best piece of advice i could give you for keeping your code down to a extreme minimum is to pile all your functionality into a class, that way you could actually have all actions thrown to one page and just add another get variable with your action your wanting to preform so you could send all requests to something like doAction.php?id=5&page=windows&action=remove and the php page doAction.php would actually have a instance of your database class that could preform all the actions in it, not sure how comfortable you are in the language yet or if youve looked into classes but have a pretty basic one made up if your looking for a start point another good resource on them is http://www.spoono.com/php/tutorials/tutorial.php?id=27 back to the question at hand though 😉 your edit.php file looks fine the only problem i saw was that once they hit submit on form on your edit.php page method="post" so any get variables will be erased so now the file doesnt know what to do as far as what table is being used which im going to assume is the problem your having try adding <input type="hidden" name="table" value="$table"> to your form inside the if(!_$POST) section. should work. that way your passing the get variable table through the post form so that it stays. Hope it helps 🙂

[chrisdee]

Hello and thank you very much for you reply. Im walking in the dark when it comes to more advanced php code than simple loops and if else statements, but I'll bookmark you page and have a look. Hopefully I'll learn in the end. However, I'm still struggling to get my edit.php script to work.
I tried to add the hidden input as you suggested, but when I hit update im taken to a blank page, and no update is preformed. Below is my current edit.php file :

<?php
include("connect.php");
$id=$_GET['id'];
$table=$_GET['table'];
$tittel = $_POST['tittel'];
$innhold = $_POST['innhold'];

if(!$_POST) {
$sql = "SELECT * FROM $table WHERE id='$id'";
$query = mysql_query($sql) or die(mysql_error());
$result = mysql_fetch_array($query);

$id= $result["id"];
$tittel = $result["tittel"];
$innhold = $result["innhold"];

echo "<form method='post' action='edit.php'>";
echo "<input type='hidden' name='table' value='$table'>";
echo "Tittel :<br><input type='text' size='70' name='tittel' value='$tittel'><br>";
echo "Innhold : <br><textarea rows='28' cols='100' name='innhold'>$innhold</textarea><br>";
echo "<input type='hidden' name='id' value='$id'>";
echo "<input type='submit' value='Update'>";
echo "<input type='button' onclick='history.go(-1)' value='Cancel'>";
echo "</form>";
}
elseif($table == 'windows') {
$id=$_POST['id'];
$sql = "UPDATE $table_windows SET tittel='$tittel', innhold='$innhold' WHERE id='$id'";
$query = mysql_query($sql) or die(mysql_error());
echo "Table updated.......<br><a href='windows_admin.php'>To mainpage</a>";
}
elseif($table == 'unix') {
$id=$_POST['id'];
$sql = "UPDATE $table_unix SET tittel='$tittel', innhold='$innhold' WHERE id='$id'";
$query = mysql_query($sql) or die(mysql_error());
echo "Table updated.......<br><a href='unix_admin.php'>To mainpage</a>";
}
?>

It looks like the beginning of the script works ok, because the values are fetched from the correct mysql table and put into the form. The problem comes when I change these values and click update, the script does not update the values and gives me as I said a blank page. 😕

[SyWill]

hey again chrisdee you had it all down cept for in the elseif statements, the variable $table at top of your page is trying to be assigned by the get variable table which doesnt exist since we posted a form using post 😉 so all you have to do is change the if statements to check which table is in the post variables. should look something like this.

if(!$_POST){
//Preform  Miracle of Php Form Printing
}
// Just like you access id from the post variables have to do the same for the table
// variable we passed with that same form. :D
elseif($_POST['table'] == 'windows') { 
$id=$_POST['id']; 
$sql = "UPDATE $table_windows SET tittel='$tittel', innhold='$innhold' WHERE id='$id'"; 
$query = mysql_query($sql) or die(mysql_error()); 
echo "Table updated.......<br><a href='windows_admin.php'>To mainpage</a>"; 
} 
elseif($_POST['table'] == 'unix') { 
$id=$_POST['id']; 
$sql = "UPDATE $table_unix SET tittel='$tittel', innhold='$innhold' WHERE id='$id'"; 
$query = mysql_query($sql) or die(mysql_error()); 
echo "Table updated.......<br><a href='unix_admin.php'>To mainpage</a>";

Like i said you had it all down just forgot your table variable was now needing to be pulled from post :queasy: happens to all us 😉 Hope it helps, as always email [e-mail] if you needing anything further or post 🙂

[chrisdee]

Wow. This is great. It finally works. Thank you so much.

No I just need to understand what happens in the script.

I guess that when I click on the link < a href=edit.php?id=$id&table=xxx>Edit< /a>
in one of the admin files (windows_admin.php, unix_admin.php, mysql_admin and encylopedia_admin.php) it sends the id and table information to the edit.php file.
The edit.php file recives this information with the global variable $GET['id'] and $GET['table']. It so puts this information into the form. Then I can change it and the global variable $_POST['table'] registers this and makes the table info avalible to the sql update statement. Finally the table is updated.

I hope this is somewhat correct ?

[SyWill]

Hey chrisdee,

OKay so first off grats on page working 🙂, glad could lend a hand, and now for the techno-babble and yup thats really a word 😉.

Html forms can have two methods post and get set inside the <form> tag. They both obviously send the data to your php by using the name give to the input field inside the <input tag> and whatever value is inside the input is sent along with its name. so in the case of get your information is sent by attaching the variable names and their values through the url itself
ex. edit.php?id=1&table=windows.
post variables however and not going to get into how because thats for another more boring post lol, send the information as well just not in the url, so whats happening with your script is your sending user to edit?id=1&table=windows so edit.php sees the super long variable added url and takes and puts each one into global get, now the reason we have to input your get data back into the form before we submit it is because your using post for your second method and since it doesnt add anything to the url when you submit form your going to edit.php with no ?id= so php doesnt set any globals for gets because it doesnt see them in the url, so only way to make sure that your script can see them is to pass them in hidden inputs in the form were sending with post, so now all variables are in $_POST['variablename'] and life is good 🙂 hope that answers your question if your needing anything more specific about any topic be sure and let me know via email [e-mail] or post some more 🙂

[chrisdee]

I think I undertand a little bit more about $POST and $GET now, but I think it's a bit complicated. The first question that comes to mind is if there is a smarter, better or easier way of doing what I'm trying to do ?

My goal is to keep things simple, short, consise and smart.

But since I'm not so advanced when it comes to PHP it's a bit complicated to make things smart yet.

[SyWill]

Hey chrisdee,

First off let me tell you that you've been going through the code and working pretty well at picking out what this or that is and how it fits into the bigger picture shouldnt doubt your ability to pick up on the "advanced" methods used by some. That said 🙂 theres not a whole lot you can play with to whittle the code down alot more except maybe to start using switch statements instead of if statements, a good resource on switch is http://www.tizag.com/phpT/switch.php they provide a little cleaner code, easier to read most the time so you dont get lost in nested if's and generally have better preformance speeds. Try and stick to one type of form would be a suggestion if your going to use forms that I think would give you a little less headache 😉 select get or post and try to really keep to one or the other i usally select post for my forms for a couple reasons, mainly though because people are either A curios or B out to break your script and so with a long url with variables all attached at end unless you have some strict protection from bad input from users its a temptation few users can resist. Honestly though one of my major headache saving tips for you is to learn sessions!! cant stress how much cleaner, easier, and readable code is when they are using session variables. again a excellent resource is http://www.tizag.com/phpT/phpsessions.php they give you the basics of how stuff works but wont bog you down too much with the technical hows and whys or even advanced usage of sessions. Honestly if it were me recreating what youve done id be using sessions and classes, and the code for actually altering your fields would look like
To Delete:
$tableWindows->removeEntry($_SESSION['id']);
echo "<p>Entry Removed From $tableWindows->tableName<p>";
echo "<a href='$tableWindows->adminPage'>Go Back</a>";
But that is getting a little more advanced with php classes, youll get there and like i said with all the tools and methods you learn as you go it brings you one step closer to bein the one answering my questions about stuff which definatly could see happening 😃 well hope everything has helped again feel free to post or email me at [e-mail] if you needin anything else

[chrisdee]

You have been very helpfull and informative.
3 x thank you, or said in php :

<?
for($i=0; $i<3; $i++) {
 print "Thank You&#9786;<br>";
}
?>