Page 1 of 1

searching keywords from different databses from my web page

Posted: Thu Feb 24, 2005 9:55 am
by naz
i have made a form that includes code which allows me to carry out a search i have three different databses called sofas, wardrobes and coffeetables and basically when you type in certain keywords in the textfield it should display the product


that works fine but i can only search from one database only and when i change the $sql to 1 or 2 in this piece of code

Code: Select all

if ( isset( $sql) && !empty( $sql)){
      echo "<!--".$sql."-->";
      $result = mysql_query( $sql )
         or die("Invalid query: " . mysql_error());
it retrieves the search results from that database

Code: Select all

<html>
<head>
<title>Hanover Furniture</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<STYLE type="text/css">
<!--
A {
	text-decoration:none;
	font-weight: bold;
}
A:hover { color:#FF8000 }
.style1 {font-weight: bold}
-->
</STYLE>
<link href="nav.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
.style3 {font-size: 12px}
a:link {
	color: #000000;
}
.style4 {
	color: #000000;
	font-weight: bold;
}
.style5 {color: #000000}
.style6 {color: #FFFFFF}
-->
</style>
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_reloadPage(init) {  //reloads the window if Nav4 resized
  if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) {
    document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }}
  else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload();
}
MM_reloadPage(true);
//-->
</script>
</head>

<body bgcolor="#FEFCD3"  vlink="white" alink="blue" style="overflow-x:scroll; overflow-y:hidden;" tracingsrc="dtable.jpg" tracingopacity="49">

<div id="Layer1" style="position:absolute; left:10px; top:49px; width:171px; height:22px; z-index:1"><span class="style1"><font size="5" color="#0000FF">HANOVER</font><font size="5" color="#0099FF">FURNITURE</font><font size="4"></font></span></div>
<div id="Layer1" style="position:absolute; width:96px; height:21px; z-index:1; left: 467px; top: 29px; font-family: Arial, Helvetica, sans-serif; font-size: 14px;">
  <div align="center">Quick Search </div>
</div>
<p> </p>

<p>  <br>	
</p>
<table width="644" height="29" border="0" cellpadding="0" cellspacing="0">
  <tr align="right">
    <td width="800" bgcolor="#606060">
      <div align="right" class="nav style6"><a href="main%20frame.php"> Home |</a> <a href="furniturelink.php">Furniture |</a> <a href="feedback.php" >Feedback |</a> <a href="contactus.php">Contact Us </a> </a> </div></td>
  </tr>
</table>
	   
	  


<?php
	
	$link = mysql_connect("linux2.inf.brad.ac.uk", "nmaroof1", "bradford")
      or die("Could not connect: " . mysql_error());
   mysql_select_db('nmaroof1', $link) or die ( mysql_error());
   switch( $_GETї'action'] ){
      case "search":
         switch( $_GETї'type'] ){
            case "home page":
               їb]$sqlї/b] = "SELECT sofa_image, sofa_name, sofa_id, description, dimensions, price from sofas WHERE category = '".$_POSTї'tsearch']."'
			   																							OR sofa_name ='".$_POSTї'tsearch']."'
																										OR sofa_id ='".$_POSTї'tsearch']."'";																																													
		      їb] $sql2ї/b] ="SELECT table_image, table_name, table_id, description, dimensions, price from coffeetables WHERE category = '".$_POSTї'tsearch']."'
			   																		          			OR table_name ='".$_POSTї'tsearch']."'
																										OR table_id ='".$_POSTї'tsearch']."'";		
			    їb]$sql3ї/b] ="SELECT wardrobe_image, wardrobe_name, wardrobe_id, description, dimensions, price from wardrobes WHERE category = '".$_POSTї'tsearch']."'
			   																		          			OR wardrobe_name ='".$_POSTї'tsearch']."'
																										OR wardrobe_id ='".$_POSTї'tsearch']."'";		
			 
			     
               break;
         }
         break;
   }
   if ( isset( $sql) && !empty( $sql)){
      echo "<!--".$sql."-->";
      $result = mysql_query( $sql )
         or die("Invalid query: " . mysql_error());
		 
		
		 

print '<table width="577" height="187" border="0" cellpadding="0" cellspacing="0">';
while($row = @ mysql_fetch_row($result))


{
$imgdir = 'http://linux2.inf.brad.ac.uk:59285/Unnamed%20Site%201/images/' . $rowї0];

    print '<tr>';
    print "<td width='184' rowspan='3'><img width='240' height='160' border='0' src=$imgdir ></td>";
    print '<td width="393" height="33" class="nav">' . $rowї1] . ' id: ' .$rowї2] . '</td>';
    print '</tr>';
   
	print '<tr>';
    print '<td height="138" class="nav"><div id="Layer1" style="position:absolute; width:180px; height:144px; z-index:1; top: 176px;" class="nav"></div>' .$rowї3] . '</td>';
    print '</tr>'; 
	 
    print '<tr>';
    print '<td height="38" class="nav">' .$rowї4] . ' ' .$rowї5] . '<hr></td>';
    print '</tr>';}
	 
    print '</table>';
		 

  }
  
 
  
  ?>
