I'm currently building my first database in
MySQL with an interface written in PHP and am using the 'learn-by-doing' approach. The figure below illustrates my database.
Table names are at the top, and the attribute names are as they appear in the real database. I am attempting to query the values of each of these attributes using the code seen below the
table. I think there is something wrong with my mysql_query() function since I am able to observe the expected behaviour when my form is successfully submitted, but no search results are returned. Can anyone see where I'm going wrong here?
Update 1: I've updated the question with my enter script, minus the database login credentials.
<html>
<head>
<title>Search</title>
</head>
<body>
<h1>Search</h1>
<!--Search form - get user input from this-->
<form name = "search" action = "<?=$PHP_SELF?>" method = "get">
Search for <input type = "text" name = "find" /> in
<select name = "field">
<option value = "Title">Title</option>
<option value = "Description">Description</option>
<option value = "City">Location</option>
<option value = "Company_name">Employer</option>
</select>
<input type = "submit" name = "search" value = "Search" />
</form>
<form name = "clearsearch" action = "Search.php">
<input type = "submit" value = "Reset search" />
</form>
<?php
if (isset($_GET["search"])) // Check if form has been submitted correctly
{
// Check for a search query
if($_GET["find"] == "")
{
echo "<p>You did not enter a search query. Please press the 'Reset search' button and try again";
exit;
}
echo "<h2>Search results</h2>";
?>
<table align = "left" border = "1" cellspacing = "2" cellpadding = "2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">No.</font></th>
<th><font face="Arial, Helvetica, sans-serif">Title</font></th>
<th><font face="Arial, Helvetica, sans-serif">Employer</font></th>
<th><font face="Arial, Helvetica, sans-serif">Description</font></th>
<th><font face="Arial, Helvetica, sans-serif">Location</font></th>
<th><font face="Arial, Helvetica, sans-serif">Date Posted</font></th>
<th><font face="Arial, Helvetica, sans-serif">Application Deadline</font></th>
</tr>
<?
// Connect to the database
$username=REDACTED;
$password=REDACTED;
$host=REDACTED;
$database=REDACTED;
mysql_connect($host, $username, $password);
@mysql_select_db($database) or die (mysql_error());
// Perform the search
$find = mysql_real_escape_string($find);
$query = "SELECT job.Title, job.Description, employer.Company_name, address.City, job.Date_posted, job.Application_deadline
WHERE
(
Title = '{$_GET['find']}'
OR Company_name = '{$_GET['find']}'
OR Date_posted = '{$_GET['find']}'
OR Application_deadline = '{$_GET['find']}'
)
AND job.employer_id_job = employer.employer_id
AND job.address_id_job = address.address_id";
if (!$query)
{
die ('Invalid query:' .mysql_error());
}
$result = mysql_query($query);
$num = mysql_numrows($result);
$count = 0;
while ($count < $num)
{
$title = mysql_result ($result, $count, "Title");
$date_posted = mysql_result ($result, $count, "Date_posted");
$application_deadline = mysql_result ($result, $count, "Application_deadline");
$description = mysql_result ($result, $count, "Description");
$company = mysql_result ($result, $count, "Company_name");
$city = mysql_result ($result, $count, "City");
?>
<tr>
<td><font face = "Arial, Helvetica, sans-serif"><? echo $count + 1; ?></font></td>
<td><font face = "Arial, Helvetica, sans-serif"><? echo $title; ?></font></td>
<td><font face = "Arial, Helvetica, sans-serif"><? echo $company; ?></font></td>
<td><font face = "Arial, Helvetica, sans-serif"><? echo $description; ?></font></td>
<td><font face = "Arial, Helvetica, sans-serif"><? echo $date_posted; ?></font></td>
<td><font face = "Arial, Helvetica, sans-serif"><? echo $application_deadline; ?></font></td>
<td><font face = "Arial, Helvetica, sans-serif"><? echo $education_level; ?></font></td>
<td><font face = "Arial, Helvetica, sans-serif"><? echo $years_of_experience; ?></font></td>
<?
$count ++;
}
}
?>
</body>
</html>
