how can I make my query choose from auto_increment field?

[seanpaulcail]

I have the following query that chooses from a list of consultants then presents randomly 2 consultants out of 15. It presents the consultant image, text and a link to a page profiling the consultant.

here's my query

$query1 = 'SELECT *
             FROM mb_consultants
            WHERE consultant_id < 15
         ORDER BY rand()
            LIMIT 1';


$result1 = mysql_query($query1);


$db_result1 = mysql_fetch_array($result1);


// query for box 2
$query2 = 'SELECT *
             FROM mb_consultants
            WHERE consultant_id < 15
              AND consultant_id != '.$db_result1['consultant_id'].'
         ORDER BY rand()
            LIMIT 1';


$result2 = mysql_query($query2);


$db_result2 = mysql_fetch_array($result2);

But instaed of hardcoding "15" I want to choose from the consultant_id which is an auto_increment field that currently has 15 items. When the client adds more consultants I don't want to have to go in and write 16, 17, 18 etc in place of 15. How can I select 1 random number from the auto_increment field which is labeled "consultant" id"?

thanks

[seanpaulcail]

The answer is

ORDER BY rand(consultant_id)

instead of

 ORDER BY rand()

[seanpaulcail]

The web site seems to be reloading the same consultants when I press reload.

My code must be wrong

any heklp is appreciated.

thanks

[seanpaulcail]

I've also tried to extract the number of rows, then use that in my query

$query0 = 'SELECT *
             FROM mb_consultants;

$result0= mysql_query($query0, $conn);
$num_rows = mysql_num_rows($result0);


$query1 = 'SELECT *
             FROM mb_consultants
            WHERE consultant_id < $num_rows
         ORDER BY rand()
            LIMIT 1';



$result1 = mysql_query($query1);

but I get an error. I think I'm on the right track it's just that my syntax is wrong.

thanks

[laserlight]

$query = "SELECT * FROM mb_consultants";
$result = mysql_query($query)
	OR die(mysql_error()); //for debugging
$num_rows = mysql_num_rows($result);

$query = "SELECT *
	FROM mb_consultants
	WHERE consultant_id < '{$num_rows}'
	ORDER BY rand()
	LIMIT 1";
$result = mysql_query($query)
	OR die(mysql_error()); //for debugging

The difference is that strings delimited (enclosed) by double quotes are parsed for variables, strings delimited by single quotes are not.

[seanpaulcail]

But I'm still getting an error message;

here's the full query code

$query0 = "SELECT * FROM mb_consultants";
$result0 = mysql_query($query0)
    OR die(mysql_error()); //for debugging
$num_rows = mysql_num_rows($result0);

$query1 = "SELECT *
    FROM mb_consultants
    WHERE consultant_id < '{$num_rows}'
    ORDER BY rand()
    LIMIT 1";
$result1 = mysql_query($query1)
    OR die(mysql_error()); //for debugging


$db_result1 = mysql_fetch_array($result1);



// query for box 2
$query2 = "SELECT *
             FROM mb_consultants
            WHERE consultant_id < '{$num_rows}'
            and consultant_id != '.$db_result1['consultant_id'].'
         ORDER BY rand()
            LIMIT 1";


$result2 = mysql_query($query2);


$db_result2 = mysql_fetch_array($result2);

I get the following error:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/macboulder/html/index.php on line 52 

line 52 is this line:

and consultant_id != '.$db_result1['consultant_id'].'

thanks again

[laserlight]

$query2 = "SELECT *
	FROM `mb_consultants`
	WHERE `consultant_id` < '{$num_rows}'
		AND `consultant_id` != '{$db_result1['consultant_id']}'
	ORDER BY rand()
	LIMIT 1";

btw, if you arent going to use $query1 or $query2 again, it is better to simply re-use the variable, IMO.

[seanpaulcail]

the syntax always gets me.