Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 6 of 6
  1. #1
    Junior Member
    Join Date
    Apr 2009
    Posts
    13
    Member #
    18829
    Good evening,

    I have a database, inside it *some* of the records have images, some don't.

    How do I randomly select *just* from the records that have images?

    ie in pigeon SQL

    SELECT * FROM database where imgdata contains an image ORDER BY RAND()

    imgdate is set as type longblob in the database, I have some test php where I can display an image fine if I give it the Id, but I need to be able to randomly select just those records which have an image.

    thanks,

    Greg

  2.  

  3. #2
    Junior Member
    Join Date
    Apr 2009
    Posts
    2
    Member #
    18828
    SELECT * FROM table_name where imgdata <> '' ORDER BY RAND() limit 1

    it's simple as that. i know you said imgdata is of type longblob. it works, try it.

  4. #3
    Junior Member
    Join Date
    Oct 2009
    Posts
    2
    Member #
    20298
    hi
    SELECT * FROM table_name where imgdata <> '' ORDER BY RAND() limit 1
    it will work correctly , myself has tried it once.

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Better would be:
    Code:
    SELECT * 
    FROM table_name 
    WHERE imgdata IS NOT NULL 
    ORDER BY RAND() LIMIT 1;
    More efficient, because you're not pulling the blob for testing purposes... you're only checking if the null flag is set, which will show significant performance increases as your database grows.

    Just make sure that the column isn't NOT NULL, and that you're not inserting '' when no image is set.


    PS - Older versions of MySQL better support the != operator over the ANSI-92 <> operator, though the latter is preferred if you ever plan to switch databases (e.g. to MS-SQL or Postgres).

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    PS - blobs in general are terribly inefficient. I rarely suggest them. You're much better off (in terms of performance) with file-based image management. Not really related to your request, just a general thought.

  7. #6
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    If you're saying that the database can contain any type of file and you only want images back, either:
    • Store the MIME type of each file and look for ones starting with "image/" (by far the best option), or
    • Look for the magic number in the data that identifies the type of image.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 12:29 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com