And, assuming the same table (just got the new email, sorry) for the update:
function makeUpdateQuery(array $authors, $bookId) {
foreach($authors as &$author) {
$author = mysql_real_escape_string($author);
}
if(!ctype_digit($bookId)) { return false; }
$existing = array();
$getAuthors = 'SELECT AuthorName FROM book_authors';
mysql_query($getAuthors);
while($row = mysql_fetch_assoc()) {
$existing[] = $row['AuthorName'];
}
$authorString = implode("', '", $existing);
$sql = "DELETE FROM book_authors WHERE AuthorName NOT IN ('";
$sql .= $authorString;
$sql = substr($sql, 0, -3) . ')';
mysql_query($sql);
$existing = array();
mysql_query($getAuthors);
while($row = mysql_fetch_assoc()) {
$existing = $row['AuthorName'];
}
if(arsort($existing) != arsort($authors)) {
foreach($existing as $exist) {
$key = array_search($exist, $authors);
unset($authors[$key]);
}
}
makeInsertQuery($authors, $bookId);
}
Probably needs some tweaking, both of them, as I'm writing compleletly off
the cuff, but the logic should be about right :)
On Tue, Jun 16, 2009 at 8:59 AM, Eddie Drapkin <oorza2k5@xxxxxxxxx> wrote:
> I'm going to assume that your table is setup to have the rows BookID and
> AuthorName, adjust accordinging:
>
> function makeInsertQuery(array $authors, $bookId) {
>
> $sql = "INSERT INTO book_authors (BookID, AuthorName) VALUES ('$bookId',
> '";
>
> foreach($authors as &$author) {
> $author = mysql_real_escape_string($author);
> }
>
> if(!ctype_digit($bookId)) {
> return false;
> }
>
> $sql .= implode("', '$bookid'), ('", $authors); // Eric Foo', '123'),
> ('John Bar', '123'), ('Ricky Bam', '123'), (';
>
> $sql = substr($sql, 0, -4);
>
> return $sql;
>
> }
>
> On Tue, Jun 16, 2009 at 8:50 AM, PJ <af.gourmet@xxxxxxxxxxxx> wrote:
>
>> Ashley Sheridan wrote:
>> > On Mon, 2009-06-15 at 17:38 -0400, PJ wrote:
>> >
>> >> Jay Blanchard wrote:
>> >>
>> >>> [snip]
>> >>> In what way would this simplify or ease my pain?
>> >>> The difficulty, it seems to me, is not in retrieving the rows, but
>> >>> rather how to pass the row data to the variables. And since the number
>> >>> of rows is variable, I believe that the only way to assign the
>> variables
>> >>> is by use of a loop? I think I'm beating my head against the wall for
>> >>> nothing... :-(
>> >>> [/snip]
>> >>>
>> >>> You asked for easier. In this way the data is assigned to a usable
>> >>> variable right out of the gate and that is an array variable. You can
>> >>> actually use it with either mysql_fetch_array or mysql_fetch_row.
>> >>>
>> >>> Here is your query;
>> >>>
>> >>> $sql = "SELECT first_name, last_name, book_author.ordinal
>> >>> FROM author, book_author
>> >>> WHERE book_author.bookID = $idIN && book_author.authID =
>> author.id
>> >>> ORDER BY ordinal";
>> >>>
>> >>> You need not declare another array, but do it this way instead;
>> >>>
>> >>> if ($results = mysql_query($sql, $db)) {
>> >>> while ( $row = mysql_fetch_array($results, MYSQL_ASSOC) ) {
>> >>> echo $row['first_name'];
>> >>> echo $row['last_name'];
>> >>>
>> >>>
>> >> Then I have to add some loopy thing to assign the values to the
>> >> $variables... a real pita since my variable do not lend themselves too
>> >> well to linear alterations ($varIn, $var2IN, $var3IN... etc... so $i=0
>> >> and $i++ arren't going to be too cooperative here... I guess I'd have
>> to
>> >> change that to $var1IN... and then figure out how to do $var.$i.IN ...
>> I
>> >> already tried, but don't seem to have it right
>> >>
>> >>> ....etcetera.....
>> >>> }
>> >>> }
>> >>>
>> >>> The lazy part I will agree with :)
>> >>>
>> >>>
>> >> Well, I shouldn't say it, but laziness is a characteristic of
>> >> "intelligence": why do more than you have to when you can be doing
>> >> something else. Actually, I am anything but lazy, I spend innumerable
>> >> hours trying to understand what all this coding is about... searching
>> >> the web and rummaging (and I do mean "rummaging") in all the lists and
>> >> posts ... but just going through 20 or 30 listings out of more than
>> >> 20,000 is already taxing... much easier to ask on the list... if
>> someone
>> >> can understand my fuzzy questions, they may find the grace and
>> >> generosity to take pity on the ignoramus... ;-)
>> >> And I enjoy the ribbing and the humour ... it's a really very nice
>> list!
>> >>
>> >>
>> >> --
>> >> Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
>> >> -------------------------------------------------------------
>> >> Phil Jourdan --- pj@xxxxxxxxxxxxx
>> >> http://www.ptahhotep.com
>> >> http://www.chiccantine.com/andypantry.php
>> >>
>> >>
>> >>
>> > Why do you need them in separate variables? Can't you use them using
>> > array syntax elsewhere in your code?
>> Basically, I don't know how else to handle inserting and updating random
>> number of authors. Each book in the db may have up to 5 authors. I
>> imagine there is a simpler way to do it; I just don't have the knowledge
>> or experience or the time to learn another way. I'd be happy to have you
>> look at the code and hear some feedback. It's probably a miracle that
>> the site is working... ;-)
>> Oh, it's www.ptahhotep.com - my daughter's egyptology site; I just
>> migrated from pure (almost) html to php/mysql/css and am just doing the
>> finishing touches now - the edit page.
>>
>> --
>> Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
>> -------------------------------------------------------------
>> 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
>>
>>
>
