insert into sql

[lmcgr44]

hello

i am new to all this php and mysql, my question is, i have a log in area, when a person logs in it takes them to there account home page, they then go into add results to add information about there fish tank, once added it gets stored in mysql, then on the page results it then gets those fields added in mysql and displays them, mu problem is when the person adds the form the the mysql i want his user_id saved with it, so when he goes to retrieve it, he will only see what he has added not what everyone has added, what would i do to make this happen?

thank-you

[lmcgr44]

bradgrafelman;11002484 wrote:

Add a "user_id" column to the table where the data is stored, and insert the user's ID into that column when inserting the rest of the data.

thanks, i have already added the column in mysql I'm just having trouble with the script posting the logged in user id, here is my posting so far

<?php
$id = $_SESSION['user_id'];
$con = mysql_connect("localhost","lachlanmcgrath","********");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("lachlanmcgrath", $con);

$sql="INSERT INTO tracker (user_id, Date, Time, Amonia, Nitrite, Nitrate, PH, Salinity, Tempreture)
VALUES
('$_POST[user_id]','$_POST[Date]','$_POST[Time]','$_POST[Amonia]','$_POST[Nitrite]','$_POST[Nitrate]','$_POST[PH]','$_POST[Salinity]','$_POST[Tempreture]')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";

mysql_close($con);
?>

[lmcgr44]

bradgrafelman;11002488 wrote:

Few things:

1. Welcome to PHPBuilder! (A belated welcome - didn't notice you were new 'round here.) When posting PHP code, please use the board's [noparse]

   ..

   [/noparse] bbcode tags as they make your code much easier to read and analyze.

2. Note that user-supplied data should never be placed directly into a SQL query string, else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. Instead, you must first sanitize the data such as with a function like [man]mysql_real_escape_string/man (for string data) or by using prepared statements.

3. Do you have error_reporting set to E_ALL (or better) and display_errors set to On? Doing this will allow you to detect whether any of those $_POST variables are empty.

   Another easy way to check this would be to output the SQL query so that you can visually examine it. Can you do this and show us the query?

4. Why are you trying to get the user_id value from the $_POST array? If you're including the user_id in the HTML form, note that this is a very insecure method since the form contents could easily be altered to make it appear as if it came from a different user.

sorry i really do not understand much, like i said I'm very new to all this! its a bit confusing, also I'm not letting the user add in there own user_id value I'm trying to get in from there login session! i can't find anywhere, where i can post the logged in users user_id

[lmcgr44]

bradgrafelman;11002491 wrote:

Well the fact that you're trying to access an element named 'user_id' of the $_POST array suggested that you're expecting the user ID value to come form the POST'ed form data. If that's not where it's supposed to come from, then... well, where is that data stored?

By "login session," do you mean you're using a PHP [man]session[/man] (e.g. via the use of [man]session_start/man)? If not, then how do you keep track of this "session" from one page request to another?

i have include 'dbc.php'; onto of the loved in pages witch then in that php is the start session

[lmcgr44]

bradgrafelman;11002494 wrote:

So I take it that means you are using a PHP session when the user logs in? If so, what does your "login" code/script look like? Do you store the user's ID value in the $_SESSION array at any point?

this is my login code

<?php 
/*************** PHP LOGIN SCRIPT V 2.3*********************
(c) Balakrishnan 2009. All Rights Reserved

Usage: This script can be used FREE of charge for any commercial or personal projects. Enjoy!

Limitations:
- This script cannot be sold.
- This script should have copyright notice intact. Dont remove it please...
- This script may not be provided for download except from its original site.

For further usage, please contact me.

***********************************************************/
include 'dbc.php';

$err = array();

foreach($_GET as $key => $value) {
	$get[$key] = filter($value); //get variables are filtered.
}

