Page 1 of 1
mySql query: Defining LIMIT with a variable
Posted: Tue Nov 29, 2011 7:03 pm
by salrathor1
Hey guys,
I've just been playing around with a quick comment posting script I wrote. It basically allows users to post a message with a name and comment, and shows the last 10 comments underneath sorted by 'date' order.
the query is:
$query = "SELECT `post_id`, `user_name`, `user_comment`, `date` FROM `wall` ORDER BY `date` DESC LIMIT 0, 8";
which works fine.
However, I wanted to allow users to be able to define the number of posts that are displayed.
So I tried,
$query = "SELECT `post_id`, `user_name`, `user_comment`, `date` FROM `wall` ORDER BY `date` DESC LIMIT 0, '{$last}'";
but this doesn't work. So the question is, what is the way to update the 'LIMIT' with a variable?
Thanks
Sal
Re: mySql query: Defining LIMIT with a variable
Posted: Tue Nov 29, 2011 9:48 pm
by Tino
What you did
should work... so long as you remove those quotes around {$last}
Re: mySql query: Defining LIMIT with a variable
Posted: Tue Nov 29, 2011 11:13 pm
by salrathor1
Hey, thanks for you're reply.
You're right, I just tried it and it does work without the apostrophes if I use a variable. However, i'm also trying to allow users to select the number of posts to be displayed.
If I create an <input type="input" name="display" value="8" /> and use $display = "$_POST['display'] and try to set {$display} into the query it doesn't seem to work.
Any ideas? The error message is:
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /Applications/XAMPP/xamppfiles/htdocs/test/index.php on line 30
It seems like the value="8" is not setting the variable and that's why the sql query is failing, but i'm pretty new at all this stuff.
and thanks for your previous message.
Sal
Re: mySql query: Defining LIMIT with a variable
Posted: Tue Nov 29, 2011 11:21 pm
by bowersbros
post your actual code.. and use the
Code: Select all
[/code ] tags :)
EDIT (Jacek): No use the [syntax ] [/syntax ] tags ;)
Re: mySql query: Defining LIMIT with a variable
Posted: Wed Nov 30, 2011 12:05 am
by salrathor1
Hey,
This is me practicing so it's not although great. Saying that, even if it was my best attempt it probably wouldn't be all that great.
Index.php
<?php
include ('connect.inc.php');
$display = $_POST['display'];
if (isset($_POST['name'], $_POST['comment'])) {
add_comment($_POST['name'], $_POST['comment']);
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://wwww.w3.org/TR/xhtml1/DTD/dhtml1-strict.dtd">
<html xlmns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title> </title>
</head>
<body>
<form action="" method="POST">
Name: <p><input type="text" name="name" /></p>
Comment: <p><textarea name="comment" rows="10" cols="100"></textarea></p>
Number of posts <p><input type="text" name="display" /> </p>
<p><input type="submit" value="Submit Comment" /></p>
</form>
<p>
<?php
$query = "SELECT `post_id`, `user_name`, `user_comment`, `date` FROM `wall` ORDER BY `date` DESC LIMIT 0, {$display}";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
// Gather all $row values into local variables for easier usage in output
echo '<strong>Date: </strong>',$row["date"].'<br />';
echo '<strong>Name: </strong>',$row["user_name"].'<br />';
echo '<strong>Comment: </strong>',$row["user_comment"].'<br />';
echo '<hr />';
}
?>
</p>
</body>
</html>
connect.inc.php
<?php
mysql_connect('localhost','root','');
mysql_select_db('wall');
function add_comment($name, $text) {
$name = mysql_real_escape_string($name);
$text = mysql_real_escape_string($text);
$date = date("D j M Y (G:i:s)");
mysql_query("INSERT INTO `wall` (`user_name`, `user_comment`, `date`) VALUES ('{$name}', '{$text}', '{$date}')");
}
?>
There you go.
Re: mySql query: Defining LIMIT with a variable
Posted: Wed Nov 30, 2011 12:10 am
by Tino
You are trying to set $display even before you have submitted the form. As such, the query fails, becaus $display doesn't have a value yet when the query is being run.
Is that enough to put you in the right direction?
Re: mySql query: Defining LIMIT with a variable
Posted: Wed Nov 30, 2011 12:23 am
by salrathor1
Hey,
Yea kind of, but is there a way to set a default value? I tried to use value="8" to define 8 as default but also allow users to also change that figure.
If there is i'd appreciate the answer. If not i'll have a think about it and try something different.
Thanks for the info.
Re: mySql query: Defining LIMIT with a variable
Posted: Wed Nov 30, 2011 7:39 am
by Tino
A default value? Yes, of course! You could do something along the likes of this.
$value = 8;
if ( isset($_POST['value']) ) {
$value = (int) $_POST['value'];
}
This checks if a value has been submitted. If that's the case, it will use that value (the (int) makes sure the value is an integer), otherwise it will use the default value you set at the top, which would be 8 in this case.
Re: mySql query: Defining LIMIT with a variable
Posted: Wed Nov 30, 2011 3:56 pm
by salrathor1
Hey,
Thanks Tino. A simple solution that I probably would have overlooked due to lack of experience.
Appreciated.
Sal