Refining a php search of mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
whittler
Forum Newbie
Posts: 2
Joined: Tue Mar 15, 2005 9:10 pm

Refining a php search of mysql

Post by whittler »

Hi,

I have a simple php search that I want to refine a little. I have a MySQL database with columns for artist, album, and song. There is one record per song, so there are multipe records for each album. That is, if I search for an album (by searcing the album column), I get multiple results because each record represents one song. E.g. a search for Dark Side of the Moon returns 9 results. Is there any way to change the code so that it just returns the first result for an album and discards the rest of the songs of that album? Here's the current code:

Code: Select all

<?
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","root","********"); 
    
//select which database you want to edit
mysql_select_db("collection"); 
 
$search=$_POST["search"];
 
//get the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search
$result = mysql_query("SELECT * FROM `monty` WHERE `album` LIKE '%$search%' limit 1000");

//grab all the content
while($row = mysql_fetch_array($result))
{    
   //the format is $variable = $row["nameofmysqlcolumn"];
   //modify these to match your mysql table columns
  
    $performer = $row["artist"];
	$disc = $row["album"];
    
   //display the row
   echo("<div id=wrapper><table>
	<tr>
		<td><b>Artist:</b></td>
		<td>$performer</td>
	</tr>
	<tr>
		<td><b>Album:</b></td>
		<td>$disc</td>
	</tr>
	</table>
</div>
");
   }
?>

feyd | Please review how to post code using

Code: Select all

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

research GROUP BY.
whittler
Forum Newbie
Posts: 2
Joined: Tue Mar 15, 2005 9:10 pm

Post by whittler »

Tops! Thanks for that!
Post Reply