difficult query problem, please help

[xiaodao]

database

CREATE TABLE `".$db_prefix."subcat` (

subid INT(10) AUTO_INCREMENT NOT NULL,
catid INT(10) NOT NULL,

subname VARCHAR(15) NOT NULL,

PRIMARY KEY(subid)

);


CREATE TABLE `".$db_prefix."photo` (

photoid INT(20) AUTO_INCREMENT NOT NULL,

photoname VARCHAR(50) NOT NULL,

subid INT(10) NOT NULL,
userid INT(10) NOT NULL,

description TEXT,

level INT(2) NOT NULL DEFAULT '0',

uploadtime INT(15) NOT NULL DEFAULT '0',

PRIMARY KEY(photoid)

);


now i want to select randomly from photo based on the value on catid(no subid), is it possible?

[missing-score]

I'm not 100% sure where you want to select the data from exactly, I'm not 100% sure how your DB links up, but for selecting a random entry you can use:

SELECT field FROM table WHERE (codition) ORDER BY RAND() LIMIT 1

[xiaodao]

hi, please see my database

maincat table has catid, catname
subcat table has subid, catid, subname
photo table has photoid, subid, photourl

now i want to randomly select photo based on catid where catid column does not exist in photo table, how to do?

[Spookster]

This is a database question. We do have a database forum which I will move this to now.

[xiaodao]

please... php forum usually got more experts and more popular than mysql forum

[Velox Letum]

Let me get this straight, you wish to select an image from the photo table where the subcategory is whatever is selected?

Building on missing-score's query:

SELECT * FROM photo WHERE subcat = 1 ORDER BY RAND() LIMIT 1

(The subcat being filled in by a PHP variable instead)

Or am I missing the point? If so, explain to me exactly what you want.

[xiaodao]

solved, thanks