Simple Search using PHP to MySQL

suttercain · February 21, 2007

Hello everyone,

I am trying to run a simple search using a basic form and submit. I have OWNERS, REFERENCE, VEHICLE and DATE. I am trying to search by Owner and list those four as the result.

My error is one of two things: Either I can get it to work and it echoes the correct result NINE times or I get the following error:

Warning: mysql_close(): no MySQL-Link resource supplied in C:\wamp\www\ARB\letterres.php on line 295

Please suggest.

FORM CODE:

<form action="letterres.php" method="POST" enctype="application/x-www-form-urlencoded">
  <center>
  <table width="375" border="0" align="left" cellpadding="0" cellspacing="0">
<TR>
  <TD><div align="left"><strong>Search by Owner Name:</strong></div></TD>
  </TR>
<TR>
  <TD><div align="center">
        <p align="left">
          <input name="own" type="text" id="own" size="25">
        </p>
    <p align="left">
      <input name="searchtype" type="submit" id="searchtype" value="Search by REF" />
        </p>
    </div>
    <div align="left"></div></TD>
  </TR>

And the Search Results page where I get one of the two errors listed above:

<?php
                
         $getit = $_POST['searchtype'];  //Determines which search button is pushed
         
         //Populate needed variables
         $owner = $_REQUEST[own];
         $manuf = $_REQUEST[manufacture];
	     $model = $_REQUEST[model];
	     $efn = $_REQUEST[efn];
	     
	     $year_arr = array("1997","2000","2001","2002","2003","2004","2005","2006","2007");
         $arr_size = count($year_arr);
               
        
        //EFN SEARCH
        if ($getit == "Search by OWNER")
           {
              $flag = 0;
              for ($i = 0; $i < $arr_size; $i++) 
              {
                //SQL Statement
                $sql = "SELECT * FROM letters WHERE OWNER = '$own'";
              
                //Connect to server
                $connection=mysql_connect("localhost", "root", NULL);
     	          $db=mysql_select_db("pio", $connection);
     	          $sql_result=mysql_query($sql, $connection);
             
                //count the number of records
                $result = mysql_query("SELECT * FROM letters WHERE OWNER = '$own'");  
              
                $rows = mysql_num_rows($result);
              
                //Check to see if there are any records matching search criteria
                if ($rows > 0)
                {   
                $flag = 1;  //Marks that there has been at least one result
                //Print Results for the SEARCH SECTION
                for ($res = 0; $res < $rows; $res++)
                  {
                  echo "<TD WIDTH='41%' BGCOLOR='white'><P ALIGN='LEFT'>";    
			$tempown = mysql_result($sql_result,$res,'OWNER');
			$tempref = mysql_result($sql_result,$res,'REF');
			$tempvehicle = mysql_result($sql_result,$res,'VEHICLE');
                $tempsent = mysql_result($sql_result,$res,'DATE_SENT');
                echo strtoupper("Reference: $tempref");
			echo "<BR>";
			echo "Owner: $tempown";
                echo "<BR>";
			echo "Vehicle: $tempvehicle";
			echo "<BR>";
			echo "Date Sent: $tempsent";
			echo "<BR>";
                  }  			  
                }
              }
            if ($flag == 0)
              echo "Sorry, no records were found matching your search criteria.";
            }
        //Close the database connection
         mysql_close();

?>

Jessica · February 21, 2007

$sql_result=mysql_query($sql, $connection) or die($sql.' - '.mysql_error());

TRI0N · February 21, 2007

First the Submit Button is going to return: "Search by REF" and not something simple like just "REF"

The results is confussed:

Try this as a basic format to gett down to where you want it to echo out results...:

<?php
//Populate needed variables
$owner = $_REQUEST[own]; //Here is where you went wrong with the Query to look for.
$manuf = $_REQUEST[manufacture]; //Form doesn't even show this input.
$model = $_REQUEST[model]; //Form doesn't even show this input.
$efn = $_REQUEST[efn]; //Form doesn't even show this input.

$year_arr = array("1997","2000","2001","2002","2003","2004","2005","2006","2007");
$arr_size = count($year_arr);

// Database Settings
$dbms = 'mysql' ;
$dbhost = 'localhost' ;
$dbname = 'pio' ;
$dbuser = 'root' ;
$dbpasswd = '';

// Database Connection
mysql_connect("$dbhost","$dbuser","$dbpasswd") ;

// Database Selection
mysql_select_db("$dbname") ;

//Search DB
$result = mysql_query("SELECT * FROM letters WHERE (OWNER = '$owner')") ; //See Note 1
if(mysql_num_rows($result) == 0){
echo "<font color=#800000>Sorry, no records were found matching your search criteria.</font>" ;
}else{
while($row = mysql_fetch_row($result)) {
$tempown = $row[3] ;//See note 2
$tempref = $row[4] ; //See note 2

//Echo out formated results
echo "WHATER FORMAT YOU WISH TO USE HERE" ;
echo "CONTINUING FORMAT HERE" ;
 }
}
?>

