I think I should use JOIN but I did this instead Let me start by saying that I am brand new to PHP and MySQL.

I have two tables. One table is called domains and it contains the "rep" column which is an integer. It is related to the column "id" in the reps table. The reps table also houses the First and Last names of the rep assigned to a particular domain and when I query the database to show all of the domains, I need to show rep.first_name instead of domain.rep.

The first way I thought to do it was to make a loop that will create variables with the rep names and then inside of my loop to list the domains, I'll show them. It looks like this:

//Make Query
$rep_query = "SELECT * ";
$rep_query .= "FROM reps";
$rep_query_result = mysqli_query($connection, $rep_query);
//Test for Query Error
confirm_query($rep_query_result);
$rep_i = 1;
$rep_array = array("");
while($rep = mysqli_fetch_assoc($rep_query_result)) {
    ${'rep_' . $rep_i} = $rep['first_name'];
    array_push($rep_array, ${'rep_' . $rep_i});
    $rep_i++;
}


and inside of the loop where I'm looping through the domains, I can use the variable

$rep_array[$domains['rep']]


I then did some research on JOINs and realized this is the more common way to handle relationships and things of this sort, but my way seems okay.

What would you do differently?

Here is the domain query:

    //Make Query
    $domain_query = "SELECT * "; //IGNORE THIS FOR NOW &lt;_&lt;
    $domain_query .= "FROM domains ";
    $domain_query .= "WHERE status = 'Available' ";
    $domain_query .= "ORDER BY ". $order;
    $domain_query_result = mysqli_query($connection, $domain_query);
    //Test for Query Error
    confirm_query($domain_query_result);


and this is the join I came up with:

    $rep_query = "SELECT ";
    $rep_query .= "r.first_name, d.rep ";
    $rep_query .= "FROM `domains` AS d ";
    $rep_query .= "INNER JOIN `reps` AS r ";
    $rep_query .= "ON r.id = d.rep ";


How is it that I use the JOIN inside of the other query / loop to automatically set the IDs to the first_name? This seems to be the part that doesn't get explained in many of the tutorials and such that I've found.
First, about your current code:


You're doing a SELECT *. This is generally poor practice; instead, explicitly identify the columns you need: SELECT id, first_name.
Your $rep_array is a lookup table, so name it as such. Maybe $rep_id_to_name or something?
Your way of adding to the array is bad; it won't work if the rep ids are not contiguous. Instead, just directly set the id:

$rep_array = array();
while ($rep = mysqli_fetch_assoc($rep_query_result)) {
    $rep_array[$rep['id']] = $rep['first_name'];
}



Now, about using a JOIN...


I don't know the query for domains, but I'll assume it's something like this:

SELECT d.rep, ...
FROM `domains` d
WHERE ...


Then in order to use a join, you could do (note the absence of d.rep in the SELECT list:

SELECT r.first_name AS rep, ...
FROM `domains` d
JOIN `reps` r ON d.rep = r.id
WHERE ...


This will get the behaviour you want, but it wouldn't hurt to give a more meaningful name to rep. Keyboard strokes are (almost) free! Consider representative_name or something.