I need some help PHP and MySQL

[Boerboel649]

OK, a friend of mine is into this virtual flying stuff. Here's wanting to start his own virtual flying site. One of the things they do is they fly certain assignments that are given to them. After so many hours of flying they get promoted. What my friend would like, is to have the people in his virtual flying thing to be able to get on the site, put in their hours, and then have the number of hours be updated on the site. (eg lets say they just joined, and they flew a one hour assignment, they go to the site, put in one hour and bingo, it's updated. Then lets say the next day, they fly two hours, they go to the site, punchin two, and then, the thing adds that to the numbers of hours they already have.) How would I do this? Also, how would I have it only update THEIR hours and not some other members? I'm kinda new to PHP, and need some help. Any help would be greatly appreciated. Any tutorials on something like this would also be appreciated.

I have a mySQL table named "members" with the columns Username Password First name last name etc. It also has a column "user_hours". When a user registers, the default value is 0.

Someone on another forum told me to checkout the UPDATE query. But I'm not exactly sure how to use it in my case. I would need it to update ONLY the hours of the person who is logged in. Also it would need to ADD the number of hours to the hours that the user already has instead of just replacing the previous number.


I'll be VERY VERY greatful is someone could help me with this.
Thanks!

[.josh]

[code]
if (!$username or !$password or !$addhours) { // if info is not entered into form
/* make a form posting to phpself, to enter in username, password, and hours. let's say it's names on the form                                      
    are "username"  "password" and  "addhours" so when you submit, you get $_POST['username']  
    $_POST['password'] and  $_POST['addhours'] that hold the info the user just entered in.  */
}
else { // all info is filled into form
   $username = $_POST['username'];
   $password = $_POST['password'];
   $addhours = $_POST['addhours'];
   $sql = "select user_hours from members where username='$username' and password='$password'";
   $result = mysql_query($sql) or die(mysql_error());
   if (mysql_numrows($result) != 0) {
      $currenthours = mysql_fetch_array($result);      
      $addhours = $addhours + $currenthours['user_hours'];
      $sql="update members set user_hours='$addhours' where username='$username' and password='$password'";
      $result = mysql_query($sql) or die(mysql_error());
   }
}
[/code]

[Barand]

No need to SELECT, Add, UPDATE. All you need is a single update.
[code]
   $username = $_POST['username'];
   $password = $_POST['password'];
   $addhours = $_POST['addhours'];

   $sql="UPDATE members
            SET user_hours= user_hours + '$addhours'
            WHERE username='$username' AND password='$password'";

      $result = mysql_query($sql) or die(mysql_error());
   }[/code]

[Boerboel649]

