Re: [PHP] insert array values

[PJ]

Ashley Sheridan wrote:
> On Sun, 2009-03-08 at 17:52 -0500, PJ wrote:
>   
>> Ashley Sheridan wrote:
>> 
>>> On Sun, 2009-03-08 at 16:52 -0500, PJ wrote:
>>>   
>>>   
 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  > 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, tomorro

 
 
 
>>> To do something like what you are describing above, you would do the
>>> following:
>>>
>>> >>
>>> # 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
>>>  

Re: [PHP] insert array values

[Ashley Sheridan]

On Sun, 2009-03-08 at 17:52 -0500, PJ wrote:
> Ashley Sheridan wrote:
> > On Sun, 2009-03-08 at 16:52 -0500, PJ wrote:
> >   
> >> 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  >>> > 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, tomorro
> >>
> >> 
> >> 
> > To do something like what you are describing above, you would do the
> > following:
> >
> >  >
> > # 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);
> > }
> >>

Re: [PHP] insert array values

[PJ]

Ashley Sheridan wrote:
> On Sun, 2009-03-08 at 16:52 -0500, PJ wrote:
>   
>> 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 >> > 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, tomorro
>>
>> 
>> 
> To do something like what you are describing above, you would do the
> following:
>
> 
> # 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 directi

Re: [PHP] insert array values

[Ashley Sheridan]

On Sun, 2009-03-08 at 16:52 -0500, PJ wrote:
> 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  > > 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, tomorro
> 
>  
> >>>
> >>> To do something like what you are describing above, you would do the
> >>> following:
> >>>
> >>>  >>>
> >>> # 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 

Re: [PHP] insert array values

[PJ]

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

 
>>>
>>> To do something like what you are describing above, you would do the
>>> following:
>>>
>>> >>
>>> # 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
>> 

Re: [PHP] insert array values

[Jim Lucas]

PJ wrote:

Jim Lucas wrote:

PJ wrote:
  

9el wrote:


On Sat, Mar 7, 2009 at 5:37 AM, PJ mailto:af.gour...@videotron.ca>> 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, tomorro




To do something like what you are describing above, you would do the following:

 $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. :-)






--
Jim Lucas

   "Some men are born to greatness, some achieve greatness,
   and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] insert array values

[PJ]

Jim Lucas wrote:
> PJ wrote:
>> 9el wrote:
>>> On Sat, Mar 7, 2009 at 5:37 AM, PJ >> > 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, tomorro
>>
>
>
> To do something like what you are describing above, you would do the
> following:
>
> 
> # 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);
>
> # 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.
Hi Jim & thanks for the suggestions. I'll try to figure them out...
looks complicated.

What I finally came up with was this: the $autoid was previously retrieved
$autoId = mysql_insert_id($result);

foreach($categoriesIN as $category){
print "$category";
$insert_category = "INSERT INTO book_categories (book_id, categories_id)
VALUES ($autoid, $category)";
mysql_query($insert_category,$db);
}
That did it. But it looks like the values are inserted as individual
arrays of 1 field each.

So now I have to figure out either how to convert the array to a number
or how to store & recover the fields to display in an html table with
hrefs to category pages... lot's of fun ... heh...heh...heh...
-- 
unheralded genius: "A clean desk is the sign of a dull mind. "
-
Phil Jourdan --- p...@ptahhotep.com
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

Re: [PHP] insert array values

[PJ]

Jim Lucas wrote:
> PJ wrote:
>   
>> 9el wrote:
>> 
>>> On Sat, Mar 7, 2009 at 5:37 AM, PJ >> > 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, tomorro
>>
>> 
>
>
> To do something like what you are describing above, you would do the 
> following:
>
> 
> # 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);
>
>   # 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 --- p...@ptahhotep.com
   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

Re: [PHP] insert array values

[Jim Lucas]

PJ wrote:
> 9el wrote:
>> On Sat, Mar 7, 2009 at 5:37 AM, PJ > > 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, tomorro
> 


