Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Dec 2008
    Posts
    95
    Thanks
    7
    Thanked 0 Times in 0 Posts

    just want to relate two tables

    Hello everyone, I have two tables I've created using phpmyadmin: 'CustomerInfo' and 'Login'. For the CustomerInfo table, I have the field cust_ID set as the primary key (bigint, unsigned, auto_increment). For my Login table, I want to also have the cust_ID field that stores the correct cust_id from the CustomerInfo table. However, when I add the cust_ID field to the Login table, it just records zero in that column for every entry. How can I relate the two tables using the cust_ID field?

  • #2
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    something like this will do it;

    Code:
    SELECT * FROM CustomerInfo c, Login l WHERE c.cust_ID = l.cust_ID

  • #3
    New Coder
    Join Date
    Dec 2008
    Posts
    95
    Thanks
    7
    Thanked 0 Times in 0 Posts
    But let's say someone I have stored in my CustomerInfo table logs into there account and their login date/time is recorded in the Login table. How can I have it store the correct cust_ID for this user from the CustomerInfo table in the Login table when the user logs in?

  • #4
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    well, i assume that when someone logs in you are opening the db to compare login details and looking for a match to allow access. so why not just grab the account id at that time, then run another query to insert the date/time/cust_ID ?

  • #5
    New Coder
    Join Date
    Dec 2008
    Posts
    95
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Yes, that makes sense. Thanks, I'll try that.

  • #6
    New Coder
    Join Date
    Dec 2008
    Posts
    95
    Thanks
    7
    Thanked 0 Times in 0 Posts
    how do I "grab the account id"? I am confused how to select a field from my row of results and then insert it into a table.

  • #7
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    example:

    Code:
    $result = mysql_query("SELECT * FROM CustomerInfo WHERE etc...");
    $row = mysql_fetch_assoc($result);
    
    mysql_query("INSERT INTO Login SET cust_ID = " . $row['cust_ID'] . "");

  • #8
    New Coder
    Join Date
    Dec 2008
    Posts
    95
    Thanks
    7
    Thanked 0 Times in 0 Posts
    awesome, now it's storing the correct cust_ID. However, now it's just storing 00-00-0000 for the date. Here is the code, any suggestions?

    PHP Code:
                if($count==1)
                {
                
    // Register $myusername, $mypassword and redirect to file "login_success.php"
                
    echo "<p>SUCCESS!</p>";
                
    $cxn mysqli_connect($host,$user,$passwd,$dbname)
                               or die(
    "Couldn't connect to server");
                
    $clean_data[$field] = strip_tags(trim($value));
                foreach(
    $clean_data as $field => $value)
                {
                    
    $clean_data[$field] = mysqli_real_escape_string($cxn,$value);
                }
                
    $sql2 "INSERT INTO Login (date_time) VALUES (CURDATE())";
                
    $result2 mysqli_query($cxn,$sql2
                              or die(
    mysqli_error($cxn));
                
    $row mysqli_fetch_assoc($result);
                
    $sql3 "INSERT INTO Login SET cust_ID = " $row['cust_ID'] . "";
                
    $result3 mysqli_query($cxn,$sql3
                              or die(
    mysqli_error($cxn));
                }
                else {
                
    $message "<p style='color: red; margin-bottom: 0; font-weight: bold'>The password or username you entered was incorrect.                            </p>";            
                echo 
    $message;
                include(
    "form_Login.inc");
                exit();
                } 

  • #9
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    try this;
    Code:
                if($count==1) 
                { 
                // Register $myusername, $mypassword and redirect to file "login_success.php" 
                echo "<p>SUCCESS!</p>"; 
                $cxn = mysqli_connect($host,$user,$passwd,$dbname) 
                               or die("Couldn't connect to server"); 
                $clean_data[$field] = strip_tags(trim($value)); 
                foreach($clean_data as $field => $value) 
                { 
                    $clean_data[$field] = mysqli_real_escape_string($cxn,$value); 
                } 
                $row = mysqli_fetch_assoc($result); 
                $sql2 = "INSERT INTO Login (date_time, cust_ID) VALUES (CURDATE(), " . $row['cust_ID'] . ")"; 
                $result2 = mysqli_query($cxn,$sql2)  
                              or die(mysqli_error($cxn)); 
    
                else { 
                $message = "<p style='color: red; margin-bottom: 0; font-weight: bold'>The password or username you entered was incorrect.                            </p>";             
                echo $message; 
                include("form_Login.inc"); 
                exit(); 
                }

  • #10
    New Coder
    Join Date
    Dec 2008
    Posts
    95
    Thanks
    7
    Thanked 0 Times in 0 Posts
    That works! Thanks so much. I'm just learning php and mysql and it's very encouraging to have people like you who are willing to help when a noob gets stuck.

  • #11
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    no prob, we were all n00bs at some point


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •