mySql query: Defining LIMIT with a variable

Ask about a PHP problem here.
Post Reply
salrathor1
Posts: 26
Joined: Tue Jun 21, 2011 2:23 am

mySql query: Defining LIMIT with a variable

Post 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
Tino
Posts: 360
Joined: Thu May 05, 2011 8:55 pm
Location: The Netherlands

Re: mySql query: Defining LIMIT with a variable

Post by Tino »

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

Post 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
bowersbros
Posts: 534
Joined: Thu May 05, 2011 8:19 pm

Re: mySql query: Defining LIMIT with a variable

Post by bowersbros »

post your actual code.. and use the

Code: Select all

 [/code ] 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
salrathor1
Posts: 26
Joined: Tue Jun 21, 2011 2:23 am

Re: mySql query: Defining LIMIT with a variable

Post 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.
Tino
Posts: 360
Joined: Thu May 05, 2011 8:55 pm
Location: The Netherlands

Re: mySql query: Defining LIMIT with a variable

Post 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?
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

Post 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.
Tino
Posts: 360
Joined: Thu May 05, 2011 8:55 pm
Location: The Netherlands

Re: mySql query: Defining LIMIT with a variable

Post 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.
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

Post by salrathor1 »

Hey,

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

Appreciated.
Sal
Post Reply