Thank you guys SOOOOO much!!!! I've posted this question on two different forums, and didn't get the answer I was looking for. (On one forum, I didn't get any answere... last I checked at least). Grant it, the other forum wasn't specifically PHP. Mostly Flash.
This forum is great! You'll probably be hearing from me again. :D
I'll have to try this out later. Busy with a cliend project currently.
Thanks again!

[Boerboel649]

Thanks Barand! It worked!! Sorry, Crayon Violent, but your code didn't work. Maybe 'cause I did it wrong :D
OK, how do I validate that the username and password submitted are in the database. Sorry that's a stupd question, but like I said, I'm new at this.

[Barand]

See if you get a record returned when you search for username and password

[code]$res = mysql_query(" SELECT COUNT(*) FROM members
                        WHERE username='$username'
                        AND password='$password'");
if (mysql_result ($res, 0) > 0) {
    # user found
}[/code]

[Boerboel649]

OK, maybe I don't have that code that you just gave in the right spo, but it didn't work. Here's the code for my form:
[code]<?php require_once('Connections/pvfarm.php'); ?>
<?php
// Load the tNG classes
require_once('includes/tng/tNG.inc.php');

// Make unified connection variable
$conn_pvfarm = new KT_connection($pvfarm, $database_pvfarm);

//Start Restrict Access To Page
$restrict = new tNG_RestrictAccess($conn_pvfarm, "");
//Grand Levels: Any
$restrict->Execute();
//End Restrict Access To Page
?>
</head>

<body>

<form name="hours" method="post" action="addhours.php">
<p> Number of hours
  <input type="text" name="addhours">
<p>Username
<input type="text" name= "username">
  <br>Password
  <input type="password" name="password">
<p>    <input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>[/code]
And then here's the code for the file addhours.php
[code]<?php $dbh=mysql_connect ("localhost", "peaceful_matthew", "mmdr9092") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("peaceful_members");  
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
<?php
$username = $_POST['username'];
   $addhours = $_POST['addhours'];

   $sql="UPDATE members
            SET user_hours= user_hours + '$addhours'
            WHERE username='$username'";

      $result = mysql_query($sql) or die(mysql_error());
    $res = mysql_query(" SELECT COUNT(*) FROM members
                        WHERE username='$username'
                        AND password='$password'");
if (mysql_result ($res, 0) > 0) {
    # user found
}
?>

</body>
</html>[/code]
It updates the hours beautifully, I just want it to return an error if the username and/or password are wrong. That way they don't think they've succesfully updated it even though maybe their username and/or password are wrong. The addhours.php page comes up blank and doesn't give any results. Thank you so much for your help and patience. It's greatly appreciated.

[AndyB]

[code]
<?php
$username = $_POST['username'];
$addhours = $_POST['addhours'];

$res = mysql_query(" SELECT COUNT(*) FROM members
                        WHERE username='$username'
                        AND password='$password'");
if (mysql_result ($res, 0) > 0) {
    // found a match
    $sql="UPDATE members
            SET user_hours= user_hours + '$addhours'
            WHERE username='$username'";
    $result = mysql_query($sql) or die(mysql_error());
} else {
    echo "No results for that username.";
}
?>

[/code]

[Boerboel649]

That didn't work. It always echoes "No results for that username" whether I put in correct info or incorrect info. It doesn't even update the table. It updates the table if I remove that code for checking the username and password, but obviously doesn't echo anything. What I'd like is for it to echo something like "Your hours have been submitted succesfully!" and then if the usernmae and password are incorrect it echoes "Your username or password were incorrect" or something like that.
Thank you for bearing with this newbie :)

[AndyB]

Wait a minute. password isn't in that code (which I duplicated from yours) so there will never be any matches so there will never be any updates. Code below is likely to work:

[code]<?php
$username = $_POST['username'];
$addhours = $_POST['addhours'];
$password = $_POST['password']; // get ALL the user input

$res = mysql_query(" SELECT COUNT(*) FROM members
     WHERE username='$username'
     AND password='$password'");
if (mysql_result ($res, 0) > 0) {
    // found a match
    $sql="UPDATE members
        SET user_hours= user_hours + '$addhours'
        WHERE username='$username'";
    $result = mysql_query($sql) or die(mysql_error());
} else {
    echo "No results for that username/password combination.";
}
?>[/code]

[Boerboel649]

It still isn't working. it always gives the message that no record was found for that username/password.
I can't thank you enough for all your help (and patience. :p)

[Boerboel649]

Here's my curent code for addhours.php
[code]<?php
$username = $_POST['username'];
$addhours = $_POST['addhours'];
$password = $_POST['password']; // get ALL the user input

$res = mysql_query(" SELECT COUNT(*) FROM members
     WHERE username='$username'
     AND password='$password'");
if (mysql_result ($res, 0) > 0) {
    // found a match
    $sql="UPDATE members
        SET user_hours= user_hours + '$addhours'
        WHERE username='$username'";
    $result = mysql_query($sql) or die(mysql_error());
} else {
    echo "No results for that username/password combination.";
}
?>[/code]

[Boerboel649]

I don't mean to be annoying, but could somebode tell me why the above code isn't working? I always returns that there's no username/password match, whether I type in a correct username and password, or even if I type in a wrong username and password.
Thanks!

[hitman6003]

Try this:

[code]<?php
$username = $_POST['username'];
$addhours = $_POST['addhours'];
$password = $_POST['password']; // get ALL the user input

$res = mysql_query("SELECT * FROM members WHERE username='$username' AND password='$password'");
if (mysql_num_rows($res) > 0) {
    // found a match
    $sql="UPDATE members
        SET user_hours= user_hours + '$addhours'
        WHERE username='$username'";
    $result = mysql_query($sql) or die(mysql_error());
} else {
    echo "No results for that username/password combination.";
}
?>[/code]

And, obviously, make sure you are connected to the db.

[Boerboel649]

Thanks for replying! However, it still isn't working. It wouldn't have anything to do with the password being encrypted in the database would it? Again, it still returns the same message whether I use correct username and password or if I use incorrect username and password.
Thank you guys so much for helping me out with this! I'm learning more and more.
Thanks!
P.S. And yes, I'm obviously connected to the DB. It works if I don't have that validation code.

[hitman6003]

[!--quoteo(post=348907:date=Feb 23 2006, 09:51 PM:name=Boerboel649)--][div class=\'quotetop\']QUOTE(Boerboel649 @ Feb 23 2006, 09:51 PM) [snapback]348907[/snapback][/div][div class=\'quotemain\'][!--quotec--]
It wouldn't have anything to do with the password being encrypted in the database would it?
[/quote]

Well, since in the code you've shown us you haven't encrypted the password in your select, then yes...that will affect it.

When it's encrypted it becomes a different string...so you can't compare the user input password with the stored encrypted password one-to-one. You have to encrypt the user input password using the same algorithm (md5, sha1, blowfish, whatever) as it is stored in the db with, then do your select.

[Boerboel649]

[!--quoteo(post=348910:date=Feb 23 2006, 09:56 PM:name=hitman6003)--][div class=\'quotetop\']QUOTE(hitman6003 @ Feb 23 2006, 09:56 PM) [snapback]348910[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Well, since in the code you've shown us you haven't encrypted the password in your select, then yes...that will affect it.

When it's encrypted it becomes a different string...so you can't compare the user input password with the stored encrypted password one-to-one. You have to encrypt the user input password using the same algorithm (md5, sha1, blowfish, whatever) as it is stored in the db with, then do your select.
[/quote]
Oooooohhhh... OK so that explains it. Sorry about that. The password is encrypted when the user registers. I have not actually started with the db and registration stuff on my friend's site yet, but I've been playing around with it on my server so that I know what I'm doing (or at least have an idea of what I'm doing.) Not sure what encryption I used, so I'll check into that. I'll see if I can figure it out from there, but if I don't, you'll probably be hearing from me again. :D
Again, Thank you SOOOOO much!!!!!!!!!!!

[Barand]

[code]It wouldn't have anything to do with the password being encrypted in the database would it?[/code]

There's a strong possibility!

if the pwd in db is encrypted then you need to encrypt posted passwprd also to get a match.

[Boerboel649]

Hey guys,
I just thought I'd drop in and say thanks for your help!!! Sorry about not mentioning earlier that the password was encrypted. I got it working now (thanks to you guys) here's my final working code:

[code]<?php
$username = $_POST['username'];
$addhours = $_POST['addhours'];
$password = $_POST['password']; // get ALL the user input
$encrypted_pw= md5($password);
$res = mysql_query("SELECT * FROM members WHERE username='$username' AND password='$encrypted_pw'");
if (mysql_num_rows($res) > 0) {
    // found a match
    $sql="UPDATE members
        SET user_hours= user_hours + '$addhours'
        WHERE username='$username'";
    $result = mysql_query($sql) or die(mysql_error());
    echo "Your hours have been updated!";
} else {
    echo "No results for that username/password combination.";
}
?>[/code]

[AndyB]

Thanks for the thanks. Not everyone remembers that the help is offered free. Glad you got your problem sorted out, and I'm sure that when you next have a problem you'll be giving us great information about it right from the start now you know what peculiar things we need to know :)