Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts

    I need advice on a query structure

    Hi all, I am new to this forum and php/mysql. I will try to be as descriptive as possible to make my question easier to understand.

    I have 3 tables.

    Table user
    column UserID
    column username
    column password
    column email

    Table title
    column titleID
    column UserID
    column name

    Table listitems
    column listitemsID
    column titleID
    column text

    The goal here is to have a user be able to create a list, with a title of that list at the top. Being new to coding, I am struggling. I can get the title entered into the database, but since I am submitting the list items with the title it makes the query more complicated and I am not sure how to structure it. Here is my code so far:
    Code:
    <?php
    require 'connect.inc.php';
    require 'core.inc.php';
    
    
    if (loggedin()) {
      if (isset($_POST['name']) && isset($_POST['text'])) {
    	$name = $_POST['name'];
    	$text = $_POST['text'];
    	  
      if (!empty($name) && !empty($text)) {
    	$query = "INSERT INTO `title` VALUES ('','$name','".$_SESSION['user_id']."')";
    		if ($query_run = mysql_query($query)) {
    		  
    		  }
    	  } else {
    	  echo 'Please fill in title and at least one item';
    	  }
    	}
    }
    ?>
    
    
    <form action="<?php $current_file ; ?>" method="POST">
    <input type="text" name="name" /><br>
    1.<input type="text" name="text" /><br>
    <input type="submit" value="submit" />
    </form>
    I only have one list item on there to make it simple, obviously the goal is to have multiple text areas so they can have a longer list.

    The main problem I can't wrap my head around is, how am I supposed to get the titleID to put into the VALUES for the query when i go to insert it into the database?

    Thank you for your time. If you have any suggestions on things I should read and study I would appreciate it. I am looking through your tutorials and I think the stuff on mySQL is a good place to start
    Last edited by <?php???>; 01-19-2012 at 07:14 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    http://dev.mysql.com/doc/refman/5.1/...last-insert-id

    You don't *say* that titleID is an AUTO_INCREMENT column, but I'm guessing it is from the syntax of your INSERT statement.

    And, just incidentally, that syntax is a little off. You should use NULL instead of '' as the value for an autoincement column. And if userid is an INT column, as it almost surely is, then you should *NOT* use apostrophes around its value.
    Code:
    $query = "INSERT INTO `title` VALUES (NULL,'$name',".$_SESSION['user_id'].")";
    You should also be sanitizing your $name field value, just in case it contains an apostrophe and/or to defend against SQL injection attacks.

    Anyway...

    After performing that query then just perform another one using LAST_INSERT_ID(). Or just just that function as the id in your next INSERT query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thank you for helping me out. LAST_INSERT_ID will be very useful. The problem is before that query gets read, I ran into another problem with the first query.

    It is saying: Cannot add or update a child row: a foreign key constraint fails (`myprac`.`title`, CONSTRAINT `title_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `user` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE)

    I did some searching for why this problem exists but I still don't understand. I even deleted my database and redid the indexes so there was no information in it. I am using phpmyadmin, and I made an index for userID in the title table, and then put a column called titleID in the listitems table, then went to relation view and assigned a foreign key constraint to them.

    I am not sure how to go about fixing this. I've seen a thread of you helping another forum member with this problem but I am not if your explanation also applies to me.

    Again, thank you for the help.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    That error means that you tried to use a USERID value in your TITLE table where that particular USERID value does not exist in your USERS table.

    In other words, you *MUST* create your USERS record *first* before you can link to it from the TITLE table.

    If you DEBUG just a little you'll probably find that you have either forgotten to first create the appropriate record in the USERS table or, perhaps, you have somehow mangled the USERID value when you put it into the session value and/or retrieved it.

    To find out, just add one little debug statement:
    Code:
    $query = "INSERT INTO `title` VALUES (NULL,'$name',".$_SESSION['user_id'].")";
    
    echo "<hr>DEBUG SQL: " . $query . "<hr>\n";
    
    ...
    So look at what userid value you are attempting to use there and then just use phpmysqlladmin or some other DB tool to look at your USERS table and see if, indeed, that userid exists in the USERS table. (Hint: It won't. So now figure out what went wrong. Probably an error in your session value.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    The debug worked and I am getting the user_ID value (its 1, I forgot to mention I put one in.)

    So does that mean I have to delete my foreign keys and try to remake them again? Because the session_ID is being passed on.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    No. If you indeed have a USERID with a value of 1 in the USERS table, then a value of 1 for the USERID in that insert is correct.

    It occurs to me that maybe your problem is with the order of fields in the table.

    You probably ought to list the field names in your INSERT, just to be safe (I always do).

    SO:
    Code:
    $query = "INSERT INTO `title` ( name, userID ) VALUES ('$name',".$_SESSION['user_id'].")";
    IN fact, now that I look at your first post, that's almost surely the problem!

    You said the fields in the title table were in the order
    titleid
    userid
    name
    And when you omit the list of field names, you *MUST* insert the VALUES in that same order. So MYSQL was taking the $name value and trying to use it as the userid. Kablooey.

    Anyway, if you list the field names, as I just showed, you'll never have an ordering problem.

    Plus you can omit the name of the AUTO_INCREMENT field and then omit the NULL for its value.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    <?php???> (01-19-2012)

  • #7
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    It worked! Thank you so much for your time and help. Now I get to move on to the fun part and add multiple forms for list items!

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Quote Originally Posted by <?php???> View Post
    Now I get to move on to the fun part and add multiple forms for list items!
    No no...*ONE* <form>, only. You can only send one <form> from a web page to the server.

    Multiple *items*, yes. But all in one <form>.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    No no...*ONE* <form>, only. You can only send one <form> from a web page to the server.

    Multiple *items*, yes. But all in one <form>.
    Yes, sorry I meant items. However right now I am only able to send one item. When I add another list item, it will not add the second one. When I was reading about INSERT_LAST_ID() it said the currently executed statement does affect not the value, so I assume it will still be the original title ID? When I use the debug the code just comes up as INSERT_LAST_ID, and then I added a die (mysql_error()) on the end and it says Cannot add or update a child row: a foreign key constraint fails (`myprac`.`listitems`, CONSTRAINT `listitems_ibfk_1` FOREIGN KEY (`titleID`) REFERENCES `title` (`TitleID`) ON DELETE CASCADE ON UPDATE CASCADE)

    This is basically the same message I was dealing with before. So I changed the code to look like this

    Code:
    <?php
    require 'connect.inc.php';
    require 'core.inc.php';
    
    
    if (loggedin()) {
      if (isset($_POST['name']) && isset($_POST['text']) && isset($_POST['text2'])) {
    	$name = $_POST['name'];
    	$text = $_POST['text'];
    	$text2 = $_POST['text2'];
    	  
      if (!empty($name) && !empty($text) && !empty($text2)) {
    	$query = "INSERT INTO `title` ( name, userID ) VALUES ('".mysql_real_escape_string($name)."',".$_SESSION['user_id'].")";
    	  if ($query_run = mysql_query($query)) {
    		  $query = "INSERT INTO `listitems` ( titleID, text ) VALUES (LAST_INSERT_ID(),'".mysql_real_escape_string($text)."')";
    		   if ($query_run = mysql_query($query)) {
    		    $query2 = "INSERT INTO `listitems` ( titleID, text ) VALUES (LAST_INSERT_ID(),'".mysql_real_escape_string($text2)."')";
    			  if ($query_run  = mysql_query($query2)) {
    			    echo 'ok'; 
    			  } else {
    			    die (mysql_error());
    			  }
    		  } 
    	  
    	  } 
    	} else {
        echo 'Please fill in the title and at least one item';
      }
      }
    }
    ?>
    
    
    <form action="<?php $current_file ; ?>" method="POST">
    <input type="text" name="name" /><br>
    1.<input type="text" name="text" /><br>
    2.<input type="text" name="text2" /><br>
    <input type="submit" value="submit" />
    </form>
    But I still get the same message.
    Last edited by <?php???>; 01-19-2012 at 08:30 PM. Reason: missed a word

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    No no... When you did the first INSERT into listitems *THAT* query generated a new AUTO_INCREMENT id (the new listitemsID) and then *THAT* is the value you get from LAST_INSERT_ID in the next statement!

    Which is obviously bogus.

    You will have to do it this way (omitting the "or die" stuff, etc., for simplicity):

    Code:
    $name = mysql_real_escape_string( $_POST["name"] );
    $query = "INSERT INTO `title` ( name, userID ) VALUES ('" . $name . "',".$_SESSION['user_id'].")";
    mysql_query($query);
    $result = mysql_query("SELECT LAST_INSERT_ID() AS titleid");
    $titleid = mysql_fetch_assoc($result)["titleid"]
    
    for ( $t = 1; $t < 1000000; ++$t )
    {
        $item = mysql_real_escape_string( $_POST["text" . $t] );
        if ( $item == "" ) break; // leave the loop
        $query = "INSERT INTO `listitems` ( titleID, text ) VALUES ( $titleid,'$item')";
        mysql_query($query);
    }
    This assumes that you name your text field in the <form> as "text1", "text2", "text3", etc. And that you will stop inserting as soon as you find the first one that is blank.

    I don't use PHP, so sorry if there are a couple of syntax goofs there. But you hopefully get the idea.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    No no... When you did the first INSERT into listitems *THAT* query generated a new AUTO_INCREMENT id (the new listitemsID) and then *THAT* is the value you get from LAST_INSERT_ID in the next statement!

    Which is obviously bogus.

    You will have to do it this way (omitting the "or die" stuff, etc., for simplicity):

    Code:
    $name = mysql_real_escape_string( $_POST["name"] );
    $query = "INSERT INTO `title` ( name, userID ) VALUES ('" . $name . "',".$_SESSION['user_id'].")";
    mysql_query($query);
    $result = mysql_query("SELECT LAST_INSERT_ID() AS titleid");
    $titleid = mysql_fetch_assoc($result)["titleid"]
    
    for ( $t = 1; $t < 1000000; ++$t )
    {
        $item = mysql_real_escape_string( $_POST["text" . $t] );
        if ( $item == "" ) break; // leave the loop
        $query = "INSERT INTO `listitems` ( titleID, text ) VALUES ( $titleid,'$item')";
        mysql_query($query);
    }
    This assumes that you name your text field in the <form> as "text1", "text2", "text3", etc. And that you will stop inserting as soon as you find the first one that is blank.

    I don't use PHP, so sorry if there are a couple of syntax goofs there. But you hopefully get the idea.
    When you say you will stop inserting as soon as you find the first one blank does that mean the last item form needs to be blank? Because an error message pops up that says Notice: Undefined index: text3 when there is no text 3 (which I assume is the coding ['text' . +$t]).

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,155
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Well, I did say I'm not a PHP person. In ASP/JSP, trying to reference an undefined POST value is not an error. You just bet back a blank string.

    In PHP, maybe you need to simply use isset( ) instead of checking for blank string. I leave that to you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #13
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Well, I did say I'm not a PHP person. In ASP/JSP, trying to reference an undefined POST value is not an error. You just bet back a blank string.

    In PHP, maybe you need to simply use isset( ) instead of checking for blank string. I leave that to you.
    Yeah I found the problem, I used the debug code and the $titleID is coming in as 'Array'. I've seen this before so I should be able to fix it, thanks again for all your help.

  • #14
    New Coder
    Join Date
    Jan 2012
    Posts
    15
    Thanks
    5
    Thanked 0 Times in 0 Posts
    For anyone reading this is what the code ended up looking like running correctly:

    Code:
    $name = mysql_real_escape_string($_POST['name']);
    	$query = "INSERT INTO `title` ( name, userID ) VALUES ('" . $name . "',".$_SESSION['user_id'].")";
    	mysql_query($query);
    	$query = "SELECT LAST_INSERT_ID() AS `titleid`";
    	$result = mysql_query($query);
    	$titleid = mysql_fetch_array($result);
    	
    
    		for ( $t = 1; $t < 1000000; $t++ ) {
              if (isset($_POST['text' . $t])) {
    			$item =  mysql_real_escape_string($_POST['text' . $t]);
    			if ( $item == "" ) break; // leave the loop
    			$query = "INSERT INTO `listitems` ( titleID, text ) VALUES ( '$titleid[titleid]','$item')";
    			echo "<hr>DEBUG SQL: " . $query . "<hr>\n";
    			mysql_query($query);
    			 
    			}
    			
    		}
    Thank you Old Pedant for having patience!

    edit:i forgot to take debugging code out but it helped alot.


  •  

    Posting Permissions

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