[SOLVED] Help posting data to table using id from 2nd table

[sanderphp]

I am working on my first db that has 2 tables. 

 

Table 1 has 'id' and 'rider'.

Table 2 has 'id', 'ave_speed', 'total_time', 'total_distance', 'rider_id'

 

I'm using php to have the riders login to the page. I was thinking that based on the login id, I could use that to post data to table 2.

 

I'm confused how to join the tables together and post data.  I need 'rider_id' to equal the 'id' from table 1.

 

My code so far is below. Be easy on me, this is my biggest project so far.

 

 

 

<?php

$host="localhost"; // Host name 
$username="sanderan_admin"; // Mysql username 
$password="password"; // Mysql password 
$db_name="sanderan_cycling"; // Database name 
$tbl_name="riders"; // Table name 

// Connect to server and select databse.
$conn = mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");


// Open database connection
// $conn = mysql_connect('localhost', 'sanderan_usernam', 'password') or die("Unable to connect, the reason is: " . mysql_error());
// mysql_select_db('sanderan_test') or die("Could not select database, the reason is: " . mysql_error());

// Pick up the form data and assign it to variables (if it was submitted)
if ($_POST['Status'] == '1') {
$date = $_POST['date'];
$total_distance = $_POST['total_distance'];
$total_time = $_POST['total_time'];
$ave_speed = $_POST['ave_speed'];

// Insert data
$query = "INSERT INTO info (`date`, `total_distance`, `totaltime`, `ave_speed`,)
  VALUES ('$date', '$total_time', '$total_distance', '$ave_speed')";
mysql_query($query) or die(mysql_error());


// Close connection
mysql_close($conn);

// Redirect
header("Location: success.html");
}

?>

[sanderphp]

I'm attempting to clarify what I need help with.  I don't know how to read data from one table and post it in another.  Like I said, I have 2 tables. I need the ID field from the first table to populate the 'rider_id' in the 2nd table so I can sum up how many miles are ridden.    I'm not sure how to explain this and apparently I'm not doing a very good job.  The code example that I posted shows what I know how to do. I don't have any references to ID because I don't know how to do that, hence the reason for my post. 

[Barand]

When the user logged in, where did you store the login id?

[sanderphp]

I used the tutorial that I found http://www.phpeasystep.com/workshopview.php?id=6

 

<?php
ob_start();
$host="localhost"; // Host name 
$username="sanderan_admin"; // Mysql username 
$password="password"; // Mysql password 
$db_name="sanderan_cycling"; // Database name 
$tbl_name="riders"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// Define $myusername and $mypassword 
$myusername=$_POST['login']; 
$mypassword=$_POST['password']; 

// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM $tbl_name WHERE login='$myusername' and password='$mypassword'";
print "$sql<br>";
$result=mysql_query($sql);
print mysql_error()."<br>";

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
session_register("myusername");
session_register("mypassword"); 
header("location:login_success.php");
}
else {
echo "Wrong Username or Password";
}

ob_end_flush();
?>

 

 

<?
// Check if session is not registered , redirect back to main page. 
// Put this code in first line of web page. 

session_start();
if(!session_is_registered(myusername)){
header("location:main_login.php");
}

?>

[Barand]

Is the user who logged in the same person as the rider for whom you are entering data?

 

In other words, is the rider_id the same as the id of the logged in user?

[sanderphp]

correct.

[Barand]

The store the user id in the session variables and write that to the second table.

 

BTW, session_register() is deprecated (old tutorial). You should be using the global $_SESSION array.

[sanderphp]

Thanks, I will try to find some information on how to store that.

 

 

[Barand]

<?php
session_start();                                                                          // at top of script

$sql="SELECT id FROM $tbl_name WHERE login='$myusername' and password='$mypassword'";     // get the id
$result=mysql_query($sql);
if (!$result) {
    print "$sql<br>";
    print mysql_error()."<br>";
}

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
$_SESSION['username'] = $myusername;
$_SESSION['password'] = $mypassword;
$_SESSION['login_id'] = mysql_result($result, 0);                                          // store the id

