• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

Reset MySQL-database with a button on homepage?

Hey! I have a homepage. After you login on that homepage, there is an iframe with a PHP-script that spawns a list of random items from my database (called "items"). There is a button infront of each item where you can delete the item, so it won't ever shown again for you (the user, that is signed on), if you hate that item fx . When you delete items, it actually just adds the items id and your username to a new database called "deleted_items". The script in my iframe checks for items that is in the "deleted_items" and then just don't show those items, before showing the random items.

But what I would like is a button or something on my homepage, that when clicked empties "deleted_items", so that the database will be empty again for that user only and all his or hers items will show again. But you would still be able to delete them again afterwards.

In "deleted_items" is two rows: id (INT3) and username (VARCHAR20).
I think it would be something like this: "check username, delete all id that belongs to that username"

Could someone help me do this? I am still somewhat a beginner to PHP and MySQL. Thanks alot!! I hope it isn't that hard to do :-)
0
MisterHamper
Asked:
MisterHamper
  • 15
  • 12
1 Solution
 
MisterHamperAuthor Commented:
This is how my database for deleted_items looks by the way
deleted-items.png
0
 
Roger BaklundCommented:
The sql statement would be:

DELETE FROM deleted_items WHERE username='heyhey'

You should have a unique numeric user id (primary key), this should be used in place of 'username' in the deleted_items table.
0
 
MisterHamperAuthor Commented:
Thanks for the answer!

In my items.php, the username is described as this
"    if (mysql_num_rows(mysql_query("SELECT id FROM deleted_items WHERE id=".mysql_real_escape_string($_GET['delete'])." AND username='".mysql_real_escape_string($_SESSION['user_name'])."'")) == 0)"
which works perfectly when adding an item to deleted_items
I pressume the "AND username='"." is what makes the username pick the user that is signed on, I mean that the username is written as a '.

But I am not really sure how to make a button like this. I think it should just be a hyperlink or something, maybe "<a href=# onClick=>" or how it would look. I don't really know, I have never tried something like this before.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Roger BaklundCommented:
Because this operation will update the database, it should be a button and a POST operation, and not a link.
<form method="post">
<input type="hidden" name="action" value="reset_deleted_items" />
<input type="submit" value="Reset deleted_items" />
</form>
 
<?php
if(isset($_POST['action']) and ($_POST['action']=='reset_deleted_items')) {
  mysql_query('DELETE FROM deleted_items WHERE username="'.
    mysql_real_escape_string($_SESSION['user_name']).'"');
  # redirect?
}
?>

Open in new window

0
 
MisterHamperAuthor Commented:
Oh I see! Thank you! Well, that is fine with me too.
What should the php-file that your button directs to, be called? Or should I just copy-paste all that code directly into my main homepage? Thanks!
0
 
MisterHamperAuthor Commented:
I just tried copy-pasting the code into my index.php. It doesn't seem to work. When I click the button, it just refreshes my site, and in the url-bar in the bottom it says http://mysitehere.com. The database is still as it was before. Hmm maybe I did something wrong? Please help :)
0
 
Roger BaklundCommented:
Do you connect to the database at the start of index.php? If not you must do that within the if block, before you call mysql_query().
0
 
MisterHamperAuthor Commented:
Yes I have to connect to the database in order to acces my homepage, else I get redirected to a login-page. My iFrames won't work without having connceted to the database, from the username, either.
0
 
Roger BaklundCommented:
Try this, to see if the code gets executed at all:
<?php
if(isset($_POST['action']) and ($_POST['action']=='reset_deleted_items')) {
  $sql = 'DELETE FROM deleted_items WHERE username="'.
    mysql_real_escape_string($_SESSION['user_name']).'"';
  if(!mysql_query($sql)) 
    echo '<p>Error: '.mysql_error().'<br />SQL:'.$sql.'</p>';
  else
    echo '<p>Removed rows from deleted_items for user '.$_SESSION['user_name'].'</p>';
}
?>

