PJ wrote:
Jochem Maas wrote:
PJ schreef:
Seems it should be simple, but how does one extract values from an array
and assign them to a variable?
foreach ($categoriesIN as $category) {
1. if $categoriesIN comes from a POST, use $_POST['categoriesIN'] instead.
It does, but results are the same. Maybe it has something to do with
better yet use the filter extension (part of the core) to retrieve the

2. validate & santize your input (the reason to use the filter extension)
I imagine it is duplicate work but since I am learning and want to
concentrate on 1 thing at a time, I am leaving the sanitizing part as
the lest task before validating the whole script (page).
3. WHY do you want to extract the values and assign them to vars?
shows you I don't know what i'm doing...
Her is what finally worked and that's where I learned that the values
are saved as arrays. =-O
$autoId = mysql_insert_id($result);// this was recovered earlier in page
foreach($categoriesIN as $category){
print "$category<br />";
$insert_category = "INSERT INTO book_categories (book_id, categories_id)
VALUES ($autoid, $category)";

Here's the problem...
the values saved interesect the book_variables with variables and books.
I believe the variabless are saved as arrays (1 entry per fiels -e.g.
there are 4 categories for 1 book) I have to recover the names of the
categories (field category in categories table) and display them in an
HTML table with hrefs to the correesponding pages. What I am trying to
figure out is how to select and echo the categories for each book.

here is part of the select statement concerning the categories:
(the rest of the select works, but since there are several categories,
my table shows several entries for 1 book that has several categories)
LEFT JOIN book_publisher as abc ON b.id = abc.bookID
LEFT JOIN publishers AS c ON abc.publishers_id = c.id
LEFT JOIN book_categories AS d ON b.id = d.book_id
LEFT JOIN categories AS e ON d.categories_id = e.id
ORDER BY title ASC ";

I suppose that
LEFT JOIN categories AS e ON d.categories_id = e.id
needs some qualifier that will output the categories as
cat1, cat2, cat3, cat4... for count($rows)... or something like that; in
other words, as 1 row instead of 1 row per category.
Hope I'm not too confusing.
since you don't know the number of entries there is no point in creating
a stack of variables of which you have no idea what they are even called
or how many there are. instead loop the array or use an index into the
array to find the data your looking for. you don't have to create a
specific variable to use a piece of data the given element of an array
is just as valid.

4. there is the extract() function ... use at your own risk.
5. do something like this:

foreach ($array as $key => $val)
$$key = $val;

.. again use at your own risk.

echo "$category<br>";
foreach( $_POST['categoriesIN'] as $key => $value) {
echo "value = $value<br>";
both show the values entered in the form but do not create a variable.
Since the number of entries will vary, I cannot assign a number of
variable names before knowing how many entries there may be.

After I sent that last email a few hours ago, I started thinking that you were 
using a JOIN in your SQL.

Since you were complaining that you were getting duplicate entries for each book that had more then one category assigned to it.

The problem is with the JOIN.

What you need to do it execute 1 SQL call to the books table and 1 call to the 
book_categories table.

All the following code it typed directly into my email client.  It is 
completely untested.

Hopefully it works for you.


# Do your db setup...
$dblink = mysql_connect(.....);

# call to your books table (without a join to the books_categories table)
$SQL = "SELECT id, title, author, etc... FROM books ORDER BY author, title LIMIT 

$books = array();

if ( ( $results = mysql_query($SQL, $dblink) ) !== false ) {

        # loop through results, place in array.
        while ( $row = mysql_fetch_assoc($results, $dblink) ) {
                $books[] = $row;

        # we must have one book, otherwise this will return false and we would 
not need to call for other information
        if ( $books ) {

                $book_categories = array();

                # call to your books_categories table
                # ( do an ORDER BY in your SQL so the categories always show up 
in the same order)
                $SQL = "SELECT book_id, cat_id FROM books_categories ORDER BY 

                # loop through results, place in array
                if ( ( $results = mysql_query($SQL, $dblink) ) !== false ) {
                        while ( $row = mysql_fetch_assoc($results) ) {
                                $book_categories[$row['book_id']][] = 

                # we must have one book2cat association, otherwise this will 
return false
                if ( $book_categories ) {

                        $categories = array();

                        # call to your categories table
                        # ( do an ORDER BY in your SQL so the categories always 
show up in the same order)
                        $SQL = "SELECT id, category_name, etc... FROM 

                        # loop through results, place in array
                        if ( ( $results = mysql_query($SQL, $dblink) ) !== 
false ) {
                                while ( $row = mysql_fetch_assoc($results) ) {
                                        $categories[$row['id']] = $row;




# Ok, now that all of that is out of the way, lets display the results...

if ( $books ) {
        # Open results table
        echo '<table>';
        # Loop through data
        foreach ( $books AS $book ) {

                # Display book information
                echo '<tr><td>Title: ', $book['title'], '<br />Author: ', $book['author'], 

                # Check to see if the book has associated categories
                if ( isset($book_categories[$book['book_id']]) ) {

                        # loop through associated categories
                        foreach ( $book_categories[$book['book_id']] AS $cat_id 
) {

                                if ( isset( $categories[$cat_id] ) ) {
                                        echo $categories[$cat_id]['category_name'], 
"<br />\n";
                                } else {
                                        echo "Unrecognized category ID: '", $cat_id, 
"'<br />\n";

                } else {
                        echo 'No associated categories';

                echo '</td></tr>';          

        echo '</table>';
} else {
        # If no books are found, tell them...
        echo 'No books found';


Wow, now that took one foot work. I combed over the past couple threads that you started to collect all the information for the above code.

obviously you will need to change the SQL statements a little, but the code should pretty much work once you update the SQL for your actual column names and the related bits to that display code.

Hope this points when you should and (in this case) should not use a JOIN in 
your SQL.


Jim Lucas

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

Reply via email to