Originally Posted by ardav

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

Originally Posted by 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:

Help with Code Tags

php Syntax (Toggle Plain Text)

<?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();
}
?>

<?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(); } ?>