Open in new window

0
 
MisterHamperAuthor Commented:
It doesn't say anything now. When I click the button, it just reloads my index.php. No messages or anything, and the database isnt emptied. It doesn't show anything when hovering above the button anymore, though. Should I try to put the PHP code you just posted into a new PHP file, instead of having it in my index.php?
0
 
Roger BaklundCommented:
It should be possible to have it within index.php. Where in index.php did you put it? Are you sure that part is beeing executed? You should put it relatively close to the top of the script.

You can easily put it in a separate file, for instance called "reset_deleted_items.php". Just add action="reset_deleted_items.php" to the <form>, and add session_start() and database connection at the top.
0
 
MisterHamperAuthor Commented:
Nope it still does the same thing :( I made a PHP file called that and included the action=
It is placed somewhere in the middle of my index.php, but there isn't that much on my site anyway..
I had an session_start and database connect at the top of my index.php from my login-script
0
 
Roger BaklundCommented:
Can you post your code?
0
 
MisterHamperAuthor Commented:
Sure! Which one do you have in mind? My index.php, my items.php or my redirect.php/config.php (from the login system)?
0
 
Roger BaklundCommented:
I meant reset_deleted_items.php. :)
0
 
MisterHamperAuthor Commented:
Hey again! The button I have now, when I click it, it goes to mysite.com/reset_items.php, but the page is completely blank. And when I return to mysite.com/index.php and refresh, the things still haven't been deleted.

Here is the codes you requested
<form method="post" action="reset_items.php">
<input type="hidden" name="action" value="reset_deleted_items" />
<input type="submit" value="Reset items" />
</form>

<?php
session_start();
 
if(isset($_POST['action']) and ($_POST['action']=='reset_items.php')) {
  $sql = 'DELETE FROM deleted_items WHERE username="'.
    mysql_real_escape_string($_SESSION['username']).'"';
  if(!mysql_query($sql)) 
    echo '<p>Error: '.mysql_error().'<br />SQL:'.$sql.'</p>';
  else
    echo '<p>items was reset for user '.$_SESSION['username'].'</p>';
}
?>

Open in new window

0
 
Roger BaklundCommented:
The $_POST['action'] variable refers to the hidden input field with name="action", not to the action attribute in the <form>. It should be:

