Foreign relations with jQuery / PHP
Well that didn’t take long, unsurprisingly. I needed the interface to handle foreign relations, for this I would’ve liked to use conventions, for instance that all primary autoincrementing keys be named id. However the legacy database I’m working with won’t allow that, so we need configuration:
$this->foreign = array(
'user_id' => array(
'table' => 'users',
'label' => 'username',
'fid' => 'user_id'
)
);
function foreignAttr($col_name){
if(!empty($this->foreign[ $col_name ]))
echo "foreign=\"$col_name\"";
}The above will be used soon enough to get at the functionality we need. The foreign relation in this case is the user table (users) which is referenced through user_id which is also the name of its unique id column. The current table is user_points which will hold point entries containing the amount of points and a date when they were inserted.
The HTML has been slightly adjusted, we now add our own custom foreign attribute (using the above foreignAttr method):
<input class="add_input" <?php $this->foreignAttr( $row->Field ) ?> type="text" name="<?php echo $row->Field ?>"/>Let’s move on to the jQuery:
$("#add_form").find("input").keydown(function(event){
var cur = $(this);
if(cur.attr('foreign').length > 0){
if($("#el_list").length == 0){
cur.after('<select id="el_list"></select>');
$("#el_list").blur(function(){
var cur_id = $(this).find("option:selected").val();
cur.val( cur_id );
$(this).remove();
});
}
if(cur.val().length >= 2){
$.post(window.location.href, {value: cur.val(), func: 'getForeignList', fkey: cur.attr('foreign')}, function(res){
res = eval( '(' + res + ')' );
var str = '';
$.each(res, function(){
str += '<option value="'+this.el_id+'">'
+ this.el_label
+ '</option>';
});
$("#el_list").html(str);
});
}
}
});We are:
1.) Checking for all key presses in input boxes in the form with id add_form.
2.) Checking to se if we have the foreign attribute detailed above.
3.) If the current input has the foreign attribute and does not have a select box right next to it we create the select box and connect a function to its blur event. The function will get the value of the currently selected option and make it the value of its “parent” input box, and then remove the whole select box as it is not needed anymore.
4.) If the text in the current input box is longer than two characters (to avoid too many hits and subsequent entries in the select box) we post (by way of AJAX) the contents and the contents of the foreign attribute to the method getForeignList.
5.) The JSON result from PHP is used to generate the options in the select box.
And the getForeignList method:
function getForeignAttr($key){
return $this->foreign[ $_POST['fkey'] ][$key];
}
function getForeignList(){
$table = $this->getForeignAttr('table');
$label = $this->getForeignAttr('label');
$sql = "SELECT * FROM ".$this->getForeignAttr('table')." WHERE $label LIKE '%{$_POST['value']}%'";
$rarr = array();
foreach($this->sql->loadArray($sql) as $el)
$rarr[] = array('el_id' => $el[ $this->getForeignAttr('fid') ], 'el_label' => $el[ $label ]);
echo json_encode($rarr);
}Not much to add here, we run a simple SQL query to get the users whose username partially match what we just wrote in the input box and echo the JSON back.