Prepared statement not working

New coder here trying to figure out why my prepared statement is not working.

The current code that works fine is:

$query = "SELECT * FROM states WHERE country_id = ".$_POST['country_id']." AND status = 1 ORDER BY state_name ASC"; 
$result = $pdo->query($query); 

I am changing this code to:

$stmt = $pdo->prepare('SELECT * FROM states WHERE country_id = ? AND status = 1 ORDER BY state_name ASC');
$stmt->execute([$_POST['country_id']]);
$result = $stmt->fetch();

This query does not get any data. Can anyone offer ideas as to why this wouldn’t work?

What’s the NEXT line? How are you handling this? Because $result is NOT the same thing at the end of both code blocks; $stmt->fetch(); returns a row. $pdo->query() returns a PDOStatement.

Here’s the entire code block:

if(!empty($_POST["country_id"])){ 
    // Fetch state data based on the specific country 
	//$stmt = $pdo->prepare('SELECT * FROM states WHERE country_id = ? AND status = 1 ORDER BY state_name ASC');
	//$stmt->execute([$_POST['country_id']]);
	//$result = $stmt->fetch();
	
    $query = "SELECT * FROM states WHERE country_id = ".$_POST['country_id']." AND status = 1 ORDER BY state_name ASC"; 
    $result = $pdo->query($query); 
	
    // Generate HTML of state options list 
    if($result->rowCount() > 0){ 
        echo '<option value="">Select State</option>'; 
        while($row = $result->fetch()){  
            echo '<option value="'.$row['state_id'].'">'.$row['state_name'].'</option>'; 
        } 
    }else{ 
        echo '<option value="">State not available</option>'; 
    } 
} 
?>

right, and if you say $result = $stmt->fetch, $result is an array with no rowCount method…

Sorry, but as a new coder I just don’t know how that helps me solve my issue. I want to test if data exists – if I can’t do it with row count, then what other options are available?

I have tried if(!empty($result)) but this doesn’t work either.

I really do hate while loops as they never seem to work correctly for me. I want to use a foreach, but again, I can’t figure out how to test if $result has data.

if(!empty($_POST["country_id"])){ 
    // Fetch state data based on the specific country 
	$stmt = $pdo->prepare('SELECT * FROM states WHERE country_id = ? AND status = 1 ORDER BY state_name ASC');
	$stmt->execute([$_POST['country_id']]);
	$result = $stmt->fetchAll();
	
    // Generate HTML of state options list 
    if(!empty($result){ 
        echo '<option value="">Select State</option>'; 
             foreach ($result as $row) {
                  echo '<option value="'.$row['state_id'].'">'.$row['state_name'].'</option>'; 
             } 
    }else{ 
        echo '<option value="">State not available</option>'; 
    } 
} 

As mentioned above the !empty condition is working. Any suggestions?

and

Which is it? Does it work, or not work?

You could var_dump($result); and have a look at what’s in the array.

The way I do it is roughly like this:

$query = "select * from whatever where condition = ?";
  $prep = $db->prepare($query);
   $result = $prep->execute([$_POST['country_id']]);
   $sections = $prep->fetchAll();
   if (count($sections) > 0) { 
2 Likes

What this means is:-

Is how you call a method belonging to an object’s class. But $result is not an object, it’s an array, therefore has no methods.
But you can still use standard functions with an array using the standard syntax for the function you wish to use.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.