Jim Lucas wrote:
> PJ wrote:
>> haliphax wrote:
>>> On Fri, Mar 13, 2009 at 1:24 PM, PJ <af.gourmet@xxxxxxxxxxxx> 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>";
> }
Hell, Jim, if you're not a teacher, you sure qualify as one.
My biggest problem is one of not understanding just what is going on
with code. Your explanations allow me to get a sort-of visualization of
what is happening. Things begin to fall into place.
Yes, I see how bloated the application would have become.
Thanks again for your input. You rock! :-)
--
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
