me and my buddy got some project for our thesis.
our problem is how can we make a Universal Search Page. a search page that can query any table in our database.
there will be 3 inputs.
1. Key Search
2. Search Type(it will be a dropdown list that contains the table headers, something like that)
3. Condition
any help will be greatly appreciated..
thank you very much in advance..
lets you have three tables in database.
You can manually check in all tables by placing if-else.
you can combine all above 3 inputs and make select query to generate output.
i see..
can you help me with a sample code..
so i have something as a guide.
thanks!!
@vib:
can you help me with the codes?just a sample code
so that i can use it as a guide..
thanks!!
thanks for the tip...
i've got some codes here and can any check if there is a problem with the codes and can anyone please help me with the code on how can i display the result..
thanks in advance
<html>
<head><title>Inventory</title>
</head>
<body>
< ?php
$hostname_logon = "localhost" ;
$database_logon = "databaseName" ;
$username_logon = "databaseUser" ;
$password_logon = "databasePass" ;
//open database connection
$connections = mysql_connect($hostname_logon, $username_logon, $password_logon) or die ( "Unabale to connect to the database" );
//select database
mysql_select_db($database_logon) or die ( "Unable to select database!" );
//specify how many results to display per page
$limit = 10;
// Get the search variable from URL
$var = @$_GET['q'] ;
$s = $_GET['s'] ;
//trim whitespace from the stored variable
$trimmed = trim($var);
//separate key-phrases into keywords
$trimmed_array = explode(" ",$trimmed);
// check for an empty string and display a message.
if ($trimmed == "") {
$resultmsg = "<p>Search Error</p><p>Please enter a search...</p>" ;
}
// check for a search parameter
if (!isset($var)){
$resultmsg = "<p>Search Error</p><p>We don't seem to have a search parameter! </p>" ;
}
// Build SQL Query for each keyword entered
foreach ($trimmed_array as $trimm){
// EDIT HERE and specify your table and field names for the SQL query
$query = "SELECT Table1.column1, Table1.column2, Table1.column3, Table1.column4, Table1.column5, Table1.column6, Table1.column7, Table1.column8, Table1.column9, Table1.column10, Table1.column11, Table1.column12, Table2.column2, Table3.column2, Table4.column2
FROM Table1, Table2, Table3, Table4 WHERE Table1.column1 LIKE '%$trimm%' OR Table1.column2 like '%$trimm%' OR Table1.column3 like '%$trimm%' OR Table1.column4 LIKE '%$trimm%' OR Table1.column5 like '%$trimm%' OR Table1.column6 like '%$trimm%' OR Table1.column7 LIKE '%$trimm%' OR Table1.column8 like '%$trimm%' OR Table1.column9 like '%$trimm%' OR Table1.column10 LIKE '%$trimm%' OR Table1.column11 like '%$trimm%' OR Table1.column12 like '%$trimm%' OR Table1.column13 like '%$trimm%' OR Table2.column2 LIKE '%$trimm%' OR Table3.column2 like '%$trimm%' OR Table4.column2 like '%$trimm%' ((Table1.column1 = Table2.column1) and (Table1.column2 = Table3.column1) and(Table1.column3 = Table4.column1)) ORDER BY $q DESC" ;
// Execute the query to get number of rows that contain search kewords
$numresults=mysql_query ($query);
$row_num_links_main =mysql_num_rows ($numresults);
// next determine if 's' has been passed to script, if not use 0.
// 's' is a variable that gets set as we navigate the search result pages.
if (empty($s)) {
$s=0;
}
// now let's get results.
$query .= " LIMIT $s,$limit" ;
$numresults = mysql_query ($query) or die ( "Couldn't execute query" );
$row= mysql_fetch_array ($numresults);
//store record id of every item that contains the keyword in the array we need to do this to avoid display of duplicate search result.
do{
$adid_array[] = $row[ 'fieldid' ];
}while( $row= mysql_fetch_array($numresults));
} //end foreach
if($row_num_links_main == 0 && $row_set_num == 0){
$resultmsg = "<p>Search results for: ". $trimmed."</p><p>Sorry, your search returned zero results</p>" ;
}
//delete duplicate record id's from the array. To do this we will use array_unique function
$tmparr = array_unique($adid_array);
$i=0;
foreach ($tmparr as $v) {
$newarr[$i] = $v;
$i++;
}
// now you can display the results returned. But first we will display the search form on the top of the page
? >
<form name="search" method="get" action="search.php">
<div>
<input name="q" value=" < ?php echo $q; ? > " type="text">
<input name="search" value="Search" type="submit">
</div>
</form>
</body>
</html>and one more thing..
could the result should somewhat display like this:
<table cellpadding=5>
<tr bgcolor=black>
<td><font color=white><b>Table1.column1 [Table2.column1]</b></font></td>
<td><font color=white><b>Table1.column2 [Table3.column1]</b></font></td>
<td><font color=white><b>Table1.column3 [Table4.column1]</b></font></td>
<td><font color=white><b>Table1.column4</b></font></td>
<td><font color=white><b>Table1.column5</b></font></td>
<td><font color=white><b>Table1.column6</b></font></td>
<td><font color=white><b>Table1.column7</b></font></td>
<td><font color=white><b>Table1.column8</b></font></td>
<td><font color=white><b>Table1.column9</b></font></td>
<td><font color=white><b>Table1.column10</b></font></td>
<td><font color=white><b>Table1.column11</b></font></td>
<td><font color=white><b>Table1.column12</b></font></td>
<td><font color=white><b>Table1.column13</b></font></td></tr>
</table>We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.