Need Help with a Search Query

[noise]

Hi

 

First of all I'm very new with php and mysql queries and would appreciate all the help i can get.

 

I have managed to take the following search script sample from the internet and changed a few fields to do what i want.

 

http://www.webreference.com/programm...rch/index.html

 

By changing the following script I achieved to query the the first name and lastname and display all the other added tables I added as well. Please see my changed code below.

 

You may search either by first or last name
<form method="post" action="search.php?go" id="searchform">
  <p>
  <input type="text" name="name">
</p>
  <p>
    <input type="submit" name="submit" value="Search">
    </p>
</form>
<?php

if(isset($_POST['submit'])){
if(isset($_GET['go'])){
if(preg_match("/[A-Z | a-z]+/", $_POST['name'])){
$name=$_POST['name'];

//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error()); 

//-select the database to use
$mydb=mysql_select_db("fresheye_staff");

//-query the database table
$sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%" . $name . "%' OR LastName LIKE '%" . $name ."%'";

//-run the query against the mysql query function
$result=mysql_query($sql);

//-count results

$numrows=mysql_num_rows($result);

echo "<p>" .$numrows . " results found for " . stripslashes($name) . "</p>"; 

//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){

$FirstName =$row['FirstName'];
$LastName=$row['LastName'];
$ID=$row['ID'];

//-display the result of the array

echo "<ul>\n"; 
echo "<li>" . "<a href=\"search.php?id=$ID\">"  .$FirstName . " " . $LastName . "</a></li>\n";
echo "</ul>";
}
}
else{
echo "<p>Please enter a search query</p>";
}
}
}

if(isset($_GET['by'])){
$letter=$_GET['by'];

//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error()); 

//-select the database to use
$mydb=mysql_select_db("fresheye_staff");

//-query the database table
$sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%" . $letter . "%' OR LastName LIKE '%" . $letter ."%'";


//-run the query against the mysql query function
$result=mysql_query($sql); 

//-count results
$numrows=mysql_num_rows($result);

echo "<p>" .$numrows . " results found for " . $letter . "</p>"; 

//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){

$FirstName =$row['FirstName'];
$LastName=$row['LastName'];
$ID=$row['ID'];

//-display the result of the array

echo "<ul>\n"; 
echo "<li>" . "<a href=\"search.php?id=$ID\">"  .$FirstName . " " . $LastName . "</a></li>\n";
echo "</ul>";
}
}

if(isset($_GET['id'])){
$contactid=$_GET['id'];

//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error()); 

//-select the database to use
$mydb=mysql_select_db("fresheye_staff");

//-query the database table
$sql="SELECT * FROM staff WHERE ID=" . $contactid;


//-run the query against the mysql query function
$result=mysql_query($sql); 

//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){

  $FirstName =$row['FirstName'];
  $LastName=$row['LastName'];
  $idnumber=$row['idnumber'];
  $position=$row['position'];
  $Recommended=$row['Recommended'];

//-display the result of the array

echo "<ul>\n"; 
echo "<li><h4>Full Name : " . $FirstName . " " . $LastName . "</h4></li>\n";
echo "<li><h4>ID Number   : " . $idnumber . "</h4></li>\n";
echo "<li><h4>Position    : " . $position . "</h4></li>\n";
echo "<li><h4>Recommended  : " . $Recommended . "</h4></li>\n";
echo "</ul>";
}
}

?>

 

All I need to do now is change the code a little bit to quary(search) the idnumber table in the database and NOT the first name and lastname as it is currently doing.

 

Can anybody please help me figure this out. I will greatly appreciate it.

[Alt_F4]

sorry can you clarify what it is that you want to do here? I'm not sure I fully understand.

[roopurt18]

It would be a good idea if you removed your database credentials from your script before posting it on a public forum.

[fullyscintilla]

seem to me that you have alot of unneccesary code here..

 

If you want to have a form to search your database I can help you with that but i won't filter through that unneeded code.

[waynew]

You connect to the database on several occasions?

[kickstart]

Hi

 

Had a look and nothing much:-

 

You may search either by first or last name
<form method="post" action="search.php?go" id="searchform">
  <p>
  <input type="text" name="name">
</p>
  <p>
    <input type="submit" name="submit" value="Search">
    </p>
</form>
<?php

//connect to the database
$db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error()); 

//-select the database to use
$mydb=mysql_select_db("fresheye_staff");


if(isset($_POST['submit']))
{
if(isset($_GET['go']))
{
	if(preg_match("/[A-Z | a-z]+/", $_POST['name']))
	{
		$name=mysql_real_escape_string($_POST['name']);

		//-query the database table
		$sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%$name%' OR LastName LIKE '%$name%'";

		//-run the query against the mysql query function
		$result=mysql_query($sql);

		//-count results

		$numrows=mysql_num_rows($result);

		echo "<p>$numrows results found for " . stripslashes($name) . "</p>"; 

		//-create while loop and loop through result set
		echo "<ul>\n"; 
		while($row=mysql_fetch_array($result))
		{

			$FirstName =$row['FirstName'];
			$LastName=$row['LastName'];
			$ID=$row['ID'];

			//-display the result of the array

			echo "<li>" . "<a href=\"search.php?id=$ID\">$FirstName $LastName</a></li>\n";
		}
		echo "</ul>";
	}
	else
	{
		echo "<p>Please enter a search query</p>";
	}
}
}
else
{
if(isset($_GET['by']))
{
	$letter=mysql_real_escape_string($_GET['by']);

	//-query the database table
	$sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%$letter%' OR LastName LIKE '%$letter%'";

	//-run the query against the mysql query function
	$result=mysql_query($sql); 

	//-count results
	$numrows=mysql_num_rows($result);

	echo "<p>$numrows results found for $letter</p>"; 

	//-create while loop and loop through result set
	echo "<ul>\n"; 
	while($row=mysql_fetch_array($result))
	{

		$FirstName =$row['FirstName'];
		$LastName=$row['LastName'];
		$ID=$row['ID'];

		//-display the result of the array

		echo "<li>" . "<a href=\"search.php?id=$ID\">$FirstName $LastName</a></li>\n";
	}
	echo "</ul>";
}

if(isset($_GET['id']))
{
	$contactid=((is_numeric($_GET['id'])) ? $_GET['id'] : 0);

	//-query the database table
	$sql="SELECT FirstName, LastName, idnumber, position, Recommended FROM staff WHERE ID=" . $contactid;

	//-run the query against the mysql query function
	$result=mysql_query($sql); 

	//-create while loop and loop through result set
	while($row=mysql_fetch_array($result))
	{

		$FirstName =$row['FirstName'];
		$LastName=$row['LastName'];
		$idnumber=$row['idnumber'];
		$position=$row['position'];
		$Recommended=$row['Recommended'];

		//-display the result of the array

		echo "<ul>\n"; 
		echo "<li><h4>Full Name : $FirstName $LastName</h4></li>\n";
		echo "<li><h4>ID Number   : $idnumber</h4></li>\n";
		echo "<li><h4>Position    : $position</h4></li>\n";
		echo "<li><h4>Recommended  : $Recommended </h4></li>\n";
		echo "</ul>";
	}
}
}

?>

 

All the best

 

Keith

[fullyscintilla]

lets wait for the OP to give us some logic, we still need to know exactly what he wants to happen..

 

He wants to search for an ID in his database... but based on what?? First and last name? If the sky is purple?? we don't know yet..

[roopurt18]

He just wants to modify it to search on fields other than firstname and lastname.

 

In addition to his search form, he'll need to modify his SQL statement:

$sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%" . $name . "%' OR LastName LIKE '%" . $name ."%'";