Lex Braun wrote: > PJ, > > On Tue, Mar 10, 2009 at 3:46 PM, PJ <af.gourmet@xxxxxxxxxxxx > <mailto:af.gourmet@xxxxxxxxxxxx>> wrote: > > <snip> > $sql1 = "INSERT INTO book ( title, sub_title, descr, > comment, bk_cover, copyright, ISBN, language, sellers ) > VALUES ('$titleIN', '$sub_titleIN', '$descrIN', '$commentIN', > '$bk_coverIN', '$copyrightIN', '$ISBNIN', '$languageIN', > '$sellersIN')"; > $result1 = mysql_query($sql1, $db); > $autoid = mysql_insert_id($result1); > }/* <--- IF THIS LIKE IS DELETED, THE PAGE DOES NOT DISPLAY > So, if I select insert on the page, only the first query is executed > since the rest is commented out. Thus, I get onle the book table > inserted but not other tables, like author, book_author, or > publishers, > book_publisher or categories, book_categories... > Is there something wrong with what follows immediately... > like, do I have the brackets right? I've tried about every > combination possible with no change. > > //Check if Author is entered & exists > if( (strlen($_POST["first_nameIN"]) > 0) && > (strlen($_POST["last_nameIN"]) > 0) ) { > $sql2 = "SELECT (first_name, last_name) > FROM author WHERE (first_name LIKE '$first_nameIN' > && last_name LIKE '$last_nameIN)'"; > > > LIKE is going to do full-text search, which isn't what you want when > searching for a specific author. You can have the query return the ID > for that specific author (to use in your $sql2a query). > $sql2 = "SELECT id FROM author WHERE first_name = '$first_nameIN' AND > last_name = '$last_nameIN' "; > > $result2 = mysql_query($sql2); > if (mysql_num_rows($result2) > 0) { > > $row = mysql_fetch_assoc($result2); // gives you the row > return from $sql2 > > > $sql2a = "INSERT INTO book_author (authID, bookID, ordinal) > VALUES (author.id <http://author.id> WHERE > (first_name LIKE '$first_nameIN' > && last_name LIKE '$last_nameIN'), > book.ID WHERE book.title LIKE '$titleIN'), '1'"; > > With the change in $sql2 and the fact that the bookID is stored in > $autoid, this becomes: > $sql2a = "INSERT INTO book_author (authID, bookID, ordinal) VALUES (" > . $row['id'] . ", " . $autoid . ", '1')"; > > > $result2a = mysql_query($sql2a, $db); > } > elseif (mysql_num_rows($result2) = 0) { > $sql2b = "INSERT INTO author (first_name, last_name) > VALUES ('$first_nameIN', '$last_nameIN')"; > $result2b = mysql_query($sql2b, $db); > > $authorID = mysql_insert_id($result2b); // gives you > the id of the newly inserted author for use in your book_author table > > > $sql2c = "INSERT INTO book_author (authID, bookID, ordinal) > VALUES (author.id <http://author.id> WHERE > (first_name LIKE '$first_nameIN' > && last_name LIKE '$last_nameIN'), book.ID > WHERE book.title LIKE '$titleIN'), '1'"; > > With the addition of $authorID and the fact that bookID is stored in > $autoid, your $sql2c should now be: > $sql2c = "INSERT INTO book_author (authID, bookID, ordinal) VALUES (" > . $authorID . ", " . $autoid . ", '1')"; > > > $result2c = mysql_query($sql2c, $db); > } > } > > > - Lex > Thanks for your suggestions. They helped me solve several errors and explained some things like LIKE that were not clear. It takes me some time to ferret out some of the problems on this particular php page and I try to do what I can with limited knowledge and experience but I am learning. :-) For some reason I cannot understand, the $autoid and the $authorID = mysql_insert_id ($result) do not work. What I do not understand is when a query is successful and I seen in the database that the data has been inserted and the id field has been auto-inserted, the myssql_insert_id() returns what appears to be null since nothing is echoed. Here is some code: snip ... else { $sql1 = "INSERT INTO book ( title, sub_title, descr, comment, bk_cover, copyright, ISBN, language, sellers ) VALUES ('$titleIN', '$sub_titleIN', '$descrIN', '$commentIN', '$bk_coverIN', '$copyrightIN', '$ISBNIN', '$languageIN', '$sellersIN')"; $result1 = mysql_query($sql1, $db); $autoid = mysql_insert_id($result1); $sql2 = "INSERT INTO author (first_name, last_name) VALUES ('$first_nameIN', '$last_nameIN')"; $result2 = mysql_query($sql2, $db); $authorID = mysql_insert_id($result2); $sql2a = "INSERT INTO book_author (authID, bookID, ordinal) VALUES ( '$authorID', '$autoid', '1')"; $result2a = mysql_query($sql2a, $db); $sql2b = "INSERT INTO author (first_name, last_name) VALUES ('$first_name2IN', '$last_name2IN')"; $result2b = mysql_query($sql2b, $db); $author2ID = mysql_insert_id($result2b); $sql2c = "INSERT INTO book_author (authID, bookID, ordinal) VALUES ( '$author2ID', '$autoid', '2')"; $result2c = mysql_query($sql2c, $db); $sql3 = "INSERT INTO publishers (publisher) VALUES ('$publisherIN')"; $result3 = mysql_query($sql3, $db); $publisherID = mysql_insert_id($result3); $sql3a = "INSERT INTO book_publisher (bookID, publishers_id) VALUES ( '$autoid', '$publisherID' )"; $result3a = mysql_query($sql3a, $db); foreach($_POST['categoriesIN'] as $category){ $sql4 = "INSERT INTO book_categories (book_id, categories_id) VALUES ($autoid, $category)"; $result4 = mysql_query($sql4,$db); } echo $autoid; // shows: "blank" echo $authorID; // shows: "blank" echo $author2ID; // shows: "blank" echo $publisherID; // shows: "blank" echo "<br>autoid = $autoid<br>";// shows: autoid = "blank" echo "authorID = $authorID<br>";// shows: authorID = "blank" echo "author2ID = $author2ID<br>";// shows: author2ID = "blank" echo "publisherID = $publisherID<br>";// shows: publisherID = "blank" here is what is inserted: book is correctly inserted author is correctly inserted book_author is not correctly inserted book_categories is correctly inserted book_publisher bookID = 0 publishers_id = 0 (not correct) publishers is correctly inserted -- unheralded genius: "A clean desk is the sign of a dull mind. " ------------------------------------------------------------- Phil Jourdan --- pj@xxxxxxxxxxxxx http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
