Hello,

So I try to learn php and decided to make one site where I add images, save them in folder and id, name,type, path in mysql. Then show on page. So far I have upload form and I can upload and save images. Also I showing them successfully on the page.

Now I'm trying to make categories like - Nature, Funny ... etc. So I added one field in my main table -> img_category.
Also I maded second table - cats whit cat_id and cat_name fields. Using this to show the categories on the page:

           <?php
            $q = mysqli_query($con,"select * from cats");
            while ($res = mysqli_fetch_assoc($q))
            {
                echo '<a href="pic.php?cat_id='. $res['cat_id'] .'">'.$res['cat_name'].'<br/>';
            }

So now how can I make when I click on some category link to load images only from this category?
I have managed to make something like this but it doesn't work like is expected. Sorry for long lines:

<?php
            $q = mysqli_query($con,"select * from cats");
            while ($res = mysqli_fetch_assoc($q))
            {
                echo '<a href="pic.php?cat_id='. $res['cat_id'] .'">'.$res['cat_name'].'<br/>';
            }

            ?>
            <hr>
            <?php
            $cat_id = $_GET['cat_id'];
            $query = "SELECT * FROM images JOIN cats ON images.img_category = cats.cat_id WHERE cats.cat_id = '$cat_id'";
            $result = mysqli_query($con, $query) or die("Query failed: " . mysqli_errno($con));

            $line = mysqli_fetch_array($result, MYSQL_BOTH);
            if (!$line) echo '';
            $previd = -1;
            $currid = $line[0];
            if (isset($_GET['id'])) {
                do {
                    $currid = $line[0];
                    if ($currid == $_GET['id']) break;
                    $previd = $currid;
                    $line = mysqli_fetch_array($result, MYSQL_BOTH);
                } while ($line);
            }

            if ($line) {
                echo "<div id=\"picture\">";

                echo "<img style=\"width:100%;margin:0 auto;\" src=\"upload/".$line['name']."\" /></a><br />";
                echo "<div id=\"caption\">".$line['caption']."</div><br />";
            }
            else echo "There is no images!\n";

            if ($previd > -1) echo '<a href="pic.php?cat_id='.$previd.'" class="prev_pic"><span>Prev</span></a>';
            echo str_repeat('&nbsp;', 5);

            $line = mysqli_fetch_array($result, MYSQL_BOTH);

            $query = "select * from images order by RAND() LIMIT 1";
            $result = mysqli_query($con, $query) or die("Query failed: " . mysqli_errno($con));
            while ($row = mysqli_fetch_array($result, MYSQL_BOTH)){
                echo '<a href="pic.php?cat_id='.$row['id'].'"class="random">Random</a>';
            }
            echo str_repeat('&nbsp;', 5);
            if ($line) echo '<a href="pic.php?cat_id='.$line[0].'" class="next_pic"><span>Next</span> </a><br /><br />';

            echo "</div>";

            ?>

The results are:

  1. When there is image in the category is showed but and if I click on 'Next' button I get the same image.
  2. If there is no image in the category I get all echoes like link whit the ID of last category for exam: There is no image like link and if I click it I get last category ID loaded. In my case I have 8 categories so ID=8.

Any help is appreciate!
Thank's

EDIT:
Ok this line:

echo '<a href="pic.php?cat_id='.$line[0].'" class="next_pic"><span>Следваща</span> </a>

Where is pic.php?cat_id=... i think is wrong. Here I must take next image ID not next category ID. But how to change it for image? If i make it pic.php?id=... I get empty page.
I don't understand it.

Member Avatar for diafol

Have you thought of a slightly different approach where you can assign an image to more than one category as in tagging?

Image table
Category table
ImageCategory table just has 2fields imageid and catid

But there is no possibility to add image to more than one category in upload form. And don't know really how to make it even whit one category.

diafol is correct. This would make your site scalable in the future. You can add a select category option in your upload form. Then create a trigger in your database that will insert the cat_id with the image_id in the imageCategory table. Or you could create a query like so:

INSERT INTO image_table (`image_path`) VALUES('$image_path');
INSERT INTO imageCategory (`image_id`, `cat_id`) VALUES(LAST_INSERT_ID(), '$cat_id');

