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.