To do something like what you are describing above, you would do the following:

 $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);

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

-- 
Jim Lucas

   "Some men are born to greatness, some achieve greatness,
   and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
by William Shakespeare

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] insert array values

[PJ]

9el wrote:
>
> On Sat, Mar 7, 2009 at 5:37 AM, PJ  > 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, tomorro

-- 
unheralded genius: "A clean desk is the sign of a dull mind. "
-
Phil Jourdan --- p...@ptahhotep.com
   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

Re: [PHP] insert array values

[9el]

On Sat, Mar 7, 2009 at 5:37 AM, PJ  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.

Re: [PHP] insert array values

[PJ]

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?



-- 
unheralded genius: "A clean desk is the sign of a dull mind. "
-
Phil Jourdan --- p...@ptahhotep.com
   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

Re: [PHP] insert array values

[PJ]

haliphax wrote:
> On Fri, Mar 6, 2009 at 4:07 PM, PJ  wrote:
>   
>> haliphax wrote:
>> 
>>> On Fri, Mar 6, 2009 at 3:00 PM, PJ  wrote:
>>>   
 I've been racking my little peanut-brain as well as big Google with
 little hope...
 I can retrieve the array from the multiple select dropdown box but I
 can't quite manage to insert the data which is just id numbers for a
 table.
 I've tried some while stuff but doesn't work. I know I don't have the
 right syntax on anything at this point. Here's an effort that doesn'
 work.
 Dont know if I should be using $categoriesIN[] type of stuff...
 but my tries were from web illustrations...
 the outcommented line returns the values of the array... but how to pass
 them to the query?

 foreach ($categoriesIN as $category) {
 Â  Â //"$category";
 $sql = "INSERT INTO book_categories ( book_id, category )
 Â  Â VALUES( book.id WHERE title = $titleIN, $category )";
 Â  Â  Â  Â $result = mysql_query($query, $db);;
 Â  Â }
 
>>> Phil, you don't want to use an INSERT if you're just updating an
>>> existing row. You can't do an INSERT and use the current row's values
>>> (book.id/book_id/whatever) in your insert, since there IS no current
>>> row. I'm assuming what you're trying to do is add a record to an
>>> associative table that links Categories to Books, but only if that
>>> Book title matches your $title string.
>>>
>>> Try this:
>>>
>>> foreach($categoriesIN as $category)
>>> $sql = "update book_categories set category = '$category' where
>>> book_id in (select id from book where title = '$titleIN')";
>>>
>>> If that's not what you're going for, and you just wanted to update
>>> something's category, try this:
>>>
>>> foreach($categoriesIN as $category)
>>> $sql = "update book_categories set category = '$category' where
>>> book_id = '$somevalue'";
>>>
>>> Perhaps it would help us help you if you would explain what it is
>>> you're trying to accomplish with this query?
>>>   
>> Hmmm, maybe I'm chasing my own tail...
>> following the trail of execution is tough... :'(
>> I'm trying to enter a new book into the database and that means I have
>> to enter the info for the relational tables as well.
>> I think I stumbled here a bit as what I need is the id that is inserted
>> in the db before inserting this category stuff.
>> As I look at the code, it looks like i have to find the
>> "last-entered-id" for the book that is being entered.
>> 
>
> Well, there are two ways you can do that with PHP/MySQL. First, you
> can do it on the DB server. Second, you can do it in PHP after your
> insert query has been performed.
>
> MySQL:
> SELECT LAST_INSERT_ID();
>
> PHP:
> mysql_query("insert into tablename(foo, bar) values('foo', 'bar');", $db);
> echo mysql_insert_id();
>
> If you're just trying to tie a book to existing categories, I would
> insert the book and then separately insert the records in the
> associative table (book_categories).
>
> Pseudocode:
> insert book into book table
> grab last inserted id with mysql_insert_id()
> for each category book is a part of
> {
>   insert book (via last inserted id) and category IDs into book_categories
> }
>
> If you're trying to insert a book and a category and then link them
> together, I would do this:
>
> Pseudocode:
> insert book into book table
> grab last inserted book id with mysql_insert_id()
> insert category into category table
> grab last inserted category id with mysql_insert_id()
> insert last book and last category IDs into book_categories
>
> Hope this helps,
>   
The problem is that every book may have several categories and I'm just
not getting them inserted, updated or whatever...
I understand the psuedocode (which for me is the logical thread of the
script) I just don't got the gits. ;-)

