Search database – how to pull data from child tables? (beginner, sorry)

Hello,

I’m a beginner with databases and MySQL/PHP, but know enough to macguyver things together …somewhat.

I’ve managed to create a database with three tables – Merchants (a list businesses and their contact details), Issues (issues with merchants), Updates (updates to issues with merchants).

I’ve set my foreign keys up as follows:

Merchants – no FK
Issues – MerchantID to Merchants > MerchantID
Updates – IssueID to Issues > IssueID

I’m using this mini tutorial to set up my first database search page: [url]http://teamtutorials.com/web-development-tutorials/php-tutorials/creating-a-form-that-will-search-a-mysql-database#.UKVJhobZ6YF[/url]

I’m trying to set up a single search box where if a user types in a merchant’s name, it brings up that merchant plus all it’s issues and updates against those issues.

I’ve set out my code below in a logical fashion but I don’t know the correct syntax/operators/functions to accomplish this, let alone if I need to append the three sql statements as $sql1 = mysql_query, $sql2 = mysql_query, $sql3 = mysql_query

Please would you be able to advise me on any handy pointers to enable me to accomplish this?

The code:

[code]<form action=”search.php” method=”post”>
Search: <input type=”text” name=”term” />
<br />
<input type=”submit” name=”submit” value=”Submit” />
</form>

<p></p>

<?php
mysql_connect (“localhost”, “username”,”password”) or die (mysql_error());
mysql_select_db (“dbname”);

$term = $_POST[‘term’];

$sql = mysql_query(“select * from Merchants where Name like ‘%$term%'”);

while ($row = mysql_fetch_array($sql)){
echo ‘Merchant #’.$row[‘MerchantID’];
echo ‘<br/> Name: ‘.$row[‘Name’];
echo ‘<br/> Phone: ‘.$row[‘Phone’];
echo ‘<br/> Email: ‘.$row[‘Email’];
echo ‘<br/>’.$row[‘Private?’];
echo ‘<br /><br />’;
}

$sql = mysql_query(“select * from Issues if MerchantID matches Merchants > MerchantID”);

while ($row = mysql_fetch_array($sql)){
echo ‘Issue #’.$row[‘IssueID’];
echo ‘<br/> Date of issue: ‘.$row[‘IssueDate’];
echo ‘<br/> Issue: ‘.$row[‘Issue’];
echo ‘<br/> Solution: ‘.$row[‘Solution’];
echo ‘<br /><br />’;
}

$sql = mysql_query(“select * from Updates if IssueID matches Issues > IssueID”);

while ($row = mysql_fetch_array($sql)){
echo ‘Update #’.$row[‘UpdateID’];
echo ‘<br/> Date of update: ‘.$row[‘UpdateDate’];
echo ‘<br/> Update: ‘.$row[‘Update’];
echo ‘<br /><br />’;
}

?>[/code]

Also in #PHP _↴