table join question

[bschultz]

Hi.  I have a database for my shopping cart.  I need to authenticate a user based on the "customer" table and the "orders" table.

 

Here's what I have so far:

 

Login Page

<form name="form1" method="post" action="login.php">
  <div align="center">
    <p>Please login with your email adddress <br>
      <br>
      Email Address: 
      <input type="text" name="myemail">
      <br>
      <br>
      Password:
      <input type="text" name="password">
<br>
      <br>
      <input type="submit" name="Submit" value="Submit">
    </p>
  </div>
</form>

 

Here's the authentication script...

 

if (!$conn1) {
   echo "Unable to connect to DB: " . mysql_error();
   exit;
}

if (!mysql_select_db("cart")) {
   echo "Unable to select mydbname: " . mysql_error();
   exit;
}
$sql1 = "SELECT orders.customers_email_address, orders.date_purchased, orders.order_total customers.customers_password FROM orders WHERE customers_email_address = '$_POST[myemail]' AND order_total = '100.00' AND customers.customers_password = 'md5$_POST[password]'";

$result1 = mysql_query($sql1);

 

Any ideas what I'm doing wrong?  The error I'm getting is this:

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 '.customers_password FROM orders WHERE orders.customers_email_address = 'xxx@xxx.com' at line 1

 

 

Thanks!

[bschultz]

Alright, I was missing a comma, but it still gives me a false return on the password

 

$sql1 = "SELECT orders.customers_email_address, orders.date_purchased, orders.order_total, customers.customers_password FROM orders, customers WHERE orders.customers_email_address = '$_POST[myemail]' AND orders.order_total = '100.00'  AND customers.customers_password = 'md5$_POST[password]'";

 

Is this the right syntax for the password?  The password is encrypted in the database.

 

Thanks.

[js_280]

Are you getting any PHP errors? 

 

Try this if you have a foreign key set...

 

$sql1 = 'SELECT orders.customers_email_address, orders.date_purchased, orders.order_total, customers.customers_password FROM orders, customers WHERE orders.customers_email_address = "'.$_POST['myemail']'.'" AND orders.order_total = "100.00"  AND customers.customers_password = "'.md5($_POST['password']).'"';

 

Otherwise join the tables on the referenced key...

 

$sql1 = 'SELECT orders.customers_email_address, orders.date_purchased, orders.order_total, customers.customers_password FROM orders LEFT JOIN customers ON orders.customer_ID = customer.customer_ID WHERE orders.customers_email_address = "'.$_POST['myemail']'.'" AND orders.order_total = "100.00"  AND customers.customers_password = "'.md5($_POST['password']).'"';

 

The orders.customer_ID and customer.customer_ID will need to be whatever your referenced key is.

 

Also, is the orders.order_total = "100.00" valid?  Does the customer have an order_total field with 100.00 in it?  If not, that would cause the query to return without any results.

[bschultz]

Thanks for the help.  The problem appears to be in checking the password.  If I remove the password part (which is in the "customers" table) and just use the part of the query for the email address and order total (which are in the "order" table) everything works fine.

 

I tried both of your examples, and neither worked.  In fact, neither exited the query properly.

 

Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in login.php on line 14

 

As for the primary keys, on the orders table, it's orders_id...on the customer table, it's customers_id.  The field customers_id is on both tables, however.

 

Any new ideas?

 

Again, thanks for the help!

 

 

[bschultz]

OK...new wrinkle!  The password isn't md5.  It's encrypted in the cart, and I can't find out what method of encryption they're using.  The string is 35 characters long, ending in :xx where xx is 2 letters of numbers or one of each.  The shopping cart is a Zencart...any ideas what method they are using to encyrpt?  The field is varchar 40, and I've compared the hashed password in the db to an md5 and sha1 and neither matched!

 

Thanks.

[bschultz]

I did some digging, and it's md5+salt.  Here's the function used by the cart:

 

<?php
/**
* password_funcs functions 
*
* @package functions
* @copyright Copyright 2003-2005 Zen Cart Development Team
* @copyright Portions Copyright 2003 osCommerce
* @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
* @version $Id: password_funcs.php 2618 2005-12-20 00:35:47Z drbyte $
*/

////
// This function validates a plain text password with an encrpyted password
  function zen_validate_password($plain, $encrypted) {
    if (zen_not_null($plain) && zen_not_null($encrypted)) {
// split apart the hash / salt
      $stack = explode(':', $encrypted);

      if (sizeof($stack) != 2) return false;

      if (md5($stack[1] . $plain) == $stack[0]) {
        return true;
      }
    }

    return false;
  }

////
// This function makes a new password from a plaintext password. 
  function zen_encrypt_password($plain) {
    $password = '';

    for ($i=0; $i<10; $i++) {
      $password .= zen_rand();
    }

    $salt = substr(md5($password), 0, 2);

    $password = md5($salt . $plain) . ':' . $salt;

    return $password;
  }
?>

 

I'm not at all familiar with functions and if they're compatible across multiple directories.  Can someone help me implement this using my login page?

 

Thanks!