I'm having a huge problem searching through about 15 tables of my database..
my code is below.
$bigarray = array("abeparts" => array("ABEPN", "CABEPN", "QUANTITY",
"PRICE"),
"avdelspareparts" => array("AVDELPN", "CAVDELPN", "PRICE"),
"avdeltextronparts" => array("AVDELPN", "CAVDELPN", "SO", "QUANTITY",
"PRICE"),
"cfparts" => array("CFPN", "PEMPN", "PRICE"),
"gudenparts" => array("GUDENPN", "CGUDENPN"),
"heycoparts" => array("HEYCOPN", "CHEYCOPN"),
"keystoneparts" => array("KEYSTONEPN", "CKEYSTONEPN", "RESALE"),
"popparts" => array("POPPN", "CPOPPN", "CREATIVEPN", "CCREATIVEPN",
"MARSONPN", "CMARSONPN", "GESIPAPN", "CGESIPAPN", "CELUSPN", "CCELUSPN",
"AVDELPN", "CAVDELPN", "AUTOMATICPN", "CAUTOMATICPN"),
"rafparts" => array("RAFPN", "CRAFPN", "SMITHPN", "CSMITHPN", "AMATOMPN",
"CAMATOMPN", "RICHCOPN", "CRICHCOPN", "CONCORDPN", "CCONCORDPN",
"CATAMOUNTPN", "CCATAMOUNTPN", "KEYSTONEPN", "CKEYSTONEPN", "PROMPTUSPN",
"CPROMPTUSPN", "USECOPN", "CUSECOPN", "JOHNSONPN", "CJOHNSONPN",
"LYNTRONPN", "CLYNTRONPN", "CAMBIONPN", "CCAMBIONPN"),
"rayconparts" => array("RAYCONPN", "CRAYCONPN", "3MPN", "C3MPN",
"ADAMTECHPN", "CADAMTECHPN", "AIPN", "CAIPN", "AMPPN", "CAMPPN", "AUGATPN",
"CAUGATPN", "BURNDYPN", "CBURNDYPN", "LEOCOPN", "CLEOCOPN", "MILMAXPN",
"CMILMAXPN", "MOLEXPN", "CMOLEXPN", "RNPN", "CRNPN", "SAMTECPN",
"CSAMTECPN", "TBPN", "CTBPN"),
"recoilparts" => array("RECOILPN", "CRECOILPN", "MSPN", "PRICE"),
"southcoparts" => array("SOUTHCOPN", "CSOUTHCOPN", "PRICE"));
$num = 0;
echo "<p>Results:</p>";
while (list($key1) = each ($bigarray)) {
$subquery = "SELECT DESCRIPTION, ";
$subquery .= join( ", ", $bigarray[$key1]);
$subquery .= " from $key1 where ";
while (list($key2, $val) = each ($bigarray["$key1"])) {
$subquery .= " $val like '$PARTNUM%' or ";
}
$subquery .= " DESCRIPTION LIKE '$PARTNUM%'";
$query = mysql_query($subquery);
echo $subquery;
echo '<br>';
/* while (list($key1) = each ($bigarray)) {
while (list($key2, $val) = each
($bigarray["$key1"])) {
$subquery = "SELECT DESCRIPTION, ";
$subquery .= join( ", ", $bigarray[$key1]);
$subquery .= " from $key1 where ";
$subquery .= " $val like '$PARTNUM%'";
$query = mysql_query($subquery);
echo $subquery;
echo '<br>';
*/
// now print out the form with results
if ($row = mysql_fetch_array($query)) {
echo "<form action=\"order.php\" method=\"POST\">";
echo "<table border=\"1\" width=\"80%\" align=\"center\"><tr>";
$mainpart = $bigarray[$key1][0];
if ($mainpart != $val) {
echo "<td>$mainpart</td>";
}
echo "<td>$val</td>";
echo "<td>Description</td>";
echo "<td>Price</td>";
echo "<td>Order</td>";
echo "</tr>";
do {
echo "<tr>";
if ($mainpart != $val) {
echo "<td>$row[$mainpart]</td>";
}
echo "<td><input type=\"text\" size=\"20\" name=\"PARTCOLUMN[$num]\"
value=\"$row[$val]\"</td>";
echo "<td><input type=\"text\" size=\"40\" name=\"DESCRIPTION[$num]\"
value=\"$row[DESCRIPTION]\"></td>";
echo "<td><input type=\"text\" name=\"PRICE[$num]\"
value=\"$row[PRICE]\"></td>\n";
echo "<td><input type=\"checkbox\" name=\"ORDER[$num]\"
value=\"checked\"></td>\n";
echo "</tr>";
$num++;
} while ($row = mysql_fetch_array($query));
echo "</table>";
echo "<p align=\"center\"><input type=\"submit\"></p>";
echo "</form>";
}
Ok.. now, the problem is with the first one is that it does a number of
queries equal to the total number of databases, with queries that look like:
SELECT DESCRIPTION, RECOILPN, CRECOILPN, MSPN, PRICE from recoilparts where
RECOILPN like '100%' or CRECOILPN like '100%' or MSPN like '100%' or PRICE
like '100%' or DESCRIPTION LIKE '100%'
The problem is, I don't know how to tell it which of those fields it
matched.. I.E., if there are parts where MSPN is like '100%', I want to
display the MSPN part numbers on a form. If '100%' is in the description, I
want to display that.
The other way I was doing it, which is commented out below, is to break each
thing up into a seperate query.. and use the variable $val to keep the name
of that column, and use it to print out the results...
SELECT (etc) from tablename where RECOILPN like '100%'
SELECT (etc) from tablename where CRECOILPN like '100%'
SELECT (etc) from tablename where MSPN like '100%'
so then I know which column matched, because it's the only column.. however
this is terribly inefficient and takes about 35-40 seconds to do all these
querys (50+)..
So what I need is a way to find what column it's matching in those big
querys, and assign it a variable so I can use it and the results from it in
my form.
Any ideas? I'm totally stumped.
Thanks in advance,
Chad
my code is below.
$bigarray = array("abeparts" => array("ABEPN", "CABEPN", "QUANTITY",
"PRICE"),
"avdelspareparts" => array("AVDELPN", "CAVDELPN", "PRICE"),
"avdeltextronparts" => array("AVDELPN", "CAVDELPN", "SO", "QUANTITY",
"PRICE"),
"cfparts" => array("CFPN", "PEMPN", "PRICE"),
"gudenparts" => array("GUDENPN", "CGUDENPN"),
"heycoparts" => array("HEYCOPN", "CHEYCOPN"),
"keystoneparts" => array("KEYSTONEPN", "CKEYSTONEPN", "RESALE"),
"popparts" => array("POPPN", "CPOPPN", "CREATIVEPN", "CCREATIVEPN",
"MARSONPN", "CMARSONPN", "GESIPAPN", "CGESIPAPN", "CELUSPN", "CCELUSPN",
"AVDELPN", "CAVDELPN", "AUTOMATICPN", "CAUTOMATICPN"),
"rafparts" => array("RAFPN", "CRAFPN", "SMITHPN", "CSMITHPN", "AMATOMPN",
"CAMATOMPN", "RICHCOPN", "CRICHCOPN", "CONCORDPN", "CCONCORDPN",
"CATAMOUNTPN", "CCATAMOUNTPN", "KEYSTONEPN", "CKEYSTONEPN", "PROMPTUSPN",
"CPROMPTUSPN", "USECOPN", "CUSECOPN", "JOHNSONPN", "CJOHNSONPN",
"LYNTRONPN", "CLYNTRONPN", "CAMBIONPN", "CCAMBIONPN"),
"rayconparts" => array("RAYCONPN", "CRAYCONPN", "3MPN", "C3MPN",
"ADAMTECHPN", "CADAMTECHPN", "AIPN", "CAIPN", "AMPPN", "CAMPPN", "AUGATPN",
"CAUGATPN", "BURNDYPN", "CBURNDYPN", "LEOCOPN", "CLEOCOPN", "MILMAXPN",
"CMILMAXPN", "MOLEXPN", "CMOLEXPN", "RNPN", "CRNPN", "SAMTECPN",
"CSAMTECPN", "TBPN", "CTBPN"),
"recoilparts" => array("RECOILPN", "CRECOILPN", "MSPN", "PRICE"),
"southcoparts" => array("SOUTHCOPN", "CSOUTHCOPN", "PRICE"));
$num = 0;
echo "<p>Results:</p>";
while (list($key1) = each ($bigarray)) {
$subquery = "SELECT DESCRIPTION, ";
$subquery .= join( ", ", $bigarray[$key1]);
$subquery .= " from $key1 where ";
while (list($key2, $val) = each ($bigarray["$key1"])) {
$subquery .= " $val like '$PARTNUM%' or ";
}
$subquery .= " DESCRIPTION LIKE '$PARTNUM%'";
$query = mysql_query($subquery);
echo $subquery;
echo '<br>';
/* while (list($key1) = each ($bigarray)) {
while (list($key2, $val) = each
($bigarray["$key1"])) {
$subquery = "SELECT DESCRIPTION, ";
$subquery .= join( ", ", $bigarray[$key1]);
$subquery .= " from $key1 where ";
$subquery .= " $val like '$PARTNUM%'";
$query = mysql_query($subquery);
echo $subquery;
echo '<br>';
*/
// now print out the form with results
if ($row = mysql_fetch_array($query)) {
echo "<form action=\"order.php\" method=\"POST\">";
echo "<table border=\"1\" width=\"80%\" align=\"center\"><tr>";
$mainpart = $bigarray[$key1][0];
if ($mainpart != $val) {
echo "<td>$mainpart</td>";
}
echo "<td>$val</td>";
echo "<td>Description</td>";
echo "<td>Price</td>";
echo "<td>Order</td>";
echo "</tr>";
do {
echo "<tr>";
if ($mainpart != $val) {
echo "<td>$row[$mainpart]</td>";
}
echo "<td><input type=\"text\" size=\"20\" name=\"PARTCOLUMN[$num]\"
value=\"$row[$val]\"</td>";
echo "<td><input type=\"text\" size=\"40\" name=\"DESCRIPTION[$num]\"
value=\"$row[DESCRIPTION]\"></td>";
echo "<td><input type=\"text\" name=\"PRICE[$num]\"
value=\"$row[PRICE]\"></td>\n";
echo "<td><input type=\"checkbox\" name=\"ORDER[$num]\"
value=\"checked\"></td>\n";
echo "</tr>";
$num++;
} while ($row = mysql_fetch_array($query));
echo "</table>";
echo "<p align=\"center\"><input type=\"submit\"></p>";
echo "</form>";
}
Ok.. now, the problem is with the first one is that it does a number of
queries equal to the total number of databases, with queries that look like:
SELECT DESCRIPTION, RECOILPN, CRECOILPN, MSPN, PRICE from recoilparts where
RECOILPN like '100%' or CRECOILPN like '100%' or MSPN like '100%' or PRICE
like '100%' or DESCRIPTION LIKE '100%'
The problem is, I don't know how to tell it which of those fields it
matched.. I.E., if there are parts where MSPN is like '100%', I want to
display the MSPN part numbers on a form. If '100%' is in the description, I
want to display that.
The other way I was doing it, which is commented out below, is to break each
thing up into a seperate query.. and use the variable $val to keep the name
of that column, and use it to print out the results...
SELECT (etc) from tablename where RECOILPN like '100%'
SELECT (etc) from tablename where CRECOILPN like '100%'
SELECT (etc) from tablename where MSPN like '100%'
so then I know which column matched, because it's the only column.. however
this is terribly inefficient and takes about 35-40 seconds to do all these
querys (50+)..
So what I need is a way to find what column it's matching in those big
querys, and assign it a variable so I can use it and the results from it in
my form.
Any ideas? I'm totally stumped.
Thanks in advance,
Chad