Having trouble, multiple tables with one query

kilbey1

I am having a heck of a time laying this out. Maybe a second set of eyes can assist.

We have:
1 text field - users can enter anything, titled "Searchbox"
4 database tables:
placement_primarytech = holds skill titles
placement_candidate = holds all candidate information
placement_position = shows open positions available at the time
placement_candidatetech = cross-reference table for placement_primarytech and placement_candidate

Scenario:
User enters "C++" for a skill a candidate must have.
Result page pulls back every candidate with C++ listed in their resume.

The 3 tables I primarily want to work with are placement_primarytech, placement_candidate and placement_candidatetech; this is only good for a SKILL search so far, and not a name search.

The query in question:


//THIS IS SEARCHING BY SKILL ONLY!
					$sql = "SELECT Technology.PrimaryTechID, placement_candidatetech.PrimaryTechID 
					FROM placement_candidatetech,placement_primarytech AS Technology
					WHERE Technology.PrimaryTechnology LIKE '%C%'
					AND placement_candidatetech.PrimaryTechID = Technology.PrimaryTechID";
					//echo $sql;
					$result = mysql_query($sql);
					while($row=mysql_fetch_array($result)) {
						echo $row['PrimaryTechID'] . "<BR>";
					}

I still don't get the results back correctly, first off.
Second, I need to be able to cross-reference between placement_candidate.CandidateID and placement_candidatetech.CandidateID.

kilbey1

Alright, this is worked out. Now... instead of hard-coded values, I'm trying to put in variables for the LIKE statements. I don't remember how to do this. 🙁


			    /* connect to database */
  $link = mysql_connect("localhost", "dbuser", "dbpass")
        or die("Could not connect: " . mysql_error());
   mysql_select_db("wowcorp");
//THIS IS SEARCHING BY SKILL ONLY!
					$sql = "SELECT *
					FROM placement_primarytech
					WHERE PrimaryTechnology LIKE 'C'
					";
					$result = mysql_query($sql);
					while($row=mysql_fetch_array($result)) {
						echo $row['PrimaryTechID'] . " - " . $row['PrimaryTechnology'] . "<BR>";

					$sql2="SELECT *
					FROM placement_primarytech,placement_candidatetech
					WHERE placement_candidatetech.PrimaryTechID = placement_primarytech.PrimaryTechID
					AND placement_primarytech.PrimaryTechnology LIKE 'C'";
					$result2 = mysql_query($sql2);

					while($row2=mysql_fetch_array($result2)) {

						echo "&nbsp;&nbsp;" . $row2['PrimaryTechID'] . " - " . $row2['PrimaryTechnology'] . "<BR>";

						$sql3 = "SELECT *
						FROM placement_candidatetech,placement_candidate, placement_primarytech
						WHERE placement_candidatetech.CandidateID = placement_candidate.CandidateID
						AND
						placement_primarytech.PrimaryTechID = placement_candidatetech.PrimaryTechID
						AND
						placement_primarytech.PrimaryTechnology LIKE 'C'";
						$result3 = mysql_query($sql3);

						while($row3=mysql_fetch_array($result3)) {
							echo "&nbsp;&nbsp;&nbsp;&nbsp;" . $row3['CandidateID'] . " - " . $row3['FirstName'] . "<BR>";
						}

					}


				}