Jump to content


Photo

I need some help PHP and MySQL


  • Please log in to reply
19 replies to this topic

#1 Boerboel649

Boerboel649
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 17 February 2006 - 07:59 PM

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!

#2 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 17 February 2006 - 08:56 PM

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());
   }
}

Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 18 February 2006 - 11:18 AM

No need to SELECT, Add, UPDATE. All you need is a single update.
   $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());
   }

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 Boerboel649

Boerboel649
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 19 February 2006 - 02:20 PM

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!


#5 Boerboel649

Boerboel649
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 19 February 2006 - 06:13 PM

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.

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 19 February 2006 - 07:23 PM

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

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 Boerboel649

Boerboel649
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 20 February 2006 - 01:10 AM

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:
<?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>
And then here's the code for the file addhours.php
<?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>
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.

#8 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 20 February 2006 - 01:35 AM

<?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.";
}
?>


Legend has it that reading the manual never killed anyone.
My site

#9 Boerboel649

Boerboel649
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 20 February 2006 - 04:23 PM

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 :)

#10 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 20 February 2006 - 04:41 PM

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:

<?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.";
}
?>

Legend has it that reading the manual never killed anyone.
My site

#11 Boerboel649

Boerboel649
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 20 February 2006 - 04:51 PM

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)

#12 Boerboel649

Boerboel649
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 22 February 2006 - 01:59 AM

Here's my curent code for addhours.php
<?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.";
}
?>


#13 Boerboel649

Boerboel649
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 24 February 2006 - 01:54 AM

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!

#14 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 24 February 2006 - 02:12 AM

Try this:

<?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.";
}
?>

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

#15 Boerboel649

Boerboel649
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 24 February 2006 - 02:51 AM

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.

#16 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 24 February 2006 - 02:56 AM

[!--quoteo(post=348907:date=Feb 23 2006, 09:51 PM:name=Boerboel649)--][div class=\'quotetop\']QUOTE(Boerboel649 @ Feb 23 2006, 09:51 PM) View Post[/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.

#17 Boerboel649

Boerboel649
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 24 February 2006 - 03:06 AM

[!--quoteo(post=348910:date=Feb 23 2006, 09:56 PM:name=hitman6003)--][div class=\'quotetop\']QUOTE(hitman6003 @ Feb 23 2006, 09:56 PM) View Post[/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!!!!!!!!!!!

#18 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 February 2006 - 08:03 AM

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

There's a strong possibility!

if the pwd in db is encrypted then you need to encrypt posted passwprd also to get a match.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#19 Boerboel649

Boerboel649
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 26 February 2006 - 10:13 PM

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:

<?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.";
}
?>


#20 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 27 February 2006 - 02:23 AM

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 :)
Legend has it that reading the manual never killed anyone.
My site




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users