Creating form for many-to-many relationship

[tanha]

Hi everyone,
I have a registration form for the book, which has two many to many relationships with Author and Category, So I don't know what is the best way to implement and to be user friendly.

If there is an example, please let me know and also share your idea...

NOTE:
I think AJAX would be suitable, but I wanna know how?
See attachment

[djjjozsi]

there is no need to use AJAX if the author / category aren't in relation.
many to many relations should stored with connector tables.

main tables
========
author table
category table
book table (neither author nor category info, as books can have multiples of both)

link tables
=======
author_book (e.g. id; book_id; author_id)
category_book (e.g. id; book_id; category_id)

Therefore as quoted above, no need for many to many tables nor ajax in form. However, you'll need inner joins on your SQL statements and a multiple select listbox for authors and categories. Perhaps AJax would be useful for selecting multiple authors or adding authors to a form (like a new dynamic list when adding attachments to an email).

[djjjozsi]

If you then make an update code, on the internet you cannot find a good trick to list selected properties from a many-many relation connector table :)

i've set the table names with variable names, to make this code re-useable:

<?php
include("dbconn.php");
$filename="books_update.php"; 

$table1 = "books";  //this table woulnd not used in this code, becouse the updater code removed
$idfield1 = "book_id"; //this ID field woulnd not used in this code, becouse the updater code removed

$filename="books_update.php";

/* many relations*/
$idfield2 = "author_id";
$table2 = "author";
$fieldname_2 = "author_name";

/* connector table to handle many properties*/
$conntablename = "author_book";
$connector_id1 = "book_id";
$connector_id2 = "author_id"; 


if ( isset( $_GET["edit"] ) ) {
/*
You need to select the other field's values from the books table...
*/

	/*  Build a select box from author based on the data from the connector table*/
    $sql2 = sprintf( "
SELECT `$idfield2`,`$fieldname_2`,if($table2.$idfield2 IN (select $connector_id2 from $conntablename where $conntablename.$connector_id1 =%d),1,0) AS is_in FROM `$table2`
;" , $_GET["edit"] );
    // print $sql2."<br />";
    print '<form method="post" action="'.$filename.'">';
    print "<input type=\"hidden\" name=\"id\" value=\"" . $_GET["edit"] . "\">";
    echo "<select size=\"5\" multiple=\"multiple\" name=\"ch[]\">";

    $result = mysql_query( $sql2 );
    while ( $rows = mysql_fetch_assoc( $result ) ) {
        $selected = empty( $rows["is_in"] )?"":" selected=\"selected\"";
        echo "<option value=\"{$rows["$idfield2"]}\"{$selected}>".htmlspecialchars($rows["$fieldname_2"])."</option>\n";
    }
    print "</select>\n";
    /*  Select ends...*/
    
    /*  other fields ... */
    print '<input type="submit" name="submit" value="submit">';
    print "</form>";
    die();
}
?>

[tanha]

Thanks for reply,
The tables are really what I have, but I think I need to use AJAX, because I have registered authors and also registered category in the list boxes, so what if I am in need to have new Author or Category, then I need to insert in place and then new author and category should be available in list boxes without page reloading.

I hope I could state what I meant..

main tables
========
author table
category table
book table (neither author nor category info, as books can have multiples of both)

link tables
=======
author_book (e.g. id; book_id; author_id)
category_book (e.g. id; book_id; category_id)

Therefore as quoted above, no need for many to many tables nor ajax in form. However, you'll need inner joins on your SQL statements and a multiple select listbox for authors and categories. Perhaps AJax would be useful for selecting multiple authors or adding authors to a form (like a new dynamic list when adding attachments to an email).

[tanha]

Hi and Thanks for reply,
Actually to have the list of author and category, I have no problem, because I have info and do the query and fill the list boxes according to the query, BUT

The problem is that if there is no author or category available, then I want to add them in place, not to go another page to add ones and then come back to book registration page and to see the author or category available.

If you then make an update code, on the internet you cannot find a good trick to list selected properties from a many-many relation connector table :)

i've set the table names with variable names, to make this code re-useable:

<?php
include("dbconn.php");
$filename="books_update.php"; 