<div id="Layer2" style="position:absolute; width:242px; height:45px; z-index:2; left: 432px; top: 50px;">
  <form name="form1" method="post" action="">
    <input name="tsearch" type="text" id="tsearch">
    <input name="search" type="submit" id="search" value="Search">
  </form>
</div>
</body>
</html>

in this code you can see that the little section i have highlighted in bold is why this works

can someone please tell me what to to in order to make sure all the queries can work at the same time

thanks in advance



feyd | please start using

Code: Select all

while

Code: Select all

is down!! [/color]

Posted: Thu Feb 24, 2005 10:03 am
by feyd
your code is set up such that it will only search one table (not database) at a time. You can use a UNION to stick the queries together, or you will need to query all three seperately and pool the results.

Posted: Thu Feb 24, 2005 10:15 am
by naz
sorry but i dont undesrtaand u what do u mean by union or pool the results

Posted: Thu Feb 24, 2005 11:27 am
by feyd
http://dev.mysql.com/doc/mysql/en/union.html

pooling results = combining the results in code by either using an array storage or just sequentially querying the next batch to seach through..

your tables appear similar enough such that you could combine the product lines together into a single table (differenciating the products with a 'category' field)

Posted: Thu Feb 24, 2005 11:34 am
by naz
feyd wrote:http://dev.mysql.com/doc/mysql/en/union.html

pooling results = combining the results in code by either using an array storage or just sequentially querying the next batch to seach through..

your tables appear similar enough such that you could combine the product lines together into a single table (differenciating the products with a 'category' field)
would i have to combine the product lines together in a single table throught sql or what? if yes how would i go about doing that so everything is retrieved from one table including sofas wardrobes and tables

Posted: Thu Feb 24, 2005 11:44 am
by feyd
yes, a single database table. You create a new table that uses "generic" names for each field from the original tables.. export the original data, import it to the new table (update all new rows with correct category)

Posted: Thu Feb 24, 2005 12:19 pm
by naz
feyd wrote:yes, a single database table. You create a new table that uses "generic" names for each field from the original tables.. export the original data, import it to the new table (update all new rows with correct category)
once this new table has been created and if i decide to add new products to any of my other tables such as a new table will i be able to add it to this new table at the same time so when u carry out a search it retrieves the new item that has been added. would i have to insert a second sql query on that particular page?

Posted: Thu Feb 24, 2005 12:21 pm
by feyd
no.. remove the old tables. Only use the single table.

Posted: Thu Feb 24, 2005 12:26 pm
by naz
feyd wrote:no.. remove the old tables. Only use the single table.
cheers mate thanks for your advice i will give it a go and let u know the outcome. so stupid of me not to think of this anyway thanks again for your advice let u know the outcome later on

Posted: Sat Feb 26, 2005 3:43 pm
by naz
naz wrote:
feyd wrote:no.. remove the old tables. Only use the single table.
cheers mate thanks for your advice i will give it a go and let <span style='color:blue' title='ignorance is bliss'>you</span> know the outcome. so stupid of me not to think of this anyway thanks again for your advice let <span style='color:blue' title='ignorance is bliss'>you</span> know the outcome later on

cheers mate for all ur advice it has worked