select value form one table and insert into another - mysql
Moderator: General Moderators
-
gluttonous_pet
- Forum Newbie
- Posts: 13
- Joined: Thu Aug 19, 2010 4:06 am
select value form one table and insert into another - mysql
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!
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: select value form one table and insert into another - my
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]
[text]INSERT INTO `blog` (`userid`, `entry`) (SELECT `id` FROM `users` WHERE `username` = '$username'), '$entry'[/text]
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
-
gluttonous_pet
- Forum Newbie
- Posts: 13
- Joined: Thu Aug 19, 2010 4:06 am
Re: select value form one table and insert into another - my
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
The syntax abracadaver used is a bit off, it should be
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
Code: Select all
INSERT INTO `blog` (`userid`, `entry`) SELECT `id`,'$entry' FROM `users` WHERE `username` = '$username'-
gluttonous_pet
- Forum Newbie
- Posts: 13
- Joined: Thu Aug 19, 2010 4:06 am
Re: select value form one table and insert into another - my
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
success.php
add-entry.php
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
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 -
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
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 {
...
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
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: select value form one table and insert into another - my
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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
-
gluttonous_pet
- Forum Newbie
- Posts: 13
- Joined: Thu Aug 19, 2010 4:06 am
Re: select value form one table and insert into another - my
Awesome! Thanks so much, I have amended some of my code with this in mind. I tested using the following on checklogin.php:
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
success.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.
Code: Select all
$userid = ($user[id]);
echo $userid;
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 } ?>
Re: select value form one table and insert into another - my
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)
-
gluttonous_pet
- Forum Newbie
- Posts: 13
- Joined: Thu Aug 19, 2010 4:06 am
Re: select value form one table and insert into another - my
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!