Call to a member function setFetchMode() on a non-object

donboe · June 28, 2014, 7:49am

I have the following test query and while loop for a search result page:


$isActive = 1;
  
  $zoekt    = filter_input(INPUT_GET, 'zoekt', FILTER_SANITIZE_NUMBER_INT);
  
  $gryResultaten = $pdo->query("
                               SELECT P.profiel_id,
							          P.gebruikersnaam,
									  G.geslact_id
							   FROM
							          profielen P
							   INNER
							    JOIN  profiel_geslacht PG
								  ON  P.profiel_id = PG.profiel_id
  							 INNER
							    JOIN  geslacht G
								  ON  PG.geslacht_id = G.geslacht_id
							   WHERE
							          isActive = '$isActive'	
								");
								if ($zoekt) {
									$gryResultaten .= 'AND geslact_id = $zoekt';
                                }
								
   $gryResultaten->setFetchMode(PDO::FETCH_ASSOC);

    while($row = $gryResultaten->fetch()) {
	    echo "<p>{$row['gebruikersnaam']}</p>";	
	}

I land on this page true a search form (get) with just 1 dropdown list (zoekt). What am I doing wrong?

Thank you in advance

fretburner · June 28, 2014, 11:23am

Hi donboe,

This line is trying to concatenate your result object with a string:


if ($zoekt) {
    $gryResultaten .= 'AND geslact_id = $zoekt';
}

which is probably why you’re getting the ‘non-object’ error (although I’m surprised PHP doesn’t complain about trying to concatenate an object and a string).

Try moving your SQL to a string, and modify it before performing the query, like this:


$sql = "SELECT P.profiel_id,  P.gebruikersnaam, G.geslact_id
FROM profielen P
INNER JOIN  profiel_geslacht PG ON  P.profiel_id = PG.profiel_id
INNER JOIN  geslacht G ON  PG.geslacht_id = G.geslacht_id
WHERE isActive = '$isActive' ";

if ($zoekt) {
    $sql .= 'AND geslact_id = $zoekt';
}

$gryResultaten = $pdo->query($sql);

// etc

fretburner · June 28, 2014, 3:22pm

The PDO::query() method also returns FALSE if it fails, so you might want to echo the SQL to check there are no problems with it.

donboe · June 28, 2014, 5:16pm

Is that realy the problem? I just took the

if ($zoekt) {
$gryResultaten = ‘AND geslacht_id = $zoekt’;
}

out. But still get the same error.

fretburner · June 28, 2014, 5:27pm

Check my previous post, I suspect there’s a problem with your SQL and so the query() method is returning false.

Make sure PDO is set to throw exceptions like this:


// Do this just after creating your DB connection
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

donboe · June 28, 2014, 5:39pm

Hi Fretburner. Thanks for your reply. This is all very confusing for me. I just switched to PDO. I used this sam kind of query before and it was working. Ok it was not true a form, but it returned results. I have no idea where to add the $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Should that be in my connection file? Again sorry for the ignorance, but I have no idea where to look at.

fretburner · June 28, 2014, 6:34pm

Yeah, just after you create your connection. Something like this:


$pdo = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

donboe · June 28, 2014, 7:51pm

Hi fretburner. I just added that and get the following error:

Fatal error: Uncaught exception ‘PDOException’ with message 'SQLSTATE[23000]: Integrity constraint violation: 1052 Champ: ‘geslacht_id’

This is the complete code on the result page:


try
{
   $pdo = new PDO('mysql:host=db_host;dbname=$db_name',   $db_user, $db_pass);
   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e)
{
  echo 'Er is een database fout opgetreden.';
  exit();
}

$isActive = 1;
  
 $zoekt    = filter_input(INPUT_GET, 'zoekt', FILTER_SANITIZE_NUMBER_INT);
  
$sql = "SELECT P.profiel_id,  P.gebruikersnaam, G.geslacht_id
FROM profielen P
INNER JOIN  profiel_geslacht PG ON  P.profiel_id = PG.profiel_id
INNER JOIN  geslacht G ON  PG.geslacht_id = G.geslacht_id
WHERE isActive = '$isActive' ";

if ($zoekt) {
    $sql .= 'AND geslacht_id = $zoekt';
}

$gryResultaten = $pdo->query($sql);

    while($row = $gryResultaten->fetch()) {
        echo "<p>{$row['gebruikersnaam']}</p>";    
     }

So what should I adjust in this? Thanks again for your time and patience.

P.s. Concrats with the win of Brazil. Today I was in a bar in my town. The wife of the owner is Brazilian and they were giving away caipirinha’s. I loved them

Drummin · June 29, 2014, 3:20am

Identify which table geslacht_id belongs to, P., G., PG.

if ($zoekt) {
    $sql .= 'AND geslacht_id = $zoekt';
}

Also, probably not a big deal on INNER JOIN but you normally would list the Join table field first compared to one you are comparing it to.
So this would be backwards.

P.profiel_id = PG.profiel_id

This would be correct.

PG.profiel_id = P.profiel_id

Also, isActive should probably have a table identifer as in P.isActive

donboe · June 29, 2014, 7:36am

Drummin:

Identify which table geslacht_id belongs to, P., G., PG.
if ($zoekt) {
    $sql .= 'AND geslacht_id = $zoekt';
}
Also, probably not a big deal on INNER JOIN but you normally would list the Join table field first compared to one you are comparing it to.
So this would be backwards.
P.profiel_id = PG.profiel_id
This would be correct.
PG.profiel_id = P.profiel_id
Also, isActive should probably have a table identifer as in P.isActive

Hi Drummin. I tried all your suggestions i.e.


  $isActive = 1;
  
  $zoekt    = filter_input(INPUT_GET, 'zoekt', FILTER_SANITIZE_NUMBER_INT);
  
  $sql      = "SELECT P.profiel_id,  
                      P.gebruikersnaam, 
					  PG.geslacht_id
                 FROM profielen P
                INNER 
			     JOIN profiel_geslacht PG ON  PG.profiel_id = P.profiel_id
                INNER 
				 JOIN geslacht G ON  G.geslacht_id = PG.geslacht_id
                WHERE P.isActive = '$isActive' ";

   if ($zoekt) {
       $sql .= 'AND PG.geslacht_id = $zoekt';
   }

   $gryResultaten = $pdo->query($sql);

   while($row = $gryResultaten->fetch()) {
        echo "<p>{$row['gebruikersnaam']}</p>";    
    }

but now I get the message that the column zoekt couldn’t be found while it isn not even a column:


Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Champ '$zoekt'

PDOException: SQLSTATE[42S22]: Column not found: 1054 Champ '$zoekt'

What frustrating is this.

Drummin · June 29, 2014, 9:12am

That’s because the line below is not in full quotes so the variable is not recognized as such and being interpreted as a field name.

$sql .= 'AND PG.geslacht_id = $zoekt';

But let’s fix this up by binding input.

<?php
$isActive = 1;

$zoekt    = filter_input(INPUT_GET, 'zoekt', FILTER_SANITIZE_NUMBER_INT);

$sql      = "SELECT P.profiel_id,
	P.gebruikersnaam,
	PG.geslacht_id
	FROM profielen P
		INNER JOIN profiel_geslacht PG
			ON  PG.profiel_id = P.profiel_id
		INNER JOIN geslacht G
			ON  G.geslacht_id = PG.geslacht_id
	WHERE P.isActive = :isActive ";

	if ($zoekt) {
	$sql .= "AND PG.geslacht_id = :zoekt";
	}
	
	$gryResultaten = $pdo->prepare($sql);
	$gryResultaten->bindParam(":isActive", $isActive);
	
	if ($zoekt){
		$gryResultaten->bindParam(":zoekt", $zoekt);
	}
	$gryResultaten->execute();
	
	while($row = $gryResultaten->fetch(PDO::FETCH_ASSOC)){
		echo "<p>{$row['gebruikersnaam']}</p>";
	}	
?>

donboe · June 29, 2014, 2:19pm

Hi Drummin. Thank you for all your input :tup: That works as a charm. Like I said in the opening post this was just a test query in the actual search form there are a few more fields involved i.e. leeftijd_van(age_from) and leeftijd_tot(age_to) so the query is now looking like this:


  $isActive = 1;

  $zoekt        = filter_input(INPUT_GET, 'zoekt', FILTER_SANITIZE_NUMBER_INT);
  $leeftijd_van = filter_input(INPUT_GET, 'leeftijd_van', FILTER_SANITIZE_STRING); // not sure if this should be a string
  $leeftijd_tot = filter_input(INPUT_GET, 'leeftijd_tot', FILTER_SANITIZE_STRING);    // not sure if this should be a string

  $sql      = "SELECT P.profiel_id,
                      P.gebruikersnaam,
					  P.dob,
					  EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`)))) AS age,
					  PG.geslacht_id
                 FROM profielen P
                INNER
			     JOIN profiel_geslacht PG ON  PG.profiel_id = P.profiel_id
                INNER
				 JOIN geslacht G ON  G.geslacht_id = PG.geslacht_id
                WHERE P.isActive = $isActive ";

so following your previous code I added this to the PHP as well:


   if ($leeftijd_van) {
       $sql .= "AND age &gt;= $leeftijd_van";
   }
   if ($leeftijd_tot) {
       $sql .= "AND age &lt;= $leeftijd_tot";
   }
   if ($leeftijd_van && $leeftijd_tot) {
       $sql .= "AND age BETWEEN $leeftijd_van AND $leeftijd_tot";
   }

    if ($leeftijd_van){
        $gryResultaten-&gt;bindParam(":leeftijd_van", $leeftijd_van);
    }
    if ($leeftijd_tot){
        $gryResultaten-&gt;bindParam(":leeftijd_tot", $leeftijd_tot);
    }
   if ($leeftijd_van && $leeftijd_tot) {
       $gryResultaten-&gt;bindParam(":leeftijd_van", $leeftijd_van);
       $gryResultaten-&gt;bindParam(":leeftijd_tot", $leeftijd_tot);
   }

But that is giving me errors again. I tried to sanitize the $leeftijd_van and $leeftijd_tot as NUMBER_INT as well. It doesn’t give a clear error just a Fatal error. What am I doing wrong with this?

Again thank you for the previous and thank you in advance.

Drummin · June 29, 2014, 3:53pm

It’s close but you need to use the placeholder names you are binding in the $sql. Also don’t forget that space between each one. I added the space before " AND…"

   if ($leeftijd_van) {
       $sql .= " AND age >= :leeftijd_van";
   }
   if ($leeftijd_tot) {
       $sql .= " AND age <= :leeftijd_tot";
   }
   if ($leeftijd_van && $leeftijd_tot) {
       $sql .= " AND age BETWEEN :leeftijd_van AND :leeftijd_tot";
   }

    if ($leeftijd_van){
        $gryResultaten->bindParam(":leeftijd_van", $leeftijd_van);
    }
    if ($leeftijd_tot){
        $gryResultaten->bindParam(":leeftijd_tot", $leeftijd_tot);
    }
   if ($leeftijd_van && $leeftijd_tot) {
       $gryResultaten->bindParam(":leeftijd_van", $leeftijd_van);
       $gryResultaten->bindParam(":leeftijd_tot", $leeftijd_tot);
   }

I’m not all that sure about your IF statements. If you are going to use the Third BETWEEN IF statement I think it the fist two should also check that the “other one” is not set.

	if (isset($leeftijd_van) && !isset($leeftijd_tot)) {
		$sql .= " AND age >= :leeftijd_van";
	}
	if (isset($leeftijd_tot) && !isset($leeftijd_van)) {
		$sql .= " AND age <= :leeftijd_tot";
	}
	if (isset($leeftijd_van) && isset($leeftijd_tot)) {
		$sql .= " AND age BETWEEN :leeftijd_van AND :leeftijd_tot";
	}
	
	if (isset($leeftijd_van) && !isset($leeftijd_tot)){
		$gryResultaten->bindParam(":leeftijd_van", $leeftijd_van);
	}
	if (isset($leeftijd_tot) && !isset($leeftijd_van)){
		$gryResultaten->bindParam(":leeftijd_tot", $leeftijd_tot);
	}
	if (isset($leeftijd_van) && isset($leeftijd_tot)) {
		$gryResultaten->bindParam(":leeftijd_van", $leeftijd_van);
		$gryResultaten->bindParam(":leeftijd_tot", $leeftijd_tot);
	}

However I don’t think you need the third one at all. The first two will handle the BETWEEN.

	if ($leeftijd_van) {
		$sql .= " AND age >= :leeftijd_van";
	}
	if ($leeftijd_tot) {
		$sql .= " AND age <= :leeftijd_tot";
	}
	
	if ($leeftijd_van){
		$gryResultaten->bindParam(":leeftijd_van", $leeftijd_van);
	}
	if ($leeftijd_tot){
		$gryResultaten->bindParam(":leeftijd_tot", $leeftijd_tot);
	}

donboe · June 30, 2014, 7:45am

Brilliant Drummin. I have learned a lot the last few days with the help of you and fretburner.

I am facing another problem though. When I tested your code I got an error that the column age could not be found. What is logic, since you can’t use a alias (age is created in the query i.e. EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),dob)))) AS age,) in a where clause . So first of all to make sure it was working I temporary added a field age to my table, and it was working as expected. After that I added a subquery to my query the following way:


    $sql    = "SELECT *
                 FROM (
                      P.profiel_id,
                      P.gebruikersnaam,
                      P.dob,
                      EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`)))) AS age,
                      PG.geslacht_id
                 FROM profielen P
                INNER
                 JOIN profiel_geslacht PG ON  PG.profiel_id = P.profiel_id
                INNER
                 JOIN geslacht G ON  G.geslacht_id = PG.geslacht_id
                      ) AS SUB
                WHERE P.isActive = :isActive ";