NOTE 1: Added extra ( ) around the WHERE (OWNER = $owner). You can remove those will work with but put them in so you can see the ' " more clearly. Also the $owner is the correct string to use since you REQUESTED from the form to make $owner see 'own' as the new string. if your Table Name is OWNER in all caps then that is fine but if not make sure you match how it is in the database.

NOTE 2: $row[X] ; is where you look at your table in the data base and count starting from the begining as 0 to the row that contains the data you are setting the string to. example table:

id

fname

lname

tempown

id is $row[0] and tempown is $row[3].

*Side Note: You really should use a password for your Database if you just obmitted one to put in the post here fine but you really should use a password.

Hope this gets you headed in the right direction.

TRI0N · February 21, 2007

If you want it to actually count results with line numbers spiffy here is a simple way to do so...

<?php
//Populate needed variables
$owner = $_REQUEST[own]; //Here is where you went wrong with the Query to look for.
$manuf = $_REQUEST[manufacture]; //Form doesn't even show this input.
$model = $_REQUEST[model]; //Form doesn't even show this input.
$efn = $_REQUEST[efn]; //Form doesn't even show this input.

$year_arr = array("1997","2000","2001","2002","2003","2004","2005","2006","2007");
$arr_size = count($year_arr);

// Database Settings
$dbms = 'mysql' ;
$dbhost = 'localhost' ;
$dbname = 'pio' ;
$dbuser = 'root' ;
$dbpasswd = '';

// Database Connection
mysql_connect("$dbhost","$dbuser","$dbpasswd") ;

// Database Selection
mysql_select_db("$dbname") ;

//Counter to Zero
$i = 0 ;

//Search DB
$result = mysql_query("SELECT * FROM letters WHERE (OWNER = '$owner')") ; //See Note 1
if(mysql_num_rows($result) == 0){
echo "<font color=#800000>Sorry, no records were found matching your search criteria.</font>" ;
}else{
while($row = mysql_fetch_row($result)) {
$tempown = $row[3] ;//See note 2
$tempref = $row[4] ; //See note 2

//Counter Function
$i = $i+1 ;

//Echo out formated results
echo "(".$i."WHATER FORMAT YOU WISH TO USE HERE" ;
echo "CONTINUING FORMAT HERE" ;
  }
}

echo "<p>Total Number of Returned Results: ".$i."</p>" ;
?>

suttercain · February 21, 2007

Got it. Thanks. I played with it for a little while and it works. Thank you very much for your great explanation. I really appreciate it!

Shannon

suttercain · February 21, 2007

Just for an additional learning tool for myself. Let's say I wanted the search to query more than just the "OWNER" column of the MYSQL table but also the another column. Would I just...

$result = mysql_query("SELECT * FROM letters WHERE (OWNER = '$owner', REF = '$reference')") ;

Also, how do I make it so the search isn't so strict. I get the proper result is I search John Smith, but not if I enter John. How do I get it to display results of all the John's in the OWNER's?

Thanks again.

TRI0N · February 21, 2007

$result = mysql_query("SELECT * FROM letters WHERE (OWNER = '$owner' AND REF = '$reference')") ;

TRI0N · February 21, 2007

To use just a wildcard as is you use the % not *...

Though you can make a script to pull apart a search that will find the * and replace it with % by doing a search here on the forums for use of the EXPLODE function. and then write it how you want it..

I'll give you an idea to start with...

From the Search page the user will type "J*h*" for John:

<?

$owner = $REQUEST['own'] ;

php list($s1, $s2,) = explode('*', $owner) ;

$owner = $s1.'%'.$s2.'%' ;

$result = mysql_query("SELECT * FROM letters WHERE (OWNER = '$owner' AND REF = '$reference')") ;

?>

Not the best way to do it but thats just off the top of my head. You will need to look into it a little more.

Or you can also just go

$result = mysql_query("SELECT * FROM letters WHERE (OWNER = '$owner%' AND REF = '$reference')") ;

suttercain · February 21, 2007

Hi TRI0N,

When I tried:

$result = mysql_query("SELECT % FROM letters WHERE (OWNER = '$owner')") ; //See Note 1

I got:

Search Results:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\ARB\letterres.php on line 214

Sorry, no records were found matching your search criteria.

When I tried:

$result = mysql_query("SELECT * FROM letters WHERE (OWNER = '$owner%')") ; //See Note 1

I got:

Search Results:

Sorry, no records were found matching your search criteria.

Am I entering something wrong? Thank you for your time.

Shannon

TRI0N · February 21, 2007

Were you searching for John or Joh?

suttercain · February 21, 2007

John

Sign In

Simple Search using PHP to MySQL

Recommended Posts

suttercain

Link to comment

Share on other sites

Jessica

Link to comment

Share on other sites

TRI0N

Link to comment

Share on other sites

TRI0N

Link to comment

Share on other sites

suttercain

Link to comment

Share on other sites

suttercain

Link to comment

Share on other sites

TRI0N

Link to comment

Share on other sites

TRI0N

Link to comment

Share on other sites

suttercain

Link to comment

Share on other sites

TRI0N

Link to comment

Share on other sites

suttercain

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information