Page 1 of 1
Need algorithmic help with a mysql question.
Posted: Tue Sep 20, 2011 2:41 pm
by drayarms
Hello folks, this is a purely conceptual question. Well I'm trying to display results from a mysql select query. I want to limit the number of returned rows to say 3, which would be what the user sees when he first loads the page. Then I want him to be able to increase the number of returned rows to say 10 simply by clicking a button. Something similar to facebook comments. The way I tried to solve this problem was to use a variable to set the limit on the mysql query, then AJAX to send a request to the server, to change the value of this variable when an onclick event takes place. This didn't seem to work. I don't know if it is because I didn't get the AJAX coding right, or simply because that concept is just plain wrong. If the latter is true, can someone give me any fresh ideas as to how to go about this problem? Thanks.
Re: Need algorithmic help with a mysql question.
Posted: Tue Sep 20, 2011 4:27 pm
by Celauran
Sounds like a good approach to me. Initial query is sent with LIMIT 3. Clicking "more" sends a second query with LIMIT 3, 7, whose results are appended to the element containing the first 3 results. What isn't working for you?
Re: Need algorithmic help with a mysql question.
Posted: Tue Sep 20, 2011 4:39 pm
by pickle
There's a lot that could be going wrong here. The first thing I would check is to make sure your AJAX request is working. Firebug is really helpful in determining if your request is formatted properly & sending the correct info.
Re: Need algorithmic help with a mysql question.
Posted: Tue Sep 20, 2011 10:49 pm
by drayarms
@pickle, i'll look into that. @ celauran, well the code just didn't work. maybe i didn't format it right. i'm not that good at AJAX. I'll include the code here and hopefully someone can tell me what i'm doing wrong.
somewhere at the top of the php page which queries and displays the result, i have this block of code that defines the limit variable(set_stream_limit).
Code: Select all
//Define the stream limit variabe.
if(isset($_POST["send_data"])) {
$set_stream_limit = $_POST["send_data"];
} else {$set_stream_limit = 3;}
Then here is the onclick event that's supposed to trigger the AJAX functions
Code: Select all
<a href ="javascript:;" onclick = "set_stream_limit('member.php' , 10);"> Set Limit </a>
And here is the AJAX bit.
Code: Select all
<script type="text/javascript">
function set_stream_limit(url, data_to_send){
var page_request = false;
if (window.XMLHttpRequest) page_request = new XMLHttpRequest();
else if (window.ActiveXObject) page_request = new ActiveXObject("Microsoft.XMLHttp");
else return false;
if (data_to_send) {
var send_data = 'send_data=' + data_to_send;
pageRequest.open ('POST' , url , true);
pageRequest.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
pageRequest.send(send_data);
} else {
pageRequest.open('GET, url, true);
pageRequest.send(null);
}
</script>
Re: Need algorithmic help with a mysql question.
Posted: Wed Sep 21, 2011 6:56 am
by Celauran
Have you called member.php?send_data=10 to ensure the PHP page is working correctly?
You're missing a closing quote here:
Code: Select all
pageRequest.open('GET, url, true);
Also, I don't see any callback functions for onreadystate change or, indeed, any mention of onreadystatechange at all. What are you doing with the responseText?
Finally, you might run into status = 0 problems when using the onclick event inside an anchor, which will also cause things to fall apart.
Re: Need algorithmic help with a mysql question.
Posted: Wed Sep 21, 2011 7:21 am
by Benjamin
This is not being engineered correctly because:
1. It's over complicated.
2. A second request to the server to only retrieve 7 records results in a poor user experience because there will be a slight delay.
3. Other technical issues depending on other aspects of the system.
An easier, and IMO more correct way to do this would be to simply return all 10 records. The last 7 can be hidden via CSS and all that is required is a dash of JS to display them. With all the time you've saved, go ahead and add some nice effects for good measure.
Re: Need algorithmic help with a mysql question.
Posted: Wed Sep 21, 2011 4:45 pm
by drayarms
@celauran, I skipped the onreadystate change and the response text part because the goal is not really to output anything from the server, just to post some data to the server. I'm not sure if my thinking is correct here, like I said, I'm still new to Ajax.
@benjamin, well i thought about that approach, but can't seem to figure out exactly how I can hide and show rows via css. As a matter of fact, the way I first went about the problem was to place the results in divs with fixed heights. Lets say each div hieght was 40px, I proceeded to use css to restrict the height of the parent div to 120 pixels by defualt and then with the onclick event, changed the parent div height to 400 pixels (for 10 rows). But then I noticed that this was a quite limiting approach and I the divs to be flexible so that the user won't be restricted as to how much content he can type it. Well, how can I display only the first 3 results: Would this be reasonable ?
print $row['column'][0]
print $row['column'][1]
print $row['column'][2]
And if that makes sense, how would I return the rest of the rows upon onclick?
Re: Need algorithmic help with a mysql question.
Posted: Wed Sep 21, 2011 5:09 pm
by Benjamin
The jQuery library would be the easiest. There are easy to use and implement functions such as hide(), show() etc. Your best bet would be to review the jQuery documentation and become familiar with it. It will be worth the time spent as there are many cases when you can use it on various projects.
Re: Need algorithmic help with a mysql question.
Posted: Thu Sep 22, 2011 2:33 pm
by califdon
I agree with Benjamin that jQuery would be the easy way and that it's well worth your learning. However, it is pretty simple to do it without, as well. In your PHP mysql_fetch loop, echo the <div> tags, using inline CSS attribute "display:inline;" for the first 3 results and "display:none;" for the rest. Then in your HTML include a Javascript function that sets all <div>s to display:inline. Or something close to that. It should only require a couple of lines of code.