Okay, so I may have gotten a bit in over my head here, but I’ll see if I can explain my situation simple enough so I don’t have to paste giant chunks of code, and complex enough so that I can get the answer I’m looking for.
I’m working on a project where the user will search for a product number, and receive a tree of all the parts that make up that product. For example, if they searched for Car, they’d get a table that had car, and underneath car would be door, and underneath that would be window, and so on.
The table that connects every part number to every other part number (parent to child) is a bit confusing. Not even I fully understand it, but I’ll try to explain as best as I can. There are three columns, type, prtnumberHigh, and prtnumberLow. Type can either be A or B, which I think determines whether it is a parent or a child. Somehow this connects all the parts together.
Currently, I’m using a lot of while loops and sql queries to make this “work” but I’ve run into a problem. Here’s some code to illustrate how I’ve been doing this so far.
[php]
//$stockcode comes from a $_GET earlier in the code
$query = “SELECT * FROM example_table
WHERE prtnumberHIGH LIKE '”.$stockcode."’
AND type LIKE ‘%%’
ORDER BY prtnumberHIGH ASC
";
$qr = odbc_exec($conn, $query);
if (odbc_num_rows($qr)>=1) { //check to see if query returned any results
echo '<table border="0px">';
while ($query_row = odbc_fetch_array($qr)) {
//display first parent results
echo '<tr style="padding: 5px;">
<td style="padding: 5px;">'.$query_row['type'].'</td>
<td style="padding: 5px;">'.$query_row['prtnumberHigh'].'</td>
<td style="padding: 5px;">'.$query_row['prtnumberLow'].'</td>
</tr>';
//second query to find correlation between prtnumberHigh and prtnumberLow
$query = "SELECT * FROM example_table
WHERE prtnumberHigh LIKE '".$query_row['prtnumberLow']."'
AND type LIKE '%%'
ORDER BY prtnumberHigh ASC
";
$qr = odbc_exec($conn, $query);
//check to see if parent has any children
if (odbc_num_rows($qr)>=1) {
while ($query_row = odbc_fetch_array($qr)) {
echo '<tr style="padding: 5px;">
<td style="padding: 5px;">-'.$query_row['type'].'</td>
<td style="padding: 5px;">'.$query_row['prtnumberHigh'].'</td>
<td style="padding: 5px;">'.$query_row['prtnumberLow'].'</td>
</tr>';
//3rd level query for children's children
$query = "SELECT * FROM example_table
WHERE prtnumberHigh LIKE '".$query_row['prtnumberLow']."'
AND type LIKE '%%'
ORDER BY prtnumberHigh ASC
";
$qr = odbc_exec($conn, $query);
if (odbc_num_rows($qr)>=1) {
while ($query_row = odbc_fetch_array($qr)) {
echo '<tr style="padding: 5px;">
<td style="padding: 5px;">--'.$query_row['type'].'</td>
<td style="padding: 5px;">'.$query_row['prtnumberHigh'].'</td>
<td style="padding: 5px;">'.$query_row['prtnumberLow'].'</td>
</tr>';
}
}
[/php]
My problem is that I can’t get multiple parents. (I’m somewhat fresh to PHP, so bare with me here) The results bring back the 1st level, the 2nd level (but only one item in the second level), and all the 3rd level children. I think it has something to do with the way that I’m setting up these loops. Please help?
It should look like:
Car
- Door
-
- Door Handle
-
- Window
- Roof
-
- Antenna
-
- Bike Rack
but instead it comes back like
Car
- Door
-
- Door Handle
-
- Window
What am I doing wrong?