-- 
unheralded genius: "A clean desk is the sign of a dull mind. "
-
Phil Jourdan --- p...@ptahhotep.com
   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

Re: [PHP] insert array values

[PJ]

haliphax wrote:
> On Fri, Mar 6, 2009 at 4:07 PM, PJ  wrote:
>> haliphax wrote:
>>> On Fri, Mar 6, 2009 at 3:00 PM, PJ  wrote:
 I've been racking my little peanut-brain as well as big Google with
 little hope...
 I can retrieve the array from the multiple select dropdown box but I
 can't quite manage to insert the data which is just id numbers for a
 table.
 I've tried some while stuff but doesn't work. I know I don't have the
 right syntax on anything at this point. Here's an effort that doesn'
 work.
 Dont know if I should be using $categoriesIN[] type of stuff...
 but my tries were from web illustrations...
 the outcommented line returns the values of the array... but how to
 pass
 them to the query?

 foreach ($categoriesIN as $category) {
 Â  Â //"$category";
 $sql = "INSERT INTO book_categories ( book_id, category )
 Â  Â VALUES( book.id WHERE title = $titleIN, $category )";
 Â  Â  Â  Â $result = mysql_query($query, $db);;
 Â  Â }
>>> Phil, you don't want to use an INSERT if you're just updating an
>>> existing row. You can't do an INSERT and use the current row's values
>>> (book.id/book_id/whatever) in your insert, since there IS no current
>>> row. I'm assuming what you're trying to do is add a record to an
>>> associative table that links Categories to Books, but only if that
>>> Book title matches your $title string.
>>>
>>> Try this:
>>>
>>> foreach($categoriesIN as $category)
>>> $sql = "update book_categories set category = '$category' where
>>> book_id in (select id from book where title = '$titleIN')";
>>>
>>> If that's not what you're going for, and you just wanted to update
>>> something's category, try this:
>>>
>>> foreach($categoriesIN as $category)
>>> $sql = "update book_categories set category = '$category' where
>>> book_id = '$somevalue'";
>>>
>>> Perhaps it would help us help you if you would explain what it is
>>> you're trying to accomplish with this query?
>> Hmmm, maybe I'm chasing my own tail...
>> following the trail of execution is tough... :'(
>> I'm trying to enter a new book into the database and that means I have
>> to enter the info for the relational tables as well.
>> I think I stumbled here a bit as what I need is the id that is inserted
>> in the db before inserting this category stuff.
>> As I look at the code, it looks like i have to find the
>> "last-entered-id" for the book that is being entered.
>
> Well, there are two ways you can do that with PHP/MySQL. First, you
> can do it on the DB server. Second, you can do it in PHP after your
> insert query has been performed.
>
> MySQL:
> SELECT LAST_INSERT_ID();
>
> PHP:
> mysql_query("insert into tablename(foo, bar) values('foo', 'bar');", $db);
> echo mysql_insert_id();
>
> If you're just trying to tie a book to existing categories, I would
> insert the book and then separately insert the records in the
> associative table (book_categories).
>
> Pseudocode:
> insert book into book table
> grab last inserted id with mysql_insert_id()
> for each category book is a part of
> {
> insert book (via last inserted id) and category IDs into book_categories
> }
>
> If you're trying to insert a book and a category and then link them
> together, I would do this:
>
> Pseudocode:
> insert book into book table
> grab last inserted book id with mysql_insert_id()
> insert category into category table
> grab last inserted category id with mysql_insert_id()
> insert last book and last category IDs into book_categories
>
> Hope this helps,
Well, I did this but it don't woik...
foreach($categoriesIN as $category)
/* $sql = "update book_categories set category = '$category' where
book_id = '$autoid'";*/
$sql = "INSERT INTO book_categories ( book_id, categories_id )
VALUES ( '$autoid', '$category' )";
$result = mysql_query($sql, $db);
neither one; the INSER enter only the last value but misses the other 3
that were there; there should be like 4 book_ids with 1 and the
correspongind catgories_id should contain 1,2, 4,5 respectively...

