[RESOLVED] Problem with "mysql_fetch_array()"

[Design-is-BS]

Hi, I'm trying to setup a profile editing form where the user fills out information, goes onto the database, yadda yadda yadda...

However I want them to have to sign in before their information is thrown onto the Database, security reasons.

I've tried and tried but I can't seem to get past this error I keep getting...

Here's the page:
Click (http://www.designisbs.ca/johnbursic/edit_profile_verification.php)

And here's the PHP (BTW, thers no concern with connection to my Database, I've tested that already.):

<?php

echo "Please enter password<br>";
echo "<form method=post>";
echo "<input type=password name=password>";
echo "<input type=submit name=submit value=Submit>";

$form_pass = $_POST['password'];

$passSQL = "SELECT * FROM user_login WHERE password = '$form_pass'";

$DBpass = mysql_query($passSQL, $link);
while ($row = mysql_fetch_array($DBpass)) { $DBpass2 = "row[1]"; }
if ($DBpass2 == $form_pass) { echo "success"; }
else { echo "failure"; }

$surname = $_POST['surname'];
$givenName = $_POST['givenName'];
$homeSuite = $_POST['homeSuite'];
$mailSuite = $_POST['mailSuite'];
$homeAddy = $_POST['homeAddy'];
$mailAddy = $_POST['mailAddy'];
$homeCity = $_POST['homeCity'];
$mailCity = $_POST['mailCity'];
$homeProv = $_POST['homeProv'];
$mailProv = $_POST['mailProv'];
$homeCount = $_POST['homeCount'];
$mailCount = $_POST['mailCount'];
$homePost = $_POST['homePost'];
$mailPost = $_POST['mailPost'];
$busPhone = $_POST['busPhone'];
$celPhone = $_POST['celPhone'];
$homPhone = $_POST['homPhone'];
$fax = $_POST['fax'];
$busEmail = $_POST['busEmail'];
$perEmail = $_POST['perEmail'];

$query = "INSERT INTO user_info (surname, givenName, homeSuite, mailSuite, homeAddy, mailAddy, homeCity, mailCity, homeProv, mailProv, homeCount, , homePost, mailPost, busPhone, celPhone, homPhone, fax, busEmail, perEmail) VALUES('" . $surname . "', '" . $givenName . "', '" . $homeSuite . "', '" . $mailSuite . "', '" . $homeAddy . "', '" . $mailAddy . "', '" . $homeCity . "', '" . $mailCity . "', '" . $homeProv . "', '" . $mailProv . "', '" . $homeCount . "', '" . $mailCount . "', '" . $homePost . "', '" . $mailPost . "', '" . $busPhone . "', '" . $celPhone . "', '" . $homPhone . "', '" . $fax . "', '" . $busEmail . "', '" . $perEmail . "' WHERE password = '$formpass')";
$result = mysql_query($query,$link);

if (mysql_affected_rows() == 1) {
echo "<br>Success";
} else {
echo "<br>failed";

}


?>

[SyCo]

Echo the queries to the page and look for errors. Run it on a command line in something like putty and see what the error is too.

Also can you add which is line 35 (is this an extract from your real script?

is this "row[1]" meant to be "$row[1]"?

if you're only after a single result you dont need the while loop.

and you can save a line here too.

$row = mysql_fetch_array($DBpass)
if ($row[1] == $form_pass) { echo "success"; }

You're not sanitizing you're database inputs either. You're wide open to some idiot wiping your entire database. That might fix the error if you've got any weird input in your form.

[MrCoder]

Use mysql_real_escape_string() and typecasting on your $_POST values.

[Design-is-BS]

SyCo,

This is line 35, where the supposed error is:

while ($row = mysql_fetch_array($DBpass)) { $DBpass2 = "$row[1]"; }

[Design-is-BS]

MyCoder,

I'm not sure what you mean by that. Can you please explain?

[Design-is-BS]

You're not sanitizing you're database inputs either. You're wide open to some idiot wiping your entire database. That might fix the error if you've got any weird input in your form.

Woah, thats scary.

I mean right now this Database has nothing of significance. It's just a testing Database so I can get the code to work.

[SyCo]

Yea it is, but it's easy to fix too. Even though it's said often lots of people still don't get sanitize their inputs. Look into MrCoder's suggestions.

Check this out too :)
http://xkcd.com/327/

