Marking rows as favourites

jimbob72

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.

Alawrence

You can probably do this just by editing the data source files query and use something along the lines of a left or right join assuming that the product ID exists in both the table for the user and the table that populated the page data. Basically at start say this is the user ID and if this user this user exists then tag these products. You could then have a checkbox or button that allowed the user to toggle the favorites view. So when checked the favorites would be tagged when unchecked the view would be as normal. There are probably many ways to do this but if you have built any type of custom search and filtering for the table it should be pretty simple to do most of this with a few additional variables passed to the data source file and some creativity with your SQL statement and output. I currently use checkboxes in a column to allow edits to multiple rows in one task. You could use the same idea to allow the user to add/remove favorites as well as identify products already marked. So if you go that route you could simply set the query to identify the products that have been chosen from the user table and then in turn using a CASE statement set a value to true or false. Then you could to your output and add a column that creates the checkbox with a value set true or false. Then on your HTML page have some JavaScript that identifies it and based on the value checks it or unchecks it. It's late so I know this may be rambling but hopefully it gives you some ideas lol. I like this idea by the way I think it would work well for me to allow users to track specific orders in a favorites type aspect.

allan

Hi jimbob72,

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

jimbob72

Thanks gents for your (very) early morning responses. The suggested process is pretty much what I was thinking - though I don't want to actually exclude any favourites, merely flag which ones the user has marked as a favourite (i.e. I wont filter out non-favourites, just mark the favourites and allow the user to sort by them).

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

Ah I see - sorry I misunderstood. So it's basically just another column you want - that probably makes life easier :-). There are a couple of examples of joins knocking around the forum (should be easy to find with the search) - but each of them is specific to the individual instance. It should be a case of just adding in the table names to the fields in the 'easy setup' stuff for the demo scripts and and making the table name something like $sTable = 'table1, table2';. You'll also need to add the join expression to the $sWhere parameter to make sure the join happens as you would expect.

Allan

jimbob72

Have got it working showing favourites from a separate table. I was in the process of posting my code here but it chucked me out and when i tried to go back my post had been wiped. So will have to wait until i've added the clickable add/remove functionality.

allan

Cool - good to hear you got it going. Look forward to seeing your code (assuming the forum behaves itself... :-/ ).

Allan

jimbob72

Ok, here goes. How to add a favourites option for server side processing.

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

Alawrence

Awesome glad to see that you got it to work. Now that your past that you'll think of three other things that would be nice based off the idea and the cycle will begin again lol.

jimbob72

Indeed ;-) One thing this did throw up was an annoying caching issue in IE. It was storing the GET requests so that favourites were returning to an old state after refreshing the browser/revisiting the page. I've since added a unique time string to the URL:
[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.