CODEWITHSUNDEEP
phpmysqlpdo
user16174
user16174

Reputation: 59

Accessing Data VIA PDO within a function - PHP

I'm quite new to PHP, so I apologise in advance for any nauseatingly bad code that I post...

I have created a basic membership system for a fitness bootcamp. I have two SQL tables - "members" and "payments".

I have created a table, and using a loop, I can display all members. Now, I wish to be able to check if the member has paid using the function checkpayment().

This function checks the payments table to see if there is a row that contains a payment for that member id, within the current month and year.

Even when a payment is entered into the payments table, the user still shows as unpaid.

function checkpayment($memberid, $month, $year){
    global $db;
            $payments = "SELECT * FROM 'payments' WHERE member_id = :memberid";
            $result = $db->prepare($payments);
            $result->bindParam(':memberid', $memberid, PDO::PARAM_INT);
            $result->bindParam(':month', $month, PDO::PARAM_INT);
            $result->bindParam(':year', $year, PDO::PARAM_INT);
            return $result->fetchColumn();
}

Here is the code to display the members:

foreach($db->query('SELECT * FROM `members`') as $row) {
             $checkpayment = checkpayment($row['id'],10,2014);
             if(!$checkpayment){
              $status = "danger";
              $paid = "Unpaid";
             } else {
              $status = "success";
              $paid = "Paid";
             }
                echo '<tr class="'. $status .'">
                <td>'. $row['id'] .'</td>
                <td>' . $row['first_name'] . '</td>
                <td>' . $row['last_name'] .'</td>
                <td>' . $paid . '</td>
                <td>' . $row['programme'] . '</td>
              </tr>';

            }

However, even when a payment row is present, the members still show as unpaid...

Upvotes: 1

Views: 101

Answers (3)

Mike
Mike

Reputation: 2761

You are selecting * which returns multiple columns and then PDOStatement::fetch_column which will only return the first. Since this is probably the member id, the result would always be true.

Or at least it would be if you had actually called $result->execute () before trying to read the results! ;-)

This is a prime opportunity for a join:

    $month = 1;
    $year = 2014;
    $pdo = new PDO ("mysql:host=localhost;dbname=test", "xxx", 'xxx');
    $sel = $pdo->prepare ("select * from member as m
left join payments as p
on m.member_id = p.member_id
and p.month = :month
and p.year = :year");
    $sel->bindParam('month', $month, PDO::PARAM_INT);
    $sel->bindParam('year', $year, PDO::PARAM_INT);
    $sel->execute ();
    var_dump ($sel->fetchAll ());

Results:

array(2) {
  [0]=>
  array(5) {
    ["member_id"]=>string(1) "1"
    ["member_name"]=>string(4) "Good"
    ["pmember_id"]=>string(1) "1"
    ["year"]=>string(4) "2014"
    ["month"]=>string(1) "1"
  }
  [1]=>
  array(5) {
    ["member_id"]=>string(1) "2"
    ["member_name"]=>string(8) "Deadbeat"
    ["pmember_id"]=>NULL
    ["year"]=>NULL
    ["month"]=>NULL
  }
}

As you can see, all members who have not paid for the specified month and year have a pmember_id, year and month of NULL.

I changed the name of the member_id in the payments query so that the fetchAll would work for this illustration.

Upvotes: 1

hakki
hakki

Reputation: 6521

if you are using prepare function also you must use execute() as far as I know. Try below, if not work I'll update.

Here you can see some details about execute()

function checkpayment($memberid, $month, $year){
    global $db;
            $payments = "SELECT * FROM payments WHERE member_id = :memberid";
            $result = $db->prepare($payments);
            $result->bindParam(':memberid', $memberid, PDO::PARAM_INT);
            $result->execute();//ADD This line
            return $result->fetchColumn();
}

Upvotes: 3

jeroen
jeroen

Reputation: 91734

You are using the wrong kinds of quotes in your query:

$payments = "SELECT * FROM 'payments' WHERE member_id = :memberid";

should be:

$payments = "SELECT * FROM payments WHERE member_id = :memberid";

or:

$payments = "SELECT * FROM `payments` WHERE member_id = :memberid";

And you should only bind the variables that you are actually using, :memberid in this case OR add the other variables to your query. In your case you should obviously do the latter.

To catch these errors, you should set-up PDO to throw exceptions. You can do that by adding this right after you open the database connection:

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Upvotes: 2

Related Questions