0

I have created two tables in MySql one where blog content saved and another where blog ID and different pics path saved. I have saved more than one pics in one postID. Now I am trying to fetch the pics and blog post at same time but its not working. code below...

blogposts.php

<?php
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($conn,"SELECT  * , upload_data.FILE_NAME from blog_posts LEFT JOIN upload_data ON blog_posts.postID=upload_data.postID  ; ");
while($row = mysqli_fetch_array($result)) 
{?>

  <div class="b-slider j-smallslider" data-height="382">
                    <ul><?php 
                    if (!empty($FILE_NAME)) {
                   $result = mysqli_query($conn,"SELECT  * from upload_data where postID=postID ; ");
                while($row = mysqli_fetch_array($result)) 

                    {?>
                            <li data-transition="3dcurtain-vertical" data-slotamount="12">
                            <img data-retina src="articles/user_data/<?php echo( htmlspecialchars( $row['FILE_NAME'] ) ); ?>">
                        </li> <?php }} ?>
                     </ul>
                </div>
 <div class="b-blog-one-column__info">
        Title : <a href="#" class="f-more"><?php echo( htmlspecialchars( $row['postTitle'] ) ); ?></a>, <a href="#" class="f-more"><?php echo( htmlspecialchars( $row['postCat'] ) ); ?></a>
        <span class="b-blog-one-column__info_delimiter"></span>
        Tag : <a href="#" class="f-more">Nllam</a>
        <span class="b-blog-one-column__info_delimiter"></span>
        <a href="#" class="f-more f-primary"><i class="fa fa-comment"></i>12 Comments</a>
    </div>
<?php } ?>

The Problem is I am unable to fetch the pics. :(

Improve this question
5
  • A good rule of thumb is 'never execute a query inside a loop'. In this instance, your second query seems entirely redundant, as the required data has already been returned in the first query. Commented Aug 6, 2016 at 6:34
  • how should i do it then slider and post related to it without any loop ? @Strawberry Commented Aug 6, 2016 at 6:35
  • I have saved more than one pics in one postID I'm not quite sure what this means. Commented Aug 6, 2016 at 8:54
  • i mean , suppose in blog_posts table postID = 24 , and in upload_data table i have save 2 pics pic1.jpg and pic2.jpg and their id is 24 for both pic. so, i want to display 2 pics for the post id 24 @Strawberry Commented Aug 6, 2016 at 9:00
  • If you like, consider following this simple two-step course of action:1. If you have not already done so, provide proper CREATE and INSERT statements (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. Commented Aug 6, 2016 at 11:39

3 Answers 3

Reset to default
1

use this query

$result = mysqli_query($conn,"SELECT  blog_posts.* , upload_data.FILE_NAME from blog_posts,upload_data WHERE blog_posts.postID=upload_data.postID");

and then print your results with print_r() function and check its get results or not.

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

3 Comments

if its works then accept the answer and close this question :)
getting result but not the picx which i wanted in the slider. and getting 3 or 4 results instead of 3 or 4 picx and one post.
you can store your images in a single row by serialize() function then you need to store all images in one array and serialize that array. . or if you store images in multiple rows then you can run two queries to get all images.
0 
SELECT blog_posts.*, group_concat(upload_data.FILE_NAME) FROM `blog_posts` 
LEFT JOIN upload_data on blog_posts.postID = upload_data.postID 
GROUP BY blog_posts.postID

Try like this you will get number of post rows and filename concadinated with commas if you have images. then Apply logic to display results. Any help you need. Don't hesitate to ask me.

Improve this answer

1 Comment

what should be the query to fetch the picx of single post? @kaja
0

hope this work;

$post_id = 1337;
$conn = new PDO(......); //get connection
$conn->query('SET group_concat_max_len = 4096;');
$query = "SELECT  bp.* , 
            (SELECT GROUP_CONCAT(ud.FILE_NAME SEPARATOR ',')   
             from upload_data ud where ud.postID=bp.postID) as files
          FROM blog_posts bp 
          WHERE bp.postID=:post_id ;";
$result = $conn->prepare($query);
$result->bindParam(":post_id", $post_id);
$result->execute();

//don't really need while for one row but oh well
while($row = $result->fetch()){

    $files=$row['files'];
    $files = empty($files) ? [] : explode(',', $files);
    ?>

    <div class="b-slider j-smallslider" data-height="382">
            <ul>
            <?php foreach ($files as $file){?>
                <li data-transition="3dcurtain-vertical" data-slotamount="12">
                    <img data-retina src="articles/user_data/<?php echo( htmlspecialchars( $file ) ); ?>">
                </li> 
            <?php } ?>
            </ul>
    </div>
    <div class="b-blog-one-column__info">
        Title : <a href="#" class="f-more"><?php echo( htmlspecialchars( $row['postTitle'] ) ); ?></a>, <a href="#" class="f-more"><?php echo( htmlspecialchars( $row['postCat'] ) ); ?></a>
        <span class="b-blog-one-column__info_delimiter"></span>
        Tag : <a href="#" class="f-more">Nllam</a>
        <span class="b-blog-one-column__info_delimiter"></span>
        <a href="#" class="f-more f-primary"><i class="fa fa-comment"></i>12 Comments</a>
    </div>
<?php } ?>
Improve this answer

3 Comments

getting all picx in one slider and only one post.
oh do a where statement after left join; where bp.postID=x
sorry had to use PDO as i am more familiar with the syntax; must set group concat max len or else if a post has too many files; you will get memory problems as it does have concat string limit;

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.