-- 
unheralded genius: "A clean desk is the sign of a dull mind. "
-
Phil Jourdan --- p...@ptahhotep.com
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

Re: [PHP] insert array values

[haliphax]

On Fri, Mar 6, 2009 at 4:07 PM, PJ  wrote:
> haliphax wrote:
>> On Fri, Mar 6, 2009 at 3:00 PM, PJ  wrote:
>>> I've been racking my little peanut-brain as well as big Google with
>>> little hope...
>>> I can retrieve the array from the multiple select dropdown box but I
>>> can't quite manage to insert the data which is just id numbers for a
>>> table.
>>> I've tried some while stuff but doesn't work. I know I don't have the
>>> right syntax on anything at this point. Here's an effort that doesn'
>>> work.
>>> Dont know if I should be using $categoriesIN[] type of stuff...
>>> but my tries were from web illustrations...
>>> the outcommented line returns the values of the array... but how to pass
>>> them to the query?
>>>
>>> foreach ($categoriesIN as $category) {
>>> Â  Â //"$category";
>>> $sql = "INSERT INTO book_categories ( book_id, category )
>>> Â  Â VALUES( book.id WHERE title = $titleIN, $category )";
>>> Â  Â  Â  Â $result = mysql_query($query, $db);;
>>> Â  Â }
>>
>> Phil, you don't want to use an INSERT if you're just updating an
>> existing row. You can't do an INSERT and use the current row's values
>> (book.id/book_id/whatever) in your insert, since there IS no current
>> row. I'm assuming what you're trying to do is add a record to an
>> associative table that links Categories to Books, but only if that
>> Book title matches your $title string.
>>
>> Try this:
>>
>> foreach($categoriesIN as $category)
>> $sql = "update book_categories set category = '$category' where
>> book_id in (select id from book where title = '$titleIN')";
>>
>> If that's not what you're going for, and you just wanted to update
>> something's category, try this:
>>
>> foreach($categoriesIN as $category)
>> $sql = "update book_categories set category = '$category' where
>> book_id = '$somevalue'";
>>
>> Perhaps it would help us help you if you would explain what it is
>> you're trying to accomplish with this query?
> Hmmm, maybe I'm chasing my own tail...
> following the trail of execution is tough... :'(
> I'm trying to enter a new book into the database and that means I have
> to enter the info for the relational tables as well.
> I think I stumbled here a bit as what I need is the id that is inserted
> in the db before inserting this category stuff.
> As I look at the code, it looks like i have to find the
> "last-entered-id" for the book that is being entered.

Well, there are two ways you can do that with PHP/MySQL. First, you
can do it on the DB server. Second, you can do it in PHP after your
insert query has been performed.

MySQL:
SELECT LAST_INSERT_ID();

PHP:
mysql_query("insert into tablename(foo, bar) values('foo', 'bar');", $db);
echo mysql_insert_id();

If you're just trying to tie a book to existing categories, I would
insert the book and then separately insert the records in the
associative table (book_categories).

Pseudocode:
insert book into book table
grab last inserted id with mysql_insert_id()
for each category book is a part of
{
  insert book (via last inserted id) and category IDs into book_categories
}

If you're trying to insert a book and a category and then link them
together, I would do this:

Pseudocode:
insert book into book table
grab last inserted book id with mysql_insert_id()
insert category into category table
grab last inserted category id with mysql_insert_id()
insert last book and last category IDs into book_categories

Hope this helps,


-- 
// Todd

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] insert array values

