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:
[syntax=php]$query = "SELECT `post_id`, `user_name`, `user_comment`, `date` FROM `wall` ORDER BY `date` DESC LIMIT 0, 8"; [/syntax]
which works fine.
However, I wanted to allow users to be able to define the number of posts that are displayed.
So I tried,
[syntax=php]$query = "SELECT `post_id`, `user_name`, `user_comment`, `date` FROM `wall` ORDER BY `date` DESC LIMIT 0, '{$last}'"; [/syntax]
but this doesn't work. So the question is, what is the way to update the 'LIMIT' with a variable?
Thanks
Sal
mySql query: Defining LIMIT with a variable
-
salrathor1
- Posts: 26
- Joined: Tue Jun 21, 2011 2:23 am
Re: mySql query: Defining LIMIT with a variable
What you did should work... so long as you remove those quotes around {$last} 
Please check out my CodeCanyon items.
-
salrathor1
- Posts: 26
- Joined: Tue Jun 21, 2011 2:23 am
Re: mySql query: Defining LIMIT with a variable
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
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
-
bowersbros
- Posts: 534
- Joined: Thu May 05, 2011 8:19 pm
Re: mySql query: Defining LIMIT with a variable
post your actual code.. and use the [code ] [/code ] tags 
EDIT (Jacek): No use the [syntax ] [/syntax ] tags
EDIT (Jacek): No use the [syntax ] [/syntax ] tags
I don't like to brag, but I wasn't circumcised. I was circumnavigated.
Want to learn something new? Or maybe reinforce what you already know? Or just help out? Please subscribe to my videos: http://goo.gl/58pN9
Want to learn something new? Or maybe reinforce what you already know? Or just help out? Please subscribe to my videos: http://goo.gl/58pN9
-
salrathor1
- Posts: 26
- Joined: Tue Jun 21, 2011 2:23 am
Re: mySql query: Defining LIMIT with a variable
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
[syntax=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>
[/syntax]
connect.inc.php
[syntax=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}')");
}
?>[/syntax]
There you go.
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
[syntax=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>
[/syntax]
connect.inc.php
[syntax=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}')");
}
?>[/syntax]
There you go.
Re: mySql query: Defining LIMIT with a variable
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?
Is that enough to put you in the right direction?
Please check out my CodeCanyon items.
-
salrathor1
- Posts: 26
- Joined: Tue Jun 21, 2011 2:23 am
Re: mySql query: Defining LIMIT with a variable
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.
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
A default value? Yes, of course! You could do something along the likes of this.
[syntax=php]$value = 8;
if ( isset($_POST['value']) ) {
$value = (int) $_POST['value'];
}[/syntax]
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.
[syntax=php]$value = 8;
if ( isset($_POST['value']) ) {
$value = (int) $_POST['value'];
}[/syntax]
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.
Please check out my CodeCanyon items.
-
salrathor1
- Posts: 26
- Joined: Tue Jun 21, 2011 2:23 am
Re: mySql query: Defining LIMIT with a variable
Hey,
Thanks Tino. A simple solution that I probably would have overlooked due to lack of experience.
Appreciated.
Sal
Thanks Tino. A simple solution that I probably would have overlooked due to lack of experience.
Appreciated.
Sal