Random Select From Database - Code Help

[alexsaidani]

Well i have written this script that will allow me to search through a MYSQL database to see if the name of a film is there, and if it is there the script will then display what was typed into the search bar. However i have tried editing this script to enable it to select and display a random film from the database once one film has been typed in, it does not seem to work. I was just wondering if anyone could offer any assistance as to why it's not working that would be great.
Thanks in advance

<?php

$host = 'localhost';
$username = 'user';
$password = 'pass';
$tbl_name = 'movies';
$name = $_POST['name'];
$cname =$_POST['cname'];
$cname2 =$_POST['cname2'];
$ccategory = $_POST['ccategory'];
$category = $_POST['category'];
$crate = $_POST['crate'];
$rate = $_POST['rate'];
$type = $_POST['type'];

mysql_connect("localhost", "user", "pass")or die("Cannot Connect please contact [e-mail] error code 1");
mysql_select_db("web244-movies")or die("Cannot Connect please Contact [e-mail] error code 2");

$name = stripslashes($name);
$name = mysql_real_escape_string($name);
$tbl_name = stripslashes($tbl_name);
$tbl_name = mysql_real_escape_string($tbl_name);

if( $cname == 'yes' && $cname2 != 'yes' ){

$sql = "SELECT Names FROM $tbl_name ORDER BY RAND($name) LIMIT 1";
$result = mysql_query($sql);
$count=mysql_numrows($result);

while ($row = mysql_fetch_assoc($result)) {
$art = $row['link'];
}

if($count == 0 ){
echo '<div id="header"><a href="/index.html" class="menubar">Click Here</a> to return to the home page.</div>
<div id="info"></div> </br> The movie does not exist in our database';

}

else{
//$head = "/reviews/$art";
//header("location:$head");

echo '<div id="header"><a href="/index.html" class="menubar">Click Here</a> to return to the home page.</div>
<div id="info"></div> </br>', '<u>Results are show below:</u></br> </br>',
"<b>Movie Name:</b> $name",
"&nbsp; <b>Movie Category:</b> $type",
"&nbsp; <b>Movie Rating:</b> $rate",
"&nbsp; <b>Movie Description:</b> $description</br>";

}
mysql_free_result($result);
}

//variables search
else if($cname2 == 'yes' && $cname != 'yes'){

//Category search
if( $ctype == 'on'){
$category = strtolower($category);
$categorysql = "indsaty ='$category'";
}
else{ $categoryesql = "";
}

//rating search
if( $crate == 'on' && $ctype != 'on' && $length != 'on'){
$ratesql = " rating='$rate'";
}
else if ( $crate != 'on'){
$ratesql = "";
}
else{ $ratesql = " and rating='$rate'";
}

$categorysql= stripslashes($categorysql);
$categorysql = mysql_real_escape_string($categorysql);
$ratesql= stripslashes($ratesql);
$ratesql = mysql_real_escape_string($ratesql);

$sql2 = "SELECT * FROM $tbl_name WHERE $categorysql $ratesql";
$result2 = mysql_query($sql2);
$num = mysql_numrows($result2);
//echo $sql2; for testing sql

while( $i < $num){
$field1 = mysql_result($result2,$i,"link");
$field2 = mysql_result($result2,$i,"name");
echo "<br><a href=/reviews/$field1>$field2</a></br>";

$i++;

}

}

else { echo "We cannot find the film you are looking for.";

}

?>  

[Weedpacket]

I don't use MySQL but I'm going to guess that "RAND(Some film title)" would be meaningless.

[johanafm]

correct. it's ORDER BY RAND()

[alexsaidani]

Thanks for your replies, slight problem though, the thing is i have tried using that function, along with a few other functions and still it won't do what i want it to. I think it may be the way i actually wrote the script, because i mean it confuses me right now 😛

[Ashley_Sheridan]

For what purpose do you have the call to RAND() in there anyway? By my understanding of your problem, you want to retrieve the data for the film that is being searched for. If you want to do that, then a WHERE clause is what you need, not an ORDER BY.

[laserlight]

Ashley Sheridan wrote:

For what purpose do you have the call to RAND() in there anyway? By my understanding of your problem, you want to retrieve the data for the film that is being searched for.

I think you missed this part:

alexsaidani wrote:

However i have tried editing this script to enable it to select and display a random film from the database once one film has been typed in, it does not seem to work.

[Ashley_Sheridan]

Ah yes, so I did.

So the film selected is not dependent on what the user types at all, or is it?