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-)
--
Another genius: "I'm not poor; I just don't have any money. "
-------------------------------------------------------------
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
