How to PDO query to retrieve rows that match a value in an array

Question

I have a table with column nearbyAttractions which contains values seperated by a comma. For example A001,A002

I am querying that column then converting the contents to an array called $NearbyAttractionsArray.

print_r($NearbyAttractionsArray);

Array ( [0] => A001 [1] => A003 )

I want to run a PDO SQL query to find records that match for each value of the array then output them in a loop.

$arrayQuery = $this->con->prepare("SELECT attid, attTitle, attThumbnail, AttTagline FROM attractions WHERE attid=$NearbyAttractionsArray AND attStatus=1");

$arrayQuery->execute();

when I try this it converts $NearbyAttractionsArray to a string called array...

What would be the correct syntax to output values attid, attTitle, attThumbnail, AttTagline for each row where attId matches one of the values in the array?

I suspect some kind of foreach loop would be needed but cannot find the correct way to write this as a PDO statement.

Many thanks

For perspective my complete class code is:

<?php 

class NearbyAttractionsProvider {

private $con, $attId;

    public function __construct($con, $attId) {
        $this->con = $con;
        $this->attId = $attId;
    } // end of constructor


    public function createNearbyAttractionsList($con, $attId) {
        $query = $this->con->prepare("SELECT nearbyAttractions FROM attractions WHERE attId=:attId");
            $query->bindParam(":attId", $attId);
            $query->execute();

            $this->sqlData = $query->fetch(PDO::FETCH_ASSOC);
            $NearbyAttractions = $this->sqlData["nearbyAttractions"];
            $NearbyAttractionsRemoveCommas = str_replace(',', '',$NearbyAttractions);
            $NearbyAttractionsArray = str_split($NearbyAttractionsRemoveCommas, 4);

            print_r($NearbyAttractionsArray);

            $CountNearbyAttractions = sizeof($NearbyAttractionsArray);

            if($NearbyAttractionsArray[0] != "") {
                $results = "<h3>There are $CountNearbyAttractions Nearby Attractions</h3>";

                $arrayQuery = $this->con->prepare("SELECT attid, attTitle, attThumbnail, AttTagline FROM attractions WHERE 
                                attid=$NearbyAttractionsArray AND attStatus=1");
                $arrayQuery->execute();

                $results .= "<div class='nearbyAttractionsItemsContainer'>";
                while($row = $arrayQuery->fetchAll(PDO::FETCH_ASSOC)) {
                        $id = $row["attid"];
                        $img = $row["attThumbnail"];
                        $title = $row["attTitle"];
                        $tagline = $row["AttTagline"];

                $results .= "<div class='nearbyAttractionListItem'>
                                <a href='Attraction.php?attId=$id&title=$title' data-AttractionId='$id' 
                                class'NearbyAttractionItem-url'>
                                <img class='NearbyAttractionItem-thumbnail' src='$img' height='220' width=300'>
                                <h3 class='NearbyAttractionItem-title'>$title</h3>
                                <p class='NearbyAttractionItem-tagline'>$tagline</p>
                                </a>
                            </div>";
                } // End of while loop
            $results .= "</div>"; // End of nearbyAttractionsItemsContainer

            } // End of IF statement

            else {
                $results = "<h3>There are No Nearby Attractions</h3>";
            } // End of Else statement

        return $results;         

    } // End of public function createNearbyAttractionsList

} // End pf class NearbyAttractionsProvider

See this possible dupe, https://stackoverflow.com/questions/14767530/php-using-pdo-with-in-clause-array — Lawrence Cherone, Mar 05 '21 at 21:37
This is another good example of why comma delimited data is a bad choice for DB column. If this were just in a 1nf you could use a subquery. See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — user3783243, Mar 05 '21 at 22:21
A join is to link rows in two or more tables but here there is only 1 table so not a valid solution. — Elliott Farmer, Mar 05 '21 at 23:18

score 1 · Answer 1 · answered Mar 05 '21 at 22:22

1

Your query should look like:

SELECT attid, attTitle, attThumbnail, AttTagline 
FROM attractions 
WHERE attid IN('A001', 'A003')
AND attStatus=1

Of course I assume that attid stores atomic values. To convert array items to string you can use foreach() loop or implode() function. In second case, you'll need wrap it with apostrophes before you'll join it.

answered Mar 05 '21 at 22:22

biesior

55,576
10
125
182

Placeholders should be joined, no need for apostrophes. – user3783243 Mar 05 '21 at 22:23

score 0 · Answer 2 · answered Mar 06 '21 at 00:37

Many hours of fannying around and it works with a foreach loop.

<?php 
    
    class NearbyAttractionsProvider {
    
    private $con, $attId;
    
        public function __construct($con, $attId) {
            $this->con = $con;
            $this->attId = $attId;
        } // end of constructor
    
    
        public function createNearbyAttractionsList($con, $attId) {
            $query = $this->con->prepare("SELECT nearbyAttractions FROM attractions WHERE attId=:attId");
                $query->bindParam(":attId", $attId);
                $query->execute();
    
                $this->sqlData = $query->fetch(PDO::FETCH_ASSOC);
                $NearbyAttractions = $this->sqlData["nearbyAttractions"];
                $NearbyAttractionsRemoveCommas = str_replace(',', '',$NearbyAttractions);
                $NearbyAttractionsArray = str_split($NearbyAttractionsRemoveCommas, 5);
    
                $CountNearbyAttractions = sizeof($NearbyAttractionsArray);
    
                if($NearbyAttractionsArray[0] != "") {
                    $results = "<h3>There are $CountNearbyAttractions Nearby Attractions</h3>";
    
                    foreach($NearbyAttractionsArray as $row) {
    
                        $arrayQuery = $this->con->prepare("SELECT attId, attTitle, attThumbnail, AttTagline FROM attractions WHERE 
                                    attId=:row AND attStatus=1");
                        $arrayQuery->bindParam(":row", $row);
                        $arrayQuery->execute();
    
                        $results .= "<div class='nearbyAttractionsItemsContainer'>";
                        while($row = $arrayQuery->fetch(PDO::FETCH_ASSOC)) {
                                $id = $row["attId"];
                                $img = $row["attThumbnail"];
                                $title = $row["attTitle"];
                                $tagline = $row["AttTagline"];
    
                        $results .= "<div class='nearbyAttractionListItem'>
                                        <a href='Attraction.php?attId=$id&title=$title' data-AttractionId='$id' 
                                        class'NearbyAttractionItem-url'>
                                        <img class='NearbyAttractionItem-thumbnail' src='$img' height='220' width=300'>
                                        <h3 class='NearbyAttractionItem-title'>$title</h3>
                                        <p class='NearbyAttractionItem-tagline'>$tagline</p>
                                        </a>
                                    </div>";
                        } // End of while loop
                    $results .= "</div>"; // End of nearbyAttractionsItemsContainer
                    } // End of foreach loop
                    
                } // End of IF statement
    
                else {
                    $results = "<h3>There are No Nearby Attractions</h3>";
                } // End of Else statement
    
            return $results;         
    
        } // End of public function createNearbyAttractionsList
    
    } // End pf class NearbyAttractionsProvider

How to PDO query to retrieve rows that match a value in an array

2 Answers2