Problem with categories

[vinsbg]

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.

[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

[vinsbg]

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.

[gabrielcastillo]

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');

[vinsbg]

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

[gabrielcastillo]

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';
            }
    }

[vinsbg]

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

[gabrielcastillo]

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>';

[vinsbg]

Thank's. I'll try it now.

[vinsbg]

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.