What is wrong with this simple database query code?

[Tarantulus]

Hi,

for some reason, in the following code $result is always empty. yet if I reun the query directly on the database (in phpmyadmin) it returns a value.

can anyone tell me what I'm missing?

<?php

class Page {

public function getContent($page_id) {

/* Create a new mysqli object with database connection parameters */
$mysqli = new mysqli('localhost', 'root', '', 'clg');

if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}

/* Create a prepared statement */
if($stmt = $mysqli -> prepare("SELECT content FROM content WHERE id > ?"))
{

/* Bind parameters
s - string, b - boolean, i - int, etc */
$stmt -> bind_param("s", $page_id);

/* Execute it */
$stmt -> execute();

/* Bind results */
$stmt -> bind_result($result);

$stmt -> fetch();

echo $page_id;
print_r($stmt);
echo mysqli_stmt_affected_rows($stmt);

/* Close statement */
$stmt -> close();
}

/* Close connection */
$mysqli -> close();

}

}

$page = new Page;
$page->getContent(1);

Thanks in advance!

[dlite922]

You've got your bind_result() before the execute().

Check out the manual for more info:

http://www.php.net/manual/en/mysqli-stmt.prepare.php

Cheers,


Dan

[Tarantulus]

oops, that was an error on my part, I moved it around to see if it would make any difference.

even the correct way doesn't work, $result is still empty

[dlite922]

How do you know $result is empty. you're not printing result, you're printing $stmt.

Did you try print_r($result )?

[Tarantulus]

yep, I am 100% sure $result is empty, I was printing $stmt to see if it had any clues hidden in the array.

[JKing]

if($stmt = $mysqli -> prepare("SELECT content FROM content WHERE id > ?"))

What is the ? in the where clause for?

[Tarantulus]

as far as I understand it the ? is substituted with the variable given to the function ($page_id).

[JKing]

This was mentioned earlier that you had your bind_result before the execute and that you had moved it. It now sits between the execute and the fetch?

[Tarantulus]

yep, I'll edit it.

[Tarantulus]

Updated the code, still no luck.

Thanks for the help so far, any other ideas?

[JKing]

This might seem silly but does the id of 1 exist in the database?

Could you post your current code. Thanks.

[Tarantulus]

yeah, not silly at all I'm often tripped by the smallest of things!

current code is as follows, (cleaned up a bit)

<?php

class Page {

  public function getContent($page_id) {
  
   /* Create a new mysqli object with database connection parameters */
     $mysqli = new mysqli('localhost', 'root', '', 'clg');
  
     if(mysqli_connect_errno()) {
        echo "Connection Failed: " . mysqli_connect_errno();
        exit();
     }
  
     /* Create a prepared statement */
     if($stmt = $mysqli -> prepare("SELECT content FROM content WHERE id = ?")) 
     {
  
        /* Bind parameters
           s - string, b - boolean, i - int, etc */
        $stmt -> bind_param("s", $page_id);
        
        /* Execute it */
        $stmt -> execute();
  
        /* Bind results */
        $stmt -> bind_result($result);
  
        $stmt -> fetch(); 
     
        echo $page_id;
        echo $result;
        echo mysqli_stmt_affected_rows($stmt);

        /* Close statement */
        $stmt -> close();
     }
  
     /* Close connection */
     $mysqli -> close();

  }
  
}

$page = new Page;
$page->getContent(1);

[Tarantulus]

oh and just to prove it's getting through to the database I checked the mysql.log

101012 17:31:29      156 Connect    root@localhost on clg
          156 Prepare    SELECT content FROM content WHERE id = ?
          156 Execute    SELECT content FROM content WHERE id = '1'
          156 Close stmt    
          156 Quit

[Tarantulus]

Further update... the issue is only apparent on the one column I want to access, if i try it on the rest of the columns it works.

the column in question is a "LONGTEXT" type with no other attributes set.

[Tarantulus]

I don't believe it! changed the type from LONGTEXT to simply TEXT and it works...

thanks for trying anyway!