Which I expected would do the trick but it sn’t instead I get a fatal error i.e.

Fatal error: in C:\wamp\www\mysite\resultaten.php on line 75

What could be the reason? Thank you again in advance.

donboe · June 30, 2014, 10:22am

I have tried to take a different approach. Instead of using a subquery I used the DATEDIFF as I have it in the query itself in the AND clause as well, i.e.

 $sql .= " AND EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`)))) AS age &gt;= :leeftijd_van";

But still I get the fatal errorr. What should I adjust to make this work?

fretburner · June 30, 2014, 11:58am

Thanks, although I’m not actually Brazilian, just a Brit far from home I might consider switching allegiance though if there were free caipirinhas on offer!

donboe:

After that I added a subquery to my query the following way:


    $sql    = "SELECT *
                 FROM (
                      P.profiel_id,  
                      P.gebruikersnaam,
                      P.dob, 
                      EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`)))) AS age, 
                      PG.geslacht_id
                 FROM profielen P
                INNER 
                 JOIN profiel_geslacht PG ON  PG.profiel_id = P.profiel_id
                INNER 
                 JOIN geslacht G ON  G.geslacht_id = PG.geslacht_id
                      ) AS SUB
                WHERE P.isActive = :isActive ";

Which I expected would do the trick but it sn’t instead I get a fatal error i.e.

Fatal error: in C:\wamp\www\mysite\resultaten.php on line 75

What could be the reason?

The problem with that query is I don’t think the syntax is correct for a sub-query… I think it has to be like this:

SELECT * FROM (SELECT x,y, z FROM c WHERE a = b) AS SUB …

Seeing as your main query is selecting all columns though, you don’t need a subquery. You could do:

SELECT P.profiel_id, P.gebruikersnaam, P.dob, EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`)))) AS age, PG.geslacht_id
FROM profielen P
INNER JOIN profiel_geslacht PG ON  PG.profiel_id = P.profiel_id
INNER JOIN geslacht G ON  G.geslacht_id = PG.geslacht_id
WHERE P.isActive = :isActive

