MySQL JOIN Help

[Hobbyist_PHPer]

MySQL version 5.1.61-log

 

Hi, I was hoping to get some help with a join... I have these two tables:

 

RequirementTypes

RequirementTypeID

RequirementType

 

OrderTicketRequirements

OrderTicketRequirementID

OrderTicketID

OrderTicketRequirement

 

These are the two separate queries that I need to join together:

 

$result = mysql_query("SELECT RequirementType FROM RequirementTypes WHERE 1");

 

$result2 = mysql_query("SELECT OrderTicketRequirement FROM OrderTicketRequirements WHERE OrderTicketID = '{$_GET['id']}'");

 

The results are printed out in HTML Form Checkboxes and I need to set the ones that match to "Checked"

echo '<input type="checkbox" name="OrderTicketRequirements" value="'.$row['RequirementType'].'" />'.$row['RequirementType'].'<br />';

 

As you can see, I need to list all of the RequirementType from the first table, and then I want to ECHO in "Checked" value for those that match from the second query.

 

EDIT: I forget to mention that, RequirementType  & OrderTicketRequirement  are the matching fields between the two tables...

[jazzman1]

Don't use too long names, assign $_GET['id'] to a variable

$id = intval($_GET['id']); 

Try,

$query = "SELECT `r`.`RequirementType` FROM RequirementTypes AS `r` 
JOIN `OrderTicketRequirements` AS `o` ON `r`.`RequirementType` = `o`.`OrderTicketRequirement `
WHERE `o`.`OrderTicketID` = $id"

[Christian F.]

I don't see any relation between those two tables, so could you please point out what your PKs and FKs are and the reference between these two tables?

[Hobbyist_PHPer]

I don't see any relation between those two tables, so could you please point out what your PKs and FKs are and the reference between these two tables?

 

RequirementTypes

RequirementTypeID (PK, INT, AI)

RequirementType

 

OrderTicketRequirements

OrderTicketRequirementID (PK, INT, AI)

OrderTicketID

OrderTicketRequirement

 

I can that there is something messed up about this, 'cause how it is, it doesn't allow for a Foreign Key...

 

I have a table called OrderTickets and a table called OrderTicketRequirements ... There is also a table called RequirementTypes, which is just a list of all of the different types of OrderTicketRequirements that can be selected to go with an OrderTicket... On a page for editing the OrderTicket, I have to once again list all of the potential OrderTicketRequirements, but also put in a check for those that are in the OrderTicketRequirements table that are connected with that particular OrderTicket, by the OrderTicketID

[Hobbyist_PHPer]

Don't use too long names, assign $_GET['id'] to a variable

$id = intval($_GET['id']); 

Try,

$query = "SELECT `r`.`RequirementType` FROM RequirementTypes AS `r` 
JOIN `OrderTicketRequirements` AS `o` ON `r`.`RequirementType` = `o`.`OrderTicketRequirement `
WHERE `o`.`OrderTicketID` = $id"

 

 

I tried the query, it didn't work.

[Christian F.]

Hmm.. I think you'll find

quite useful. Should teach you how to properly set up a relational database model, and help avoid you getting into situations like the above.

 

[Hobbyist_PHPer]

I know this way isn't the best way to do it, but it nearly works... The only problem that I have with it so far is that it repeats the RequirementTypes

 

<td>
    <?
    $result3 = mysql_query("SELECT RequirementType FROM RequirementTypes WHERE 1");
    $result3Counter = mysql_num_rows($result3);
    $result3Counter = $result3Counter / 2;
    $myCounter = 0;
    while ($row3 = mysql_fetch_assoc($result3))
    {
        $myCounter++;
        if ($myCounter <= $result3Counter)
        {
            $result5 = mysql_query("SELECT OrderTicketRequirement FROM OrderTicketRequirements WHERE OrderTicketID = '{$_GET['id']}'");
            while ($row5 = mysql_fetch_assoc($result5))
            {
                    echo '<input type="checkbox" name="OrderTicketRequirements" value="'.$row3['RequirementType'].'"'; if ($row3['RequirementType'] == $row5['OrderTicketRequirement']){echo ' checked ';}  echo '/>'.$row3['RequirementType'].'<br />';
            }
        }
    }
    ?>
</td>
<td>
    <?
    $result3 = mysql_query("SELECT RequirementType FROM RequirementTypes WHERE 1");
    $result3Counter = mysql_num_rows($result3);
    $result3Counter = $result3Counter / 2;
    $myCounter = 0;
    while ($row3 = mysql_fetch_assoc($result3))
    {
        $myCounter++;
        if ($myCounter > $result3Counter)
        {
        $result5 = mysql_query("SELECT OrderTicketRequirement FROM OrderTicketRequirements WHERE OrderTicketID = '{$_GET['id']}'");
            while ($row5 = mysql_fetch_assoc($result5))
            {
                    echo '<input type="checkbox" name="OrderTicketRequirements" value="'.$row3['RequirementType'].'"'; if ($row3['RequirementType'] == $row5['OrderTicketRequirement']){echo ' checked ';}  echo '/>'.$row3['RequirementType'].'<br />';
            }
        }
    }
    ?>
</td>

[jazzman1]

Wow..... never ever do this 

Give me descriptions of these tables.

EXPLAIN `RequirementTypes`

EXPLAIN `OrderTicketRequirements`

[fenway]

That JOIN you were provided with should work -- what's the error?

[Hobbyist_PHPer]

Wow..... never ever do this 

Give me descriptions of these tables.

EXPLAIN `RequirementTypes`

EXPLAIN `OrderTicketRequirements`

 

There's an OrderTickets table and its Primary Key is OrderTicketID => Integer, auto-increment ... When a client fills out an Order Ticket, the ticket can have multiple Order Ticket Requirements, that's why there's an OrderTicketRequirements table, which is as follows:

 

OrderTicketRequirements

OrderTicketID

OrderTicketRequirement

 

For each OrderTicketRequirement that an OrderTicket has, there is a row entered into the OrderTicketRequirements table, putting the OrderTicketID and the OrderTicketRequirement in it ...

 

For consistency sake, I created a table called RequirementTypes, which is just a list of all of the different OrderTicketRequirements that can be chosen from when adding them to an Order Ticket ... So in the HTML form when a client is filling it out, a checkbox list of all the different types of RequirementTypes are displayed for the client to choose from ...

 

The problem is that when I want a client to be able to go in and EDIT the OrderTicket, I need to show the list of RequirementTypes like I do on the INSERT form, but also to have the ones that are linked to that particular OrderTicket, put a "checked" value in the checkbox inputs ... so that the client can see what Requirements were previously selected for that OrderTicket, and be able to deselect them or select new ones ...

 

I hope this explains everything ...

[Hobbyist_PHPer]

That JOIN you were provided with should work -- what's the error?

 

There wasn't an error, it only output a single RequirementType, there's 6 rows in that table.

[jazzman1]

There wasn't an error, it only output a single RequirementType, there's 6 rows in that table.

 

You said in post # 4 - it didn't work.

Have you tried to loop the content?

Post the script that you used before.

 

EXPLAIN `RequirementTypes` and EXPLAIN `OrderTicketRequirements` are not just regular words.

Explain is a sql statement, if you want to see the result running it in your  mysql client application.

[fenway]

That JOIN you were provided with should work -- what's the error?

 

There wasn't an error, it only output a single RequirementType, there's 6 rows in that table.

 

Then show us the data in the rows you expect to see.