[PJ]

haliphax wrote:
> On Fri, Mar 6, 2009 at 3:00 PM, PJ  wrote:
>> I've been racking my little peanut-brain as well as big Google with
>> little hope...
>> I can retrieve the array from the multiple select dropdown box but I
>> can't quite manage to insert the data which is just id numbers for a
>> table.
>> I've tried some while stuff but doesn't work. I know I don't have the
>> right syntax on anything at this point. Here's an effort that doesn'
>> work.
>> Dont know if I should be using $categoriesIN[] type of stuff...
>> but my tries were from web illustrations...
>> the outcommented line returns the values of the array... but how to pass
>> them to the query?
>>
>> foreach ($categoriesIN as $category) {
>> Â  Â //"$category";
>> $sql = "INSERT INTO book_categories ( book_id, category )
>> Â  Â VALUES( book.id WHERE title = $titleIN, $category )";
>> Â  Â  Â  Â $result = mysql_query($query, $db);;
>> Â  Â }
>
> Phil, you don't want to use an INSERT if you're just updating an
> existing row. You can't do an INSERT and use the current row's values
> (book.id/book_id/whatever) in your insert, since there IS no current
> row. I'm assuming what you're trying to do is add a record to an
> associative table that links Categories to Books, but only if that
> Book title matches your $title string.
>
> Try this:
>
> foreach($categoriesIN as $category)
> $sql = "update book_categories set category = '$category' where
> book_id in (select id from book where title = '$titleIN')";
>
> If that's not what you're going for, and you just wanted to update
> something's category, try this:
>
> foreach($categoriesIN as $category)
> $sql = "update book_categories set category = '$category' where
> book_id = '$somevalue'";
>
> Perhaps it would help us help you if you would explain what it is
> you're trying to accomplish with this query?
Hmmm, maybe I'm chasing my own tail...
following the trail of execution is tough... :'(
I'm trying to enter a new book into the database and that means I have
to enter the info for the relational tables as well.
I think I stumbled here a bit as what I need is the id that is inserted
in the db before inserting this category stuff.
As I look at the code, it looks like i have to find the
"last-entered-id" for the book that is being entered.

-- 
unheralded genius: "A clean desk is the sign of a dull mind. "
-
Phil Jourdan --- p...@ptahhotep.com
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

Re: [PHP] insert array values

[PJ]

Bastien Koert wrote:
> On Fri, Mar 6, 2009 at 4:00 PM, PJ  wrote:
>
>   
>> I've been racking my little peanut-brain as well as big Google with
>> little hope...
>> I can retrieve the array from the multiple select dropdown box but I
>> can't quite manage to insert the data which is just id numbers for a table.
>> I've tried some while stuff but doesn't work. I know I don't have the
>> right syntax on anything at this point. Here's an effort that doesn' work.
>> Dont know if I should be using $categoriesIN[] type of stuff...
>> but my tries were from web illustrations...
>> the outcommented line returns the values of the array... but how to pass
>> them to the query?
>>
>> foreach ($categoriesIN as $category) {
>>//"$category";
>> $sql = "INSERT INTO book_categories ( book_id, category )
>>VALUES( book.id WHERE title = $titleIN, $category )";
>>$result = mysql_query($query, $db);;
>>}
>> TIA
>>
>> --
>> unheralded genius: "A clean desk is the sign of a dull mind. "
>> -
>> Phil Jourdan --- p...@ptahhotep.com
>>   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
>>
>>
>> 
> As its an array, you could join the data with an IMPLODE into a string, but
> does that meet your needs? The other option is to create a sub-table for
> this element and insert each item thru a loop individually.
>
>   
The returned array holds numbers like 1 or 2 or 3 or whatever up to60+
I need to enter the id of book.id into book_categories.book_id and the
values from the array into book_categories.categories_id.
book_categories is an intersecting table with foreign keys relating book
to categories.
The entries need to show the categories that are related to the book.
So, if the book.id = 1 and the categories id are 2, 4 and 5 I have to see
book_id  |   categories_id
--
   1 |   2|
   1 |   4|
   1 |   5|
