MySQL query help

PHP
1

Hi,
I have a little problem. I have a website which I need to collect information from the database and present it. I’m using the same code I have on another page, with a few changes.

Here’s what I have:


// The query
$sql  = "SELECT * " .
    	" FROM `table`".
		" WHERE `this_address` LIKE '%$area_s%'".
		" AND `this_bedrooms` = '$beds'".
		" AND `this_price` <= '$maxprice'".
		" AND `sale` = '1'".
		" ORDER BY `$sort` $order".
		" LIMIT $limitvalue, $limit";

// Trying to find the problem
echo mysql_num_rows(mysql_query($sql));
echo $sql;
// The num rows returns the correct number
// The query is correct, tried in phpMyAdmin and results were correct

// Create a list
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
		$id = $row["this_id"];
		$number = $row["this_number"];
		$address = $row["this_address"];
		$postcode = $row["this_postcode"];
		$details = $row["this_details"];
		$bedrooms = $row["this_bedrooms"];
		$price = $row["this_price"];
		$image = $row["this_image"];

echo "all the info etc";
//PROBLEM: Only the first 2 results are displayed.
}

Can anyone see the reason only 2 results are displayed?

Thanks,
Rhys.

2

What is output when you echo the SQL query?

3

SELECT * FROM table WHERE this_address LIKE ‘%%%’ AND this_bedrooms = ‘1’ AND this_price <= ‘500000’ AND sale = ‘1’ ORDER BY this_price ASC LIMIT 0, 5

The query works perfectly when run from phpMyAdmin.

I’ve also found the 2 results that do display have prices of 185 and 290, where as all of the undisplayed have prices around 180000. The field is set a DECIMAL, but I can’t see any problem with the DB setup if the query runs from PHPMA.

Also if I order by price DESC, no results are displayed (unless in PHPMA).

Really confusing.

Thanks,
Rhys.

4

Something I just noticed, after the second list item is displayed, the rest of the page is cut off. That is, anything following the while loop is not displayed.
Still have no ideas though.

5

What is the $limitvalue ?

Off Topic:

You should try to avoid using the * in your select statement

6

Hi,
$limitvalue is 0.

It’s just this specific query that is failing on the loop. Others work fine. I’m just trying to figure why it stops and why on these certain results.

7

OK found the problem. It isn’t returning the results where the price is X00000. It will only return results at X00 price. Now to find why.

8

Finally narrowed it down to the echo of the price. It seems this function I made years ago is the problem:


function makecomma($input)
{

   if(strlen($input)<=3)
   { return $input; }
   $length=substr($input,0,strlen($input)-3);
   $formatted_input = makecomma1($length).",".substr($input,-3);
   return $formatted_input;
}

Problem found.

Cheers,
Rhys.