making a search and counting and returning results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

making a search and counting and returning results

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

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