Basic form password input problem

[PHP-UZZLED]

Hi - I'm a real novice at this and trying my best to find my way through HTML PHP and MySQL. I'd like to have a form take an input (the password) and pass this to a db table which treats the password as an index returning an integer in an adjacent field in the table. Four strings of length 4 are concatenated and submited as the unique index.

eg input AAAA BBBB CCCC DDDD, which is concatenated to AAAABBBBCCCCDDDD and queried to the table which should return a value eg 30

Here's some code fragments:

This is in index.php.........

<form method="POST" action="connection.php">
<input type="PASSWORD" name="AAAA" size="4"><input type="PASSWORD" name="BBBB" size="4"><input type="PASSWORD" name="CCCC" size="4"><input type="PASSWORD" name="DDDD" size="4">
<p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>

<?php include("connection.php");?>


This is in connection.php


echo $_POST['AAAA'] . $_POST['BBBB'] .$_POST['CCCC'] .$_POST['DDDD'] .'<br>'; //This lets me see that the form input has been concatenated correctly
$my_password = $_POST['AAAA'] . $_POST['BBBB'] .$_POST['CCCC'] .$_POST['DDDD'];
echo $my_password . '<br>'; //This lets me see that the variable is assigned
$sql = mysql_query('SELECT * FROM `table101` WHERE `key` = $my_password');

if (!$sql) {
echo 'Could not run query: ' . mysql_error();
exit;
}

echo mysql_result($sql);

And I get the following error message:

Could not run query: Unknown column '$my_password' in 'where clause'

The issue seems to be centred on the mysql_query() function and the containing ' ' marks. I've used ' ', " " and no quotes each with a different error message. I'm a bit stuck on how to pass the variable to the table and get a return value. Can you please help?

Thanks

Nigel

[Mittineague]

Actually I'm surprised you didn't get an error when you created the table. "key" is a reserved word. That is, it has "special meaning" to MySQL so it can't be used as the name of a field.

[wiggsfly]

A couple of things I noticed are that you don't need the single quotes aroung the table and field name. Also, as pointed out above "key" should not be used as a field name. Finally, if you are storing passwords in a database they really should be hashed with SHA1 or something similar. Storing plain text passwords in a database is asking for trouble!

[PHP-UZZLED]

Thanks again Jack - I've renamed the table fields - the MySql db only has one table and that table only has two fields (now called 'ided' and 'code16') but even with these changes the error message is:

Could not run query: Unknown column '1DyVeThwTSGT6rK8' in 'where clause'

The 16 characters between the single quotes are concatenated from a form which has 4 input fields (each of 4 characters) and is then passed as a variable in the mysql_query() function to query the db table101. I was hoping to have the value 239 returned from the above input.

Clearly the mysql_query() function seems to be presenting the length 16 character string as a field name rather than as a field value to reference against. Could this be to do with my construction of the query or could it be a casting issue with the type of the input in the query now being different from the type of the data stored in the table field due to the fact I've concatenated these four strings together?

The the db table101
Field: ided; Type: int(11)
Field: code16; Type: char(16)

What do you recon?

[PHP-UZZLED]

Chris

Thanks very much for your feed back. Of course you're right and in my wanderings through the php function lists I've also encountered MD5 encryption which looks fab! I'll hopefully get round to using one or both of these security features but for now I'm simply trying to get a return from a table based on a form input (!) - which is proving a tough nut to crack!!!!

I've taken the single quotes out but I'm still get an error - it looks like the mysql_query() function is looking at my form input and is treating it as a field name rather than a field value; and I can't see what I'm doing wrong - the syntax looks OK. Its a bit perplexing.

(BTW: Regards where the single quotes came from originally, I used the "Create php code" method in phpMyAdmin.)

[Mittineague]

Last I knew, you could use "=" for strings, as well as numerics, but that "LIKE" was the prefered for strings. It may be that MySQL now requires "LIKE" for strings. i.e.

$sql = mysql_query('SELECT * FROM `table101` WHERE `ided` LIKE $my_password'); 

(you may also need quotes around $my_password)

[SJH]

You're trying to echo a variable into a string enclosed in single quotes. Variables are only parsed in double-quote strings. So you need the change your code as follows:

$sql = mysql_query("SELECT * FROM `table101` WHERE `ided` LIKE '$my_password'"); 

or

$sql = mysql_query('SELECT * FROM `table101` WHERE `ided` LIKE \''.$my_password.'\''); 

Edit: fixed syntax error in second example, sorry.

[PHP-UZZLED]

SJH - your first one works but gives the following error:

Warning: Wrong parameter count for mysql_result() in C:\wamp\www\photos\connection.php on line 28

with the code being:

$sql = mysql_query("SELECT * FROM `table101` WHERE `code16` LIKE '$my_password'");
if (!$sql) { echo 'Could not run query: ' . mysql_error();
exit;
}

[line 28+++] echo mysql_result($sql);

Now it looks to me like $sql should contain all the field values in table101 where code16 = $my_password

There are two fields in the table so I'm expecting something like 926 14c7XGEMkTK7P5eB.
Also I've tried SELECT `ided` - with the same resultant error. Looking at the php function mysql_result() I realise that I can also specify a row and a column name. And when I do that it gives the following error:

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in C:\wamp\www\photos\connection.php on line 28

which looks to me like its looking for a 3rd column in the table? - I'm getting rather confused here.

[SJH]

Try using mysql_fetch_array() instead.

[Mittineague]

If you do

$query "SELECT * FROM `table101` WHERE `code16` LIKE '$my_password'";
echo $query;
$sql = mysql_query($query) or die ('<p>Error: ' .mysql_error() . '</p>'); 

is $query like
"SELECT * FROM `table101` WHERE `code16` LIKE '$my_password'"
or
"SELECT * FROM `table101` WHERE `code16` LIKE '1DyVeThwTSGT6rK8'"

[PHP-UZZLED]

Gents

Thanks again for the guidance. Before these posts landed at my end I've continued to chip away at this and eventually got a return from the table using a very stripped down version of my previous coding; then using the mysql_fetch_row() function which worked. I'm going to work through mysql_*() functions to see just how these puppies behave.

Thanks again. Nigel

[PHP-UZZLED]

Further weird behaviour.....

I'm working in a very stripped down environment - coding and tables are very limited. I have noticed that I can get a return from a db table queried using a SELECT statement when the index variable

[PHP-UZZLED]

OOOOpppps..(try again)

Further weird behaviour.....

I'm now working in a very stripped down environment - coding and tables are very limited. I have noticed that I can get a return from a db table queried using a SELECT statement when the index variable is an integer but when I try to pass a character as the index variable the query wont run and seems to be mistaking the index variable for a column name.

eg
Field 1: a, b, c (varchar)(PRIMARY)
Field 2: 1, 2, 3 (int)

with index variable 'a' in the SELECT query - query won't run BUT

Field 1: 1, 2, 3 (int)(PRIMARY)
Field 2: a, b, c (varchar)

with index variable '1' - query returns 'a'

Any ideas?

Thanks

Nigel

(PS: I'm wondering if my WAMP installation is a bit goofy...)