Displaying links the user hasn't clicked.

[zampu]

ok i need my mysql query to only pull links that the user hasn't clicked.  say there is 1000 links.  and i don't want the clicked ones reapearing.  so far i have

$query=mysql_query("SELECT link FROM users ORDER BY points ASC LIMIT 0,10");

as my query, but what would i add to make it only pull the ones not clicked.  i tried adding a field called clicked and making the query check if clicked = no, but then once one link is clicked, it doesnt display any because clicked=yes for that user.  See what i mean?

 

Any help is appreciated, thanks.

[chigley]

You can insert a pipe-separated "clicked" field, select them all with the query then filter with PHP.

 

<?php

$query = mysql_query("SELECT link, selected FROM users ORDER BY points ASC LIMIT 0, 10") or die(mysql_error());

while($row = mysql_fetch_row($query) {

$link = $row[0];
$selected = $row[1];

$pieces = explode("|", $selected);

if(!in_array($link, $pieces)) {
  echo $link;
}

}

?>

 

Not the most effective way, but it'll work. When marking as selected add [pre]|link[/pre] to the end of the select field.

 

Example select field: [pre]google|msn|phpfreaks[/pre]

[zampu]

Thanks for the help, but im really lost, so I would use that code, but then do what to my DB tables?

[per1os]

The easier way to do it is create a new table called clicked_links  in this table house the linkid and the user id. If there is a record in the table for that user and or that link then they have clicked. If not they havn't clicked it. From there you can build a query to pull all the clicked link ids for that user and use them to pull all non-clicked link ids and display those.

 

The problem is you need another table to make life much easier. It would be really easy if mysql allowed SubQuery's in versions prior to 4, but they don't. Oh well.

 

Hope that helps.

[chigley]

Add a long field called "clicked" to the users table. Commented:

 

<?php

$query = mysql_query("SELECT link, clicked FROM users ORDER BY points ASC LIMIT 0, 10") or die(mysql_error()); // Select the link and the links already clicked from the database 

while($row = mysql_fetch_row($query) { // In effect: foreach row returned...

$link = $row[0]; // Do I really need to explain this..?
$clicked = $row[1]; // ^^.....

$pieces = explode("|", $clicked); // Make an array called $pieces containing all the different links already clicked

/*
eg.
  $clicked = "google|msn|phpfreaks"; - these are fetched from the database
  ...explode..
  $pieces = array("google", "msn", "phpfreaks");
*/

if(!in_array($link, $pieces)) { // If the link we are currently outputting hasn't already been clicked...
  echo $link; // ... output it
}

}

?>

[zampu]

Thanks guys i'll give them a try and get back to ya.

[zampu]

Ok i got this worked out, but then how would my update query be? 

[chigley]

<?php

$addidion = "|phpfreaks";

$query = mysql_query("SELECT clicked FROM users WHERE id = 1") or die(mysql_error()); // Not sure if you need the WHERE clause here...
$row = mysql_fetch_row($query);
$clicked = $row[0];

$new = $clicked.$addition;

$query = mysql_query("UPDATE users SET clicked = '{$new}' WHERE id = 1") or die(mysql_error()); // Same again...

?>

 

 

[zampu]

I'll give it a try, and reply!