Selecting a random row, excluding a particular id

[chibineku]

I have a PHP script which selects a random row from a database and echoes a formatted string. This is used at page load to display a random testimonial and the same script is used in an AJAX function to swap the testimonial every 5 seconds. I don't want to get the same testimonial twice in a row, so as part of the output there is a hidden span containing the id of the result row. I send this as a parameter in my AJAX function and want to exclude this id from the next select statement.Here is my current PHP script, with SELECT statement:

<?phpinclude_once("db_include.php5");doDB();$not = (isset($_GET["counter"]))?"":"WHERE id NOT IN (".$_GET['counter'].")";$rand_sql = "SELECT * FROM edhypTestimonials $not ORDER BY RAND() LIMIT 1";$rand_res = mysqli_query($mysqli, $rand_sql) or error_log(mysqli_error($mysqli)."\r\n");$info = mysqli_fetch_array($rand_res);	  $search_res = '';		$id = $info["id"];		$name = $info["name"];		$testimony = $info["testimony"];		$search_res.= "<p><span class='counter' style='display: none;'>$id</span><span class='quote'>\"</span>$testimony<span class='quote'>\"</span><span class='name'>-$name</span></p>";echo $search_res;?>

And here is the AJAX:

$(document).ready(function() {   setTimeout("getTestimonial()", 5000);});   function getTestimonial() {	 var counter = $('#products #cart-widget .counter').html();	$.get("testimonials_include.php5", {'counter':counter}, function(data) {	   $('#products #cart-widget p').replaceWith(data);	});	setTimeout("getTestimonial()", 5000);   }

I am watching the console in Firebug and can see that the counter parameter is sent, but there must be something wrong with the SELECT as the same testimonial sometimes comes up several times in a row (there are only two entries in the database at present, just placeholders).I'm not good at making SQL queries but it doesn't seem like it should be any more complicated than that. Can anyone point me in the right direction?Additionally, the first time the script runs, as an include, it doesn't properly fetch a result, only once it's called by the jQuery function.

[jeffman]

A way to debug AJAX scripts is to call them from the URL box in your browser. Since it's a GET request, add a query string that looks like your counter. Now you're simulating the whole process and don't have to funnel it through AJAX, which can be a hassle.Add an echo statement to the script and actually see what $rand_sql looks like. Make sure it's not malformed somehow.

[chibineku]

That's a good idea, DD, and I tried it, setting counter=1. As with the live page, the script seems to ignore the counter parameter and just delivers me a random result, which can have the same id as the counter parameter. Echoing $not made me realise that my ternary operator assignment wasn't working for some reason, but I changed it for a basic if and it works great now. I can't see what was wrong with the ternary statement, but anyhoo.Many thanks for the sound advice - I don't know why I don't already do these things, like echoing variables, as soon as I have a problem. I should know better by now.

[jeffman]

Well, as I said, the echo process becomes a hassle in AJAX apps, so I think a lot of developers skip a few steps. I've just discovered a few workarounds that maybe hadn't occurred to you yet. Like, you can do the same thing I suggested with a script that uses POST data if you add a temporary line at the top of your script that does this:$_POST = $_GET;Now you can run the script with data sent in the URL, as long as it is formatted like the POST data your AJAX object would send.Life's a lot easier when you can debug the different parts of an AJAX session separately.

[thescientist]

I think this is what you were looking for (if its anything like the Javascript usage):

(isset($_GET["counter"])) ? $not = "WHERE id NOT IN (".$_GET['counter'].")" : $not = "";

if the counter is present, then exclude it from the query, else set it to empty so it will include any random testimonial