I also think that there may be a problem with your age calculation, as my understanding is that FROM_DAYS expects the number of days since year 0 into a date, but you’re passing it the difference in days between two dates. The MySQL manual suggests calculating age like this:

TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age

donboe · June 30, 2014, 2:13pm

I will have a look if this makes any difference

fretburner:

Seeing as your main query is selecting all columns though, you don’t need a subquery. You could do:

SELECT P.profiel_id, P.gebruikersnaam, P.dob, EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),`dob`)))) AS age, PG.geslacht_id
FROM profielen P
INNER JOIN profiel_geslacht PG ON  PG.profiel_id = P.profiel_id
INNER JOIN geslacht G ON  G.geslacht_id = PG.geslacht_id
WHERE P.isActive = :isActive

This is what I originally was using (see post 12) but since age is an alias I can’t use it in the where clause i.e.

if (isset($leeftijd_van) && !isset($leeftijd_tot)) {
    $sql .= " AND age >= :leeftijd_van";
}

fretburner:

I also think that there may be a problem with your age calculation, as my understanding is that FROM_DAYS expects the number of days since year 0 into a date, but you’re passing it the difference in days between two dates. The MySQL manual suggests calculating age like this.
TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age

I bwill for sure try this approach as well. I got this form a mySQL tutorial site , but f you say this will be better, I am gonna use that

