Page 1 of 1

Query one table, return another

Posted: Sun Feb 13, 2011 8:18 am
by bertles86
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:

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)
);
Here is my search.html:

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>
With my search.php:

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
	
?>
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!

Re: Query one table, return another

Posted: Sun Feb 13, 2011 10:40 am
by social_experiment

Code: Select all

   <table>
   <tr>
      <td>First Name:</td>
      <td><input type="text" name="term" />
</td>
   </tr>
   <tr>
      <td>Second Name:</td>
      <td><input type="text" name="term"/>
</td>
The name attributes should be unique to each field. $_POST['name'] in this case will probably return te value of the second name only, resulting in 'wrong' search parameters.
bertles86 wrote:2. If you enter exactly the right data in both fields it returns all jobs for that client
You have a foreign key (client_id) so use it, select the client_idfrom the 'client' table and then look for records in the 'job' table that matches it.

Re: Query one table, return another

Posted: Sun Feb 13, 2011 11:41 am
by bertles86
Thanks, so my name attributes should be more like:

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="fname" /><br /></td>
	</tr>
	<tr>
		<td>Second Name:</td>
		<td><input type="text" name="sname"/><br /></td>
	</tr>
	<tr>
		<td><input type="submit" name="submit" value="Submit" /></td>
	</tr>
	</table>
	</form>
	
	</body>
</html>
Then the php like:

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['fname'];
	$term = $_POST['sname'];
	$q = "SELECT *
			FROM job
			JOIN client
			ON job.client_id = client.client_id
			WHERE client.first_name LIKE '%$fname%'
			AND client.second_name LIKE '%$sname%'
			";

	
	//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
	
?>
That's part one, but I'm getting errors, for undefined variables...have I missed something?

Re: Query one table, return another

Posted: Sun Feb 13, 2011 3:43 pm
by social_experiment

Code: Select all

 $term = $_POST['fname'];
        $term = $_POST['sname'];
        $q = "SELECT *
                        FROM job
                        JOIN client
                        ON job.client_id = client.client_id
                        WHERE client.first_name LIKE '%$fname%'
                        AND client.second_name LIKE '%$sname%'
                        ";

Where do you define $fname and $sname? You are doing the same with the $term variable like you did with the html earlier. Only the second $term will contain data, thus from the second field in this case. Change $term to $fname and $sname respectively. :)
Hth

Re: Query one table, return another

Posted: Mon Feb 14, 2011 1:42 pm
by bertles86
Thanks buddy, I've defined them like so:

$fname = $_POST['fname'];
$sname = $_POST['sname'];

But no now errors appear, but nor do I get any data! I enter exactly the right strings for each field and it just returns an empty table.

My SQL statement is spot on, I just tested it on the console, so my PHP must be iffy!

Re: Query one table, return another

Posted: Mon Feb 14, 2011 1:51 pm
by bertles86
Ah ha I got it!

I named the POST fields from the html different (fname1) from the PHP variables (fname) to avoid confusion and it works!

Although in all my queries, the PHP outputs some records correctly, but not all. The remaining missing records are just displayed as '12' at the top of the page, above the table.

Image


Any ideas?