__

At least, I think so... :-)
>From what I understood from all the posts and tutorials and manuals was
that my code should work unless I have made some syntax boo-boo.
I would think that this should be a series of inserts with only the
$category or $categoryIN changing...

-- 
unheralded genius: "A clean desk is the sign of a dull mind. "
-
Phil Jourdan --- p...@ptahhotep.com
   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

Re: [PHP] insert array values

[haliphax]

On Fri, Mar 6, 2009 at 3:00 PM, PJ  wrote:
> I've been racking my little peanut-brain as well as big Google with
> little hope...
> I can retrieve the array from the multiple select dropdown box but I
> can't quite manage to insert the data which is just id numbers for a table.
> I've tried some while stuff but doesn't work. I know I don't have the
> right syntax on anything at this point. Here's an effort that doesn' work.
> Dont know if I should be using $categoriesIN[] type of stuff...
> but my tries were from web illustrations...
> the outcommented line returns the values of the array... but how to pass
> them to the query?
>
> foreach ($categoriesIN as $category) {
>    //"$category";
> $sql = "INSERT INTO book_categories ( book_id, category )
>    VALUES( book.id WHERE title = $titleIN, $category )";
>        $result = mysql_query($query, $db);;
>    }

Phil, you don't want to use an INSERT if you're just updating an
existing row. You can't do an INSERT and use the current row's values
(book.id/book_id/whatever) in your insert, since there IS no current
row. I'm assuming what you're trying to do is add a record to an
associative table that links Categories to Books, but only if that
Book title matches your $title string.

Try this:

foreach($categoriesIN as $category)
$sql = "update book_categories set category = '$category' where
book_id in (select id from book where title = '$titleIN')";

If that's not what you're going for, and you just wanted to update
something's category, try this:

foreach($categoriesIN as $category)
$sql = "update book_categories set category = '$category' where
book_id = '$somevalue'";

Perhaps it would help us help you if you would explain what it is
you're trying to accomplish with this query?


-- 
// Todd

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] insert array values

[Bastien Koert]

On Fri, Mar 6, 2009 at 4:00 PM, PJ  wrote:

> I've been racking my little peanut-brain as well as big Google with
> little hope...
> I can retrieve the array from the multiple select dropdown box but I
> can't quite manage to insert the data which is just id numbers for a table.
> I've tried some while stuff but doesn't work. I know I don't have the
> right syntax on anything at this point. Here's an effort that doesn' work.
> Dont know if I should be using $categoriesIN[] type of stuff...
> but my tries were from web illustrations...
> the outcommented line returns the values of the array... but how to pass
> them to the query?
>
> foreach ($categoriesIN as $category) {
>//"$category";
> $sql = "INSERT INTO book_categories ( book_id, category )
>VALUES( book.id WHERE title = $titleIN, $category )";
>$result = mysql_query($query, $db);;
>}
> TIA
>
> --
> unheralded genius: "A clean desk is the sign of a dull mind. "
> -
> Phil Jourdan --- p...@ptahhotep.com
>   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
>
>
As its an array, you could join the data with an IMPLODE into a string, but
does that meet your needs? The other option is to create a sub-table for
this element and insert each item thru a loop individually.

-- 

Bastien

Cat, the other other white meat

Re: [PHP] INSERT Array Values into mySQL Table

[Richard Lynch]

On Wed, May 2, 2007 10:42 am, Rahul S. Johari wrote:
> Here¹s the thing, I¹ve got an Array which has it¹s own set of Keys =>
> Values. I¹m using foreach() to read the Keys => Values like this:
>