if ($_POST['doLogin']=='Login')
{

foreach($_POST as $key => $value) {
	$data[$key] = filter($value); // post variables are filtered
}


$user_email = $data['usr_email'];
$pass = $data['pwd'];


if (strpos($user_email,'@') === false) {
    $user_cond = "user_name='$user_email'";
} else {
      $user_cond = "user_email='$user_email'";

}


$result = mysql_query("SELECT `id`,`pwd`,`full_name`,`approved`,`user_level` FROM users WHERE 
           $user_cond
			AND `banned` = '0'
			") or die (mysql_error()); 
$num = mysql_num_rows($result);

  // Match row found with more than 1 results  - the user is authenticated. 
    if ( $num > 0 ) { 

list($id,$pwd,$full_name,$approved,$user_level) = mysql_fetch_row($result);

if(!$approved) {
//$msg = urlencode("Account not activated. Please check your email for activation code");
$err[] = "Account not activated. Please check your email for activation code";

//header("Location: login.php?msg=$msg");
 //exit();
 }

	//check against salt
if ($pwd === PwdHash($pass,substr($pwd,0,9))) { 
if(empty($err)){			

 // this sets session and logs user in  
   session_start();
   session_regenerate_id (true); //prevent against session fixation attacks.

   // this sets variables in the session 
	$_SESSION['user_id']= $id;  
	$_SESSION['user_name'] = $full_name;
	$_SESSION['user_level'] = $user_level;
	$_SESSION['HTTP_USER_AGENT'] = md5($_SERVER['HTTP_USER_AGENT']);

	//update the timestamp and key for cookie
	$stamp = time();
	$ckey = GenKey();
	mysql_query("update users set `ctime`='$stamp', `ckey` = '$ckey' where id='$id'") or die(mysql_error());

	//set a cookie 

   if(isset($_POST['remember'])){
			  setcookie("user_id", $_SESSION['user_id'], time()+60*60*24*COOKIE_TIME_OUT, "/");
			  setcookie("user_key", sha1($ckey), time()+60*60*24*COOKIE_TIME_OUT, "/");
			  setcookie("user_name",$_SESSION['user_name'], time()+60*60*24*COOKIE_TIME_OUT, "/");
			   }
	  header("Location: myaccount.php");
	 }
	}
	else
	{
	//$msg = urlencode("Invalid Login. Please try again with correct user email and password. ");
	$err[] = "Invalid Login. Please try again with correct user email and password.";
	//header("Location: login.php?msg=$msg");
	}
} else {
	$err[] = "Error - Invalid login. No such user exists";
  }		
}



?>
<html>
<head>
<title>Members Login</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript" type="text/javascript" src="js/jquery-1.3.2.min.js"></script>
<script language="JavaScript" type="text/javascript" src="js/jquery.validate.js"></script>
  <script>
  $(document).ready(function(){
    $("#logForm").validate();
  });
  </script>
<link href="styles.css" rel="stylesheet" type="text/css">

</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="5" class="main">
  <tr> 
    <td colspan="3"><center><img src="images/Untitled-1.png" width="390" height="153" alt="logo"></center></td>
  </tr>
  <tr> 
    <td width="160" valign="top"><p>&nbsp;</p>
      <p>&nbsp; </p>
      <p>&nbsp;</p>
      <p>&nbsp;</p>
      <p>&nbsp;</p></td>
    <td width="732" valign="top"><p>&nbsp;</p>
      <h3 class="titlehdr">Login Users 
      </h3>  

	  <p>
	  <?php
	  /******************** ERROR MESSAGES*************************************************
	  This code is to show error messages 
	  **************************************************************************/
	  if(!empty($err))  {
	   echo "<div class=\"msg\">";
	  foreach ($err as $e) {
	    echo "$e <br>";
	    }
	  echo "</div>";	
	   }
	  /******************************* END ********************************/	  

	  ?></p>
      <center><form action="login.php" method="post" name="logForm" id="logForm" >
        <table width="54%" border="0" cellpadding="4" cellspacing="4" class="loginform">
          <tr> 
            <td colspan="2">&nbsp;</td>
          </tr>
          <tr> 
            <td width="28%">Username / Email</td>
            <td width="72%"><input name="usr_email" type="text" class="required" id="txtbox" size="25"></td>
          </tr>
          <tr> 
            <td>Password</td>
            <td><input name="pwd" type="password" class="required password" id="txtbox" size="25"></td>
          </tr>
          <tr> 
            <td colspan="2"><div align="center">
                <input name="remember" type="checkbox" id="remember" value="1">
                Remember me</div></td>
          </tr>
          <tr> 
            <td colspan="2"> <div align="center"> 
                <p> 
                  <input name="doLogin" type="submit" id="doLogin3" value="Login">
                </p>
                <p><a href="register.php">Register Free</a><font color="#FF6600"> 
                  |</font> <a href="forgot.php">Forgot Password</a> <font color="#FF6600"> 
                  </font></p>
                <p><span style="font: normal 9px verdana">Powered by <a href="http://php-login-script.com">PHP 
                  Login Script v2.3</a></span></p>
              </div></td>
          </tr>
        </table>
        <div align="center"></div>
        <p align="center">&nbsp; </p>
      </form>
      </center>
      <p>&nbsp;</p>

  </td>
<td width="196" valign="top">&nbsp;</td>
  </tr>
  <tr> 
    <td colspan="3">&nbsp;</td>
  </tr>
</table>

</body>
</html>

[lmcgr44]

bradgrafelman;11002496 wrote:

So there you have it:

       // this sets variables in the session 
        $_SESSION['user_id']= $id; 

Thus, if you're calling [man]session_start/man in your original script above, then you should have access to that session variable using the $_SESSION array.

ok sorry to be so slow with this matter! so how do i now get that into the user_id field in mysql when a user submits a form?

[lmcgr44]

bradgrafelman;11002496 wrote:

So there you have it:

       // this sets variables in the session 
        $_SESSION['user_id']= $id; 

Thus, if you're calling [man]session_start/man in your original script above, then you should have access to that session variable using the $_SESSION array.

so is doing this any closer to doing what i want to do?

<?php
include 'dbc.php';

$con = mysql_connect("localhost","lachlanmcgrath","simon123");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("lachlanmcgrath", $con);

$sql="INSERT INTO tracker (user_id, Date, Time, Amonia, Nitrite, Nitrate, PH, Salinity, Tempreture)
VALUES
('$_POST[user_id]','$_POST[Date]','$_POST[Time]','$_POST[Amonia]','$_POST[Nitrite]','$_POST[Nitrate]','$_POST[PH]','$_POST[Salinity]','$_POST[Tempreture]')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";

mysql_close($con);
?>

[spufi]

You still don't validate the fields being set or not. You can add in the else statements in case you want something specific to go into the data if the field isn't set. More importantly, you don't sanitize your input. This can lead to SQL injections, which can lead to exposed data and data that can be tampered with.

// DB connection
$con = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass); 

