Here are my tables:
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(8) DEFAULT NULL,
`primary_number` varchar(20) NOT NULL,
`alt_number` varchar(20) NOT NULL,
`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) DEFAULT NULL,
`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(8) DEFAULT NULL,
`date_started` date NOT NULL,
`date_finished` date NOT NULL,
`status_id` int(11) DEFAULT NULL,
PRIMARY KEY (`job_id`),
KEY `client_id` (`client_id`),
KEY `status_id` (`status_id`),
CONSTRAINT `job_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `client` (`client
_id`),
CONSTRAINT `job_ibfk_2` FOREIGN KEY (`status_id`) REFERENCES `status` (`status
_id`)
)Code: Select all
CREATE TABLE `status` (
`status_id` int(10) NOT NULL DEFAULT '0',
`status_type` varchar(50) NOT NULL,
PRIMARY KEY (`status_id`)
)Code: Select all
<?php
$page_title = 'View all jobs alphabetically';
echo '<h1>View all jobs alphabetically</h1>';
require_once ('../mysqli_connect.php');
//Create Query
$q = "SELECT job.job_id, job.client_id, job.job_address_1, job.job_address_2, job.job_address_3, job.job_town_city, job.job_postcode, job.date_started, job.date_finished, status.status_type
FROM job
INNER JOIN status
ON job.status_id=status.status_id
ORDER BY job.job_address_1 ASC";
//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>
<td align="left"><b>Status</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>
<td align="left" width="15%">' . $row['status_type'] . '</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
?>Code: Select all
12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12 12