My code is a php search function which is supposed to search from two tables in my database and displays matching results from the two tables in a line after line format.
It is supposed to match the two tables with a common key field.
However, the primary table has some rows that does not have any matches with the secondary table. (i.e. the secondary table has no matching key in any of the rows within it)
A basic search should show the results from the two tables, but if a searched term in the primary table does not have a matching key in the secondary table, only one table result (the primary) is displayed. If there is a match that links the two table together, both results will be displayed.
The problem I have with my Script at the moment is that it also produces non matching results
I.e. if I search for "A" (and "A" matches against "B"), it gives me
AB
AC
AD
AF
...(continues like this)
How can I get my script to get exact matches from the two tables
the matching key in my script is the SongNo
This is an example of what the two tables are like
Table 1:writers (primary table, there are four fields in this table)
SongNo ||Author ||title ||lyrics
1691 ||ben ||Father was a rolling stone ||blah blah blah...
1692 ||tom ||We are marching ||blah blah blah...
1693 ||mike ||We built this city ||blah blah blah...
1694 ||john || What they want from me ||blah blah blah...
Table 2: bible (Secondary table, there are two fields in this table)
ref || SongNo
Genesis || 1691
Exodus || 1691
Mark || 1692
Chronicles|| 1694
As you might have noticed, there are some songNo's that repeat in the second table and one of the matching keys (1693) is absent in the secondary table.
I need to get my search function to match searches in these conditions...
possible scenerio's
if I search for mike, I would only get results from the first table (writers) &
if I search for ben, I would get results from the first and second table since there is a matching key in the second.
<html>
<head>
<title>Saints way</title>
</head>
<body class="margin">
<br/>
<form method="post" action="search.php?go" id="searchform">
<table align="center">
<tr>
<td>
<strong>Enter:</strong>
</td>
<td>
<input type=text size="50" name="name">
</td>
<td>
<input type="submit" name="submit" value="Search">
</td>
</tr>
</table>
</form>
<?php
if(isset($_POST['submit'])){
if(isset($_GET['go'])){
if(preg_match("/^[ a-zA-Z]+/", $_POST['name'])){
$name=$_POST['name'];
//connect to the database
$db=mysql_connect ("host", "database", "password") or die ('I cannot connect to the database because: ' . mysql_error());
//-select the database to use
$mydb=mysql_select_db("database");
//-query the database table
$song="SELECT * FROM bible, writers WHERE `bible`.`SongNo`=`writers`.`SongNo` AND `Author` LIKE '%" . $name . "%' OR `title` LIKE '%" . $name . "%' AND `lyrics` IS NOT NULL AND `ref` IS NOT NULL LIMIT 0 , 100";
//-run the query against the mysql query function
$result=mysql_query($song);
//-create while loop and loop through result set
$num_rows = mysql_num_rows($result);
echo "<hr>";
echo "<h4 align='right'> Search for '$name' found $num_rows Results... \n</h4>";
while($row=mysql_fetch_array($result)){
$Author =$row['Author'];
$lyrics=$row['lyrics'];
$title=$row['title'];
$SongNo=$row['SongNo'];
$ref=$row['ref'];
$rest = substr("$lyrics",0,180);
//-display the result of the array
if($num_rows==0){
echo "Sorry, search found zero results."; //if there are zero rows, then it will echo this out.
} else {
$row = mysql_fetch_assoc($result);
print '<p class="sansserif"><b>Song title : </b> <a href="'.$lyrics.'">'.$title.'</a><br />';
print "<b>Author</b>: $Author <br />" . "<b>Reference</b>: $ref <br />" . "<b>Lyrics</b>: $rest</p>";
}
}
}
else{
echo "<p><b>Please enter search term, we suggest the name of an author or a song title</p></b><hr/>";
}
}
}
?>
<hr/>
</body>
</html>