I have two tables in my database
This ("----------") represents the end of a field. and the last field (SongNo) is the key used to match the two tables.
[table: "Writers"]
title------------------------Author-----------------SongNo
We are, we are-------Michael J--------------1691
You got the love------Holly V-----------------1692
Back to Zion-----------Steve W---------------1693
Indian giver-------------Gavin D----------------1694
[table: "Bible"]
ref-------------------------- SongNo
Numbers 1:21----------1691
Exodus 2:2-------------1691
Genesis, 3:7-1---------1691
Hebrew 12:2------------1691
Revelation 17:14------1692
Romans 12:1-----------1692
Chronicles 2:1---------1693
John 1:1------------------1694
As you can see in the table “bible”, there are some SongNo’s in field 2 which repeat. What i need is my search result to group the references for these repeated SongNo’s under each corresponding title with a common SongNo.
So for example, if i search for "we are" using an ideal script I would get
Song title: We are, we are
Author: Michael J
Reference: Numbers 1:21 Exodus 2:2 Genesis 3:7-1 Hebrew 12:2
My current script does not group the references like this, what it does is repeat each song title and author and presents one reference per result.
How can I group the references using my script please see script below,...
<html>
<head>
<title>Saints way</title>
<style type="text/css" media="screen">
ul li{
list-style-type:none;
}
</style>
</head>
<body>
<p><h3>Search Results:</h3> </p>
<DIV ALIGN=CENTER>
<P><strong>Enter:</strong>
<input type=text size="50" name="name">
<input type="submit" name="submit" value="Search">
<br />
</p>
</div>
<hr/>
<?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 ("server", "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` IS NOT NULL AND `title` LIKE '%" . $name . "%' AND `lyrics` IS NOT NULL AND `ref` IS NOT NULL LIMIT 0 , 30";
//-run the query against the mysql query function
$result=mysql_query($song);
//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){
$Author =$row['Author'];
$lyrics=$row['lyrics'];
$title=$row['title'];
$SongNo=$row['SongNo'];
$ref=$row['ref'];
//-display the result of the array
$row = mysql_fetch_assoc($result);
print '<p><b>Song title : </b> <a href="'.$lyrics.'">'.$title.'</a><br />';
print "<b>Author</b>: $Author " . "<b>Reference</b>: $ref";
}
}
else{
echo "<p>Please enter search term, we suggest the name of an author or a song title</p>";
}
}
}
?>
</body>
</html>
This ("----------") represents the end of a field. and the last field (SongNo) is the key used to match the two tables.
[table: "Writers"]
title------------------------Author-----------------SongNo
We are, we are-------Michael J--------------1691
You got the love------Holly V-----------------1692
Back to Zion-----------Steve W---------------1693
Indian giver-------------Gavin D----------------1694
[table: "Bible"]
ref-------------------------- SongNo
Numbers 1:21----------1691
Exodus 2:2-------------1691
Genesis, 3:7-1---------1691
Hebrew 12:2------------1691
Revelation 17:14------1692
Romans 12:1-----------1692
Chronicles 2:1---------1693
John 1:1------------------1694
As you can see in the table “bible”, there are some SongNo’s in field 2 which repeat. What i need is my search result to group the references for these repeated SongNo’s under each corresponding title with a common SongNo.
So for example, if i search for "we are" using an ideal script I would get
Song title: We are, we are
Author: Michael J
Reference: Numbers 1:21 Exodus 2:2 Genesis 3:7-1 Hebrew 12:2
My current script does not group the references like this, what it does is repeat each song title and author and presents one reference per result.
How can I group the references using my script please see script below,...
<html>
<head>
<title>Saints way</title>
<style type="text/css" media="screen">
ul li{
list-style-type:none;
}
</style>
</head>
<body>
<p><h3>Search Results:</h3> </p>
<DIV ALIGN=CENTER>
<P><strong>Enter:</strong>
<input type=text size="50" name="name">
<input type="submit" name="submit" value="Search">
<br />
</p>
</div>
<hr/>
<?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 ("server", "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` IS NOT NULL AND `title` LIKE '%" . $name . "%' AND `lyrics` IS NOT NULL AND `ref` IS NOT NULL LIMIT 0 , 30";
//-run the query against the mysql query function
$result=mysql_query($song);
//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){
$Author =$row['Author'];
$lyrics=$row['lyrics'];
$title=$row['title'];
$SongNo=$row['SongNo'];
$ref=$row['ref'];
//-display the result of the array
$row = mysql_fetch_assoc($result);
print '<p><b>Song title : </b> <a href="'.$lyrics.'">'.$title.'</a><br />';
print "<b>Author</b>: $Author " . "<b>Reference</b>: $ref";
}
}
else{
echo "<p>Please enter search term, we suggest the name of an author or a song title</p>";
}
}
}
?>
</body>
</html>