//note added arguments!
> function pr1($var, $var2, $var3) {
> foreach ($var as $k => $v) {
> echo ³$k => $v²;

echo "$var2[$k] $var3[$k]";

//DID YOU USE mysql_real_escape_string YET?!
$query = "insert into whatever (v1, v2, v3) ";
$query .= " values('$v', '$var2[$k]', '$var3[$k]') ";

Or maybe $v is an array itself?

//perhaps you want:
list($v1, $v2, $v3) = each($v);


> }
>
> I need to INSERT the Values from this array into a  mySQL table. The
> problem
> is, There is other data that is going into the same row of that mySQL
> Table
> as well.
>
> So I have to basically insert 3 fields and then the rest of values I
> have to
> insert using this Array. In other words, this is what I have to do:
>
> INSERT into table  (f1, f2, f3, f4, f5) VALUES (Œ$1¹, Œ$2¹, Œ$3¹,
> //and the
> rest of the values from from the $v Values from that Array $var.
>
> I¹m not able to formulate a code to do this. Any help would be really
> appreciated.
>
> Thanks.
>
> ~~~
> Rahul Sitaram Johari
> CEO, Twenty Four Seventy Nine Inc.
>
> W: http://www.rahulsjohari.com
> E: [EMAIL PROTECTED]
>
> ³I morti non sono piu soli ... The dead are no longer lonely²
>
>
>
>
>
>
>


-- 
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] INSERT Array Values into mySQL Table

[Richard Lynch]

On Wed, May 2, 2007 11:03 am, Jim Moseby wrote:
>>
>> $sql = "INSERT INTO table (f1, f2, f3, ". implode(",",
>> array_keys($pr1)) .")
>>VALUES ('abc', '123', '456', ". implode(",",
>> array_values($pr1))
>> .")";
>>
>> HTH,
>>
>> Brad
>
>
> Might not work if the array values need to be enclosed in quotes.  I
> would
> build the query string in a foreach loop and execute the query
> afterwards.

It's pretty trivial to convert to:

VALUE ('abc', '123', '456', '" . implode("','", $array_values($pr1)) .
"')";

-- 
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some indie artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] INSERT Array Values into mySQL Table - SOLVED!

[Rahul Sitaram Johari]

WORKS Like A Charm!! :)

Thanks.


On 5/2/07 12:14 PM, "Brad Fuller" <[EMAIL PROTECTED]> wrote:

> Jim Moseby wrote:
>>> $sql = "INSERT INTO table (f1, f2, f3, ". implode(",",
>>>  array_keys($pr1)) .") VALUES ('abc', '123', '456', ". implode(",",
>>> array_values($pr1))
>>> .")";
>>> 
>>> HTH,
>>> 
>>> Brad
>> 
>> 
>> Might not work if the array values need to be enclosed in
>> quotes.  I would build the query string in a foreach loop and execute
>> the query afterwards.
>> 
>> JM
> 
> Good point, Jim.  I just realized that.  I actually use this method in one
> of my scripts; in my case all of the fields are varchar and the code below
> works perfectly.  This is updated to quote all values.
> 
> $sql = "INSERT INTO coreg_lead (".implode(",", array_keys($dbFields)).")
> VALUES ('".implode("','", array_values($dbFields))."')";
> 
> -Brad

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] INSERT Array Values into mySQL Table

[Brad Fuller]

Jim Moseby wrote:
>> $sql = "INSERT INTO table (f1, f2, f3, ". implode(",",
>>array_keys($pr1)) .") VALUES ('abc', '123', '456', ". implode(",",
>> array_values($pr1))
>> .")";
>> 
>> HTH,
>> 
>> Brad
> 
> 
> Might not work if the array values need to be enclosed in
> quotes.  I would build the query string in a foreach loop and execute
> the query afterwards. 
> 
> JM

Good point, Jim.  I just realized that.  I actually use this method in one
of my scripts; in my case all of the fields are varchar and the code below
works perfectly.  This is updated to quote all values.

$sql = "INSERT INTO coreg_lead (".implode(",", array_keys($dbFields)).")
VALUES ('".implode("','", array_values($dbFields))."')";

-Brad

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] INSERT Array Values into mySQL Table

[Rahul Sitaram Johari]

Ave,

