Page 1 of 1

making a search and counting and returning results

Posted: Thu Feb 27, 2003 9:38 am
by deejay
Hi

As a newbie i realise my code may be faulty in a few areas. if you have time to look for anything that is obviously wrong to you, then i would be bvery grateful.

i am trying to search my mySQL database and return results.

I am getting this error - parse error, expecting `','' or `';'' -

on the following line

while ($i < $resultRowCount; $i++) {


here the function i have made

<?php

function searchInstallers($search)
{
global $search_keyword,$search;


// form has 2 tick boxes for keyword or postcode search

if($search == "keywords"){

/* i am looking for all mentions of any keyword ($keywords) in any table in the whole databse
as long as the installer_approved field is 'approved' */

$keyword_search = "SELECT * FROM ccMembers WHERE *='$keywords' && installer_approved='approved' ORDER BY user_id DESC";

//not sure if this line underneath is neccasary for count, just testing - hopefully will return a number


$resultRowCount = "SELECT COUNT * FROM ccMembers WHERE *='$keywords' && installer_approved='approved'";




$searchResults = mysql_query($keyword_search, $db) or die('error making query');

// at this point i am expecting the result to be a collection of rows


if ($resultRow)

echo ' Here are your search results:<br><br>';
echo '<table border="2" cellspacing="0" cellpadding="5" bgcolor="#99cccc" align="center" width="50%" bordercolor="#000066">
<tr>
<td height="30"><font class="normal">Installer
Name: </font></td>
<td><font class="normal">Comapny Name:</font>
</td>
<td><font class="normal">Address:</font> </td>
<td><font class="normal">Certificates:</font>
</td>
</tr>'

/* while statement will set up this run to be repeated until finish*/


/*having a problem is counting the row in search */


while ($i < $resultRowCount; $i++) {

// mysql_fetch_array is to take 1 result at a time and send to the follwing statement

$resultRow = mysql_fetch_array($searchResults);

// uses a multidimensional array to display results

echo '<tr bgcolor="#FFFFFF">
<td>'.$resultRow[$i]["firstName"].' '.$resultRow["lastName"].'</td>
<td height="30">'.$resultRow[$i]["company_name"].' </td>
<td height="30">'.$resultRow[$i]["address1"].', '.$resultRow["address3"].', '.$resultRow["postcode"].'</td>
<td height="30">'.$resultRow[$i]["installer_certificates"].'</td>
</tr>';


} //end while

echo '</table>';

elseif($search == "postcodes"){

// haven't started writing any code for this search yet
}
else

echo '<font class="normal">Please use the search form above or the UK Map above to the right. Your search results will appear here.</font>';


}
?>

thank you any help you may be able to give.

Posted: Fri Feb 28, 2003 2:07 am
by twigletmac
The problem is being caused by a missing semi-colon on the echo statement above the while loop however, there are a number of things other than this that will make this code not run as expected.

This is not correct syntax for the while loop:

Code: Select all

while ($i < $resultRowCount; $i++) {
You also have this:

Code: Select all

if ($resultRow)
before you've assigned anything to $resultRow and write the SQL to do a COUNT of the rows returned but don't run it:

Code: Select all

$resultRowCount = "SELECT COUNT * FROM ccMembers WHERE *='$keywords' && installer_approved='approved'";
There's no need for the second SQL statement anyway as you can just use mysql_num_rows() to find out how many there are.
why not try something like this for the function:

Code: Select all

<?php 

function searchInstallers($search) 
{ 
	// no need to make $search global if you're passing it in anyway
	global $search_keyword; 

	if ($search == 'keywords') { 

		$keyword_search = "SELECT firstName, lastName, company_name, address1, address3, postcode, installer_certificate FROM ccMembers WHERE *='$keywords' && installer_approved='approved' ORDER BY user_id DESC"; 

		$searchResults = mysql_query($keyword_search) or die('error making query');
		$resultRowCount = mysql_num_rows($searchResults);
		 
		if ($resultRowCount == 0) {
			echo 'There are no results.';
		} else {
			echo ' Here are your search results:<br><br>'; 
			echo '<table border="2" cellspacing="0" cellpadding="5" bgcolor="#99cccc" align="center" width="50%" bordercolor="#000066"> 
			<tr> 
			<td height="30"><font class="normal">Installer 
			Name: </font></td> 
			<td><font class="normal">Comapny Name:</font> 
			</td> 
			<td><font class="normal">Address:</font> </td> 
			<td><font class="normal">Certificates:</font> 
			</td> 
			</tr>';
			while ($resultRow = mysql_fetch_assoc($searchResults)) {
				echo '<tr bgcolor="#FFFFFF"> 
				<td>'.$resultRow['firstName'].' '.$resultRow['lastName'].'</td> 
				<td height="30">'.$resultRow['company_name'].' </td> 
				<td height="30">'.$resultRow['address1'].', '.$resultRow['address3'].', '.$resultRow['postcode'].'</td> 
				<td height="30">'.$resultRow['installer_certificates'].'</td> 
				</tr>'; 
			}
			echo '</table>'; 
		}
	} elseif ($search == 'postcodes'){ 
		// haven't started writing any code for this search yet
	} else {
		echo '<font class="normal">Please use the search form above or the UK Map above to the right. Your search results will appear here.</font>'; 
	}
}
On a side note, you really shouldn't have to be (or be) using FONT tags if you are using CSS.

Mac

Posted: Fri Feb 28, 2003 6:12 am
by deejay
thanks for that, was a great help.

I still have a problem with the sql querie. the problem is in using
WHERE *='$search_keyword'

and cannot search multiple fields, any ideas?

thanks again for the help

Posted: Fri Feb 28, 2003 6:18 am
by twigletmac
http://www.mysql.com/doc/en/SELECT.html

What you need to do is specify the fields you want to search on so that and separate them with ORs so you can have something like:

Code: Select all

...WHERE field1 = '$info' OR field2 = '$info' OR field3 = '$info'...
Mac