Page 1 of 1

select value form one table and insert into another - mysql

Posted: Sat Aug 21, 2010 2:16 pm
by gluttonous_pet
Hi guys, I have two tables, one is a user table, the other for blog entries. I basically want to be able to take the contents of the "id" from the user table of the logged in user and put it into the blog entry table along with the entry itself. I can log in fine and I can save the entry to the entry table fine, I just need to know how to take that id from the user table and put it in the entry table too. Any help or tips would be great, thanks!

Re: select value form one table and insert into another - my

Posted: Sat Aug 21, 2010 3:16 pm
by AbraCadaver
You haven't said how you know the user if you don't have the id, so I'm assuming you have the username in a variable as well as the blog entry. This might work (not tested)

[text]INSERT INTO `blog` (`userid`, `entry`) (SELECT `id` FROM `users` WHERE `username` = '$username'), '$entry'[/text]

Re: select value form one table and insert into another - my

Posted: Sat Aug 21, 2010 4:44 pm
by gluttonous_pet
Hmm can't seem to get it to work. Thanks for your help though, will try a few more things!

Re: select value form one table and insert into another - my

Posted: Sat Aug 21, 2010 5:49 pm
by Eran
The syntax abracadaver used is a bit off, it should be

Code: Select all

INSERT INTO `blog` (`userid`, `entry`) SELECT `id`,'$entry' FROM `users` WHERE `username` = '$username'
However, if you persist the user logged in state correctly, you should be able to retrieve it from the session instead of using this roundabout way

Re: select value form one table and insert into another - my

Posted: Sun Aug 22, 2010 5:12 am
by gluttonous_pet
Hmm OK thanks, I'd rather do it the right way I guess! I think it's a session problem as you said. If I can show you a bit of code for what I'm doing, I have tried passing the userid as a query string over from success.php to add-entry.php but no userid had been picked up to begin with on the checklogin.php page. If you'd be kind enough to point me in the right direction again that'd be fantastic, thank you!

checklogin.php

Code: Select all


/* database connection here */

$myusername=$_POST['myusername']; 
$mypassword=$_POST['mypassword'];

$sql="SELECT * FROM user WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);

$count=mysql_num_rows($result);

if($count==1){
// registers variables and redirects to file "success.php"
session_register("myusername");
session_register("mypassword");
header("location:success.php");
}
else {
echo "Wrong Username or Password";
}


success.php

Code: Select all


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

<html>
<body>
<br /><br />
Login Successful
<a href="add-entry.php">add an entry</a>
</body>
</html>


add-entry.php

Code: Select all


<?php
session_start();

$title = $_POST['title'];
$entry = $_POST['entry'];
$thetimestamp = date("Y-m-d H:i:s");

/* database connection here */
		
$query = "INSERT INTO blog (userid, title, entry, thetimestamp) VALUES ('$userid', $title', '$entry', '$thetimestamp')";
		
mysql_query($query) or die ('Error updating database');

... ?>

form html stuff here


Re: select value form one table and insert into another - my

Posted: Sun Aug 22, 2010 7:51 am
by Eran
The common practice is to register the row of user details - sans password - in the session. This includes the user ID, so you can retrieve it later for any operation that requires it. Since you are already retrieving the user row from the database, you just need to change what you save to the session -

Code: Select all

$sql = "SELECT * FROM user WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);
$user = mysql_fetch_assoc($result);

if(!empty($user){
    // registers variables and redirects to file "success.php"
    session_start();
    unset($user['password']);
    $_SESSION['user'] = $user;
    header("location:success.php");
}
else {
...
The user details will then be stored under $_SESSION['user'] (you should update other parts of your code that relied on the previous parameters)
And by the way, you should be escaping user input always when using it in queries to avoid injection attacks - using something like mysql_real_escape_string()
http://php.net/manual/en/function.mysql ... string.php

Re: select value form one table and insert into another - my

Posted: Sun Aug 22, 2010 10:53 am
by AbraCadaver
As pytrin has shown, don't use session_register() or session_is_registered() etc... Use the $_SESSION superglobal just as you would any other variable. You should visit the manual and be familiar with ALL functions that you use. http://us2.php.net/session_register shows that using $_SESSION is the preferred method since PHP 4.1 and session_register() is deprecated as of 5.3.

Re: select value form one table and insert into another - my

Posted: Sun Aug 22, 2010 11:23 am
by gluttonous_pet
Awesome! Thanks so much, I have amended some of my code with this in mind. I tested using the following on checklogin.php:

Code: Select all

$userid = ($user[id]);
    echo $userid;
and it prints the correct user id, but for some reason I can't get it to pass these values onto
the next page "success.php"
etc, I thought session_start(); would mean it persisted, any ideas? Instead I get nothing.

Right now I'm using:

checklogin.php

Code: Select all

$sql = "SELECT * FROM user WHERE username='$myusername' and password='$mypassword'";
$result = mysql_query($sql);
$user = mysql_fetch_assoc($result);

if(!empty($user)){
    // registers variables and redirects to file "success.php"
    session_start();
    unset($user['password']);
    $_SESSION['user'] = $user;
    header("location:success.php");
}
else {
	echo "sorry, didn't work";
}

success.php

Code: Select all

<?php
session_start();
if(!$_SESSION["user"]){
header("location:main_login.php");
}
else if($_SESSION["user"]) {
?>

<html>
<body>
<br /><br />
Login Successful
<a href="add-entry.php">add an entry</a>
</body>
</html>

<?php } ?>
Thanks for the info about sql injection, too. I will be using this, just trying to get the basics in place first of all.

Re: select value form one table and insert into another - my

Posted: Sun Aug 22, 2010 11:43 am
by Eran
This code works perfectly fine for me. You probably have white space before the session_start() call, which prevents it from working. If you are not seeing the appropriate error message, you should turn on display_errors in your php configuration (you should always develop with this setting on)

Re: select value form one table and insert into another - my

Posted: Sun Aug 22, 2010 2:06 pm
by gluttonous_pet
Cool, thank you, I've got the errors turned on now and I made a few changes with a bit of Googling - it works! Thanks so much for your help and patience :). Much appreciated!