MYSQL PHP Form Problem

Question

    <style type="text/css">
table {
    font-size:12px;}
</style>
<?php

//Verbindung
require_once ("connect/mysql_connect.php"); 
$poll = mysql_connect($hostname_poll, $username_poll, $password_poll) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db ("online_reg");
global $poll;
$sql = "SELECT * FROM `registration_extern_download` WHERE shop='Simmarket'";
$result = mysql_query($sql);


if ($action == 'reset')
    {
       $result = mysql_query("update online_reg set modify = now(), activation_key_1 = '', activation_key_2 = '',activation_key_3 = '' where id = '" . $id . "");

    }


//Ausgabe
echo"<table border='1' style='font-size 5pt'>";


while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>
            <td>$row[id]</td>
            <td>$row[customer_email]</td>
            <td>$row[products_serial]</td>
            <td>$row[activation_key_1]&nbsp;</td>
            <td>$row[activation_key_2]&nbsp;</td>
            <td>$row[activation_key_3]&nbsp;</td>
            <td><form id='form1' name='form1' method='post' action=simmarket.php?action=reset&id=$row[id]><input type='submit' value='Zurucksetzen' name='submit' /></form></td>
        </tr>";


}
echo"</table>";

?>  

At the momment my reset dont work with that error

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in /usr/www/users/aerosy/analysis/simmarket.php on line 27

Maybe anyone could help with that it should reset all activation keys with the id of it...

Answer 1

Here's a rewrite of the script using PDO and a class (DBConnection) to handle PDO connections and database user credentials.

<?php
$db = DBConnection::connect(array('db' => 'online_reg'));

if ($action == 'reset') {
   $resetQuery = $db->prepare(
       "UPDATE registration_extern_download
           SET modify = NOW(), activation_key_1 = '', 
               activation_key_2 = '',activation_key_3 = '' 
           WHERE id = ?");
   try {
     $resetSucceeded = $resetQuery->execute(array($id));
   } catch (PDOException $exc) {
       ?><p class="error">Reset failed.</p><?php
       /* A reason for the error & how it can be fixed should be added to output, if appropriate. */
   }
}

# query could be parameterized on 'shop' field and re-used elsewhere
$registrations = $db->query(
    "SELECT id, customer_email, products_serial, 
            activation_key_1, activation_key_2, activation_key_3 
        FROM `registration_extern_download` 
        WHERE shop='Simmarket'");

//Ausgabe
try {
    ?>
    <table class="registration">
      <?php foreach ($registrations as $idx => $row): ?>
        <tr>
          <?php foreach ($row as $field => $value): ?>
            <td><?php echo $value ?></td>
          <?php endforeach; ?>
          <td><form name="form<?php echo $idx; ?>" method="post" 
                    action="simmarket.php?action=reset&id=<?php echo htmlspecialchars($row['id']) ?>">
              <input type="submit" value="Zurucksetzen" name="submit" />
          </form></td>
      <?php endforeach; ?>
    </table>
    <?php
} catch (PDOException $exc) {
    /* Log error & inform user. If error is user-fixable, tell user what went wrong
     * and how to fix it.
     */
}

Your UPDATE statement references a table named "online_reg", which is the DB name. It appears the correct table is "registration_extern_download".

Study the example rather than copy-&-pasting it into your script. There is still a vast amount of room for improvement.

Implementation of DBConnection left as an exercise for the reader.

Answer 2

You should check if $result is an result, and not false. Good chance one of your queries triggered an error, you can check that using mysql_error.

Answer 3

Your mysql_query() call is failing, that's why you are getting that error, because $result is the boolean value FALSE. This is probably because your query has a syntax error in it - you are missing a closing single quote ' in the query.

Change

$result = mysql_query("update online_reg set modify = now(), activation_key_1 = '', activation_key_2 = '',activation_key_3 = '' where id = '" . $id . "");

To

if (!$updateResult = mysql_query("UPDATE `online_reg` SET `modify` = now(), `activation_key_1` = '', `activation_key_2` = '', `activation_key_3` = '' WHERE `id` = '".mysql_real_escape_string($id)."'")) die('MySQL Error: '.mysql_error());

...but be careful not to show the contents of mysql_error() to anyone in the real world, this should be for debugging purposes only!

EDIT

As outis rightly points out, you have overwritten the contents of the $result variable with the result of an UPDATE statement, so it will always be a boolean TRUE or FALSE. You either need to rename the second $result variable (as above in my corrected example) or drop it completely - you probably don't need to capture the return value for use later, so you can just wrap the mysql_query() call in an if statement and act appropriately at the time of the query.

Answer 4

put the followings inside if statement.

echo"<table border='1' style='font-size 5pt'>";


while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>
            <td>$row[id]</td>
            <td>$row[customer_email]</td>
            <td>$row[products_serial]</td>
            <td>$row[activation_key_1]&nbsp;</td>
            <td>$row[activation_key_2]&nbsp;</td>
            <td>$row[activation_key_3]&nbsp;</td>
            <td><form id='form1' name='form1' method='post' action=simmarket.php?action=reset&id=$row[id]><input type='submit' value='Zurucksetzen' name='submit' /></form></td>
        </tr>";