Unknown Column 'member_id' in 'where clause'

Question

I've searched this website for a solution but no result. I'm stuck on the following problem, my code gives the Unknown Colum 'member_id' in 'where clause' error. Never heard of it before.. so I am a bit confused right now.

This is my code :

<?php
require("db.php");
$id = $_REQUEST['member_id'];

$result = mysql_query("SELECT * FROM members WHERE member_id = '$id'");
$test = mysql_fetch_array($result);
if (!$result)
    {
    die("Error: Data not found..");
    }
$admin=$test['admin'] ;
$firstname=$test['firstname'] ;
$lastname=$test['lastname'] ;
$mail= $test['mail'] ;
$login=$test['login'] ;
$passwd=$test['passwd'] ;

if(isset($_POST['save']))
{
$admin_save = $_POST['admin'];
$firstname_save = $_POST['firstname'];
$lastname_save = $_POST['lastname'];
$mail_save = $_POST['mail'];
$login_save = $_POST['login'];
$passwd_save = md5($_POST['password']);

mysql_query("UPDATE lijst SET admin ='$admin_save',firstname ='$firstname_save',lastname ='$lastname_save', mail ='$mail_save', login ='$login_save',
     passwd ='$passwd_save' WHERE member_id = '$id'")
            or die(mysql_error());
echo "Saved!";

header("Location: main.php");
}
mysql_close($conn);
?>

The form :

<form method="post">
<table>
    <tr>
        <td>Admin</td>
        <td><input type="text" name="admin" class="text w_20" value="<?php echo $admin ?>"/></td>
    </tr>
    <tr>
        <td>Voornaam</td>
        <td><input type="text" name="firstname" class="text w_20" value="<?php echo $firstname ?>"/></td>
    </tr>
    <tr>
        <td>Achternaam</td>
        <td><input type="text" name="lastname" class="text w_20" value="<?php echo $lastname ?>"/></td>
    </tr>
    <tr>
        <td>E-mail</td>
        <td><input type="text" name="mail" class="text w_20" value="<?php echo $mail ?>"/></td>
    </tr>
    <tr>
        <td>Gebruikersnaam</td>
        <td><input type="text" name="login" class="text w_20" value="<?php echo $login ?>"/></td>
    </tr>
    <tr>
        <td>Password</td>
        <td><input type="text" name="passwd" class="text w_20" value="<?php echo $passwd ?>"/></td>
    </tr>
    <tr>
        <td>&nbsp;</td>
        <td><input type="submit" name="save" value="update" /></td>
    </tr>
</table>
</form>

so what's the structure of your members table? – Mark Baker Nov 21 '12 at 10:31 — Mark Baker, Nov 21 '12 at 10:31

MrCode · Accepted Answer · 2012-11-21T10:56:11.640

2

The error is saying there is no field member_id in the database table lijst. Add the field or remove the condition from the WHERE clause.

Given the SQL you just posted, your table is called members not lijst. So change the query:

UPDATE members ......

Edit: your MD5 problem is because your html input is named passwd but you are targeting password in the POST array:

$passwd_save = md5($_POST['password']);

Change to:

$passwd_save = md5($_POST['passwd']);

Finally, your query is vulnerable to SQL Injection. As a quick fix, run your user input through mysql_real_escape_string(). Or even better, switch to a modern MySQL API such as PDO, and use parameterised queries.

edited Nov 21 '12 at 10:56

answered Nov 21 '12 at 10:31

MrCode

63,975
10
90
112

This is my Members Table : CREATE TABLE `members` ( `member_id` int(11) unsigned NOT NULL auto_increment, `admin` varchar(15) NOT NULL default '0', `firstname` varchar(100) default NULL, `lastname` varchar(100) default NULL, `mail` varchar(150) NOT NULL, `login` varchar(100) NOT NULL default '', `passwd` varchar(32) NOT NULL default '', PRIMARY KEY (`member_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ; – Dave Piersma Nov 21 '12 at 10:32
1

That's the `members` table @DavePiersma. The error is caused by the `UPDATE` statement on the `lijst` table instead. – Rem.co Nov 21 '12 at 10:33
@DavePiersma see my edit... if your table is `members` then don't use `lijst` in the query, use `members`. – MrCode Nov 21 '12 at 10:34
@DavePiersma if this solved your problem, consider accepting the answer by clicking the tick to the left of it. – MrCode Nov 21 '12 at 10:40
@MrCode One more question, is the MD5 Part right ? Because it doesnt seem to change it to the right MD5, it turns out in MD5 in the database but it doesnt seem to convert it to the right MD5. – Dave Piersma Nov 21 '12 at 10:45
@Dave are you sure its the wrong md5 sum? Don't forget whitespace before or after the string will make it a different md5, use trim() if needed. Also if you have magic quotes on, that could be adding `\\` characters to it, making it different. – MrCode Nov 21 '12 at 10:51
@MrCode $passwd_save = md5($_POST['password']); This is the line that converts it to md5. I simply used 'test' as password but md5 doesn't make it 'test' – Dave Piersma Nov 21 '12 at 10:53
@DavePiersma you should use 1 language in your database to avoid confusion, for example if you set it to dutch, you use "leden" instead of "members", en "wachtwoord" instead of "password" and so on. I also recommend to check [this](http://stackoverflow.com/questions/401656/secure-hash-and-salt-for-php-passwords) as MD5 alone is unsafe ! – HamZa Nov 21 '12 at 10:53
1

@DavePiersma see my edit, your input name `passwd` doesn't match what you target in the PHP. – MrCode Nov 21 '12 at 10:56

Unknown Column 'member_id' in 'where clause'

1 Answers1