But the problem still will be there whit following category link and pagginate?
And how to put 2 querys on upload form?
Now I upload whit this query

 $query = "INSERT INTO images (caption, name, size, type, file_path, img_category) VALUES (?,?,?,?,?,?)";
            $conn = $db->prepare($query);
            if ($conn == TRUE) {
                $conn->bind_param("ssisss",$caption, $myFile, $fileSize, $fileType, $path, $category);
                if (!$conn->execute()) {
                    echo 'error insert';
                } else {

                    //header("Location: index.php");
                    echo "The image {$_FILES['userfile']['name']} was successfully uploaded<br />
                    <a href='index.php'>Add another image</a><br />";
                    //echo '<meta http-equiv="refresh" content="5">';
                    exit;
                }

I'm a bit more confused now..

You are using PDO driver so the code is a bit different. You need to user the lastinsertid function HERE

Im not very good with PDO but I blieve the process would be something like this.

    $query = "INSERT INTO images (caption, name, size, type, file_path, img_category) VALUES (?,?,?,?,?,?)";
    $conn = $db->prepare($query);
    if ($conn == TRUE) {

        $conn->bind_param("ssisss",$caption, $myFile, $fileSize, $fileType, $path, $category);

        if (!$conn->execute()) {

            echo 'error insert';

        } else {

            $id = $conn->lastInsertId();
            $conn = $db->prepare("INSERT INTO imageCategory (`image_id`, `cat_id`) VALUES('$id', '$cat_id'));

            if($conn->execute()){


                //header("Location: index.php");

                echo "The image {$_FILES['userfile']['name']} was successfully uploaded<br />

                <a href='index.php'>Add another image</a><br />";

                //echo '<meta http-equiv="refresh" content="5">';
                exit;
            }else{
            return 'errors';
            }
    }

Thank's but this mean that I must re-write the whole script- upload.php upload form, pagging.php? I don't want to start from scratch... again :)

Here is what I wrote in my localhost:

    if(isset($_GET['id'])){
        $id = $_GET['id'];
    }else{
        $id = '1';
    }

    function get_images($id)
    {
        $data = array();

        $db  = new mysqli('localhost', 'root', '', 'image_uploads');

        if($db->connect_errno > 0) die("Unable to connect to database [".$db->connect_error . ']');

        $sql = "SELECT * FROM `images` WHERE cat_id='$id'";

        if(!$result = $db->query($sql)){
            die("Error running your sql query [" . $db->error . ']');
        }

        while($row = $result->fetch_assoc()){
            $data[] = $row;
        }

        $result->free();
        $db->close();

        return $data;
    }

    $prev = 0;
    $next = 1;

    foreach(get_images($id) as $image){
        $prev =  $image['cat_id'] - 1;
        $next = $image['cat_id'] + 1;
    ?>  
        <img src="http://<?php echo str_replace('localhost', '127.0.0.1', $image['path']); ?>.jpg" alt="<?php echo $image['title']; ?>" width="200" />

    <?php
    }

    echo '<a href="/mysqlitut.php?id='.$prev.'">Prev</a>';
    echo '<a href="/mysqlitut.php?id='.$next.'">Next</a>';

Thank's. I'll try it now.

Ok, another try. I've made something like this:

$cat_id = $_GET['cat_id'];
        $cat_id = mysqli_real_escape_string($con, $cat_id);
        $query = "SELECT * FROM images JOIN cats ON images.img_category = cats.cat_id WHERE cats.cat_id = '$cat_id'";
        $result = mysqli_query($con, $query) or die("Query failed: " . mysqli_errno($con));
$prevSQL = mysqli_query($con,"SELECT cat_id FROM cats WHERE cat_id < $cat_id ORDER BY cat_id DESC LIMIT 1") or die (mysqli_error($con));
        $nextSQL = mysqli_query($con, "SELECT cat_id FROM cats WHERE cat_id > $cat_id ORDER BY cat_id ASC LIMIT 1") or die (mysqli_error($con));
        $prevobj=mysqli_fetch_object($prevSQL);
        $nextobj=mysqli_fetch_object($nextSQL);
        $pc = mysqli_fetch_object(mysqli_query($con, "SELECT COUNT(cat_id) as pid FROM cats WHERE cat_id<$cat_id ORDER BY cat_id DESC")) or die (mysqli_error($con));
        $nc = mysqli_fetch_object(mysqli_query($con, "SELECT COUNT(cat_id) as nid FROM cats WHERE cat_id>$cat_id ORDER BY cat_id ASC")) or die (mysqli_error($con));
        $prev=$pc->pid>0 ? '<a href="pic.php?cat_id='.$cat_id.'&id='.$prevobj->cat_id.'">Prev</a> |' : '';
        $next=$nc->nid>0 ? '<a href="pic.php?cat_id='.$cat_id.'&id='.$nextobj->cat_id.'">Next</a>' : '';
        $row = mysqli_fetch_array($result);
        echo "<div id=\"picture\">";
        echo "<img src=\"upload/" . $row['name']  . "\" alt=\"\" /><br />";
        echo $row['caption'] . "<br />";
        echo "</p>";
        echo $prev;
        echo $next;

Just a help where to put query for next image because when I click next now the image is same.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.