Re: insert array values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Well, I have everything more or less under control... just one little
thingie...
I was able to get everything working quite well... basically as I
thought it should go with a little help from my friends, here on the
list :-)   many of my problems were small omissions and /or typos and
misplaced brackets and ;
The only  thing (probably not) that stymies me is how to get the several
categories printed out for each book . If there is only one category,
everything is great... but when there are more than one category, the
book listing repeats the listing of a book as many times as there are
categories listed for it... fir instance, there are 10 books listed in
my test table; i have the book_category table listing 1 book as having 1
category and another just 2 categories. When running the script with the
category stuff commented out, I get 10 entries... with the categories on
I get 11 entries with the one that has 2 categories repeated once.
What should I post to analyze the problem?

Jim Lucas wrote:
> PJ wrote:
>> Jim Lucas wrote:
>>> PJ wrote:
>>>  
>>>> 9el wrote:
>>>>    
>>>>> On Sat, Mar 7, 2009 at 5:37 AM, PJ <af.gourmet@xxxxxxxxxxxx
>>>>> <mailto:af.gourmet@xxxxxxxxxxxx>> wrote:
>>>>>
>>>>>     I've done some rethingking and this may be the direction to go:
>>>>>
>>>>>     What I need to wind up with is something like this:
>>>>>
>>>>>     $sql = "INSERT INTO book_categories ( book_id, category )
>>>>>        VALUES( '$autoID', '$categoriesID[0]' ),
>>>>>        ( '$autoID', '$categoriesID[1]' ),
>>>>>        ( '$autoID', '$categoriesID[2]' ),
>>>>>        ( '$autoID', '$categoriesID[3]' )
>>>>>        ( '$autoID', '$categoriesID[4]' )";
>>>>>
>>>>>     Does it make sense?
>>>>>     How do I pass the values to the query?
>>>>>
>>>>>
>>>>>
>>>>> You can run a php loop inside the insert  clause.
>>>>> But if its already existing record then it woud be UPDATE clause.
>>>>>
>>>>>       
>>>> Could I have a small hint as to how to do that. I'll check the manual
>>>> and search G in the meantime.  Right now, it's Friday night and I'm
>>>> about to get whammied by the wife... ;-)      have a good night &
>>>> let's
>>>> hope for a brighter tomorrow, tomorrow, tomorrowwww
>>>>
>>>>     
>>>
>>> To do something like what you are describing above, you would do the
>>> following:
>>>
>>> <?php
>>>
>>> # Setup your DB connection etc...
>>>
>>>
>>> # Build insert statement for your book
>>> $sql = "INSERT INTO books (title, author, etc...) VALUES ('To kill a
>>> mocking bird', 'Harper Lee', etc...)";
>>>
>>> # Execute insert statement
>>> if ( ( $results = mysql_query($sql, $dblink) ) !== false ) {
>>>
>>>     # Grab last insert ID for my thread
>>>     $last_id = mysql_insert_id($dblink);
>>>
>>>     # Check to see if any categories were choosen
>>>     if ( $categoriesIN ) {
>>>         $values = array();
>>>
>>>         # Loop through each category and build VALUES entry...
>>>         foreach ( $categoriesIN as $k => $id ) {
>>>
>>>             # Build VALUES entry, plus run $id through escape
>>> function for sanity
>>>             $values[] = "( {$last_id},
>>> ".mysql_real_escape_string($id)." )";
>>>         }
>>>
>>>         # Join the VALUES entries just created and separate them
>>> with a comma
>>>         $sql = "INSERT INTO book_categories ( book_id, category )
>>> VALUES " . join(', ', $values);
>>>
>
> At this point, if you echo'ed this.  You would get this
>
> INSERT INTO book_categories ( book_id, category ) VALUES ( 1, 3 ), (
> 1, 5 ), ( 1, 7 ), etc...
>
> This basically takes all your inserts that would be done individually
> and groups them into one insert call.  This helps because it causes
> the table not to re-index a zillion times.
>
> You will still end up with unique row entries for each category and
> autoID group.  They will not be grouped together in one row as you
> suggest below.
>
>>>         # Execute book 2 categories SQL entry
>>>         if ( ( $results = mysql_query($sql, $dblink) ) === false ) {
>>>
>>>             # If it fails, show me why
>>>             echo 'Book to category entry failed:
>>> '.mysql_error($dblink);
>>>         }
>>>     }
>>> } else {
>>>     # Show why my book SQL entry failed
>>>     echo "Book insert failed: ".mysql_error($dblink);
>>> }
>>>
>>> ?>
>>>
>>> That is about as best as can be explained.
>>>
>>> BTW - I took away the quotes around your values in your second
>>> insert statement.  They are numbers and numbers should not have
>>> quotes around them.
>>>
>>> Hopefully this works, or guides you in the direction of the answer
>>> you seek.
>>>
>>>   
>> Interesting... but, if I understand right (and I am not wet behind the
>> ears but a newbie nevertheless), this would just enter a series of comma
>> separated values in one field (the category field; the book_id is taken
>> care of by an earlier insert followed by
>> $autoId = mysql_insert_id($result);
>> which is then inserted in to the book_id)
>>
>> To do what you are suggesting could probably be done in a simpler way by
>> using the implode function... but what I am really trying to do should
>> give a result like the one in my original post.
>> Problem is: the insert is not always going to be the same length; that
>> is there may be 2 categories selected or 3 or 4 so the insert statement
>> has to be created on the fly. So, far no one has figured out a way to do
>> it. :-(
>>
>> An alternative, and I'm going to try that now is to do a switch or case
>> or whatever it is and set up 3 or 4 inserts with 1 to 4 VALUES and then
>> populate them from the array.
>>
>> Or another way may be even simpler: implode the array and save to one
>> field and when querying explode it. :-)
>>
>>
>>
>>
>


-- 
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


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux