Database Retrival Problem

**fiziwig** · Mar 25 '06, 07:35 AM

Re: Database Retrival Problem

There's so much code there I'm not sure which part you're having
trouble with, but there's a few things that look suspicious...

// GETTING KEYWORDS HERE
$query_kwds = "SELECT * FROM moviedb ORDER BY date ASC";
$result_kwds = mysql_query($qu ery_kwds);
while($rs = mysql_fetch_arr ay($result_kwds )) {
$akey = $rs['title'];
//$akey = "$mtitle, ";
}
As near as I can tell, this piece of code accomplishes nothing
whatsoever. It assigns a value to $akey and then overwrites that values
the next time through the loop without ever doing anything with the
value. Seems like a complete waste of time.

I think what you intended was $akey[]=$rs['title']; which adds the next
keyword to the array $akey. Without the square brackets $akey is a
scalr, not an array.

Also, if 'title' is the only thing you're using you should say

$query_kwds = "SELECT title FROM moviedb ORDER BY date ASC";

instead of dragging all the db fields into memory. And if you only want
10 of them you should at a LIMIT clause, or else you will load the
entire contents of the database into the meta tag.

for this bit:

$q_count = mysql_query("SE LECT * FROM moviedb");
$totalrows = mysql_num_rows( $q_count);

This would consume a lot less memory and probably run quite a bit
faster:

$q_count = mysql_query("SE LECT COUNT(*) FROM moviedb");
$row = mysql_fetch_arr ay($q_count, MYSQL_NUM);
$totalrows = $row[0];

Take my advice with a grain of salt. I've only been using php for 8
days now, so I'm no expert.

--gary

**Chris H** · Mar 25 '06, 08:45 AM

Re: Database Retrival Problem

ok well i ended up fixing it just by moving the while loop in between the
meta tag and just echo teh keyword results

