Solved

Reset MySQL-database with a button on homepage?

Posted on 2009-04-12
27
316 Views
Last Modified: 2013-12-12
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
Comment
Question by:MisterHamper
  • 15
  • 12
27 Comments
 

Author Comment

by:MisterHamper
ID: 24127119
This is how my database for deleted_items looks by the way
deleted-items.png
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24127144
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
 

Author Comment

by:MisterHamper
ID: 24127173
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24127193
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
 

Author Comment

by:MisterHamper
ID: 24127205
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
 

Author Comment

by:MisterHamper
ID: 24127218
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24127253
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
 

Author Comment

by:MisterHamper
ID: 24127279
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24127299
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
 

Author Comment

by:MisterHamper
ID: 24127308
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24127334
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
 

Author Comment

by:MisterHamper
ID: 24127356
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24127371
Can you post your code?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:MisterHamper
ID: 24127378
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24127447
I meant reset_deleted_items.php. :)
0
 

Author Comment

by:MisterHamper
ID: 24128342
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24128858
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
 

Author Comment

by:MisterHamper
ID: 24129086
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24129628
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
 

Author Comment

by:MisterHamper
ID: 24129781
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24130444
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
 

Author Comment

by:MisterHamper
ID: 24130559
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24130686
$_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
 

Author Comment

by:MisterHamper
ID: 24130743
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
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 24131042
'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
 

Author Comment

by:MisterHamper
ID: 24131059
YEEES!!! :D
It's working, finally!! Thanks alot for your help, my good sir
0
 

Author Closing Comment

by:MisterHamper
ID: 31569377
Thanks alot my good sir!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now