$table1 = "books";  //this table woulnd not used in this code, becouse the updater code removed
$idfield1 = "book_id"; //this ID field woulnd not used in this code, becouse the updater code removed

$filename="books_update.php";

/* many relations*/
$idfield2 = "author_id";
$table2 = "author";
$fieldname_2 = "author_name";

/* connector table to handle many properties*/
$conntablename = "author_book";
$connector_id1 = "book_id";
$connector_id2 = "author_id"; 


if ( isset( $_GET["edit"] ) ) {
/*
You need to select the other field's values from the books table...
*/

	/*  Build a select box from author based on the data from the connector table*/
    $sql2 = sprintf( "
SELECT `$idfield2`,`$fieldname_2`,if($table2.$idfield2 IN (select $connector_id2 from $conntablename where $conntablename.$connector_id1 =%d),1,0) AS is_in FROM `$table2`
;" , $_GET["edit"] );
    // print $sql2."<br />";
    print '<form method="post" action="'.$filename.'">';
    print "<input type=\"hidden\" name=\"id\" value=\"" . $_GET["edit"] . "\">";
    echo "<select size=\"5\" multiple=\"multiple\" name=\"ch[]\">";

    $result = mysql_query( $sql2 );
    while ( $rows = mysql_fetch_assoc( $result ) ) {
        $selected = empty( $rows["is_in"] )?"":" selected=\"selected\"";
        echo "<option value=\"{$rows["$idfield2"]}\"{$selected}>".htmlspecialchars($rows["$fieldname_2"])."</option>\n";
    }
    print "</select>\n";
    /*  Select ends...*/
    
    /*  other fields ... */
    print '<input type="submit" name="submit" value="submit">';
    print "</form>";
    die();
}
?>

THat's no problem - you can add an author - send the form - add an author to the db - use the mysql_insert_id() to get the new author id from the db and use it in your SQL query to insert a book.

However, things get a little more complicated if you want to add multiple new authors.

[djjjozsi]

this is a really interesting question :)

you can find this useful.
http://phpcode.hu/teszt/ajax_listbox/
this is a part of a module from a form editor program, its a limited/standalone version.

Lets have a look at the generated code first, and try to intergrate it to your project. You just need to insert the category table's details, and you can get an ajax listbox and new item management too. If you generate two codes, there will be code parts which really makes the same.
i've tried to integrate a solution to build multiple listboxes at a same time, but its really easy to integrate the codes manually. This assumes that you already have the 'relation table', where the values populated.

Don't forget to set the correct unique KEYS on your fields to remove the duplications.

hello, jjozsi.

[tanha]

Hi,
Thanks for replying...

I found the solution, and it would be very nice and easy if we use the Prototype Java SCript Framework.

I did and it is working, but there is small problem, when I add a new category I see the new category in the list without page reloading or refreshing, BUT when I add new author I don't see the new author in the list, I dont know why?
I hope those who are experienced and use that framework help and guide me here.

Thanks.

[tanha]

Hi,
No one experienced the problem I mentioned?

[tanha]

any idea ...

[djjjozsi]

In the first watch:
Table 'XXXX.tbl_category' doesn't exist comes when you run the program?
in the dump there is no tbl_category table...

any idea ...

If you found another piece of code, and you don't know how to add the refresh feature, i suggest that open a new thread for it to ask.

I know the prototype is better then my suggestion, but at least mine is working, i use it evry day :) If you know what you're doing, one dropdown menu/table for it can build in 3 minutes.

There is a video on that page.

good luck.

[djjjozsi]

lets see this, i saw you code, and found that you did not used the prototype ajax refresh at all when you build the two select box.

If new entry added, you then couldn't able to refresh the select box.

lets see this, and its suggested to place the ajax processes in one .php file.

don't forget to use the mysql_real_escape_string()

http://phpcode.hu/dl/?f=1030

[tanha]

Thanks much for the code,

I will go through the code, and will post the result ...

[tanha]

yeah it is working as a charm.

Thanks

[tanha]

can i do the same thing with jQuery? I tried my best and I did open a separate thread for almost 4 days, but still i dont have the clue.