"Chris H" <lppa2004@insig htbb.com> wrote in message
news:Ks4Vf.8815 75$xm3.546425@a ttbi_s21...[color=blue]
> Ok in the following code I am trying to have my keywards meta tag auto
> insert the top 10 titles from most recently added database entries, right
> now the while loop is only setting the $keyword varibale with one title
> and
> not the top 10.. not sure what i am doing wrong...
>
> Note: keyword retrival code will be between
> ########
> //comment
> CODE
> #######
>
> PHP CODE HERE--------
> <?php
>
> require ('config.php');
>
> if (!function_exis ts('connect_to_ mysql')){
>
> function connect_to_mysq l(){
>
> global $db_location,$d b_username,$db_ password,$db_da tabase;
>
> $conn =
> mysql_connect(" $db_location"," $db_username"," $db_password");
> if (!$conn) die("Error: " . mysql_error());
> mysql_select_db ($db_database,$ conn) or die("Error: " .
> mysql_error());
>
> return;
> }
> }
>
> if (!function_exis ts('error_messa ge')){
>
> function error_message($ text){
>
> echo"<p align='left' class='errortex t'><br><b><span
> style='backgrou nd-color: #FF6666'>Error: </span></b><br>$text</p>";
>
> return;
>
> }
>
> }
> ############### ############### ############### ############### ############### #####
>
> function list_all($cat,$ sortby,$char,$p age,$pageprev,$ search){
> global $title,$site_do main,$keywords;
>
> $limit = 25;
> ############### ############### ############### ############### #########
> // GETTING KEYWORDS HERE
> $query_kwds = "SELECT * FROM moviedb ORDER BY date ASC";
> $result_kwds = mysql_query($qu ery_kwds);
> while($rs = mysql_fetch_arr ay($result_kwds )) {
> $akey = $rs['title'];
> //$akey = "$mtitle, ";
> }
> ############### ############### ############### ############### ######
> if(!$page){
> $page = 1;
> }
>
>
> $how_many = mysql_num_rows( mysql_query("SE LECT * FROM moviedb"));
> echo "<HTML><head><t itle>$title</title>
> <meta name=\"keywords \" content=\"$keyw ords, $akey\">
> <meta name=\"descript ion\" content=\"$desc \"></HEAD>";
> echo "<table width=\"820\" border=\"0\" cellspacing=\"0 \"
> cellpadding=\"0 \">";
> echo "<tr><td><cente r><font size=\"6\"><a
> href=\"$site_do main\">$title</a></font></center></td></tr>";
> echo "<tr><td><cente r>Currently $how_many movies in
> database</center></td></tr>";
> echo "<tr><td><cente r><br>";
> echo quick_links();
> echo "</center></td></tr>";
> echo "</table>\n";
> echo "<table width=\"820\" border=\"1\" cellspacing=\"0 \"
> cellpadding=\"0 \">";
> echo "<tr>";
> echo "<th width=\"35\" bgcolor=\"66ccf f\"></th>";
> echo "<th width=\"400\" bgcolor=\"66ccf f\"><strong>< a
> href=\"index.ph p?t=&sortby=tit le\">Movie Title</a> </strong></th>";
> echo "<th width=\"260\" bgcolor=\"66ccf f\"><strong>< a
> href=\"index.ph p?t=&sortby=rat ing\">Rating</a></strong></th>";
> echo "<th width=\"125\" bgcolor=\"66ccf f\"><strong>< a
> href=\"index.ph p?t=&sortby=cat \">Genre</a></strong></th>";
> echo "</tr>";
>
> connect_to_mysq l();
>
>
>
> $limitvalue = $page * $limit - ($limit);
>
> if ($sortby == "") {
> $sql = mysql_query("SE LECT * FROM moviedb ORDER BY title ASC LIMIT
> $limitvalue, $limit");
> $q_count = mysql_query("SE LECT * FROM moviedb");
> $totalrows = mysql_num_rows( $q_count);
> $plink = "";
> }
> elseif ($sortby == "recent") {
> $sql = mysql_query("SE LECT * FROM moviedb WHERE date LIKE '%-%' ORDER BY
> date DESC LIMIT $limitvalue, $limit");
> $q_count = mysql_query("SE LECT * FROM moviedb WHERE date LIKE '%-%'");
> $totalrows = mysql_num_rows( $q_count);
> $plink = "&sortby=recent ";
> }
> elseif ($sortby == "alpha") {
> $sql = mysql_query("SE LECT * FROM moviedb WHERE alpha LIKE '$char' ORDER
> BY
> title ASC LIMIT $limitvalue, $limit");
> $q_count = mysql_query("SE LECT * FROM moviedb WHERE alpha LIKE '$char'");
> $totalrows = mysql_num_rows( $q_count);
> $plink = "&sortby=alpha& char=$char";
> }
> elseif ($sortby == "genre") {
> $sql = mysql_query("SE LECT * FROM moviedb WHERE cat LIKE '$cat' ORDER BY
> title ASC LIMIT $limitvalue, $limit");
> $q_count = mysql_query("SE LECT * FROM moviedb WHERE cat LIKE '$cat'");
> $totalrows = mysql_num_rows( $q_count);
> $plink = "&sortby=genre& cat=$cat";
> }
> elseif ($sortby == "title") {
> $sql = mysql_query("SE LECT * FROM moviedb ORDER BY title ASC LIMIT
> $limitvalue, $limit");
> $q_count = mysql_query("SE LECT * FROM moviedb");
> $totalrows = mysql_num_rows( $q_count);
> $plink = "&sortby=title" ;
> }
> elseif ($sortby == "rating") {
> $sql = mysql_query("SE LECT * FROM moviedb ORDER BY rating DESC LIMIT
> $limitvalue, $limit");
> $q_count = mysql_query("SE LECT * FROM moviedb");
> $totalrows = mysql_num_rows( $q_count);
> $plink = "&sortby=rating ";
> }
> elseif ($sortby == "cat") {
> $sql = mysql_query("SE LECT * FROM moviedb ORDER BY cat ASC LIMIT
> $limitvalue, $limit");
> $q_count = mysql_query("SE LECT * FROM moviedb");
> $totalrows = mysql_num_rows( $q_count);
> $plink = "&sortby=ca t";
> }
> if ($search != "") {
> $sql = mysql_query("SE LECT * FROM moviedb WHERE title LIKE '%$search%'
> ORDER BY title ASC LIMIT $limitvalue, $limit");
> $q_count = mysql_query("SE LECT * FROM moviedb WHERE title LIKE
> '%$search%'");
> $totalrows = mysql_num_rows( $q_count);
> $plink = "&search=$searc h";
> }
>
> $bgcolor = "#E0E0E0"; // light gray
> $result = $sql;
> $z = 1;
> while($rs = mysql_fetch_arr ay($result)) {
> if ($bgcolor == "#E0E0E0"){
> $bgcolor = "#FFFFFF";
> }else{
> $bgcolor = "#E0E0E0";
> }
> $alpha = $rs['alpha'];
> $title = $rs['title'];
> $link = $rs['link'];
> $rating = $rs['rating'];
> $ratingf = format_rating($ rating);
> $category = $rs['cat'];
> $stars = get_stars($rati ng);
>
> echo "<tr>";
> echo "<td width=\"35\" bgcolor=\"$bgco lor\"><center>$ z</center></td>";
> echo "<td width=\"400\" bgcolor=\"$bgco lor\"><a
> href=\"$link\"> $title</a></td>";
> echo "<td width=\"260\" bgcolor=\"$bgco lor\">$stars ($ratingf)</td>";
> echo "<td width=\"125\"
> bgcolor=\"$bgco lor\"><center>$ category</center></td>";
> echo "</tr>\n";
> $z++;
> }
>
> echo "</table>\n";
> echo "<table border=\"0\" width=\"820\">\ n";
> echo "<tr>\n";
> echo "<td width=\"100%\"> \n";
> echo "<center>\n ";
>
> if($page != 1){
> $pageprev = $page - 1;
>
> echo("<a
> href=\"index.ph p?t=$plink&page =$pageprev\">PR EV".$limit."</a> ");
> }else{
> echo("PREV".$li mit." ");
> }
>
> $numofpages = $totalrows / $limit;
>
> for($i = 1; $i <= $numofpages; $i++){
> if($i == $page){
> echo($i." ");
> }else{
> echo("<a href=\"index.ph p?t=$plink&page =$i\">$i</a> ");
> }
> }
>
>
> if(($totalrows % $limit) != 0){
> if($i == $page){
> echo($i." ");
> }else{
> echo("<a href=\"index.ph p?t=$plink&page =$i\">$i</a> ");
> }
> }
>
> if(($totalrows - ($limit * $page)) > 0){
> $pagenext = $page + 1;
>
> echo("<a
> href=\"index.ph p?t=$plink&page =$pagenext\">NE XT".$limit."</a>");
> }else{
> echo("NEXT".$li mit);
> }
> echo "</center>\n";
> echo "</td>\n";
> echo "</tr>\n";
>
> return;
>
> }
>
> //was browse_by_cat()
> function quick_links(){
>
> print <<<EOF
> <center>
> <table border="0" cellpadding="0" cellspacing="0" style="border-collapse:
> collapse" bordercolor="#1 11111" width="820" id="AutoNumber1 ">
> <tr>
> <td width="100">
> <form action='$_SERVE R[PHP_SELF]' method='get' name="form">
> <input type='hidden' name='t' value=''>
> <input type='hidden' name='sortby' value='genre'>
> <select name="cat" size="1" id="cat" onchange="this. form.submit();" >
> <option value="" selected>Browse By Genre</option>
> <option value="action"> Action</option>
> <option value="adventur e">Adventure </option>
> <option value="animatio n">Animation </option>
> <option value="biograph y">Biography </option>
> <option value="comedy"> Comedy</option>
> <option value="crime">C rime</option>
> <option value="document ary">Documentar y</option>
> <option value="drama">D rama</option>
> <option value="educatio nal">Educationa l</option>
> <option value="family"> Family</option>
> <option value="fantasy" >Fantasy</option>
> <option value="horror"> Horror</option>
> <option value="music">M usic</option>
> <option value="musical" >Musical</option>
> <option value="mystery" >Mystery</option>
> <option value="romance" >Romance</option>
> <option value="scifi">S ci-Fi</option>
> <option value="short">S hort</option>
> <option value="sport">S port</option>
> <option value="tv">Tele vision</option>
> <option value="thriller ">Thriller</option>
> <option value="war">War </option>
> <option value="western" >Western</option>
> </select>
> </form>
> </td>
> <td width="150">
> <form action='$_SERVE R[PHP_SELF]' method='get'>
> <input type='hidden' name='t' value=''>
> <input type='hidden' name='sortby' value='alpha'>
> <select name="char" size="1" onchange="this. form.submit();" id="char" >
> <option value="" selected>Browse Alphabetically</option>
> <option value="#" >Number</option>
> <option value="a" >A</option>
> <option value="b" >B</option>
> <option value="c" >C</option>
> <option value="d" >D</option>
> <option value="e" >E</option>
> <option value="f" >F</option>
> <option value="g" >G</option>
> <option value="h" >H</option>
> <option value="i" >I</option>
> <option value="j" >J</option>
> <option value="k" >K</option>
> <option value="l" >L</option>
> <option value="m" >M</option>
> <option value="n" >N</option>
> <option value="o" >O</option>
> <option value="p" >P</option>
> <option value="q" >Q</option>
> <option value="r" >R</option>
> <option value="s" >S</option>
> <option value="t" >T</option>
> <option value="u" >U</option>
> <option value="v" >V</option>
> <option value="w" >W</option>
> <option value="x" >X</option>
> <option value="y" >Y</option>
> <option value="z" >Z</option>
> </select>
> </form>
> </td width="200">
> <td><form action='$_SERVE R[PHP_SELF]' method='get'>
> <input type='hidden' name='t' value=''>
> <select name="sortby" size="1" onchange="this. form.submit();" id="char" >
> <option value="" selected>Misc. Actions</option>
> <option value="" >Show All Titles</option>
> <option value="recent" >Show Recently Added</option>
> <option value="title" >Sort By Movie Title </option>
> <option value="rating" >Sort Rating Column </option>
> <option value="cat" >Sort Genre Column </option>
>
> </select>
> </form></td>
> <td width="350" align="left">
> <form action='$_SERVE R[PHP_SELF]' method='get'>
> <input type='hidden' name='t' value=''>
> Search Titles &nb sp;<input type="text" name="search">
> <input type="submit" value="Search">
>
> </select>
> </form>
> </td>
> </tr>
> </table>
> </center>
> EOF;
>
> return;
> }
>
> function format_rating($ rating){
>
> if ($rating == "01") { $ratingf= "1"; }
> elseif ($rating == "02") { $ratingf= "2"; }
> elseif ($rating == "03") { $ratingf= "3"; }
> elseif ($rating == "04") { $ratingf= "4"; }
> elseif ($rating == "05") { $ratingf= "5"; }
> elseif ($rating == "06") { $ratingf= "6"; }
> elseif ($rating == "07") { $ratingf= "7"; }
> elseif ($rating == "08") { $ratingf= "8"; }
> elseif ($rating == "09") { $ratingf= "9"; }
> elseif ($rating == "10") { $ratingf= "10"; }
> return $ratingf;
> }
>
> function get_stars($rati ng){
> global $star, $hstar;
>
> if ($rating == 1) { $stars="<img src=$star>"; }
> elseif ($rating == 2) { $stars="<img src=$star><img src=$star>"; }
> elseif ($rating == 3) { $stars="<img src=$star><img src=$star><img
> src=$star>"; }
> elseif ($rating == 4) { $stars="<img src=$star><img src=$star><img
> src=$star><img src=$star>"; }
> elseif ($rating == '5') { $stars="<img src=$star><img src=$star><img
> src=$star><img src=$star><img src=$star>"; }
> elseif ($rating == '6') { $stars="<img src=$star><img src=$star><img
> src=$star><img src=$star><img src=$star><img src=$star>"; }
> elseif ($rating == '7') { $stars="<img src=$star><img src=$star><img
> src=$star><img src=$star><img src=$star><img src=$star><img
> c=$star>"; }
> elseif ($rating == '8') { $stars="<img src=$star><img src=$star><img
> src=$star><img src=$star><img src=$star><img src=$star><img src=$star><img
> src=$star>"; }
> elseif ($rating == '9') { $stars="<img src=$star><img src=$star><img
> src=$star><img src=$star><img src=$star><img src=$star><img src=$star><img
> src=$star><img src=$star>"; }
> elseif ($rating == '10') { $stars="<img src=$star><img src=$star><img
> src=$star><img src=$star><img src=$star><img src=$star><img src=$star><img
> src=$star><img src=$star><img src=$star>"; }
> return $stars;
> }
>
>
>
> ############### ############### ############### ############### ############### #####
>
>
> ?>
>
>
>[/color]

**Toby Inkster** · Mar 25 '06, 10:15 AM

Re: Database Retrival Problem

fiziwig wrote:
[color=blue]
> I think what you intended was $akey[]=$rs['title'][/color]

Yup, or perhaps:

$akey[]=htmlspecialcha rs($rs['title']);

:-)

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact

Database Retrival Problem

Database Retrival Problem

Comment

Comment

Comment