0

I'm trying to get this fixed for hours now. The thing is that I don't get any errors. So here is the problematic code:

error_reporting(E_ALL);
ini_set('display_errors', 1);
//Include database configuration file
include('includes/config.php');

if(isset($_POST["country_id"]) && !empty($_POST["country_id"])){
    //Get all state data
    $query = $dbh->query("SELECT * FROM states WHERE country_id = ".$_POST['country_id']." AND status = 1 ORDER BY state_name ASC");

    //Count total number of rows
    $rowCount = $query->rowCount();

    //Display states list
    if($rowCount > 0){
        echo '<option value="">Select state</option>';
        while($row = $query->fetch(PDO::FETCH_ASSOC)){ 
            echo '<option value="'.$row['state_id'].'">'.$row['state_name'].'</option>';
        }
    }else{
        echo '<option value="">State not available</option>';
    }
}

Here is the working one:

    <script type="text/javascript">
$(document).ready(function(){
    $('#country').on('change',function(){
        var countryID = $(this).val();
        if(countryID){
            $.ajax({
                type:'POST',
                url:'ajaxData.php',
                data:'country_id='+countryID,
                success:function(html){
                    $('#state').html(html);
                }
            }); 
        }else{
            $('#state').html('<option value="">Select country first</option>');
        }
    });
});
    </script>
    <?php

    //Get all country data
    $query = $dbh->query("SELECT * FROM countries WHERE status = 1 ORDER BY country_name ASC");

    //Count total number of rows
    $rowCount = $query->rowCount();
    ?>
<div class="form-group">
<label class="col-sm-2 control-label">Country<span style="color:red">*</span></label>
<div class="col-sm-4">
    <select name="country" id="country" class="form-control" required>
        <option value="">Select Country</option>
        <?php
        if($rowCount > 0){
            while($row = $query->fetch(PDO::FETCH_ASSOC)){ 
                echo '<option value="'.$row['country_id'].'">'.$row['country_name'].'</option>';
            }
        }else{
            echo '<option value="">Country Not Found</option>';
        }
        ?>
    </select>
</div>
<label class="col-sm-2 control-label">State<span style="color:red">*</span></label>
<div class="col-sm-4">
    <select name="state" id="state" class="form-control" required>
        <option value="">Select a State</option>
    </select>
</div>
</div>

So any help would be appreciate, I can't understand why in the first file it is not working and in the second it is. Thanks in advance. I search and checked a lot of questions here on stackoverflow but I didn't got my answer yet...

1Example
  • 1
  • 2
  • You realise your code is at risk from SQL Injection Attack as you're not using PDO Prepared Statements in the first page? – Martin Apr 26 '20 at 22:51
  • @Martin when i try that i get the following error: Undefined variable: rowCount also i didn't know that not using PDO Prepared Statements will make my stuff vulnerable to SQL Injection Attack. (nice thing to learn) – 1Example Apr 26 '20 at 23:01
  • 1
    [Start learning PHP the right way](https://phptherightway.com/) – Martin Apr 26 '20 at 23:04
  • @Martin Why are you flagging as a possible duplicate *and* posted an answer? – Funk Forty Niner Apr 26 '20 at 23:43
  • @FunkFortyNiner The duplicate is the answer to the question the OP should be asking ("how to count rows returned in PDO?") rather than the question the OP actually asked ("why isn't `rowCount()` working?"). I got carried away by posting an answer as well, shortly afterwards. A comment is enough. – Martin Apr 27 '20 at 08:18
  • a) You do not need to check `isset` AND [`empty`](https://www.php.net/manual/en/function.empty.php), the empty statement does both for you so you can simply run `if(!empty($_POST["country_id"])){` – Martin Apr 27 '20 at 08:18
  • b) Your code is at risk [**from SQL Injection Attack**](https://stackoverflow.com/questions/601300/what-is-sql-injection) and you should be looking to [improve your coding standard](https://phptherightway.com/) as a matter of urgency. – Martin Apr 27 '20 at 08:19
  • c) `status` is a keyword in MySQL and for the sake of clarity, it should be wrapped in backticks when used in SQL as a column name. Ideally, that word should not be used as a column name imo. – Martin Apr 27 '20 at 08:19
  • @Martin it was late at night when i opened the question, now as you advised i took a look over my code and changed it. (Check my question again) – 1Example Apr 27 '20 at 10:24
  • @Martin there is no point in asking "how to count rows returned in PDO?" because the OP already knows that. There is no point in asking "why isn't rowCount() working?" either, as the answer is obvious: rowCount() works but the query returned no rows. And thls last question is outside of the scope of Stack Overflow – Your Common Sense Apr 27 '20 at 11:04
  • A link to comprehensive answer is linked at the top of your question. – Your Common Sense Apr 27 '20 at 13:34

0 Answers0