Basic Script (with SQL) - need help

[Kyan_Vrezpor]

Hi, Im having problems with a basic database script. Basically, I have two pages: KyanHome.php and KyanACP.php. Here is KyanHome.php:

[php]

Kyan's Name Database <?php $username="*"; $password="*"; $database="*"; function printForm() { ?>

This is the perfect chance to submit your name to our database, and NOT be entered into one of those annoying free monthly prize draws that nobody ever wins.

Please type your first name here:

Please type your surname here:

<?php }

if ( ! isset( $_POST[‘firstname’] ) and ! isset( $_POST[‘surname’] ) ) {

print “

Welcome!

”;

printForm();

} else if ( empty( $_POST[‘firstname’] ) or empty( $_POST[‘surname’] ) ) {

print “

Error!

”;

print “Please enter both a first name and a surname.”;

printForm();

} else {

$firstname = $_POST[‘firstname’];
$surname = $_POST[‘surname’];

$link = mysql_connect( “localhost”, $username, $password );

if ( ! $link ) {
die( "Couldn’t connect to MySQL: ".mysql_error() );
}

mysql_select_db( $database ) or die ( "Couldn’t open $database: ".mysql_error() );

$query = “INSERT INTO details(firstname, surname) VALUES ( ‘$firstname’, ‘$surname’)”;

mysql_query( $query, $link );

mysql_close( $link );

print “

Thanks!

”;

print “Thanks for submitting your information to our database, $firstname $surname!”;

}

?>

Admin CP

Please enter the password to access the Admin Control Panel:

[/php]

ANd KyanACP.php:

[php]

Kyan's Name Database ACP <?php // Only allows access to those who have entered the password at some point, or // arrived through pressing the delete button. if ( ! isset( $_POST['ACPpassword'] ) or $_POST['ACPpassword'] != "guess" or ( isset($_POST['deletebutton']) and $_POST['deletebutton'] == false) ) { print '

Access Denied

'; // Prints the entry portal if access is denied ?>

Admin CP

Please enter the password to access the Admin Control Panel:

<?php // Prints the Admin CP } else { ?>

Welcome to the ACP!

From here you can view and delete details added to the database!

<?php $username="*"; $password="*"; $database="*"; $link = mysql_connect( "localhost", $username, $password ); if ( ! $link ) { die( "Couldn't connect to MySQL: ".mysql_error() ); } mysql_select_db( $database ) or die ( "Couldn't open $database: ".mysql_error() ); $results = mysql_query( "SELECT * FROM details" ); $num_rows = mysql_num_rows( $results ); ?> "> <?php while( $a_row = mysql_fetch_row( $results ) ) { print ""; foreach ( $a_row as $field ) { print ""; } print ""; } print "

No.	First Name	Surname	Delete
".stripslashes($field)."

"; ?> <?php if (isset($_POST['deletechkbox'])) { foreach ($_POST['deletechkbox'] as $checkbox) { mysql_query( "DELETE FROM details WHERE id=$checkbox" ); } } mysql_close( $link ); } ?> [/php]

You can see my script in action here: http://www.triwebs.com.ru/PHP/Kyan/KyanHome.php and http://www.triwebs.com.ru/PHP/Kyan/KyanACP.php (the password to the ACP is “guess”).

The problem (yes, were finally getting there), is that my delete function wont work.

I can see why, but it would be too confusing to explain, its to do with the id fields not corresponding to $checkbox because the ID field auto-increments.

Can someone help?[/url][/code]

[carella]

Hi,

Your problem is you aren`t assigning anything to the checkboxes. You need to assign a value (usually the id) for it to work.

Your foreach loop isn`t working because there is no value for it to find. Hope that makes sense.

And try using mysql_fetch_array(), this works best.

[Kyan_Vrezpor]

Yup, I see why it my loop doesn’t work. How is it best to assign the id though? Im quite new to PHP and SQL, and Im not totally sure how to do it. There is an id field in each row, so how would I assign that as the value? And for the fetch_array recommendation, if I replace fetch_row with fetch_array i get repeat values.

Sry about the n00b questions, could anyone help?

[Kyan_Vrezpor]

I did the neccessary edits, and now it deletes. Thanks

There is one problem though - it now shows duplicates of each piece of information (you can check this through the links provided). Could anyone explain this? Here is the modified code:

[php]<?php
while( $a_row = mysql_fetch_array( $results ) ) {
print “

”;
foreach ( $a_row as $field ) {
print “”.stripslashes($field)."";
}
print “ ”;
}
print “”;

?>

<?php if (isset($_POST['deletechkbox'])) { foreach ($_POST['deletechkbox'] as $checkbox) { mysql_query( "DELETE FROM details WHERE id=$checkbox" ); } } mysql_close( $link ); } ?>[/php]

All the rest of the code has stayed the same.

EDIT: And another thing: For the edits to show I have to refresh the page. Is there any way to get the page to auto-refresh when you click the delete button? Perhaps with headers?

[carella]

Ok, firstly, mysql_fetch_array takes each row from your query and returns the row as an associative array. Because you have a foreach loop inside your while loop, the info is being duplicated. You only need the while loop.

The mysql_fetch_array() function would be used like this:

<table border="0">
    <tr>
        <td>No</td>
        <td>First name</td>
        <td>Surname</td>
        <td>Delete</td>
    </tr>

<?php
while ($row = mysql_fetch_array($results))

{

      echo "<tr>n";
      echo "  <td>" . $row['id'] . "</td>n";
      echo "  <td>" . $row['firstname'] . "</td>n";
      echo "  <td>" . $row['surname'] . "</td>n";
      echo "  <td><td><input type="checkbox" name="deletechkbox[]" value="" . $row['id'] . "" /></td></</td>n";
      echo "</tr>n";

}

?>
</table>

Hopefully you get the idea. Change the info to suit your database tables. Note that the concatenation is simply a personal preference. Simply

$row[‘id’] will work just as fine.

As for refreshing, I always like to use the meta refresh inside a function:

function redirect() {

echo "<meta http-equiv="refresh" content="3;URL=KyanACP.php">n";
echo "<p align="center"><br>Entries deleted, please wait......."</p>n;
exit;

}

Add this function to your code and then call it after the foreach loop that performs your deletion. After your mysql_close() function will be fine.

redirect();

Hope that helps you.

[Kyan_Vrezpor]

Only one more problem - when you click “delete”, the “redirect” function takes you back to the login page, so you have to log in again. Without using things such as cookies, URL query strings and sessions, is there any way so that you dont have to re-enter your log-in details?

[carella]

I`m afraid not. Unless you remove your error checking, then the script is always going to return true, thus resulting in your log in page appearing.

You should think about using sessions.

[Kyan_Vrezpor]

I managed to sort it, I created a refresh button. Sessions look interesting but I thought Id leave them out of this one, will prolly try them in the future.

Thanks for your help

[carella]

You`re welcome.