5
\$\begingroup\$

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 <_<
    $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.

\$\endgroup\$
  • \$\begingroup\$ love the question title :D \$\endgroup\$ – dss539 Jul 29 '14 at 17:01
1
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Okay, it was way simpler than I realized. It was the sort of thing that I think most people are aware of so they don't mention it. It literally just joins the tables and that allows you to get the information from them. My mind is blown, thank you very much. \$\endgroup\$ – Pquch Jul 29 '14 at 17:38
  • 1
    \$\begingroup\$ @Pquch I suggest you read about the different kinds of JOINs. The one here is an implicit INNER JOIN; you'll want to read about LEFT (OUTER) JOIN and RIGHT (OUTER) JOIN. (There's also CROSS JOIN but it's pretty niche in use case. Incidentally, a CROSS JOIN b is the same as SELECT FROM a, b.) \$\endgroup\$ – Schism Jul 29 '14 at 18:15
  • \$\begingroup\$ I've begun looking into them a lot more due to how simple and important to database structure JOINs are. I found a really cool diagram that lays JOINs out very simply but I've yet to delve into them too much. I do have a few more intricate database relationships in my current project so I imagine I'll know a lot more about them by week's end. Thanks! \$\endgroup\$ – Pquch Jul 30 '14 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.