PirateApp PirateApp - 5 months ago 16
PHP Question

PHP MySQL PDO TextArea Where clause with condition checks

I have a page as shown in the screenshot below. The idea is to enter the bus number and the list of all stops on a particular route, one per line.

My Add Route Page

The stops are already stored in a database table called 'stops' I need the ID of each stop from the textarea. My current code only gets the ID of the last stop in the textarea. I feel like I am missing something. 'busnumber' is my textfield and 'busroute' is my textarea. I would appreciate if anyone can point me out on what I need to change in order to get the ID of each stop entered in the textarea as an array. Thanks for your time in advance.

try {
$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
if(isset($_POST["busnumber"]) && isset($_POST["busroute"])){

$stops = explode(PHP_EOL, $_POST["busroute"]);
$stopsArray = '"' . implode('","', $stops) . '"';
$sql = "SELECT * FROM stops WHERE stop_name IN ($stopsArray)";
echo $sql."</br>";
$query = $conn->prepare($sql);
$query->execute();
$query->setFetchMode(PDO::FETCH_ASSOC);
$results = $query->fetchAll();
foreach($results as $result){
echo $result['stop_id'].' '.$result['stop_name'].'</br>';
}
}

} catch (PDOException $pe) {
die("Could not connect to the database $dbname :" . $pe->getMessage());


}

UPDATE 1
I changed the code as follows

$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
if(isset($_POST["busnumber"], $_POST["busroute"])){

$stops = explode(PHP_EOL, $_POST["busroute"]);

foreach($stops as $stop){
$sql = "SELECT * FROM stops WHERE stop_name = '".$stop."'";
$statement = $conn->query($sql);
echo $sql.'</br>';
$statement->setFetchMode(PDO::FETCH_ASSOC);
$results = $statement->fetchAll();
foreach($results as $result){
echo $result['stop_id'].' '.$result['stop_name'];
}
$statement = null;

}
}


and still get the same output, it only gives me the ID of the last item inside the textarea
The error is still here

Answer

I FIXED THE ERROR. The solution for anyone out there is simple, the PHP explode function was used to split the contents of a textarea into separate lines but it doesn't work if you use explode() with PHP_EOL. PHP EOL tells you the server's newline character from what I understand. I used the preg_split instead to perform the splitting and it works on both my localhost that runs on Windows and my server that runs on Linux. Thank you everyone for your help!!!

$conn = new PDO("mysql:host=$host;dbname=$db;charset=$charset", $user, $pass);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                if(isset($_POST["busnumber"], $_POST["busroute"])){
                    $stops = preg_split("/\\r\\n|\\r|\\n/", $_POST['busroute']);
                    $sql = 'SELECT * FROM stops WHERE stop_name LIKE :stop';
                    $statement = $conn->prepare($sql);
                    foreach($stops as $stop){
                        $statement->bindValue(':stop', $stop);
                        $statement->execute();
                        while($result = $statement->fetch()){
                            echo $result['stop_id'].' '.$result['stop_name'].'</br>';
                        }
                    }
                }
Comments