if(isset($_POST['action']) and ($_POST['action']=='reset_deleted_items')) {

0
 
MisterHamperAuthor Commented:
Thank you for the reply. But it still isn't working properly. All it does, when clicking on it, is reload the frontpage. Shouldn't it hmm go to www.mysite.com/reset_deleted_items.php? So it can show those:
    echo '<p>Error: '.mysql_error().'<br />SQL:'.$sql.'</p>';
  else
    echo '<p>breakfast was reset for user '.$_SESSION['username'].'</p>';

My input-box looks like tihs now
<form method="post">
<input type="hidden" name="action" value="reset_deleted_items.php" />
<input type="submit" value="Reset deleted_items" />
</form>

Thanks
<?php
session_start();
 
if(isset($_POST['action']) and ($_POST['action']=='reset_deleted_items')) {
  $sql = 'DELETE id FROM deleted_items WHERE username="'. //added "DELETE id FROM"
    mysql_real_escape_string($_SESSION['username']).'"';
  if(!mysql_query($sql)) 
    echo '<p>Error: '.mysql_error().'<br />SQL:'.$sql.'</p>';
  else
    echo '<p>items was reset for user '.$_SESSION['username'].'</p>';
}
?>

Open in new window

0
 
Roger BaklundCommented:
The form is wrong:

<form method="post">
<input type="hidden" name="action" value="reset_deleted_items.php" />
<input type="submit" value="Reset deleted_items" />
</form>

It should be:

<form method="post" action="reset_items.php">
<input type="hidden" name="action" value="reset_deleted_items" />
<input type="submit" value="Reset deleted_items" />
</form>

Is the name of the file "reset_items.php" or "reset_deleted_items.php"? The correct file name goes into the action attribute in the <form> element. This parameter dictates which script on the server will handle the form submission.

The hidden input field named "action" is a different type of action. This field could have any name, it is only used to identify this "command" in the server, to distinguish it from other form submissions and clicks. So this code:

if(isset($_POST['action']) and ($_POST['action']=='reset_deleted_items')) {

...refers to this line in the HTML:

<input type="hidden" name="action" value="reset_deleted_items" />

You could change both like this, and it would still work:

<input type="hidden" name="cheese" value="xyz" />

if(isset($_POST['cheese']) and ($_POST['cheese']=='xyz')) {
0
 
MisterHamperAuthor Commented:
Oh I see! Thank you :-)
Yes my PHP file is called reset_deleted_items.php

When I click the button now, it does indeed transport me to another page, the mysite.com/reset_deleted_items.php/ so that is good. But reset_deleted_items.php is completely blank, nothing at all shows. I have no clue why it would be blank. It should atleast show the echo. Do you have an idea why that is? Thanks

<?php
session_start();
 
if(isset($_POST['action']) and ($_POST['action']=='reset_deleted_items')) {
  $sql = 'DELETE id FROM deleted_items WHERE username="'.
    mysql_real_escape_string($_SESSION['username']).'"';
  if(!mysql_query($sql))
    echo '<p>Error: '.mysql_error().'<br />SQL:'.$sql.'</p>';
  else
    echo '<p>items was reset for user '.$_SESSION['username'].'</p>';
}
?>
0
 
Roger BaklundCommented:
Try changing it like this:
<?php
session_start();
if(!isset($_SESSION['username'])) 
  die('No access! (unknown username)');
if(isset($_POST['action']) and ($_POST['action']=='reset_deleted_items')) {
  $sql = 'DELETE id FROM deleted_items WHERE username="'.
    mysql_real_escape_string($_SESSION['username']).'"';
  if(!mysql_query($sql))
    echo '<p>Error: '.mysql_error().'<br />SQL:'.$sql.'</p>';
  else
    echo '<p>items was reset for user '.$_SESSION['username'].'</p>';
} else var_dump($_POST); # debug, remove later
?>

Open in new window

0
 
MisterHamperAuthor Commented:
Oh now it does says "No access! (unknown username)" when I enter the button :-)

I thought it is because there is no "MySQL database" connect, so I tried adding this to the top of reset_deleted_items.php:
"<?php
session_start();
include ('config.php');
$connection = mysql_connect($server, $dbusername, $dbpassword) or die(mysql_error());
$db = mysql_select_db($db_name,$connection) or die(mysql_error());"

But still the same, No Access!.... hmm what could it be? :)
0
 
Roger BaklundCommented:
$_SESSION['username'] is not set. Is this the session variable you set on successfull login?

And yes, you do need to connect to the database. :)
0
 
MisterHamperAuthor Commented:
WE ARE GETTING CLOSE!! :D

It now said this, so it now knows it is ME who is logged on
"Error: Unknown table 'id' in MULTI DELETE
SQL:DELETE id FROM deleted_items WHERE username="MisterHamper""
after I changed all the username to user_name

It looks to me like it thinks "table 'id'" is a database, but instead it should look at the row id in the database deleted_items.
Or maybe it has something to do with that "MULTI DELETE" thing? I don't really know, but I do know we are getting close :D
0
 
Roger BaklundCommented:
'id' should not be in the sql delete:

$sql = 'DELETE FROM deleted_items WHERE username="'.
    mysql_real_escape_string($_SESSION['username']).'"';

Terminologi: a "table" is a collection of columns and rows, a "database" is a collection of tables.
0
 
MisterHamperAuthor Commented:
YEEES!!! :D
It's working, finally!! Thanks alot for your help, my good sir
0
 
MisterHamperAuthor Commented:
Thanks alot my good sir!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 15
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now