Marking rows as favourites
Marking rows as favourites
I'm using Datatables with server-side processing to list some products. I'd like to allow users to mark individual products as favourites so they can sort/filter them in future by that marker. I have created a mysql table containing the product id and the site user id to link the existing product and user tables. What I'm not entirely clear on is how to add the appropriate querying to the server-side php to check if a row is a favourite for that user or not. I will then need to add something that enables users to add/remove the favourite for each row. Any help much appreciated.
This discussion has been closed.
Replies
It sounds like Alawrence is thinking much along the same lines as myself. What I think you need to do is three fold:
1. Send some extra information to the server-side processing script which will tell it to limit the result set to just the favourites, which you can do by passing an extra parameter: http://datatables.net/examples/server_side/custom_vars.html . That would be sourced from whatever UI control you have that says if you are viewing favourites only or not.
2. Modify the server-side processing script to take account of this new parameter. It should be as simple as adding to the WHERE block. i.e. AND mytable.favourite = true or whatever. It might be a little more complicated if you need a join - which it sounds like you might. Echoing out the SQL that has been generated is always useful to understand what is going on.
3. Have some Ajax handle which will pick up a click (or whatever) on a row or image in a row to mark it as a favourite and store that in the database. That is probably separate from the server-side processing script.
Regards,
Allan
I think a join will be required as I need to interrogate a second table (the favourites table). The process (in very basic terms will be):
Pass the userid to the server side php file. For each product table row, check the favourites table for a match to both productid and userid - if a row is found then output 'yes' to the favourite column, and render as a favourite icon. If the user clicks on the favourite icon, add or remove the associated row (productid and userid) and refresh the icon to the correct state.
It all sounds logical and relatively (deep breath) straightforward - but I may need some help.
Do either of you have a working example of the server side php with a join?
Allan
Allan
1. Create new db table with two fields - userid and productid. Make both primary keys and index on productid. This assumes you already have a user table and a product table.
2. Amend the server side database settings code as follows:
[code]/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
//add the userid field from the favourites table at the end of the array (or wherever you want it)
$aColumns = array( 'id','column1','column2','column3','column4','userid' );
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "id";
/* add the favourites table as a second string
$sTable = "products";
$sTable2 = "favourites";
/*get userid from the front end query string - you'll need this to check favourites later*/
$userid = ($_GET['user'] != "") ? $_GET['user'] : '0';
[/code]Don't forget to add your column in the front end settings too.
3. Amend the server side query code as follows:
[code]
/*
* SQL queries
* Get data to display
*/
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
LEFT JOIN
$sTable2
ON ($sTable2.productid = $sTable.id && $sTable2.userid = $userid)
$sWhere
$sOrder
$sLimit
";
[/code]
4. Amend the server side output code as follows:
[code]
/*
* Output
*/
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i
[code]
$('#fundlisting tbody tr td img.favicon').live( 'click', function () {
//addition of unique time stamp so each get request is unique
var r = new Date().getTime();
var nTr = this.parentNode.parentNode;
var aData = oTable.fnGetData( nTr );
$.get("../togglefav.php" + "?r=" + r,{fundid:aData[0],userid:"put the session user id here or you can get it at the back end"});
oTable.fnClearTable( 0 );
oTable.fnDraw();
});[/code]
Plus added a global ajax non-cache rule in the page header
[code]
$.ajaxSetup({
cache: false
});
[/code]
That seems to have sorted the issue.