Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
| Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 2nd, 2004, 08:35 AM
Registered User
 
Join Date: Mar 2004
Location: leeds, yorks, United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default mySQL login query problem???

Hi,

I'm relatively new to PHP and i'm trying to create a login system.

In my login.php page, i have the following query...

// query database with username and encrypted password
$myquery = "SELECT * FROM users WHERE userName = '" . $user;
$myquery .= "' AND password = '" . crypt($password, "DWMXPHP") . "'";
$result = mysql_query($myquery);
if (!$result)
  {
    $error = "Cannot run query";
    return($error);
  }

However, whenever i try to login from the page, it never seems to find the username and password from the database.

I know that the problem isn't to do with the encryption key so i'm presuming the code may be wrong somewhere.

Any ideas guys?

Thanks in advance.

Nick
 
Old March 2nd, 2004, 08:47 AM
Registered User
 
Join Date: Mar 2004
Location: leeds, yorks, United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've checked all the variables, they are all correct.

The query seems to be executed OK but just not returning any results?

 
Old March 2nd, 2004, 04:32 PM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 6 Times in 6 Posts
Default

Code:
<?php
session_start();

if (1 == mysql_num_rows($result = mysql_query("SELECT * FROM users WHERE userName = '$user' AND password = password('$password')", $link)))
{
    $_SESSION['logged_in'] = true;
}
else
{
    $_SESSION['logged_in'] = false;
   
    if (empty($result))
    {
        echo mysql_error();
    }
}
?>
We've already had quite a bit of talk on authentication on p2p lately. A breif look at recent posts would reveal more threads on this topic.

That being said a few comments...
1.) I recommend that you use sessions. (You haven't indicated how you will persist the login -- so I'll assume that is probably your next question).
2.) You don't specify the mysql resource handler in the second argument for mysql_query. *Sometimes* this can cause problems. This is the variable that you create when you call mysql_connect().
3.) You aren't reporting any database errors... use mysql_error() or mysql_errno() to find out what's going awry on the database side of things.
4.) Although its really a matter of personal preference, I would suggest using MySQL's built in password hashing method. (To check a password with this method, it must have been stored with that method as well.)

See:
http://p2p.wrox.com/topic.asp?TOPIC_ID=10392

Or better yet:
http://www.google.com/search?q=PHP+s...e:p2p.wrox.com

Or:
http://www.google.com/search?q=PHP+a...e:p2p.wrox.com

hth,
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::
 
Old March 3rd, 2004, 08:15 AM
Registered User
 
Join Date: Mar 2004
Location: leeds, yorks, United Kingdom.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm already using sessions. The page loads fine and the query seems to execute, it's just that whenever i try to enter the login details, it doesn't retrive data from the database. Even though i know i'm entering correct values and that the data is stored??

This is my login.php code.

<?php include("../Connections/dreamweaver_hotel_admin.php"); ?>
<?php function check_login($formdata)
{

    // set up mysql connection
    $error = "";
    $hostname_connHotel = "localhost";
    $database_connHotel = "dwhotel";
    $username_connHotel = "root";
    $password_connHotel = "fabric";

    // Get form data
    $form_data = trim_data($formdata);
    $user = $form_data['userName'];
    $password = $form_data['password'];

    // connect to mysql server
    $mysql = mysql_connect($hostname_connHotel, $username_connHotel, $password_connHotel);
    if (!mysql)
    {
        $error = "Cannot connect to mysql server";
        return($error);
    }

    // connect to database
    $mysqldb = mysql_select_db($database_connHotel);
    if (!mysqldb)
    {
        $error = "Cannot open database";
        return($error);
    }

    // query database with username and encrypted password
    $myquery = "SELECT * FROM users WHERE userName = '" . $user . "' AND password = '" . $password . "'";
    $result = mysql_query($myquery);
    if (!$result)
    {
        $error = "Cannot run query";
        return($error);
    }

    // check that we have a record returned
    $numRows = mysql_num_rows($result);
    if ($numRows < 1)
    {
        $error = "User name or password not recognised";
        return($error);
    }

    // get user status from returned record
    $userRecord = mysql_fetch_array($result);
    $status = $userRecord["status"];
    return($status);
}
?>
<?php
    //this section is only run when the form has been submitted
    if($HTTP_POST_VARS['Submit']=="Login")
    {
        session_start();

    // check whether the login details are correct, and put
    // the user status into a session variable.
    $statusCheck = check_login($HTTP_POST_VARS);
    if ($statusCheck == "Admin" || $statusCheck == "Staff")
        {
        session_register("statusCheck");
        header("Location: menu.php");
        }
    }
