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 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 />';
}
?>
If you are more than one query on a page, then yes you need to name the variables that the queries are assigned to differently ($sql1, $sql2 etc or whatever you want to call the variables). You also need to change your $row variable name for each while loop to prevent any conflict (i.e. $row2, $row3).
With regard to your actual queries, they should be:
PHP Code:
$sql2 = mysql_query("select * from Issues where MerchantID='".$row['MerchantID']."'");
while ($row2 = mysql_fetch_array($sql2) {
....
// and
$sql3 = mysql_query("select * from Updates where IssueID='".$row2['IssueID']."'");
while ($row3 = mysql_fetch_array($sql3) {
....
Thank you very much everyone, your help is really appreciated.
If I may ask another question, I've set up a draft of the markup that should be generated by the queries, below (using SC7639's quries).
I was wondering that if I use tables, this structure would seem to require that the Phone and Email results are echoed in the second query - but the data Phone/Email does not exist in the Updates table (see under <!-- 2nd end of merchant/issue query results -->)
Thinking about it, I guess I could query as normal using div's and CSS to position, thereby working around this problem. However I was hoping that it'd be more straight forward in PHP.
Also, is there a way to use something like the EOF thing in a PHP mailer to output a chunk of HTML rather than outputting line by line with echo? It's going to be a bit laboursome to echo this table!
Reply With Quote
Bookmarks