select value form one table and insert into another - mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gluttonous_pet
Forum Newbie
Posts: 13
Joined: Thu Aug 19, 2010 4:06 am

select value form one table and insert into another - mysql

Post 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!
User avatar
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

Post 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]
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

Post by gluttonous_pet »

Hmm can't seem to get it to work. Thanks for your help though, will try a few more things!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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
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

Post 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

User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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
User avatar
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

Post 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.
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

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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)
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

Post 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!
Post Reply