// Validate input fields
if (isset($_SESSON['user_id']))
    $user_id = $_SESSON['user_id'];

if (isset($_POST['Date']))
    $Date = $_POST['Date'];

if (isset($_POST['Time']))
    $Time = $_POST['Time'];

if (isset($_POST['Amonia']))
    $Amonia = $_POST['Amonia'];

if (isset($_POST['Nitrite']))
    $Nitrite = $_POST['Nitrite'];

if (isset($_POST['Nitrate']))
    $Nitrate = $_POST['Nitrate'];

if (isset($_POST['PH']))
    $PH = $_POST['PH'];

if (isset($_POST['Salinity']))
    $Salinity = $_POST['Salinity'];

// Assumed misspelled and fixed
if (isset($_POST['Temperature']))
    $Temperature = $_POST['Temperature'];

// Attempts DB insert
try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "
        INSERT INTO post (user_id, Date, Time, Amonia, Nitrite, Nitrate, PH, , Salinity, Temperature)
        VALUE (:user_id, :Date, :Time, :Amonia, :Nitrite, :Nitrate, :PH, :Salinity, :Temperature)
        ";
    // Makes data nice for the DB.
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(':user_id', $user_id); 
    $stmt->bindParam(':Date', $Date); 
    $stmt->bindParam(':Time', $Time);
    $stmt->bindParam(':Amonia', $Amonia);
    $stmt->bindParam(':Nitrite', $Nitrite);
    $stmt->bindParam(':Nitrate', $Nitrate);
    $stmt->bindParam(':PH', $PH);
    $stmt->bindParam(':Salinity', $Salinity);
    $stmt->bindParam(':Temperature', $Temperature);
    $stmt->execute();
    echo "1 record added"; 
}
// Insert failed.
catch (PDOExecption $e) {
    echo $e->getMessage();
}

See more here.
http://bobby-tables.com/
http://www.php.net/manual/en/intro.pdo.php
http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html
http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/