Did the preceding query look OK? That's likely where the problem is. Try hard coding the value for testing. Also run it in a terminal or Putty (or whatever command line program) to see if it runs OK.

You'd get a can't connect error if the link was bad, so that's probably OK. If you're only connecting to one database on the page you don't have to add the link resource every time. Although a lot pf people do, not sure why. Is there an advantage to it? Here's the manual page it doesn't mention any advantage but like I say, I see it a lot.

http://us3.php.net/mysql_query

link_identifier

The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If by chance no connection is found or established, an E_WARNING level error is generated.

[Design-is-BS]

OK, I've been trying to make this work but no matter what I do it gives an error of "syntax error, unexpected $end".

Here's the code:

<?php

echo "Please enter username & password<br>";
echo "<form method=post>";
echo "Username: <input type=password name=username><br>";
echo "Password: <input type=password name=password><br>";
echo "Email: <input type=password name=email><br>";
echo "<input type=submit name=submit value=Submit>";

/* $form_pass = $_POST['password'];
$form_user = $_POST['username'];
$form_email = $_POST['email'];
$DB_user = "$row[0]";
$DB_pass = "$row[1]";
$DB_mail = "$row[2]";

$sql = "SELECT * FROM users";

$DBusers = mysql_query($sql, $link);
while ($row = mysql_fetch_array($DBusers)){

if ($DB_pass == $form_pass) {
echo "<br><br><br>Pass Works";
}
else {
echo "<br><br><br>Pass Failed";
}

if ($DB_user == $form_user) {
echo "<br>User Works";
}
else {
echo "<br> User Failed";
}

if (DB_mail == $form_email) {
echo "<br>Email Works";
}
else {
echo "<br>Email Failed";
}
}*/


if (isset($_POST['username']) && isset($_POST['password']) && isset($_POST['email'])) {
// Connect

$hostname = "";
$username= "";
$password= "";
$dbid="";
$link=mysql_connect($hostname, $username, $password);
mysql_select_db($dbid) or die ("Unable to connect to MySQL");

if(!is_resource($link)) {

echo "Failed to connect to the server\n";
// ... log the error properly

} else {

// Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.

if(get_magic_quotes_gpc()) {
$username = stripslashes($_POST['username']);
$password = stripslashes($_POST['password']);
$email = stripslashes($_POST['email']);
} else {
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];
}

// Make a safe query
$query = sprintf("SELECT * FROM products (`username`, `password`, `email`) VALUES ('%s', '%s', %s)",
mysql_real_escape_string($username, $link),
mysql_real_escape_string($password, $link),
mysql_real_escape_string($email, $link));

while (mysql_fetch_assoc($row = mysql_query($query, $link))) {
echo "<br><br>" . $row[1];
}
?>
</body>
</html>
<?php mysql_close($link); ?>

[SyCo]

That error usually means you've not closed a curly bracket.

Actually, you've not closed 2 :) Close the first and second if()s

[Design-is-BS]

Well don't I feel embarassed...

THAT portion is fixed. But now I'm being told (once submitted form) that:
"mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource"

For this:

while ($row = mysql_fetch_assoc($fetch)) {
echo "<br><br>" . $row['username'];
}


Should it be this instead?:

while ($row = mysql_fetch_assoc($fetch, $link)) {
echo "<br><br>" . $row['username'];
}

[SyCo]

$fetch isn't in you previous post?

It usually means you have an error in your SQL. It's most likely a syntax error. Echo the query to the page so you can examine it for problems and add

echo mysql_error();

after running the query to get back what the server is saying.

[Design-is-BS]

I apologize, here's is where $fetch was added:


// Make a safe query
$query = sprintf("SELECT * FROM products (`username`, `password`, `email`) VALUES ('%s', '%s', %s)",
mysql_real_escape_string($username, $link),
mysql_real_escape_string($password, $link),
mysql_real_escape_string($email, $link));
$fetch = mysql_query($query, $link);

while ($row = mysql_fetch_assoc($fetch)) {
echo "<br><br>" . $row['username'];
}


I'll try to get the query back, then I'll let you know.

Thanks, SyCo.

[Design-is-BS]

I'm getting back: "SELECT * FROM products (username, password, email) VALUES (usertest, passtest, emailtest)"

Seems fine to me, am I wrong? Those ARE the inputs I have, and it matches what's on the DB.

EDIT:
--

There I go again, I changed the code to:

// Make a safe query
$query = sprintf("SELECT * FROM products (username, password, email) VALUES (%s, %s, %s)",
mysql_real_escape_string($username, $link),
mysql_real_escape_string($password, $link),
mysql_real_escape_string($email, $link));
$fetch = mysql_query($query, $link);

echo $query;

[Design-is-BS]

The MySQL error I get is:
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(username, password, email) VALUES (usertest, passtest, emailtest)' at line 1"

[Design-is-BS]

Sorry for the constant replies...

I think I might have found soemthing:

My DB Table is "users" not "products". I changed it and got an error of:

"Warning: Wrong parameter count for mysql_fetch_assoc()"

[Design-is-BS]

OK, here is what I'm trying to do...

ANYONE FEEL FREE TO ASSIST! PLEASE!!!!

1) The user is sent an email containing a Randomly Generated Password and a User-name made up of the first-part of their email.

