mySql query: Defining LIMIT with a variable

salrathor1 · Post by **salrathor1** » Tue Nov 29, 2011 7:03 pm

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

CodeCanyon items · Post by **Tino** » Tue Nov 29, 2011 9:48 pm

What you did should work... so long as you remove those quotes around {$last}

salrathor1 · Post by **salrathor1** » Tue Nov 29, 2011 11:13 pm

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

bowersbros · Post by **bowersbros** » Tue Nov 29, 2011 11:21 pm

post your actual code.. and use the

Code: Select all

 [/code ] tags :)

EDIT (Jacek): No use the [syntax ] [/syntax ] tags ;)

salrathor1 · Post by **salrathor1** » Wed Nov 30, 2011 12:05 am

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.

CodeCanyon items · Post by **Tino** » Wed Nov 30, 2011 12:10 am

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?

salrathor1 · Post by **salrathor1** » Wed Nov 30, 2011 12:23 am

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.

CodeCanyon items · Post by **Tino** » Wed Nov 30, 2011 7:39 am

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.

salrathor1 · Post by **salrathor1** » Wed Nov 30, 2011 3:56 pm

Hey,

Thanks Tino. A simple solution that I probably would have overlooked due to lack of experience.

Appreciated.
Sal

BetterPHP

mySql query: Defining LIMIT with a variable

mySql query: Defining LIMIT with a variable

Re: mySql query: Defining LIMIT with a variable

Re: mySql query: Defining LIMIT with a variable

Re: mySql query: Defining LIMIT with a variable

Re: mySql query: Defining LIMIT with a variable

Re: mySql query: Defining LIMIT with a variable

Re: mySql query: Defining LIMIT with a variable

Re: mySql query: Defining LIMIT with a variable

Re: mySql query: Defining LIMIT with a variable