donboe · June 30, 2014, 3:28pm

I just tried this:


    $sql    = "SELECT * 
	            FROM  ( P.profiel_id,  
                        P.gebruikersnaam,
					    P.dob,
					    TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS age,  
					    PG.geslacht_id
               FROM     profielen P
                INNER 
			     JOIN   profiel_geslacht PG ON  PG.profiel_id = P.profiel_id
                INNER 
				 JOIN   geslacht G ON  G.geslacht_id = PG.geslacht_id
                WHERE   P.isActive = :isActive 
				       ) AS sub";

But still without the desired result. As you see did I also included the TIMESTAMPDIFF, but can’t see if it is working as I still got the fatal error

fretburner · June 30, 2014, 4:20pm

donboe:

I just tried this:


    $sql    = "SELECT * 
	            FROM  ( P.profiel_id,  
                        P.gebruikersnaam,
					    P.dob,
					    TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS age,  
					    PG.geslacht_id
               FROM     profielen P
                INNER 
			     JOIN   profiel_geslacht PG ON  PG.profiel_id = P.profiel_id
                INNER 
				 JOIN   geslacht G ON  G.geslacht_id = PG.geslacht_id
                WHERE   P.isActive = :isActive 
				       ) AS sub";

But still without the desired result. As you see did I also included the TIMESTAMPDIFF, but can’t see if it is working as I still got the fatal error

