CODEWITHSUNDEEP
phpmysqldatabase
Moynul
Moynul

Reputation: 635

Reference child table with a main table

In my database I have a jobs table which you can say is the main table in my database. I have a column called category and this column points to a table called category which holds different categories.

Picking up the concept of foreign keys,I made turned the column categories into a foreign key, which looks at the categories table.

Foreign Key

In my categories table I make sure it points to the ID. Implementation of FK

When I run my webpage it prints out the value 1 in the category column, when in theory should it not print "Driving"? enter image description here

function getJobDetails($job,$cat){
//this connects to the database
include "connectToDatabse.php";

//show me the results from job, where category is like cat vice versa
$results = $pdo->query("SELECT * FROM job WHERE category LIKE '$cat%' OR title LIKE '$job'");
$str = "<table>";                       //prints out table
$str .= "<td>" ."Title" . "</td>";      //first row
$str .= "<td>" ."Reference" . "</td>";  //N row...
$str .= "<td>" ."Salary(£)" . "</td>";
$str .= "<td>" ."Description" . "</td>";
$str .= "<td>" ."Category" . "</td>";
foreach ($results as $row) {
    $ref = $row['reference'];
    $link = "<form method='get' action='apply.php' name='edit'>
         <input type='hidden' name='referenceNumber' value='$ref'>
         <input type='submit' value='$ref'>
    </form>";
    $str .= "<tr>";
    $str .= "<td>" . $row['title'] . "</td>";
    $str .= "<td>" . $row['reference'] . "</td>";
    $str .= "<td>" . $row['salary'] . "</td>";
    $str .= "<td>" . $row['description'] . "</td>";
    $str .= "<td>" . $row['category'] . "</td>";
    $str .= "<td> " .$link . "</td>";
    $str .= "</tr>";
}
$str .= "</table>";
echo $str;
}

The above code is a function that returns the data in the job table.

Edit: referring to the question, since the categories column is pointing to the category table, should it not refer the data back to the job table?

Upvotes: 4

Views: 78

Answers (1)

tkausl
tkausl

Reputation: 14279

No it shouldn't. You're missunderstanding foreign-keys. They do not change the data you get, they only tell the database system 'hey, this references somthing else, if this other entry gets updated or deleted, please do X (update, delete, ...) with this entry here'. You'd still need a join to get the expected result:

SELECT ..., category.title FROM job LEFT JOIN category ON category.id = job.category

Upvotes: 3

Related Questions