header("location:login_success.php");
}
else {
echo "Wrong Username or Password";
}
?>

[sanderphp]

I'm getting more and more confused.  I'm not sure where that code should have gone, but I added it to checklogin.php    When I try to login to my page with valid credentials, it just goes back the login page, if I use a bad password, it errors out and says bad password. Do I have a code problem in checklogin.php?

 

My checklogin.php code:

 

<?php
session_start(); 
$host="localhost"; // Host name 
$username="sanderan_admin"; // Mysql username 
$password="password"; // Mysql password 
$db_name="sanderan_cycling"; // Database name 
$tbl_name="riders"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// Define $myusername and $mypassword 
$myusername=$_POST['login']; 
$mypassword=$_POST['password']; 

// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT id FROM $tbl_name WHERE login='$myusername' and password='$mypassword'";     // get the id
$result=mysql_query($sql);
if (!$result) {
    print "$sql<br>";
    print mysql_error()."<br>";
}

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
$_SESSION['username'] = $myusername;
$_SESSION['password'] = $mypassword;
$_SESSION['login_id'] = mysql_result($result, 0);                                          // store the id

header("location:login_success.php");
}
else {
echo "Wrong Username or Password";
}
?>

 

 

[sanderphp]

I was able to fix the error in the above post. I needed to update my login_success.php code

 

I believe that I am now ready to tackle my original problem.  I need help with how to pass the id which I believe is being stored as a session variable to the 'rider_id'  I have question marks in the code below where I need some help.

 

<?php

$host="localhost"; // Host name 
$username="sanderan_admin"; // Mysql username 
$password="password"; // Mysql password 
$db_name="sanderan_cycling"; // Database name 
$tbl_name="riders"; // Table name 

// Connect to server and select databse.
$conn = mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");


// Open database connection
// $conn = mysql_connect('localhost', 'sanderan_usernam', 'password') or die("Unable to connect, the reason is: " . mysql_error());
// mysql_select_db('sanderan_test') or die("Could not select database, the reason is: " . mysql_error());

// Pick up the form data and assign it to variables (if it was submitted)
if ($_POST['Status'] == '1') {
$date = $_POST['date'];
$total_distance = $_POST['total_distance'];
$total_time = $_POST['total_time'];
$ave_speed = $_POST['ave_speed'];
$rider_id= Session Variable?????

// Insert data
$query = "INSERT INTO info (`date`, `total_distance`, `totaltime`, `ave_speed`, `rider_id`)
  VALUES ('$date', '$total_time', '$total_distance', '$ave_speed', 'rider_id')";
mysql_query($query) or die(mysql_error());


// Close connection
mysql_close($conn);

// Redirect
header("Location: submit_success.php");
}

?>

[Barand]

$rider_id= Session Variable?????

 

$rider_id = $_SESSION['login_id'];

 

In the query values you need '$rider_id'

[sanderphp]

I added the $rider_id into the sql query and added it to the variables but still not getting any error or data written to the table.  I also checked my error log and it did not report any errors.

 

<?php

$host="localhost"; // Host name 
$username="sanderan_admin"; // Mysql username 
$password="password"; // Mysql password 
$db_name="sanderan_cycling"; // Database name 
$tbl_name="riders"; // Table name 

// Connect to server and select databse.
$conn = mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");


// Open database connection
// $conn = mysql_connect('localhost', 'sanderan_usernam', 'password') or die("Unable to connect, the reason is: " . mysql_error());
// mysql_select_db('sanderan_test') or die("Could not select database, the reason is: " . mysql_error());