2) In this email is a link to a webpage, where the user enters in three bits of information:
2a) Username (provided)
2b) Password (provided)
2c) Email

3) upon clicking "Submit" the information is compared to what is on the DB (Database).
3a) If there is a match, the app carries on.
3b) If there isn't a match the user is prompted to try again.

4) Upon a successful match, the user is then directed to a page where they can customize their login (User-Name and Password) and they can enter in other information (Address, First Name, etc.)

Ok...Now...I haven't gotten that far yet; I'm stuck on #3. I am not able to compare what is on my DB to what the user has submitted. F0r some reason what I was doing earlier wasn't safe, so I'm forced to try a new way of doing things.

This is my Code, currently:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Testing - Investor Login</title>

</head>

<body>
<?php

echo "Please enter username & password<br>";
echo "<form method=post>";
echo "Username: <input type=text name=username><br>";
echo "Password: <input type=text name=password><br>";
echo "Email: <input type=text name=email><br>";
echo "<input type=submit name=submit value=Submit>";

/* $form_pass = $_POST['password'];
$form_user = $_POST['username'];
$form_email = $_POST['email'];
$DB_user = "$row[0]";
$DB_pass = "$row[1]";
$DB_mail = "$row[2]";

$sql = "SELECT * FROM users";

$DBusers = mysql_query($sql, $link);
while ($row = mysql_fetch_array($DBusers)){

if ($DB_pass == $form_pass) {
echo "<br><br><br>Pass Works";
}
else {
echo "<br><br><br>Pass Failed";
}

if ($DB_user == $form_user) {
echo "<br>User Works";
}
else {
echo "<br> User Failed";
}

if (DB_mail == $form_email) {
echo "<br>Email Works";
}
else {
echo "<br>Email Failed";
}
}*/


