In PHP, compare an array against a field in a MySQL table

Question

I've got two tables; one a list of location names, another a list of companies, a field of which stores potentially multiple locations that the company operates in:

id  LocationName
1   Aberdeen
2   Dundee
3   Edinburgh
4   Glasgow


idCompany   CompanyName Locations
1           CompanyA    1, 2, 3, 4
2           CompanyB    2, 4
3           CompanyC    1

For each company's details page, I want to list the locations in which they operate, displaying the name of each. Eg. for Company B:

Dundee
Glasgow

etc.

Coming from a VB background, I'd just loop through each dataset and compare each to find the matches, but this is in PHP and I can't get it to work:

// Query for specific firm
    $sqlCompany= "SELECT * FROM company_details WHERE CompanyID='".$CompanyID."';";  
    $rstCompany= mysqli_query($conn, $sqlCompany);
    $row = mysqli_fetch_assoc($rstCompany);

// Query list of Locations
    $sqlLocationNames= "SELECT * FROM Locations ORDER BY LocationName ASC;";  
    $rstLocationNames= mysqli_query($conn, $sqlLocationNames);

// Explode the field of locations into an array:    
    $LocationArray = $row["Locations"];
    $LocationArray = explode (",", $LocationArray);

    for ($i = 0; $i < count($LocationArray); $i++) {
        while ($rowLocationNames = mysqli_fetch_assoc($rstLocationNames)) {
            if ($LocationArray[$i]==$rowLocationNames["idLocation"]) {
                echo $rowLocationNames["LocationName"]."<br />";
                }
            }
        }

Running this for Company A, I'd expect to get the list of four locations from above, but I only get the first location (Aberdeen). I've tried every combination I can think of, to no avail. I need to keep the data structured this way, as I intend having a select multiple for inserting and also editing the data when I can get this working. Any ideas where I'm going wrong?

You need a Locations table with one row for every company location and join it. — AbraCadaver
– AbraCadaver, Commented Feb 11, 2019 at 21:15
WARNING: When using mysqli you should be using parameterized queries and bind_param to add any data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or data of any kind directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Feb 11, 2019 at 21:17
Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface. Before you get too invested in the procedural style it’s worth switching over. Example: $db = new mysqli(…) and $db->prepare("…") The procedural interface is an artifact from the PHP 4 era when mysqli API was introduced and ideally should not be used in new code. — tadman
– tadman, Commented Feb 11, 2019 at 21:17
Hi Tadman; the code I've posted here is cut down from the original to get to the heart of the issue. It's a simple page using a querystring that is then sanitised before being passed to the query. From reading the link on parameterized queries, there is less server overhead on a single select being written this way rather than using a prepared statement as far as I can tell? — Calum Morrison
– Calum Morrison, Commented Feb 11, 2019 at 22:55

Nick · Accepted Answer · 2019-02-11 22:53:00Z

1

Rather than looping the database fetch inside the iteration of the array, which results in there being no data left to read once you come to the second entry in the array (and hence at most one output, as you are seeing), just loop the database fetch and use in_array to determine whether to output the location name:

   while ($rowLocationNames = mysqli_fetch_assoc($rstLocationNames)) {
       if (in_array($rowLocationNames["idLocation"], $LocationArray)) {
           echo $rowLocationNames["LocationName"]."<br />";
       }
   }

edited Feb 11, 2019 at 22:53

answered Feb 11, 2019 at 21:38

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Calum Morrison Over a year ago

That's it; working now. There's a missing closing bracket in your second line, but I caught that and all is good. Many thanks.

Nick Over a year ago

@CalumMorrison sorry about that. I'll edit the answer so it's correct if anyone else sees it.

Calum Morrison Over a year ago

No worries; you got me where I needed to be, and a wee bit of extra thinking never hurt me!

Matt Garrod · Accepted Answer · 2019-02-11 21:23:41Z

0

The problem is that your while loop to read the location names from the db is nested within the for loop. After the the first iteration of the for loop, the database pointer is at the end of the recordset, so when you go through the next iteration, there are no records left to read.

Try reading the records in to an array before entering the for loop and then using the while loop to iterate over the newly created array.

answered Feb 11, 2019 at 21:23

Matt Garrod

8886 silver badges14 bronze badges

Collectives™ on Stack Overflow

In PHP, compare an array against a field in a MySQL table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related