// Pick up the form data and assign it to variables (if it was submitted)
if ($_POST['Status'] == '1') {
$date = $_POST['date'];
$total_distance = $_POST['total_distance'];
$total_time = $_POST['total_time'];
$ave_speed = $_POST['ave_speed'];
$rider_id = $_SESSION['login_id']

// Insert data
$query = "INSERT INTO info (`date`, `total_time`, `total_distance`, `ave_speed`, `rider_id`)
  VALUES ('$date', '$total_time', '$total_distance', '$ave_speed', '$rider_id')";
mysql_query($query) or die(mysql_error());


// Close connection
mysql_close($conn);

// Redirect
header("Location: submit_success.php");
}

?>

[sanderphp]

Anyone able to find any errors in the code I posted above?

[Barand]

Check the input

 

echo '<pre>', print_r($_POST, true), '</pre>';

[sanderphp]

Adding that didn't display any results, no errors either.  I commented out the redirect at the end thinking this should just display the echo results.

 

// Pick up the form data and assign it to variables (if it was submitted)
if ($_POST['Status'] == '1') {
$date = $_POST['date'];
$total_distance = $_POST['total_distance'];
$total_time = $_POST['total_time'];
$ave_speed = $_POST['ave_speed'];
$rider_id = $_SESSION['login_id'];

// Insert data
$query = "INSERT INTO info (`date`, `total_time`, `total_distance`, `ave_speed`, `rider_id`)
  VALUES ('$date', '$total_time', '$total_distance', '$ave_speed', '$rider_id')";
mysql_query($query) or die(mysql_error());

echo '<pre>', print_r($_POST, true), '</pre>';

// Close connection
mysql_close($conn);

// Redirect
//header("Location: submit_success.php");
}

[sanderphp]

I have the echo working now and it's writing to the table, but it's not adding the rider_id.

 

The echo shows me this and it's missing the rider_id

 

Array

(

    [status] => 1

    [date] => 5/1/08

    [total_distance] => 22

    [total_time] => 75

    [Ave_Speed] => 17

)

 

 

// Pick up the form data and assign it to variables (if it was submitted)
if ($_POST['Status'] == '1') {
$date = $_POST['date'];
$total_distance = $_POST['total_distance'];
$total_time = $_POST['total_time'];
$ave_speed = $_POST['ave_speed'];
$rider_id = $_SESSION['login'];


// Insert data
$query = "INSERT INTO info (`date`, `total_time`, `total_distance`,`ave_speed`,`rider_id`)
  VALUES ('$date', '$total_time', '$total_distance', '$ave_speed', '$rider_id')";
mysql_query($query) or die(mysql_error());


echo '<pre>', print_r($_POST, true), '</pre>';

// Close connection
mysql_close($conn);

// Redirect
//header("Location: submit_success.php");
}

 

[Barand]

Do you have session_start() at the top of the script. You need it to access session variables.

[sanderphp]

I have it at the top of the login_success.php script where the information is entered.  If I add it to the add_data.php script I get an error stating it is expecting t variables.

 

Parse error: syntax error, unexpected T_VARIABLE in /home/sanderan/public_html/php/cycling/add_data.php on line 4

 

 

 

[Barand]

Then you have something wrong somewhere in the first 4 lines

[sanderphp]

 

 

<?php

session_start()

 

$host="localhost"; // Host name

$username="sanderan_admin"; // Mysql username

$password="password"; // Mysql password

$db_name="sanderan_cycling"; // Database name

$tbl_name="riders"; // Table name

[sanderphp]

<?php
session_start() 

$host="localhost"; // Host name 
$username="sanderan_admin"; // Mysql username 
$password="password"; // Mysql password 
$db_name="sanderan_cycling"; // Database name 
$tbl_name="riders"; // Table name 

[Barand]

Missing ; at end of first line

[sanderphp]

yikes, at this rate you'll be at 14,000 posts soon. Thanks for sticking with me.  I added the ; at the end of the line but the rider_id still isn't displaying in the echo.

 

Since I don't know any better, I am closing and opening the mysql connection on each script. Would that have any effect on storing a session variable?

[Barand]

OK Another debugging exercise. This time we need to make sure the session vars are as expected

 

echo '<pre>', print_r($_SESSION, true), '</pre>';