Re: RE: non-auto increment question

PJ wrote:

haliphax wrote:

On Fri, Mar 13, 2009 at 1:24 PM, PJ <af.gourmet@videotron.ca> wrote:

Jim Lucas wrote:

PJ wrote:

am resending as this was erroneously ccd to mysql. sorry! Ashley Sheridan wrote:

On Thu, 2009-02-26 at 11:27 -0500, PJ wrote:

Jerry Schwartz wrote:

Being rather new to all this, I understood from the MySql manual that the auto_increment is to b e used immediately after an insertion not intermittently. My application is for administrators (the site owner & designates) to update the database from and administration directory, accessed by user/password login... so there's really very little possibility of 2 people accessing at the same time. By using MAX + 1 I keep the id number in the $idIn and can reuse it in other INSERTS

[JS] Are you looking for something like LAST_INSERT_ID()? If you INSERT a record that has an auto-increment field, you can retrieve the value that got inserted with "SELECT LAST_INSERT_ID()". It is connection-specific, so you'll always have your "own" value. You can then save it to reuse, either as a session variable or (more easily) as a hidden field on your form.

Thanks, Jerry, You hit the nail on the head.:) To refine my problem (and reduce my ignorance),here's what is happening on the form page: There is a series of INSERTs. The first inserts all the columns of "book" table except for the id, which I do not specify as it if auto-insert. In subsequent tables I have to reference the book.id (for transitional tables like "book_author"(refers authors to book) etc. If I understand it correctly, I must retrieve ("SELECT LAST_INSERT_ID()") after the first INSERT and before the following insert; and save the id as a string ($id)...e.g. $sql = "SELECT LAST_INSERT_ID() AS $id" I need clarification on the "AS $id" - should this be simply id(does this have to be turned into a value into $id or does $id contain the value? And how do I retrieve it to use the returned value for the next $sql = "INSERT ... - in other words, is the id or $id available for the next directive or do I have to do something like $id = id? I'm trying to figure this out with some trials but my insert does not work from a php file - but it works from command-line... that's another post.

Here's how I mostly do it (albeit simplified): $query = "INSERT INTO sometable(title,content) VALUES('$title','$content')"; $result = mysql_query($query); $autoId = mysql_insert_id($result); $query = "INSERT INTO another_table(link_id,value) VALUES($autoId,'$value'); $result = mysql_query($query); No need to call another query to retrieve the last inserted id, as it is tied to the last query executed within this session. Ash www.ashleysheridan.co.uk

For some reason or other $autoId = mysql_insert_id($result); just does not work for me... Yet some of the data is inserted correctly... I did find that it does not work on tables that are empty... so you can't start with an empty table. I entered data & it still did not work. I tried on another duplicate database... doesn't work. I have checked & double checked the database, I have added checks to see what is returned and the returns are 0 or null - as I get different responses for slightly different functions. sessions is on mysql is 5.1.28 php5 here's what is parsed: 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 correctly inserted book_categories is correctly inserted book_publisher bookID = 0 publishers_id = 0 (not correct) publishers is correctly inserted

You need to take a closer look at this page: http://php.net/manual/en/function.mysql-insert-id.php Under the parameters section, look at the description of the link_identifier. Â It talks about the link_identifier being the database connection handler, not the resulting resource that is returned from a call to mysql_query(). Change the value you are passing to mysql_insert_id() to be the $db variable instead of the $results variable and you will probably get the results you are looking for. Also, reading further tells me that since you did not pass mysql_insert_id() a valid connection handler, you should have gotten a E_WARNING notice. Â Did you by chance check your logs for this error? Honestly, sounds like you need to turn on full error reporting to the screen. Place the following at the top of your script and you might see the problem a little clearer. error_reporting(E_ALL); ini_set('display_errors', 1); This will make it so that every error is reported to the screen and not just logged.

