Show only certain images

[jazzman1]

To be quite honest about it, I don't have so many experience with PDO b/s most of time I'm using CakePHP and from few days I'm digging up with Silex

 

So, I have an experience with mysqli::prepare() then it was so easy for me to follow up the documentation....how to PDO from php.net. 

 

Regards,

 

jazz.

[mac_gyver]

the following code should work and if it doesn't it contains enough error checking logic to tell you why it didn't.

 

the main html page -

try {
    $pdo = new PDO('mysql:host=localhost;dbname=database','admin','root');
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$query = "SELECT i.img, i.cid FROM images i
    INNER JOIN cardId as c ON i.cid = c.id
    WHERE c.status = 1 ORDER BY c.id ASC LIMIT 10";
if(!$stmt = $pdo->prepare($query)){
    // prepare failed
    echo "<pre>Prepare failed:\n";
    print_r($pdo->errorInfo());
    echo "</pre>";
} else {
    if(!$stmt->execute()){
        // execute failed
        echo "<pre>Execute failed:\n";
        print_r($stmt->errorInfo());
        echo "</pre>";
    } else {
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if(count($result) == 0){
            echo "Query matched zero rows.";
        } else {
            //echo '<pre>', print_r($result,true),'</pre>'; // take a look at the result
            foreach($result as $image){
                $img = imagecreatefromstring($image['img']);
                $height = imagesy($img);
                $width = imagesx($img);
                if($height > 100 OR $width > 120){
                    echo "<img src='noimage.png' alt='' />";
                } else {
                    echo "<img src='images.php?cid={$image['cid']}' alt='' />";
                }
            }
        }
    }
}

the images.php file, needed to dynamically output the image that is stored in the database (the amount of code needed to do this is just one of the reasons NOT to store files in a database) -

$cid = isset($_GET['cid']) ? (int)$_GET['cid'] : 0;
if($cid < 1){
    die; // do nothing for impossible cid values
}

try {
    $pdo = new PDO('mysql:host=localhost;dbname=database','admin','root');
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$query = "SELECT img,type FROM images WHERE cid = :cid";
if(!$stmt = $pdo->prepare($query)){
    // prepare failed
    echo "<pre>Prepare failed:\n";
    print_r($pdo->errorInfo());
    echo "</pre>";
} else {
    $stmt->bindParam('cid', $cid, PDO::PARAM_INT); // this can fail and return a false, but i couldn't find an (easy) condition to cause it to fail
    if(!$stmt->execute()){
        // execute failed
        echo "<pre>Execute failed:\n";
        print_r($stmt->errorInfo());
        echo "</pre>";
    } else {
        if($result = $stmt->fetch(PDO::FETCH_ASSOC)){ // should always match the one row given by cid
            //echo '<pre>', print_r($result,true),'</pre>'; // take a look at the result
            header("Content-Type: {$result['type']}");
            echo $result['img'];
        }
    }
}

the above code ASSUMES/requires that your images database table has a type column that holds the content type for the image, such as image/jpeg for a jpg/jpeg image.

 

[jazzman1]

$query = "SELECT i.img, i.cid FROM images i

    INNER JOIN cardId as c ON i.cid = c.id

    WHERE c.status = 1 ORDER BY c.id ASC LIMIT 10";

 

 

This is a cartesian join, we should avoid using it!

[jazzman1]

This is a cartesian join, we should avoid using it!

 

It's too late - c.status = 1

 

it's time to go bed

[public]

the following code should work and if it doesn't it contains enough error checking logic to tell you why it didn't.

 

the main html page -

try {
    $pdo = new PDO('mysql:host=localhost;dbname=database','admin','root');
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$query = "SELECT i.img, i.cid FROM images i
    INNER JOIN cardId as c ON i.cid = c.id
    WHERE c.status = 1 ORDER BY c.id ASC LIMIT 10";
if(!$stmt = $pdo->prepare($query)){
    // prepare failed
    echo "<pre>Prepare failed:\n";
    print_r($pdo->errorInfo());
    echo "</pre>";
} else {
    if(!$stmt->execute()){
        // execute failed
        echo "<pre>Execute failed:\n";
        print_r($stmt->errorInfo());
        echo "</pre>";
    } else {
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if(count($result) == 0){
            echo "Query matched zero rows.";
        } else {
            //echo '<pre>', print_r($result,true),'</pre>'; // take a look at the result
            foreach($result as $image){
                $img = imagecreatefromstring($image['img']);
                $height = imagesy($img);
                $width = imagesx($img);
                if($height > 100 OR $width > 120){
                    echo "<img src='noimage.png' alt='' />";
                } else {
                    echo "<img src='images.php?cid={$image['cid']}' alt='' />";
                }
            }
        }
    }
}

the images.php file, needed to dynamically output the image that is stored in the database (the amount of code needed to do this is just one of the reasons NOT to store files in a database) -

$cid = isset($_GET['cid']) ? (int)$_GET['cid'] : 0;
if($cid < 1){
    die; // do nothing for impossible cid values
}

try {
    $pdo = new PDO('mysql:host=localhost;dbname=database','admin','root');
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$query = "SELECT img,type FROM images WHERE cid = :cid";
if(!$stmt = $pdo->prepare($query)){
    // prepare failed
    echo "<pre>Prepare failed:\n";
    print_r($pdo->errorInfo());
    echo "</pre>";
} else {
    $stmt->bindParam('cid', $cid, PDO::PARAM_INT); // this can fail and return a false, but i couldn't find an (easy) condition to cause it to fail
    if(!$stmt->execute()){
        // execute failed
        echo "<pre>Execute failed:\n";
        print_r($stmt->errorInfo());
        echo "</pre>";
    } else {
        if($result = $stmt->fetch(PDO::FETCH_ASSOC)){ // should always match the one row given by cid
            //echo '<pre>', print_r($result,true),'</pre>'; // take a look at the result
            header("Content-Type: {$result['type']}");
            echo $result['img'];
        }
    }
}

the above code ASSUMES/requires that your images database table has a type column that holds the content type for the image, such as image/jpeg for a jpg/jpeg image.

 

That's exactly it! It works perfectly, thanks!

 

Do you also have an idea how to show only 1 image instead of multiple? The code right now checks every image and shows multiple times a noimage.png and also the correct images from the database.

 

So:

If there is an image that is smaller than 120px in width and 100px in height in the database with the right id, only that image will be shown.

If there isn't an image that is smaller than 120px in width and 100px in height in the database with the right id, only /noimage.png will be shown.

[mac_gyver]

 

Do you also have an idea how to show only 1 image instead of multiple? The code right now checks every image and shows multiple times a noimage.png and also the correct images from the database.

 

So:

If there is an image that is smaller than 120px in width and 100px in height in the database with the right id, only that image will be shown.

If there isn't an image that is smaller than 120px in width and 100px in height in the database with the right id, only /noimage.png will be shown.

 

i don't have the slightest idea what that means?

 

the query and the logic in the code can be modified to do anything you want. the only thing i basically did was to make sure it would run or it would tell you why it didn't.

Edited May 8, 2013 by mac_gyver

[public]

i don't have the slightest idea what that means?

 

At this moment, the code gives me a maximum of 10 images, so I get for example 8x the noimage.png and 2x an image from the database. How can I display only one image? (So that the 8x noimage.png and the other image from the database will dissapear).

[jazzman1]

At this moment, the code gives me a maximum of 10 images, so I get for example 8x the noimage.png and 2x an image from the database. How can I display only one image? (So that the 8x noimage.png and the other image from the database will dissapear).

 

As Barand could be said - can you provide table structures and data?

 

Every image should have an ID.

 

This query is not very efficiency to me:

$query = "SELECT i.img, i.cid FROM images i
    INNER JOIN cardId as c ON i.cid = c.id
    WHERE c.status = 1 ORDER BY c.id ASC LIMIT 10";

What should be happen if you have 100 images with 10 different categories and the very first 10 images belong only to 1 category.

 

Your database logic it's sucks to me

Edited May 8, 2013 by jazzman1

[mac_gyver]

@jazzman, the query (less the order by and limit added from what the first post in this thread was doing) is what you posted and since it is joining the cardid to its image, a one-to-one relationship, its the join you would need to use to get the image that corresponds to the row in the cardid table.

[jazzman1]

@jazzman, the query (less the order by and limit added from what the first post in this thread was doing) is what you posted and since it is joining the cardid to its image, a one-to-one relationship, its the join you would need to use to get the image that corresponds to the row in the cardid table.

 

No, the relationship for me is one-to-many b/s "cid" means "category_id", so every category should have one (zero it's possible too) or more images.

 

That's why if him database structure is correct this code should be work properly:

    $pdo = new PDO('mysql:host=localhost;dbname=database', 'admin', 'root');
    $cid = $_GET['cid']; // it's the value from the category id coming from the link
    $query = $pdo->prepare("
    SELECT `i`.`img`, `c`.* FROM `images` as `i`
    INNER JOIN `cardId` as `c` ON (`i`.`cid` = `c`.`id`)
    WHERE `i`.`cid` IN (:cid) AND `c`.`status`=1");
    $query->bindParam('cid', $cid);
    $query->execute();
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    echo '<pre>'.print_r($row, true).'</pre>';
    }
    $query = null;

Edited May 8, 2013 by jazzman1