PHP and MySQL - Unable to correctly assign an object's field value from a query

Question

This is a bit of a tricky one, and may seem lengthy but it's all small snippets of code and a fairly "simple" issue. Really need help with this one (and I'm NOT allowed to use PDO or any other database handler method other than this one, as defined by the school):

I have this class (abridged with relevant fields only)

class Publication {

    public $num_author;
    public $author;

    public function __construct(..., $set_num_author) {
        $this->num_author = $set_num_author;
    }

    public function setAuthor($set_author) {
        $this->author = $set_author;
    }

    public function getNumAuthor() {
        return $this->num_author;
    }
}

And I'm trying to set its Author field. To do so, I need to use the numAuthor value for a query (Author's name and its number are in different tables), namely:

 "SELECT title, abstract, body, DATE_FORMAT(date, '%d-%m-%Y'), type, updated_user_id FROM publication WHERE type = 0 and enabled = 1 ORDER BY created_at DESC";
  //gets me the author id

 "SELECT name FROM person WHERE id = ?";
 //...which I then use to get the author's name

I instance the publications like so:

  $stmt_pubs_medicos->bind_result($title, $abstract, $body, $date, $type, $num_author);
    while ($stmt_pubs_medicos->fetch()) {
            $pub = new model\Publication($title, $abstract, $body, $date, $type, $num_author);
            array_push($latest_publications, $pub);
        }
  }

And I save them into latest_publications array as I expect to only get the 3 first (only showing the first, I unrolled the loop):

  $stmt_author->bind_param('i', $pub->getNumAuthor());
  $stmt_author->bind_result($author_name);
  $stmt_author->fetch();
  $latest_publications[0]->setAuthor($author_name);

But there's a problem. Once I show the publications in the webpage's div, although the body and date of each publication is different, as it should be, the author is the same. Namely the one from the 2nd latest publication ($latest_publications[1]). Why does this happen?

EDIT:

$stmt_autor = $db->conn->prepare($query_autor);
        $stmt_autor->bind_param('i', $latest_publications[0]->getNumAuthor());
        $stmt_autor->bind_result($nome_autor);
        $stmt_autor->fetch();
       $latest_publications[0]->setAuthor($nome_autor);

        $stmt_autor = $db->conn->prepare($query_autor);
        $stmt_autor->bind_param('i', $latest_publications[1]->getNumAuthor());
        $stmt_autor->bind_result($nome_autor);
        $stmt_autor->fetch();
      $latest_publications[1]->setAuthor($nome_autor);

        $stmt_autor = $db->conn->prepare($query_autor);
        $stmt_autor->bind_param('i', $latest_publications[2]->getNumAuthor());
        $stmt_autor->bind_result($nome_autor);
        $stmt_autor->fetch();
        $latest_publications[2]->setAuthor($nome_autor);

not sure if this is relevant - but i think i see a typo here $pub = new model\Publication($title, $abstract, $body, $date, $type, $num_autor); - $num_autor should be $num_author right? — ewizard
– ewizard, Commented Jun 17, 2014 at 23:06
also in this line $stmt_pubs_medicos->bind_result($title, $abstract, $body, $date, $type, $num_autor); — ewizard
– ewizard, Commented Jun 17, 2014 at 23:07
i dont know why that would cause the same author to be displayed...but computers can be weird :) — ewizard
– ewizard, Commented Jun 17, 2014 at 23:08
It was translated from portuguese, forgot to change those. Editing ^^ — Syler
– Syler, Commented Jun 17, 2014 at 23:08
Maybe you'd want to use the function var_dump() to monitor your vars until you find the bit that's causing the issue. This is most likely a design-related problem. You may also want to take a look at this to make your code easier to understand, it's hard to make some guesses, we're not the ones developing the application. — arielnmz
– arielnmz, Commented Jun 17, 2014 at 23:32

arielnmz · Accepted Answer · 2014-06-17 23:19:40Z

2

I suggest you better start using PHP's PDO rather than the mysql extensions, it'll save you a lot of trouble, and syntax and error handling are cleaner:

Example:

$DBH = new PDO('mysql:host=http://yourhost.com;dbname=yourdbname;charset=utf8', 'username', 'password');
$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$STH = $DBH->prepare('SELECT * FROM `yourtable` WHERE `yourcolumn` = ?');
$STH->bindParam(1, $yourparam);
$STH->execute();

$STH->setFetchMode(PDO::FETCH_OBJ);

$mydata = array();
while ($var = $STH->fetch()) {
    /* Retrieve the information */
    $mydata[] = $var;
}

$DBH = null;

The fetch() method will return an anonymous object (because of setFetchMode(PDO::FETCH_OBJ)) and its field names will be set to that of your columns' names. E.g:

If your table has the columns:

id, name, date, address

Your object will have the fields, with the corresponding information of the query:

$obj->id
$obj->name
$obj->date
$obj->address

You can find more information on phpro.org's PDO tutorial pages. They are pretty well explained.

answered Jun 17, 2014 at 23:19

arielnmz

9,21510 gold badges43 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Syler Over a year ago

That is not an option, this is for a school project and I'm obliged to use this database handling method...

arielnmz Over a year ago

As long as it's not a production environment, I think you're free to present a (much better) solution to the given problem (specially if this is school related stuff), but anyway, the suggestion will still be here in case you change yur mind.

Deegriz · Accepted Answer · 2014-06-18 12:07:52Z

real OP here. I had my friend post this question in my behalf since my school IP blocked me. So it was hard to get back with proper feedback or in time. I finally cracked the issue:

After unrolling, I ommitted the $stmt_autor->execute(); call and before that I was calling it BEFORE $stmt_autor->bind_params(); Hence the NULL/Empty Author name field. Snippet of the final, clean working code, portion of the loop:

$stmt_autor_med = $db->conn->prepare($query_autor);
    if (!$stmt_autor_med) {
        $code = $db->conn->errno;
        $message = $db->conn->error;
        printf("<p>SQL Error: %d %s</p>", $code, $message);
    }
    $stmt_autor_med->bind_param('i', $numeros_dos_autores[0]);
    $stmt_autor_med->bind_result($nome_m);
    if (!$stmt_autor_med->execute()) {
        $code = $stmt_autor_med->errno;
        $message = $stmt_autor_med->error;
        printf("<p>Execution error: %d %s</p>", $code, $message);
    } else {

        $stmt_autor_med->fetch();
        $ultimas_publicacoes_medicos[0]->autor = $nome_m;
    }
    $stmt_autor_med->free_result();
    $stmt_autor_med->close();

Variable names are in portuguese, but you'll get the jist. Thanks for the input!

Collectives™ on Stack Overflow

PHP and MySQL - Unable to correctly assign an object's field value from a query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related