PDO/MySQL fetch multiple columns with if statement

Question

I'm currently trying to fetch two images location from my database, how do I return both columns and if both empty then echo another image. This is what I've got so far. How do I return both photo and photo_small so that I may echo them in a php file.

PUBLIC FUNCTION Profile_Pic($uiD) {
    $sth = $this->db->prepare("SELECT photo,photo_small FROM users WHERE uiD = :id");
    $sth->execute(array(':id' => $uiD));

        if ($sth->rowCount() > 0) {
                $data = $row['photo'];
            return $data; 
        } else {
            $data = './icons/users.png';
            return $data;
        } 
    }

Your Common Sense · Accepted Answer · 2013-06-07 06:27:14Z

3

PUBLIC FUNCTION Profile_Pic($uiD) {
    $sql = "SELECT photo,photo_small FROM users WHERE uiD = ?";
    $sth = $this->db->prepare($sql);
    $sth->execute(array($uiD));
    $data = $sth->fetch();
    if (empty($data['photo'])) {
        $data['photo'] = './icons/users.png';
    }
    if (empty($data['photo_small'])) {
        $data['photo_small'] = './icons/users.png';
    }
    return $data;
}

if you want to replace both images if even one is absent

PUBLIC FUNCTION Profile_Pic($uiD) {
    $sql = "SELECT photo,photo_small FROM users WHERE uiD = ?";
    $sth = $this->db->prepare($sql);
    $sth->execute(array($uiD));
    $data = $sth->fetch();
    if (empty($data['photo']) || empty($data['photo_small'])) {
        $data['photo'] = './icons/users.png';
        $data['photo_small'] = './icons/users.png';
    }
    return $data;
}

edited Jun 7, 2013 at 6:27

answered Jun 7, 2013 at 5:50

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

2 Comments

Attis Over a year ago

Yes, works flawlessly. Thank you, I understand what you did now.

Attis Over a year ago

You're missing a closing parenthesis in the if statement. Overall, perfect answer.

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

Just return all of the values you want in an array.
You can ensure that both photo and photo_small are not empty strings or NULL by using empty().
Don't forget to retrieve your row using PDOStatement::fetch().
You should not use rowCount() to determine the number of rows returned in a SELECT statement. According to the documentation for PDOStatement::rowCount():

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement.

Try this:

$row = $sth->fetch(PDO::FETCH_ASSOC);
if ($row && !empty($row['photo']) && !empty($row['photo_small'])) {
  $data = array('photo' => $row['photo'], 'photo_small' => $row['photo_small']);
  return $data; 
} else {
  $data = array('photo' => './icons/users.png', 'photo_small' => './icons/users.png');
  return $data;
}

Then when you call the function, your returned result can be used like this:

$uiD = 1;
$result = Profile_Pic($uiD);
$photo = $result['photo'];
$photo_small = $result['photo_small'];

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 7, 2013 at 5:38

Aiias

4,7581 gold badge20 silver badges34 bronze badges

4 Comments

Attis Over a year ago

This has worked. I don't understand why you received a -1. Why return it in an array instead of just pulling the data?

Aiias Over a year ago

@iBrazilian - See my comments regarding using fetchColumn().

Attis Over a year ago

FetchColumn doesn't seem to be working. I deleted the fetch inside the if parameters as it's already defined outside of it but it doesn't return the database info. Only loads the icons/users.png. Note: I tried both your original and without the fetch inside if.

Aiias Over a year ago

@iBrazilian - I simplified my answer to simply check against the boolean value of $row. If there was no row returned, this should evalute to FALSE.

Collectives™ on Stack Overflow

PDO/MySQL fetch multiple columns with if statement

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related