It looks like you’re still trying to do a subquery incorrectly. What you want is either this (note the addtional SELECT keyword within the subquery):


$sql    = "SELECT * 
	            FROM  ( SELECT P.profiel_id,  
                        P.gebruikersnaam,
					    P.dob,
					    TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS age,  
					    PG.geslacht_id
               FROM     profielen P
                INNER 
			     JOIN   profiel_geslacht PG ON  PG.profiel_id = P.profiel_id
                INNER 
				 JOIN   geslacht G ON  G.geslacht_id = PG.geslacht_id
                WHERE   P.isActive = :isActive 
				       ) AS sub";

or this (no subquery, even simpler):


$sql    = "SELECT P.profiel_id,  
                        P.gebruikersnaam,
					    P.dob,
					    TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS age,  
					    PG.geslacht_id
               FROM     profielen P
                INNER 
			     JOIN   profiel_geslacht PG ON  PG.profiel_id = P.profiel_id
                INNER 
				 JOIN   geslacht G ON  G.geslacht_id = PG.geslacht_id
                WHERE   P.isActive = :isActive";

Also, if you want to use a calculated column, such as age, within a WHERE clause then it’s probably simpler in this case just to repeat the calculation.

donboe · June 30, 2014, 7:03pm

Hi fretburner. I just see my mistake! I forgot the select in the subquery. I have been starring at this way to long I guess. Sorry for the inconvinience. Now I have the query without subquery as you suggested and repeated the calculation in the where clause i.e.


    $isActive = 1;
  
    $zoekt          = filter_input(INPUT_GET, 'zoekt', FILTER_SANITIZE_NUMBER_INT);
    $leeftijd_van   = filter_input(INPUT_GET, 'leeftijd_van', FILTER_SANITIZE_STRING);
  
$sql          = "SELECT P.profiel_id,  
                        P.gebruikersnaam,
                        P.dob,
                        TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS age,  
                        PG.geslacht_id
               FROM     profielen P
                INNER 
                 JOIN   profiel_geslacht PG ON  PG.profiel_id = P.profiel_id
                INNER 
                 JOIN   geslacht G ON  G.geslacht_id = PG.geslacht_id
                WHERE   P.isActive = :isActive";

    if ($zoekt) {
    $sql .= " AND PG.geslacht_id = :zoekt";
    } 
    if ($leeftijd_van) {
        $sql .= " AND TIMESTAMPDIFF(YEAR,dob,CURDATE()) AS age >= :leeftijd_van";
    }

    $gryResultaten = $pdo->prepare($sql);
    $gryResultaten->bindParam(":isActive", $isActive);
	
    if ($zoekt){
        $gryResultaten->bindParam(":zoekt", $zoekt);
    }
    if ($leeftijd_van) {
        $gryResultaten->bindParam(":leeftijd_van", $leeftijd_van);
    }
	
	$gryResultaten->execute();  
			
    while($row = $gryResultaten->fetch()) {
        echo "<p>{$row['gebruikersnaam']}</p>";    
    }

But I still get an fatal error. I start loosing hair from pulling it