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>";
}
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