Thanks much for your input. This is great. Now I can seeeeeee! It would have solved a lot of headaches earlier in the process. :-[ I was able to catch several "Notice:"s and correct them. Now things work pretty well... Only problem now is to understand the use of subqueries... and how to display several authors and categories for books. I'm not trying to reinvent the wheel; just trying to make round and not ssquare. :-) O:-)

This might get you started... SELECT * FROM authors WHERE author_id IN (SELECT author_id FROM books WHERE book_id = ?) HTH,

It does, thanks. But here's an interesting question -- in my original select sequence with joins I have a CONCAT_WS(" ", first_name, last_name) as Author : $sql1 = "SELECT b.id, b.title, b.sub_title, b.descr, b.comment, b.bk_cover, b.copyright, b.ISBN, b.sellers, b.language, c.publisher, CONCAT_WS(' ', first_name, last_name) AS Author FROM book AS b LEFT JOIN book_author AS ab ON b.id = ab.bookID LEFT JOIN author AS a ON ab.authID=a.id LEFT JOIN book_publisher as abc ON b.id = abc.bookID LEFT JOIN publishers AS c ON abc.publishers_id = c.id ORDER BY title ASC "; $result1 = mysql_query($sql1, $db); $bookCount = mysql_num_rows($result1); for ($i=0; $i < $bookCount; $i++) {

    $row = mysql_fetch_array($result1);
    $bookid[$i]                = $row["id"];
    $booktitle[$i]            = $row["title"];       $booksub_title[$i]        = $row["sub_title"];       $bookdescr[$i]            = $row["descr"];       $bookcomment[$i]        = $row["comment"];
    $bookbk_cover[$i]        = $row["bk_cover"];
    $bookcopyright[$i]    = $row["copyright"];       $bookISBN[$i]            = $row["ISBN"];
    $booklanguage[$i]    = $row["language"];
    $booksellers[$i]    = $row["sellers"];
    $Author[$i]            = $row["Author"];
    $publisherspublisher[$i]    = $row["publisher"];

} Works fine; but it does not give me 2 authors when there are 2. I wonder if it is possible to do a second CONCAT_WS(' ', first_name, last_name) AS Author2 WHERE (book_author.bookID = '$bookid' && ordinal = '2') --- I probably do not have the right syntax, but you get the idea, I'm sure. The whole point of my exercise is to try to keep things as simple as possible. I am desperately trying to avoid more and more complicated stuff. :-) And I would think that it should be possible to work out some way of integrating another table (book_categories) to retrieve an array of up to, say 4 fields, and store them in a $string (with conditions to show only that which exists) for echoing in HTML. I just see a big problem because the code is parsing all books ...... for ($i=0;$i<$bookCount;$i++) {

    if (empty($bookbk_cover[$i]))....

and unless there's a way to include querys within the "for...) execution... actually, this is what I thought could be done. between echos I would do a query to see if there is another Author, for example, or categories and, if so, echo them at this point and then go on to the next line of code, an echo... does that sound reasonalbe? I'm probably trying to do something that is way over my head, if not impossible, but hell, that's life. :-D and I'm having one hell-of-a time (good, though) 8-)

Ok... Well, that code is something to behold... Think of your SQL queries like this. Each time you do a join in a SQL select, (if you don't involve a where clause) you are taking all the records from one table and joining them with the other table. So in the above Select with the four JOIN()'s, you are creating this monster of a table (in memory) that is finally handed back to PHP. something to the effect of this. Table A Record count * Table B Record count * Table C Record count * Table D Record count * etc... See how that could grow rather quickly? What you should do is more like Table A Record count + Table B Record count + Table C Record count + Table D Record count + etc... That seems a little more manageable. <?php $books = array(); $SQL = "SELECT * FROM book ORDER BY title ASC"; if ( ( $results = mysql_query($SQL, $db) ) !== false ) { while ( $row = mysql_fetch_assoc($results) ) { $books[$row['id']] = $row; } } $book_author = array(); $SQL = "SELECT id, bookID, authID FROM book_author"; if ( ( $results = mysql_query($SQL, $db) ) !== false ) { while ( $row = mysql_fetch_assoc($results) ) { $book_author[$row['bookID']][] = $row['authID']; } } $authors = array(); $SQL = "SELECT id, first_name, last_name FROM author"; if ( ( $results = mysql_query($SQL, $db) ) !== false ) { while ( $row = mysql_fetch_assoc($results) ) { $authors[$row['id']] = $row; } } $book_publisher = array(); $SQL = "SELECT id, bookID, publisher_id FROM book_publisher"; if ( ( $results = mysql_query($SQL, $db) ) !== false ) { while ( $row = mysql_fetch_assoc($results) ) { $book_publisher[$row['bookID']][] = $row['publisher_id']; } } $publishers = array(); $SQL = "SELECT id, publisher FROM publishers"; if ( ( $results = mysql_query($SQL, $db) ) !== false ) { while ( $row = mysql_fetch_assoc($results) ) { $publishers[$row['id']] = $row; } } ?> Now you can do something like this: <?php if ( $books ) { echo "<table>"; echo "<tr><th>Book Information</th><th>Book Authors</th><th>Book Publishers</th></tr>"; foreach ( $books AS $bookID => $book ) { echo "<tr>"; echo "<td>"; print_r($book, 1); echo "</td>"; echo "<td>"; if ( isset( $book_author[$bookID] ) ) { foreach ( $book_author[$bookID] AS $authorID ) { if ( isset( $authors[$authorID] ) ) { print_r($authors[$authorID], 1); } } } echo "</td>"; echo "<td>"; if ( isset( $book_publisher[$bookID] ) ) { foreach ( $book_publisher[$bookID] AS $authorID ) { if ( isset( $publishers[$authorID] ) ) { print_r($publishers[$authorID], 1); } } } echo "</td>"; echo "</tr>"; } echo "</table>"; }