Active MySQL add - Confused

Question

This is the error that I keep getting after I hit "Register" the email sends and all, however when I go to activate it under activate.php it gives me this error..:

Unknown column 'active' in 'field list'

I do understand that it wants me to add that field to the database I believe.. However I do not know the code on how to add it to the database, like the name and everything.. So it works fully and works with this script..

Here is the Activate.php script code:

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>GamesFX > Sign up</title>
    <link href="css/style.css" type="text/css" rel="stylesheet" />
</head>
<body>
    <!-- start header div -->   
    <div id="header">
        <h2>GamesFX > Sign up</h2>
    </div>
    <!-- end header div --> 

    <!-- start wrap div --> 
    <div id="wrap">
        <!-- start PHP code -->
        <?php

            mysql_connect("info here", "my stuff", 

"this is fine") or die(mysql_error()); // Connect to database server(localhost) with 

username and password.
            mysql_select_db("mysql works fine connecting") or die(mysql_error()); // Select 

registration database.

            if(isset($_GET['email']) && !empty($_GET['email']) AND isset

($_GET['hash']) && !empty($_GET['hash'])){
                // Verify data
                $email = mysql_escape_string($_GET['email']); // Set email 

variable
                $hash = mysql_escape_string($_GET['hash']); // Set hash 

variable

                $search = mysql_query("SELECT email, Activation 

FROM gamesfx_members WHERE email='".$email."' AND Activation='".$hash."' AND active='0'") or die

(mysql_error()); 
                $match  = mysql_num_rows($search);

                if($match > 0){
                    // We have a match, activate the account
                    mysql_query("UPDATE gamesfx_members SET active='1' 

WHERE email='".$email."' AND Activation='".$hash."' AND active='0'") or die(mysql_error

());
                    echo '<div class="statusmsg">Your account has been 

activated, you can now login</div>';
                }else{
                    // No match -> invalid url or account has already 

been activated.
                    echo '<div class="statusmsg">The url is either 

invalid or you already have activated your account.</div>';
                }

            }else{
                // Invalid approach
                echo '<div class="statusmsg">Invalid approach, please use 

the link that has been send to your email.</div>';
            }

        ?>
        <!-- stop PHP Code -->


    </div>
    <!-- end wrap div -->   
</body>
</html>

If anyone could please tell me what the code is to add the value "active" to the database so it works with everything else, let me know. Also.. Here's my current table.sql file:

    --
-- Table structure for table `gamesfx_members`
--

CREATE TABLE IF NOT EXISTS `gamesfx_members` (
  `id` int(11) NOT NULL auto_increment,
  `usr` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `pass` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `email` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `Activation` varchar(40) DEFAULT NULL,
  `regIP` varchar(15) collate utf8_unicode_ci NOT NULL default '',
  `dt` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `usr` (`usr`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

score 4 · Accepted Answer · edited May 23 '17 at 12:13

ALTER TABLE `gamesfx_members` ADD `active` integer;

MySql docs

Or, if you want to re-create the table:

CREATE TABLE IF NOT EXISTS `gamesfx_members` (
  `id` int(11) NOT NULL auto_increment,
  `usr` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `pass` varchar(32) collate utf8_unicode_ci NOT NULL default '',
  `email` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `Activation` varchar(40) DEFAULT NULL,
  `regIP` varchar(15) collate utf8_unicode_ci NOT NULL default '',
  `dt` datetime NOT NULL default '0000-00-00 00:00:00',
  `active` integer NOT NULL default 0
  PRIMARY KEY  (`id`),
  UNIQUE KEY `usr` (`usr`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Also, although this isn't directly related to your question, please have a look at How can I prevent SQL injection in PHP? for information about how to write more secure database queries in your code.

Do I just put this anywhere within the table structure for the table `gamesfx_members`? — user2955368, Nov 05 '13 at 18:48
@user2955368 The command I gave you should be run after the table is created. If you want to re-create the table with that column, see my edit. — Mansfield, Nov 05 '13 at 18:49

Active MySQL add - Confused

1 Answers1