?>
<?php echo($statusCheck) ?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<div align="center">
  <p><strong>Administration Interface</strong></p>
  <p>Enter your User name and password below to login</p>
  <form name="form1" method="post" action="<?php echo ($PHP_SELF) ?>">
    <table width="50%" border="0" cellpadding="0">
      <tr>
        <td><div align="right">User Name:</div></td>
        <td><input type="text" id="userName" "name="userName"></td>
      </tr>
      <tr>
        <td><div align="right">Password:</div></td>
        <td><input type="password" id="password" name="password"></td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        <td><input type="submit" name="Submit" value="Login"></td>
      </tr>
    </table>
  </form>
  <p></p>
</div>
</body>
</html>

Can anyone see anything wrong with the code maybe there's something wrong with my php/apache config files??

Thanks,

Nick

 
Old March 3rd, 2004, 05:35 PM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 6 Times in 6 Posts
Default

Hi Nick,
    First a few comments on your programming style... Everything looks good from a logic standpoint. You're missing a few dollar signs. You should be seeing errors because of these. What level is error_reporting set to in php.ini?

Some suggestions:

1.) As I mentioned previously you aren't reporting database errors. Use mysql_error() to retrieve the error text, if any, from mysql. PHP won't throw an error if nothing's wrong syntax-wise. This will likely tell you what exactly is wrong with your sql. If there is no error text to be had, then something isn't right with the data itself.

2.) If you can avoid creating a variable then don't create one. Memory has to be allocated for each new variable you create.
3.) As I mentioned in my last post (but didn't explain why) don't use session_register(). Are you using PHP < 4.1.0? If you are and can do nothing about it then this is O.K. otherwise use the $_SESSION superglobal. Superglobals are like any other variables but exist for certain classifications of data and may contain preset values depending on which category is being used. Superglobals are not subject to normal variable scoping rules, which means you can use them inside of functions or classes without passing as an argument.

Use the $_POST superglobal instead of $HTTP_POST_VARS.. the latter is deprecated and future versions of PHP may not support it. A $_xxxx variable exists for every $HTTP_xxx_VARS.

SEE:
http://www.php.net/manual/en/languag...predefined.php

And Nik's explaination of why session_register is bad:
http://p2p.wrox.com/topic.asp?TOPIC_ID=2052

4.) Assuming that you are going to use a database for more than just the login, make only a single database connection per script execution whenever possible. Building a database connection is resource intensive so its best to do it only once at the beginning of a script and close it at the end of a script.

So all that being said the following is how I would suggest structuring your code.

<?php include('../Connections/dreamweaver_hotel_admin.php'); ?>
<?php

function db_connect()
{
    // connect to mysql server
    // In your original code you left off the dollar sign in your check
    if (!$GLOBALS['mysql'] = mysql_connect('localhost', 'root', 'fabric')
    {
        return FALSE;
    }

    // connect to database
    // Again, you left off a dollar sign.
    // But there's no need to create a variable there anyway.
    if (!mysql_select_db('dwhotel'))
    {
        return FALSE;
    }

    return TRUE;
}

function check_login()
{
    // Get form data
    // How was the password stored in the DB?
    // If you used encryption to store it then you must use encryption to retrieve it.
    if (!$result = mysql_query("SELECT * FROM users WHERE userName = '{$_POST['userName']}' AND password = '{$_POST['password']}'", $GLOBALS['mysql']))
    {
        // Obviously you don't want to show errors to your users
        // but when you're developing you need to know what's up.

        echo mysql_errno().': ';
        echo mysql_error()."<br />\n";

        return 'Cannot run query';
    }

    // check that we have a record returned
    if (mysql_num_rows($result) < 1)
    {
        return 'User name or password not recognized';
    }

    // get user status from returned record
    $userRecord = mysql_fetch_array($result);
    return $userRecord['status'];
}
?>
<?php

    // Initiate a database connection.
    if (!db_connect())
    {
        echo 'Unable to initiate a connection to the database.<br />';
        echo 'MySQL said: '.mysql_error().'<br />';
    }

    //this section is only run when the form has been submitted
    if (isset($_POST['Submit']) && $_POST['Submit'] == 'Login')
    {
        session_start();

        // check whether the login details are correct, and put
        // the user status into a session variable.

        $statusCheck = check_login();

        if ($statusCheck == 'Admin' || $statusCheck == 'Staff')
        {
            $_SESSION['statusCheck'] = $statusCheck;
            header('Location: menu.php');
        }
        else
        {
            echo $statusCheck;
        }
    }
?>

// Snip Snip

I'm sure that's a lot to digest.. but should be very straight forward. If you run mysql error reporting and still are not getting results then we'll talk more on troubleshooting.

hth,
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::




Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Query Problem w/ PHP rdmapes PHP How-To 5 August 14th, 2010 12:55 AM
login screen in struts and mysql database mill3prog7@yahoo.co.in Struts 1 May 4th, 2006 09:07 AM
accidentally deleted my MySQL login Tachyon PHP Databases 2 May 21st, 2004 09:57 AM
MySQL query desc order problem dssachdeva MySQL 3 September 18th, 2003 01:11 AM
MySQL Query Problem cmiller Beginning PHP 4 August 26th, 2003 12:43 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.