multiple sql INSERT INTO's

thm120 · #1 (**permalink**) 10-23-10, 19:16

i have an HTML form, i also have 3 tables, one for posts, one for tags and one for blog_post_tags.

when the user clicks submit after filling out the form, its sent to a file that is supposed to upload those values to the appropriate fields in the database as well as copy certain values from tags and posts into blog_post_tags. this is what i have:

PHP Code:


			
mysql_select_db("core", $con);



$sql="INSERT INTO posts (title, post, author_id, date_posted)

VALUES

('$_POST[title]','$_POST[post]','$id','$date')";



$sql="INSERT INTO tags (name)

VALUES

('$_POST[tags]')";



$sql="INSERT INTO blog_post_tags (tag_id) SELECT MAX(id) FROM tags";

$sql="INSERT INTO blog_post_tags (blog_post_id) SELECT MAX(id) FROM posts";





echo "1 record added";



mysql_close($con)

but it doesnt do that, it doesnt do anything as a matter of fact, i tried just using one line in the db connection:

PHP Code:


			
$sql="INSERT INTO posts (title, post, author_id, date_posted)

VALUES

('$_POST[title]','$_POST[post]','$id','$date')";

and it worked, but if i try this:

PHP Code:


			
$sql="INSERT INTO posts (title, post, author_id, date_posted)

VALUES

('$_POST[title]','$_POST[post]','$id','$date')";



$sql="INSERT INTO tags (name)

VALUES

('$_POST[tags]')";

it only does the last $sql to tags instead of both

keep in mind that tags is a different table, so is there a way to get it to upload the data from the form into both tables?

please help me out with this

thm120 · #2 (**permalink**) 10-23-10, 20:51

figured it out.. instead of using $sql= just use mysql_query();

but its it possible to insert one value from a different table into two different columns in the same table? i tried :

PHP Code:


			
mysql_query("INSERT INTO blog_post_tags (tag_id, post_id) SELECT MAX(id) FROM tags");

with no luck

r937 · #3 (**permalink**) 10-23-10, 22:45

Quote:

Originally Posted by thm120

INSERT INTO blog_post_tags (tag_id, post_id) SELECT MAX(id) FROM tags

with no luck

of course no luck -- in fact, an error message

you are inserting into two columns, and providing only one value

thm120 · #4 (**permalink**) 10-23-10, 22:47

so how would i provide the other value? im afraid of using commas in SELECT.. ps im 'brand new' to php

r937 · #5 (**permalink**) 10-23-10, 22:52

Quote:

Originally Posted by thm120

so how would i provide the other value?

well, let's think about what you want to insert

MAX(id) FROM tags gives you -- presumably -- the id of the last tag added to the system

this isn't necessarily the id of the tag that was selected for the blog post by the user -- unless every tag that any user slaps on any post is automatically added as a new tag, even if it's been used before

so there's that problem

now, the other value that you want to provide is the id of the blog post, and you'd get that from php processing the form, which somehow has to indicate which post is being tagged, yes?

thm120 · #6 (**permalink**) 10-23-10, 22:56

Quote:

Originally Posted by r937

this isn't necessarily the id of the tag that was selected for the blog post by the user -- unless every tag that any user slaps on any post is automatically added as a new tag, even if it's been used before

i didnt think about that, but it does add every tag as a new tag.. lol my problems just keep growing

but overall you're pretty much right, just wish i knew what to do

it-iss.com · #7 (**permalink**) 10-24-10, 11:54

Just one other piece of advice, when adding entries into a database be sure to escape the strings. It someone posts a title "It's not fair" you will have problems. The single quote in the string being passed in will close the first string and you will end up with a MySQL syntax error. Using mysql_real_escape_string will solve this problem for you.

thm120 · #8 (**permalink**) 10-24-10, 13:31

Quote:

Originally Posted by it-iss.com

Using mysql_real_escape_string will solve this problem for you.

ok, i read up on the real escape string and im officially confused by this example:

PHP Code:


			
// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));
?>

but actually, its unnecessary since the page with the blog is only accessible by admins

it-iss.com · #9 (**permalink**) 10-24-10, 13:54

If $password is "' OR '' = '" then when replace this into your SQL query below you will get something like:

SELECT * FROM users WHERE user='username' AND password='' OR '' = ''

The OR at the end mean you have bypassed the password protection all you need to do is guess the username.

Internet Services

Web Development

Digital Marketing

Consumer Tech