SQL, Joining columns instead of rows?

[Cherphas]

Hi,

I'm building a search engine for my site. The search result page is pretty simple, only 10 results per page with a next and previous button. Problem is, I want it to search through more than one table in my database. Since all the databases contain the same column names, I was wondering if there is a way to join table by columns instead of rows? Or stacking all the tables from a database together on top of another temporarily?Or does anybody have an idea how I could achieve this? I have all the table names in a table.

Here's the code:

<?php
	$trimmed = trim($search);

$limit=10;

if ($trimmed == ""){
	echo 'Please enter something!!';
	exit;
}

if (!isset($search)){
	echo "We dont seem to have a search parameter!";
	exit;
}

//loop though table names, change to table_name
$sql = "select * from table01 where content like '%$search%'";

$sqlresults=mysql_query($sql);
$numrows=mysql_num_rows($sqlresults);

echo "You seached for: '" . $search . "'<br><br>";
echo "<b>Results</b><br><br>";

if ($numrows  == 0){
	echo "Sorry, your search: '". $search. "' returned zero results.";
}
else {
	if (empty($s)){
		$s=0;
	}

	$sql .= " limit $s,$limit";
	$result= mysql_query($sql) or die("Could not execute query");
	$count = 1 + $s;
	?>
	<table cellpadding="0" cellspacing="0" border="0">
	<?php
	while ($row=mysql_fetch_array($result)){
		$title = $row["content"];

		//$func_name = "highlight";
		//$func_name($search, $title);
		$highlightedhaystack = eregi_replace ($search, "<b>" . $search . "</b>", $title);
		$title = $highlightedhaystack;
		?>
		<tr>
			<td valign="top">
				<?php
				echo "<span class='menuLinks'>$count. &nbsp;</span>";
				?>
			</td>
			<td valign="top">
			<?php
				echo "<span class='menuLinks'>$title</span>";
			?>
			<br><br>
			</td>
		</tr>				
		<?php
		$count++ ;
	}
	?>
	</table>
	<?php
	$currPage = (($s/$limit) + 1);
	echo "<br>";

						if ($s>=1) { // bypass PREV link if s is 0
		$prevs=($s-$limit);
		print "&nbsp;<a href=\"$PHP_SELF?s=$prevs&search=$search\">&lt;&lt; 
		Prev 10</a>&nbsp&nbsp;";
	}

	// calculate number of pages needing links
	$pages=intval($numrows/$limit);

	// $pages now contains int of pages needed unless there is a remainder from division

	if ($numrows%$limit) {
		// has remainder so add one page
		$pages++;
	}

	// check to see if last page
	if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

		// not last page so give NEXT link
		$news=$s+$limit;

		echo "&nbsp;<a href=\"$PHP_SELF?s=$news&search=$search\">Next 10 &gt;&gt;</a>";
	}

	$a = $s + ($limit) ;
	if ($a > $numrows) { 
		$a = $numrows ;
	}
	$b = $s + 1 ;
	echo "<p>Showing results $b to $a of $numrows</p>";

}
?>

[Lars_Berg]

Since all the databases contain the same column names

I assume that you mean tables in the above sentence.

You should not design a database like that. There is no need to create identical tables. Use one table and a type field.

[Cherphas]

🙂 I see what you mean Lars, that is a good advice! Thanks.

I have one concern though. Since I'm building a db driven web site for a client, using ids in one table to represent actual data from other tables can become confusing. This is to keep duplications to a minimum, thus saving space, right? Won't that be too confusing for my client since they will update the db? Should I sacrifice space for less confusion or be very strict with having no duplication of data? I don't know if what I said made any sense! Anyway...

Any more tips like that?

ie:
-What you just said
-Keep duplications to a minimum

[ultraslacker]

Database design should be free from presentation concerns. You can have a proper database design and still present the data in a denormalized fashion...that's what joins are for.

It is a tenet of relational databases that the data structure can change without impacting the presentation layer.

From E. F. Codd's 12 Rules for Fully Relational DBMS

Rule 8: Physical Data Independence

Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representation or access methods.

Rule 9: Logical Data Independence

Application programs and terminal activities remain logically unimpaired when information preserving changes of any kind that theoretically permit unimpairment are made to the base tables.

[dnszero]

To answer you question directly, it sounds like you should look at the MERGE table feature in mysql. http://www.mysql.com/doc/en/MERGE.html
It basically allows you to take several tables with identical columns and treat them as 1 big table. There's some pro's and con's to this, but it can be really handy in the right situation.

The other posters have presented good advice which I do agree with, but... keep in mind that Normalization is tool that is usefull in DB design and management. (I didn't read your code, btw, so I'm speaking generally). Since, it is just a tool, do not confuse it for the "WAY" or for the "ONLY WAY" to do it. A good tool/technique should facilitate db design, use and maintenance, but there is no magic solution or a one-size fits all way to do it. So don't feel tied down by any one technique. With that said, normalization is generally 1 technique that should not be tossed aside lightly. (Just wanted to point that out, sorry for the digression.)