I was just about to post when I saw your message. I think what you're saying
is exactly what is happening. The values have Spaces and stuff so Quotes are
Required - cannot have values not enclosed in Quotes. Other then that it was
actually working.

Is there a way to enclose in Quotes using the implode statement?



On 5/2/07 12:03 PM, "Jim Moseby" <[EMAIL PROTECTED]> wrote:

>> 
>> $sql = "INSERT INTO table (f1, f2, f3, ". implode(",",
>> array_keys($pr1)) .")
>>  VALUES ('abc', '123', '456', ". implode(",",
>> array_values($pr1))
>> .")";
>> 
>> HTH,
>> 
>> Brad
> 
> 
> Might not work if the array values need to be enclosed in quotes.  I would
> build the query string in a foreach loop and execute the query afterwards.
> 
> JM

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP] INSERT Array Values into mySQL Table

[Jim Moseby]

> 
> $sql = "INSERT INTO table (f1, f2, f3, ". implode(",", 
> array_keys($pr1)) .")
> VALUES ('abc', '123', '456', ". implode(",", 
> array_values($pr1))
> .")";
> 
> HTH,
> 
> Brad


Might not work if the array values need to be enclosed in quotes.  I would
build the query string in a foreach loop and execute the query afterwards.

JM

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] INSERT Array Values into mySQL Table

[David Giragosian]

On 5/2/07, Rahul S. Johari <[EMAIL PROTECTED]> wrote:



Ave,

Here¹s the thing, I¹ve got an Array which has it¹s own set of Keys =>
Values. I¹m using foreach() to read the Keys => Values like this:

   function pr1($var) {
   foreach ($var as $k => $v) {
   echo ³$k => $v²;
   }

I need to INSERT the Values from this array into a  mySQL table. The
problem
is, There is other data that is going into the same row of that mySQL
Table
as well.

So I have to basically insert 3 fields and then the rest of values I have
to
insert using this Array. In other words, this is what I have to do:

INSERT into table  (f1, f2, f3, f4, f5) VALUES (Œ$1¹, Œ$2¹, Œ$3¹, //and
the
rest of the values from from the $v Values from that Array $var.

I¹m not able to formulate a code to do this. Any help would be really
appreciated.




Either gather all the data together first, and then do an insert, or do an
insert on the 1st part, then update the record with the remaining data with
a where clause (Use last_insert_id() to track a primary key, or pass some
unique identifier from the insert to the update query). Personally, I'd find
a way to gather all the data first and do it all with one insert.

David

RE: [PHP] INSERT Array Values into mySQL Table

[Brad Fuller]

Rahul S. Johari wrote:
> Ave,
> 
> Here¹s the thing, I¹ve got an Array which has it¹s own set of
> Keys => Values. I¹m using foreach() to read the Keys => Values like
> this: 
> 
> function pr1($var) {
> foreach ($var as $k => $v) {
> echo ³$k => $v²;
> }
> 
> I need to INSERT the Values from this array into a  mySQL
> table. The problem is, There is other data that is going into
> the same row of that mySQL Table as well.
> 
> So I have to basically insert 3 fields and then the rest of
> values I have to insert using this Array. In other words, this is
> what I have to do: 
> 
> INSERT into table  (f1, f2, f3, f4, f5) VALUES (Œ$1¹, Œ$2¹,
> Œ$3¹, //and the rest of the values from from the $v Values from that
> Array $var. 
> 
> I¹m not able to formulate a code to do this. Any help would be really
> appreciated. 
> 
> Thanks.
> 
> ~~~
> Rahul Sitaram Johari
> CEO, Twenty Four Seventy Nine Inc.
> 
> W: http://www.rahulsjohari.com
> E: [EMAIL PROTECTED]
> 
> ³I morti non sono piu soli ... The dead are no longer lonely²


$sql = "INSERT INTO table (f1, f2, f3, ". implode(",", array_keys($pr1)) .")
  VALUES ('abc', '123', '456', ". implode(",", array_values($pr1))
.")";

HTH,

Brad

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php