PJ wrote:
> 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";
this is actually: $SQL = "SELECT authID, bookID, ordinal FROM book_author";
>> if ( ( $results = mysql_query($SQL, $db) ) !== false ) {
>> while ( $row = mysql_fetch_assoc($results) ) {
>> $book_author[$row['bookID']][] = $row['authID'];
>> }
>> }
How can I select the author by ordinal? There are only 2 possibilities 1
& 2.
I believe that I need to do something like this - but with to versions -
one as k$Author 1 (referred by ordinal = 1 and $Author2 where ordinal = 2.
$Author = ($authors[$authorID]['first_name']) . ' ' .
($authors[$authorID]['last_name']);
echo $Author;
>>
>> $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'];
Could you explain this, please - this is further from me than Arcturus:
$book_publisher[$row['bookID']][] = $row['publisher_id'];
What is purpose and meaning of all the [] brackets? Especially the empty
ones?
I following your example, I have been able to finally get all the arrays
in place. Only now, I am looking into how to retrieve the parts of the
array...
>> }
>> }
>>
>> $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);
I don't understand - to what does the 1 refer in ---> print_r($book, 1);
it outputs nothing. If I remove the ", 1" I get a printout of each
array for each row of each book; which is great. Unless I am dumber
than a dodo, I have to assign the contents of the arrays to variables
that I can then echo in HTML, right?
>> 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! :-)
A couple of questions inserted above.
--
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
