jazzman1 Posted May 8, 2013 Share Posted May 8, 2013 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 8, 2013 Share Posted May 8, 2013 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. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted May 8, 2013 Share Posted May 8, 2013 $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! Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted May 8, 2013 Share Posted May 8, 2013 This is a cartesian join, we should avoid using it! It's too late - c.status = 1 it's time to go bed Quote Link to comment Share on other sites More sharing options...
public Posted May 8, 2013 Author Share Posted May 8, 2013 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 8, 2013 Share Posted May 8, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
public Posted May 8, 2013 Author Share Posted May 8, 2013 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). Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted May 8, 2013 Share Posted May 8, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 8, 2013 Share Posted May 8, 2013 @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. Quote Link to comment Share on other sites More sharing options...
jazzman1 Posted May 8, 2013 Share Posted May 8, 2013 (edited) @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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.