Hi
I'm doing a search function in PHP from an sql database. It was working before and i have no idea what happend but when i search now no results are found, but before the search it shows all the records, so there is a connection with the database. I have chceck the tables names ect. and all of them are correct. This is my code:

<?php
 $location = "localhost";
         $username = "****";
         $password = "****";
         $database = "****";
         $conn=@mysql_connect("$location","$username","$password");
         if (!$conn) die ("Error: Could not connect to database server.");
         @mysql_select_db($database,$conn) or die ("Error: Could not open database.");
         extract($_POST);

    if (isset($search))
         {
            //Search for comments which contain a keyword
            $query = "SELECT *, (PCOST * QUANTITY) AS PTotal, ((PCOST * QUANTITY) + RCOST) AS Total
            FROM REPAIR 
            INNER JOIN PARTS_USED
            ON REPAIR.REPAIRID = PARTS_USED.REPAIRID
            INNER JOIN PARTS
            ON PARTS.PARTID = PARTS_USED.PARTID
            INNER JOIN STAFF
            ON REPAIR.STAFFID = STAFF.STAFFID
            INNER JOIN CARS
            ON REPAIR.CARID = CARS.CARID
            INNER JOIN CAR_TYPE 
            ON CARS.TYPEID = CAR_TYPE.TYPEID
            INNER JOIN CUSTOMER 
            ON CARS.OWNERID = CUSTOMER.CUSTOMERID  
            WHERE $searchfield LIKE '%$keyword%'";
            echo "Search for keyword <strong>$keyword</strong> in field <strong>$searchfield</strong>:<br/>";
         }
         else $query = "SELECT *, (PCOST * QUANTITY) AS PTotal, ((PCOST * QUANTITY) + RCOST) AS Total
            FROM REPAIR 
            INNER JOIN PARTS_USED
            ON REPAIR.PUSEDID = PARTS_USED.PUSEDID
            INNER JOIN PARTS
            ON PARTS.PARTID = PARTS_USED.PARTID
            INNER JOIN STAFF
            ON REPAIR.STAFFID = STAFF.STAFFID
            INNER JOIN CARS
            ON REPAIR.CARID = CARS.CARID
            INNER JOIN CAR_TYPE 
            ON CARS.TYPEID = CAR_TYPE.TYPEID
            INNER JOIN CUSTOMER 
            ON CARS.OWNERID = CUSTOMER.CUSTOMERID";
$result = mysql_query($query);
         print("<table border=3 width=100%><thead>
<tr>
<th>REPAIR ID</th>
<th>DESCRIPTION</th>
<th>REPAIR COST</th>
<th>LOCATION</th>
<th>QUANTITY</th>
<th>PART NAME</th>
<th>PART COST</th>
<th>PARTS TOTAL COST</th>
<th>STAFF NAME</th>
<th>STAFF SURNAME</th>
<th>YEAR</th>
<th>MAKE</th>
<th>MODEL</th>
<th>CUSTOMER NAME</th>
<th>CUSTOMER SURNAME</th>
<th>TOTAL COST</th</tr></thead><tbody>");

    while($row = mysql_fetch_array($result))
  {
    print ("<tr><td>$row[REPAIRID]</td>");
    print ("<td>$row[DESCRIPTION]</td>");
    print ("<td>$row[RCOST]</td>");
    print ("<td>$row[LOCATION]</td>");
    print ("<td>$row[QUANTITY]</td>");
    print ("<td>$row[PNAME]</td>"); 
    print ("<td>$row[PCOST]</td>");
    print ("<td>$row[PTotal]</td>");
    print ("<td>$row[STAFF_NAME]</td>");
    print ("<td>$row[STAFF_SURNAME]</td>");
    print ("<td>$row[YEAR]</td>");
    print ("<td>$row[MAKE]</td>");
    print ("<td>$row[MODEL]</td>");
    print ("<td>$row[CUSTOMER_NAME]</td>");
    print ("<td>$row[CUSTOMER_SURNAME]</td>");
    print ("<td>$row[Total]</td></tr>");

  }

 $numrows = @mysql_num_rows($result);
         print("</tbody><tfoot><th colspan=\"3\">Number of responses: $numrows</th></tfoot></table>");

         //Close connection to server
         @$disconn=mysql_close($conn);
         if (!$disconn) die ("Error: Unable to disconnect from database server.");
      ?>
      <form method="post" action="viewREPAIR.php">
         <label>
            Search for:
            <input name="keyword" type="text"/>
            in field:
            <select name="searchfield">
               <option value="REPAIR.REPAIRID">REPAIR ID</option>
               <option value="STAFF.STAFFID">STAFF ID</option>
               <option value="CUSTOMERID">CUSTOMER ID</option>
               <option value="STAFF_NAME">STAFF NAME</option>
               <option value="CUSTOMER_NAME">CUSTOMER NAME</option>
            </select>
            <input name="search" type="submit" value="search"/>
         <label>
      </form>

Please help me as i need this for my final year project.

I suggest you add error checking:

$result = mysql_query($query) or die(mysql_error() . "<br/>$query");

Thank you, That helped find the problem :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.