Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Posts
    7

    Unhappy Unanswered: multiple sql INSERT INTO's

    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

  2. #2
    Join Date
    Oct 2010
    Posts
    7
    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
    Last edited by thm120; 10-23-10 at 23:04.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by thm120 View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2010
    Posts
    7
    so how would i provide the other value? im afraid of using commas in SELECT.. ps im 'brand new' to php

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by thm120 View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2010
    Posts
    7
    Quote Originally Posted by r937 View Post
    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

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Oct 2010
    Posts
    7
    Quote Originally Posted by it-iss.com View Post
    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

  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •