using LIKE with mysql

[coolicus]

My last question hopefully )

My search results page at present grabs all the data, I want it to only bring back depending on what the user types into a search field, so I guess I need to use LIKE

So for my results counter...

$query2  = "SELECT COUNT(*) as c FROM properties where city LIKE {$_GET['region_input_box']}";
$result2 = mysql_query($query2);
$returned = mysql_fetch_array($result2, MYSQL_ASSOC)

But this gives me the error Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource on the last line in the above code.

For the main results section...

//found out how many records in table in order to cut off when reach specified number
$query2=" SELECT * FROM properties  where city LIKE {$_GET['region_input_box']} ";
$result2=mysql_query($query2);
echo mysql_error();
$nume=mysql_num_rows($result2);


$query=" SELECT * FROM properties  where city LIKE {$_GET['region_input_box']} limit $eu, $limit ";
$result=mysql_query($query);
echo mysql_error();

but this gives off loads of errors

If it makes it easier to see, here is the entire code

<?php
require "dbconn.php";

//grab the number of results for the field below
$query2  = "SELECT COUNT(*) as c FROM properties where city LIKE {$_GET['region_input_box']}";
$result2 = mysql_query($query2);
$returned = mysql_fetch_array($result2, MYSQL_ASSOC)

?>

      There are <?php echo $returned['c'] ?> </span> results.

<?php

//paging
$page_name="search_results.php";
$start=$_GET['start'];
if(!($start > 0)) { 
$start = 0;
}
$eu = ($start - 0); 

if(!$limit > 0 ){ // if limit value is not available then let us use a default value
$limit = 4;    // No of records to be shown per page by default.
}                             
$this1 = $eu + $limit; 
$back = $eu - $limit; 
$next = $eu + $limit;

//found out how many records in table in order to cut off when reach specified number
$query2=" SELECT * FROM properties  ";
$result2=mysql_query($query2);
echo mysql_error();
$nume=mysql_num_rows($result2);



$query=" SELECT * FROM properties limit $eu, $limit ";
$result=mysql_query($query);
echo mysql_error();


while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
				if($style=='search_result_even'){$style='search_result_odd';}
    else{$style='search_result_even';}
    echo "<div class='$style'>{$row['name']}";
}

?> 
				

    </div> 

				
<?php
//display previous if there are previous
if($back >=0) { 
print "< <a href='$page_name?start=$back&limit=$limit'>Previous properties</a> | "; 
} 

//display page numbers
$i=0;
$l=1;
for($i=0;$i < $nume;$i=$i+$limit){
if($i <> $eu){
echo " <a href='$page_name?start=$i&limit=$limit'>$l</a> ";
}
else { echo "<b>$l</b>";}        
$l=$l+1;
}

//display next if there are next
if($this1 < $nume) { 
print "| <a href='$page_name?start=$next&limit=$limit'>Next properties</a>";} 



?>

 

[jpadie]

instead of this

$query2  = "SELECT COUNT(*) as c FROM properties where city LIKE {$_GET['region_input_box']}";
$result2 = mysql_query($query2);
$returned = mysql_fetch_array($result2, MYSQL_ASSOC)

try this

$query2  = "SELECT COUNT(*) as c FROM properties where city LIKE [red]'%[/red]{$_GET[region_input_box]}[red]%'[/red]";
$result2 = mysql_query($query2) [red] or die (mysql_error())[/red];
[red]$numRows = mysql_result($result2, 0, 0); [/red]

and instead of this

//found out how many records in table in order to cut off when reach specified number
$query2=" SELECT * FROM properties  where city LIKE {$_GET[region_input_box]} ";
$result2=mysql_query($query2);
echo mysql_error();
$nume=mysql_num_rows($result2);


$query=" SELECT * FROM properties  where city LIKE {$_GET['region_input_box']} limit $eu, $limit ";
$result=mysql_query($query);
echo mysql_error();

try

//found out how many records in table in order to cut off when reach specified number
$query2=" SELECT * FROM properties  where city LIKE [red]'%[/red]{$_GET[region_input_box]}[red]%'[/red] ";
$result2=mysql_query($query2) or die (mysql_error());

[red]//why are you doing this when you've got the result above
[/red]
$nume=mysql_num_rows($result2);

$query=" SELECT * FROM properties  where city LIKE [red]'%[/red]{$_GET[region_input_box]}[red]%'[/red] limit $eu, $limit ";
$result=mysql_query($query) or die (mysql_error());

generally the above was going wrong because you need to enquote sql variables. you should also ALWAYS escape the variables with mysql_escape_string() or mysql_real_escape_string(). I will leave you to integrate this.

the first solution is very much preferred to the second in terms of calculating number of rows.

however there is an even better method

$query2  = "SELECT [red]SQL_CALC_FOUND_ROWS[/red] * FROM properties where city LIKE [red]'%". mysql_real_escape_string($_GET['region_input_box'])."%'[/red]";
$result = mysql_query($query2) or die (mysql_error());
[red]$numrows = mysql_result(mysql_query('Select FOUND_ROWS()'), 0,0);[/red]
while ($row = mysql_fetch_assoc($result)){
 //do something with the output
}