if (isset($_POST['username']) && isset($_POST['password']) && isset($_POST['email'])) {
// Connect

$hostname = "myhost";
$username= "myuser";
$password= "mypass";
$dbid="myid";
$link=mysql_connect($hostname, $username, $password);
mysql_select_db($dbid) or die ("Unable to connect to MySQL");

if(!is_resource($link)) {

echo "Failed to connect to the server\n";
// ... log the error properly

} else {

// Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.

if(get_magic_quotes_gpc()) {
$username = stripslashes($_POST['username']);
$password = stripslashes($_POST['password']);
$email = stripslashes($_POST['email']);
} else {
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];
}


Make a safe query
$query = sprintf("SELECT * FROM users (username, password, email) VALUES (%s, %s, %s)",
mysql_real_escape_string($username, $link),
mysql_real_escape_string($password, $link),
mysql_real_escape_string($email, $link));
$fetch = mysql_query($query, $link);

echo "<br>" . $query . "<br>";
echo mysql_error();

while ($row = mysql_fetch_assoc($fetch)) {
echo "<br><br>" . $row['username'];
}
}
}
?>
</body>
</html>


Please, for the love of all that is holy, I'm so utterly stuck..

[SyCo]

I'm getting back: "SELECT * FROM products (username, password, email) VALUES (usertest, passtest, emailtest)"

is missing quotes around the string values eg ('username', 'p... etc

[Design-is-BS]

Fixed it, so now:

//Make a safe query
$query = sprintf("SELECT * FROM users ('username', 'password', 'email') VALUES ('%s', '%s', '%s')",
mysql_real_escape_string($username, $link),
mysql_real_escape_string($password, $link),
mysql_real_escape_string($email, $link));
$fetch = mysql_query($query, $link);

echo "<br>" . $query . "<br>";
echo mysql_error();

while ($row = mysql_fetch_assoc($fetch)) {
echo "<br><br>" . $row['username'];
}


I am still getting the same error:
MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('username', 'password', 'email') VALUES ('usertest', 'passtest', 'emailtest')' at line 1

PHP: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

The PHP Error is in response to:

while ($row = mysql_fetch_assoc($fetch)) {

[SyCo]

If this is a syntax for select
SELECT * FROM users ('username', 'password', 'email') VALUES ('%s', '%s', '%s'
Its a new one on me. It's more like the syntax for an insert.

The syntax I've always used for a select is

query="SELECT * FROM users WHERE username='%s' AND password='%s' AND email='%s'"

I'm a little busy today so not giving this my full attention. Sorry about missing that :)

[Design-is-BS]

Don't worry about it SyCo, I would have never even thought of that.

I tried this method like this:

$query="SELECT * FROM users WHERE username='%s' AND password='%s' AND email='%s'",
mysql_real_escape_string($username, $link),
mysql_real_escape_string($password, $link),
mysql_real_escape_string($email, $link));


And got an error of:
PHP: Parse error: syntax error, unexpected ',' in...on line 85

Line 85 is:

$query="SELECT * FROM users WHERE username='%s' AND password='%s' AND email='%s'"


So I tried:

//Make a safe query
$query="SELECT * FROM users WHERE username='$username' AND password='$password' AND email='$email'";
// mysql_real_escape_string($username, $link),
// mysql_real_escape_string($password, $link),
// mysql_real_escape_string($email, $link));
$fetch = mysql_query($query, $link);

while ($row = mysql_fetch_assoc($fetch)) {
echo "<br><br>Username: " . $row['username'];
echo "<br>Password: " . $row['password'];
echo "<br>Email: " . $row['email'];
}


And it SEEMS to be working.

I'll try the next step: comparing to what's on the DB. And because I have to learn I will try myself.

But trust my SyCo, you'll be hearing from me if I get stuck again, you always give me a hand!

[SyCo]

You're welcome. You're keen to learn, provide lots of feedback on suggestions and let me know what you're doing, multile post are OK too, the more info the better. You even gave me the line the error war given for. It's that kind of thing that makes helping you a pleaseent experience. I've only ever once given up on someone because they didn't get that if it's hard work for me to help them, why would I help?

You still need to sanitize the inputs. Maybe this will do it for you.

$username=mysql_real_escape_string($username, $link),
$password=mysql_real_escape_string($password, $link),
$email=mysql_real_escape_string($email, $link));

$query="SELECT * FROM users WHERE username='$username' AND password='$password' AND email='$email'";
$fetch = mysql_query($query, $link)