EDIT: Updated to include some current code for context, and added questions in bold
Newbie here:
I will have 20 or so tables of manufacturers filled with 15,000-100,000 rows of parts in each table. Information such as PartNumber, Superceding Part Number, List Price, MFG Price, Sale Price, etc will be stored as columns. I would like to be able to start typing a part number in a search box and have it display all the parts that match the numbers so far, and then update as I enter more of the number.
I currently have a version working with 1 table of 40,000 parts where as I type a few numbers such as '0000', it will return the top 5 parts beginning with 0000, such as: 000011, 000013,00015,000023, and 000025 and some corresponding info on them. If I type one more number '00002' it will only display 000023 and 000025. However I would like this search to run over all tables in the database in case I do not know which manufacturer made the part.
Currently I have a database named taft_test1, with two tables named ariens_prices and briggs_prices. Each table will contain the same columns, but possibly not have them filled with data on every entry.
Any ideas on the best way to go about this?
Some have mentioned UNION which is ok but should I do 20+ unions or might there be a better way to layout the database?
Appreciate the help.
Sample Code I have working:
partslookup.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Taft Power Equipment</title>
<script type="text/javascript" language="Javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script src="js/jquery.horizontalNav.js"></script>
<script>
// When document is ready...
$(document).ready(function() {
$('.full-width').horizontalNav({}); // Call horizontalNav on the navigations wrapping element
});
</script>
<script>
function showUser(str)
{
if (str=="")
{
document.getElementById("txtHint").innerHTML="";
return;
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","livesearch.php?q="+str,true);
xmlhttp.send();
}
</script>
<link rel="stylesheet" type="text/css" href="style.css" />
<!--[if lt IE 7]>
<link rel="stylesheet" type="text/css" href="style-ie.css" />
<![endif]-->
</head>
<body>
<div id="page-wrap">
<div id="inside">
<div id="header">
<a href="file:///C:/Users/ryan/Desktop/websites/ryan%20website/index.html"><img src="images/TaftLogo2.png" alt="header" /></a>
</div>
<div id="menu">
<nav class="horizontal-nav full-width horizontalNav-notprocessed">
<ul>
<li><a href="index.html">Home</a></li>
<li><a href="#">Work</a></li>
<li><a href="#">Blog</a></li>
<li><a href="#">About</a></li>
<li><a href="#">Contact</a></li>
<li><a href="partslookup.php">Parts Look Up</a></li>
</ul>
</nav>
</div>
<div id="main-content">
<input id="test" class="auto" type="text" size="25" placeholder="Enter Part Here" onkeyup="showUser(this.value)"
<br><br><br>
<div id="txtHint"><b>Part info will display here.</b></div>
<br><br><br><br>
</div>
<div style="clear: both;"></div>
<div id="footer">
<p>© Copy Right Taft Power Equipment Corp</p>
</div>
</div>
</body>
</html>
livesearch.php
<?php
$q = $_GET['q'];
echo "Part Entered: $q <br><br>";
$dbhost = 'localhost:3306';
$dbuser = 'root';
$dbpass = 'password';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = "SELECT * FROM `briggs_prices` WHERE briggs_prices.Description LIKE '$q%'
UNION
SELECT * FROM `ariens_prices` WHERE ariens_prices.Description LIKE '$q%'
LIMIT 0, 50 ";
mysql_select_db('taft_test1');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not get data: ' . mysql_error());
}
echo "<table border='1'>
<tr>
<th>Part#</th>
<th>Description</th>
<th>Price</th>
</tr>";
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
echo "<tr>";
echo "<tr>";
echo "<td>" . $row['Description'] . "</td>";
echo "<td>" . $row['SuperNum'] . "</td>";
echo "<td>" . $row['LIST1'] . "</td>";
echo "</tr>";
}
echo "</table>";
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
echo "Part# :{$row['Description']} ".
"Desc : {$row['SuperNum']} ".
"Cost : {$row['LIST1']} ".
"--------------------------------<br>";
}
//echo "Fetched data successfully\n";
mysql_close($conn);
?>
You need a better database layout. Something like:
(P) means Primary Key
(F) means Foreign Key
(I) means Indexed
manufacturers
id unsigned int(P)
name varchar(50)
...
+----+--------+-----+
| id | name | ... |
+----+--------+-----+
| 1 | Ariens | ... |
| 2 | Briggs | ... |
| .. | ...... | ... |
+----+--------+-----+
parts
id unsigned int(P)
number char(50)(I)
manufacturer_id unsigned int(F manufacturers.id)
name varchar(75)
superceding_part_id unsigned int(F parts.id) // Allow NULL
list_price double
manufacturer_price double
sale_price double
...
+----+--------+-----------------+----------+---------------------+------------+--------------------+------------+-----+
| id | number | manufacturer_id | name | superceding_part_id | list_price | manufacturer_price | sale_price | ... |
+----+--------+-----------------+----------+---------------------+------------+--------------------+------------+-----+
| 1 | 000011 | 1 | Widget A | NULL | 1.00 | .50 | .90 | ... |
| 2 | 000012 | 1 | Widget B | 1 | 2.00 | .75 | 1.75 | ... |
| 3 | 000013 | 2 | Widget A | NULL | 1.15 | .70 | 1.00 | ... |
| .. | ...... | ............... | ........ | ................... | .......... | .................. | .......... | ... |
+----+--------+-----------------+----------+---------------------+------------+--------------------+------------+-----+
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加