Query one table, return another
Posted: Sun Feb 13, 2011 8:18 am
Hi folks,
I've got two tables 'client' and 'job'.
One client can have many jobs, one job can have only one client.
I'd like to be able to query the client table, but return the job table data.
So the user enters a second name and first name in the search fields on the html page and the php page returns all job records linked to that client...
Here's the table code:
Here is my search.html:
With my search.php:
If I enter exactly correct second name and first name then I get zero records returned, if I enter just one I got all records from the table which is wrong.
How I can get it so that:
1. If you enter just one of the fields, it returns all jobs by any client who's first/second name is X
2. If you enter exactly the right data in both fields it returns all jobs for that client
Please help!
I've got two tables 'client' and 'job'.
One client can have many jobs, one job can have only one client.
I'd like to be able to query the client table, but return the job table data.
So the user enters a second name and first name in the search fields on the html page and the php page returns all job records linked to that client...
Here's the table code:
Code: Select all
CREATE TABLE client (
client_id int(11) NOT NULL auto_increment,
second_name varchar(255) NOT NULL,
first_name varchar(255) NOT NULL,
invoice_address_1 varchar(255) NOT NULL,
invoice_address_2 varchar(255) NOT NULL,
invoice_address_3 varchar(255) NOT NULL,
invoice_town_city varchar(255) NOT NULL,
invoice_postcode varchar(7) NOT NULL,
mobile_number int(20) NOT NULL,
landline_number int(20),
email varchar(255) NOT NULL,
contact_date date NOT NULL,
PRIMARY KEY (client_id)
);Code: Select all
CREATE TABLE job (
job_id int(11) NOT NULL auto_increment,
client_id int(11),
job_address_1 varchar(255) NOT NULL,
job_address_2 varchar(255) NOT NULL,
job_address_3 varchar(255) NOT NULL,
job_town_city varchar(255) NOT NULL,
job_postcode varchar(7) NOT NULL,
date_started date NOT NULL,
date_finished date NOT NULL,
status_id int(11),
PRIMARY KEY (job_id),
FOREIGN KEY (client_id) REFERENCES client(client_id),
FOREIGN KEY (status_id) REFERENCES status(status_id)
);Code: Select all
<html>
<head>
<title>Search all jobs by a client</title>
</head>
<body>
<form action="search_jobs_client.php" method="post">
<table>
<tr>
<td>First Name:</td>
<td><input type="text" name="term" /><br /></td>
</tr>
<tr>
<td>Second Name:</td>
<td><input type="text" name="term"/><br /></td>
</tr>
<tr>
<td><input type="submit" name="submit" value="Submit" /></td>
</tr>
</table>
</form>
</body>
</html>Code: Select all
<?php
$page_title = 'Search for all jobs by a client';
echo '<h1>Search fo all jobs by a client</h1>';
require_once ('../mysqli_connect.php');
//Create Query
$term = $_POST['term'];
$q = "SELECT *
FROM job
JOIN client
ON job.client_id = client.client_id
WHERE client.first_name LIKE '%$term%'
AND client.second_name LIKE '%$term%'
";
//Run Query
$r = @mysqli_query ($dbc, $q);
if ($r) {
//If it ran OK, display records
echo '<table border="1" align="center"
cellspacing="3" cellpadding="3"
width="100%">
<tr>
<td align="left"><b>Job ID</b></td>
<td align="left"><b>Client ID</b></td>
<td align="left"><b>Job Address 1</b></td>
<td align="left"><b>Job Address 2</b></td>
<td align="left"><b>Job Address 3</b></td>
<td align="left"><b>Job Town/City</b></td>
<td align="left"><b>Job Postcode</b></td>
<td align="left"><b>Date Start</b></td>
<td align="left"><b>Date End</b></td>
</tr>
';
//Fetch and print the records
while ($row = mysqli_fetch_array($r,MYSQLI_ASSOC)) {
echo '<tr>
<td align="left" width="5%">' . $row['job_id'] . '</td>
<td align="left" width="5%">' . $row['client_id'] . '</td>12
<td align="left" width="14%">' . $row['job_address_1'] . '</td>
<td align="left" width="12%">' . $row['job_address_2'] . '</td>
<td align="left" width="12%">' . $row['job_address_3'] . '</td>
<td align="left" width="12%">' . $row['job_town_city'] . '</td>
<td align="left" width="8%">' . $row['job_postcode'] . '</td>
<td align="left" width="9%">' . $row['date_started'] . '</td>
<td align="left" width="9%">' . $row['date_finished'] . '</td>
</tr>';
}
echo '</table>'; //Close table
mysqli_free_result ($r); //Free resources
} else {
echo '<p class="error">The data could not be found, sorry.</p>';
echo '<p>' . mysqli_error($dbc) . '<br/><br />Query: ' . $q . '</p>';
}
mysqli_close($dbc); //Close database
?>How I can get it so that:
1. If you enter just one of the fields, it returns all jobs by any client who's first/second name is X
2. If you enter exactly the right data in both fields it returns all jobs for that client
Please help!
