...

View Full Version : just want to relate two tables



alexmel7
09-10-2009, 08:26 PM
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?

angst
09-10-2009, 08:32 PM
something like this will do it;



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

alexmel7
09-10-2009, 08:37 PM
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?

angst
09-10-2009, 08:39 PM
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 ?

alexmel7
09-10-2009, 08:41 PM
Yes, that makes sense. Thanks, I'll try that.

alexmel7
09-10-2009, 08:55 PM
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.

angst
09-10-2009, 08:59 PM
example:




$result = mysql_query("SELECT * FROM CustomerInfo WHERE etc...");
$row = mysql_fetch_assoc($result);

mysql_query("INSERT INTO Login SET cust_ID = " . $row['cust_ID'] . "");

alexmel7
09-10-2009, 09:24 PM
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?


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

angst
09-10-2009, 09:28 PM
try this;


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

alexmel7
09-10-2009, 09:32 PM
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.

angst
09-10-2009, 09:33 PM
no prob, we were all n00bs at some point ;)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum