Jump to content

Show only certain images


public

Recommended Posts

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

$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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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 by mac_gyver
Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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 by jazzman1
Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

@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 by jazzman1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.