0

how to print 30 random images and image name from database width out repeating same images?

database: image
image_id
user_id
image(image store here blob)

iam thinking some thing like this but not sure

i = 0;
        while(i > 31)
        $queryget = mysql_query("SELECT image_name FROM user WHERE username = '$user'") or die("query didnt work");
        $row = mysql_fetch_assoc($queryget);    
        $image_name_db = $row['image_name'];


                            $image = mysql_query(SELECT image FROM image ORDER BY productID DESC LIMIT 10);
                $src = 'data:image/gif;base64,' . $image;
                <img src=\"$src\"  height='30%' width='30%'/>
                            i++

Edited by hwoarang69

2
Contributors
6
Replies
7
Views
5 Years
Discussion Span
Last Post by Biiim
1
$Q = "SELECT (SELECT image_name FROM user WHERE username = '$user') as `image_name`"
    .",`image_id`,`image`"
    ." FROM `images`"
    //." WHERE `user_id` = 1"
    ." ORDER BY RAND()"
    ." LIMIT 30";
$R = mysql_query($Q);
if($R !== false){
    while($row = mysql_fetch_assoc($R)){
        echo $row['image_id']." printed<br/>\r\n";
    }
 }else{
     echo "query error: ".mysql_error();
 }

magic of mysql and subqueries, just need to put your image printing out bit in the while loop($row['image'] contains the raw image data) . You always want to avoid putting a mysql query in a loop, it is very database heavy there is usually always a way to get the data you want in 1 or 2 queries.

Also rand() can get quite heavy for large amounts of rows(100000+) as well as it generates a random number between 0 and 1 for every row in the database, so if you get that big consider generating a rand() value as an actual database column and index it

0

i got a quertion
i have two tables called "user" and "image"
in user i have user_id, user_name, password.
in image i have image_id, user_id, image(store image here), image_name.

i am selecting image_name from user table?
also wha is this code doing .",image_id,image"

    $Q = "SELECT (SELECT image_name FROM user WHERE username = '$user') as `image_name`"
                    .",`image_id`,`image`"                    
                    ." FROM `image`"
                    //." WHERE `user_id` = 1"
                    ." ORDER BY RAND()"
                    ." LIMIT 30";

sorry new at php

1

SELECT image_name FROM user WHERE username = '$user'

You can also use joins in mysql to get data from multiple tables at once, i notice this user table and the user_id in the image table, i can guess the user table has a user_id field so you could do something like this:

SELECT u.image_name,im.image_id,im.image
FROM user u LEFT JOIN image im ON u.user_id = im.user_id
WHERE u.user_id IN(1,2,5)
ORDER BY rand()

that basically does this:

user
user_id,image_name
1,test
2,name2
3,monkey
4,elephant
5,pig

image
image_id,user_id
1,1
2,3
3,1
4,4
5,2
6,1
7,2
8,5

query result:
name2,image5
pig,image8
test,image1
test,image3
name2,image7
test,image6

0

how can i print image
echo $row['image']." printed<br/>\r\n";

prints wired letters

1

Ah i thought you had that part sorted, i've never worked with storing raw image data in a database, i'd image you have to make a separate page called show-image.php or something like that:

show-image.php?image_id=5

<?php
$image_id = $_GET['image_id'];
if(ctype_digit($image_id)){
    $query = "SELECT `image` FROM `image` WHERE `image_id` = {$image_id} LIMIT 1";
    $R = mysql_query($query);
    if($R !== false){
        $image = mysql_fetch_assoc($R);
        header("content-type: image/gif");
        //header("content-type: {$image['mimetype']}")
        echo $image['image'];
    }else{
        //show error image?
    }
}else{
    //show not found image maybe?
}
?>

Then you need to upload that file and then print out

 echo "<img style='height:100px;width:100px;' src='show-image.php?image_id={$row['image_id']}'/><br/>\r\n";
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.