Linking dropdown to multiple tables

Hi,
you can add new field to the Users table where all selected hobbies will be stored. Set up this field as Lookup wizard --> Checkbox list on the "Edit as" settings dialog on the Visual Editor tab and then parse and save valeus in the Users_Hobbies table manually:

http://www.xlinesoft.com/phprunner/docs/select_multiple_values.htm

Thanks for the suggestion, I'll check it out.
One more question:

What Mysql field type should I use when creating this field?

Jane,
I did as you suggested and created a field called hobbies in the users table with data type varchar(64). The field now serves as the checkbox lookup field and is linked to the Hobbies table. It works somewhat now.
If I select only a single hobby then all goes well. But if I select more than 1 hobby then save the record, the checkbox list no longer reflects the selected items.
Upon further investigation I discovered that the users.hobbies field concatenates the saved hobbies id. So instead of saving the hobby ids as "1,5,6" the users.hobbies field saved then as "156". I suspect that I need an BeforeAdd event that will write the selected hobbies properly the users.hobbies field. Unfortunately, I can't find any example on how to process multiple checkbox selections before saving.

Hi,
It's difficult to tell you what's happening without seeing actual files.
Please publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with instructions on reproducing this error.

Jane, I am willing to do some further troubleshooting before I resort to uploading the code.

However, I have a couple of questions:

1. What data type (int, text, varchar, etc) should I use for lookup field in the users table?
   
2. Could you provide any code snippet that can parse the contents of the lookup field (array) BEFORE saving so
   that 1 5 8 is saved as "1,5,8"
   This information will me to fix this problem myself.
   Thanks.

Hi,

1. user varchar field type to save multiple values.
   
2. here is a sample code:
   
   http://www.xlinesoft.com/phprunner/docs/select_multiple_values.htm

Jane,
I finally found the php code and generator to accomplish my main objective outlined in my initial posting.
The method of managing checkboxes as is suggested/implemented in phprunner is not ideal as explained at the URL below:
http://jpipes.com/index.php?/archives/33-Managing-Many-to-Many-Relationships-in-MySQL-Part-1.html
The ideal method of saving and retrieving selected checkbox items is by using a connector or mapping table to manage the many-to-many relationship between mysql tables.

Unfortunately, phprunner does not presently use nor suggests this method in its documentation. Hopefully, this will be addressed in future releases.
Anyway, I found this amazing website generates the php code that easily manages selected items from a checkbox
http://phpcode.hu/teszt/checkbox_expert/
Below is the code that I generated for myself. You will see that code is pretty simple and effective. It also implements the many-to-many relationship via the recommended connector/mapping table. Now I just have massage the code to get it work within phprunner.
<code>
<?php

session_start();

// Save this file into phpconnect.php file

// This file generated by http://phpcode.hu/teszt/checkbox_expert/

// Please leave this message if you post the generated code on forums, or sell this program
/*

You don't need to run the table create SQL code, if you already have the 3 tables.

/
// / For the following details,

// / please contact your server vendor
$hostname = 'xxxxxxxx'; //// specify host, i.e. 'localhost'

$user = 'xxxxxxxx'; //// specify username

$pass = 'xxxxxxxx'; //// specify password

$dbase = 'xxxxxxxx'; //// specify database name

$connection = mysql_connect( "$hostname" , "$user" , "$pass" )

or die ( "Can't connect to MySQL" );

$db = mysql_select_db( $dbase , $connection ) or die ( "Can't select database." );

$table1 = "users"; //this table will be listed

$idfield1 = "user_id";

$filename="phpconnect.php";
/ many relations/

$idfield2 = "hobby_id";

$table2 = "hobbies";

$fieldname_2 = "name";
/ connector table to handle many properties/

$conntablename = "user2hobby";

$connector_id1 = "user_id";

$connector_id2 = "hobby_id";

print "<a href=\"$filename\">List</a><br />";
if ( !empty( $_POST["id"] ) ) {

$sql = sprintf( "delete from $conntablename WHERE $connector_id1=%d" , $_POST["id"] );

$res = mysql_query( $sql );
if ( !empty( $_POST["ch"] ) ) {

foreach( $_POST["ch"] AS $ids ) {

$sql = sprintf( "insert into $conntablename ($connector_id1,$connector_id2) VALUES('%d','%d')" , $_POST["id"] , $ids );

$res = mysql_query( $sql );

}

}
unset( $sql );

header( sprintf( "Location: $filename?edit=%d&ok=1", $_POST["id"] ) );

}
if ( isset( $_GET["edit"] ) ) {

if ( isset( $_GET["ok"] ) )

print "Updated succesfully<br />";

$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"] . "\">";

$result = mysql_query( $sql2 );

while ( $rows = mysql_fetch_assoc( $result ) ) {

$checked = empty( $rows["is_in"] )?"":" checked=\"checked\"";

echo "<input type=\"checkbox\" name=\"ch[]\" value=\"{$rows["$idfield2"]}\"$checked>{$rows["$fieldname_2"]}<br />";

}

print '<input type="submit" name="submit" value="submit">';

print "</form>";

die();

}

$query = "select from $table1";

$result = mysql_query( $query ) or die("Error in mysql query:" . mysql_error());
?>

<table width="520" border="1">

<tr>

<th align="center" valign="top" bgcolor="#CCCCCC" scope="col">Properties</th>

<th align="center" valign="top" bgcolor="#CCCCCC" scope="col">Firstname</th>

<th align="center" valign="top" bgcolor="#CCCCCC" scope="col">Lastname</th>

<th align="center" valign="top" bgcolor="#CCCCCC" scope="col">Email</th>

</tr>

<?php
if ( mysql_num_rows( $result ) > 0 ) {
while ( $one_row = mysql_fetch_assoc( $result ) ) {

print '<tr>';
?>

<th align="center" valign="top" bgcolor="#CCCCCC" scope="col"> <a href="?edit=<?php echo $one_row[$idfield1];

?>">Edit</a> </th>

<td align="center" valign="top"><?php echo !empty( $one_row["firstname"] )? htmlspecialchars( $one_row["firstname"] ):"&nbsp;"; ?></td>

<td align="center" valign="top"><?php echo !empty( $one_row["lastname"] )? htmlspecialchars( $one_row["lastname"] ):"&nbsp;"; ?></td>

<td align="center" valign="top"><?php echo !empty( $one_row["email"] )? htmlspecialchars( $one_row["email"] ):"&nbsp;"; ?></td>

</tr>

<?php
}

print '</table>';

} else

print "</table>no result";

/ End of listing/
/

--

-- Table structure for the connector table table user2hobby

--
CREATE TABLE user2hobby (

user_id int(11) NOT NULL,

hobby_id int(11) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--

-- Table structure for (second) table hobbies

--
CREATE TABLE hobbies (

hobby_id int(11) NOT NULL auto_increment,

name varchar(255) NOT NULL,

PRIMARY KEY (hobby_id)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
--

-- Table structure for table users

--
CREATE TABLE users (

user_id int(11) NOT NULL auto_increment,

firstname varchar(255) NOT NULL,

lastname varchar(255) NOT NULL,

email varchar(255) NOT NULL,

PRIMARY KEY (user_id)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
/
?>
</code>