SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2002
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face ORDER BY $sort problem, sorts all elements in db

    !!!!!!!!!This is my first ever post so please help!!!!!!!!!

    Hi,
    I have a problem with my results table for my vhs database. The database is modelled on Kevin Yanks joke db. I wish to have a link at the top of each table column where user can order table by the 4 fields (Title, Year, Director and Production Company). This works fine when all (7 at present) vhs in databse are selected in the search option. When vhs specific to a genre, production company or director are only selected, the reults page displays these specific vhs fine, but when I click on link for a field to sort, the sort results in the whole lot of vhs being sorted

    The code is as follows:
    $dbcnx = mysql_connect("localhost", "user", "pass");
    mysql_select_db("my_db");

    // The basic SELECT statement
    $select = "SELECT DISTINCT vhs1.ID, vhs1.TITLE, vhs1.YEAR, directors.NAME, prodComp.PCNAME";
    $from = " FROM vhs1, directors, prodLookup, prodComp";
    $where = " WHERE vhs1.ID > 0 AND vhs1.DIRID = directors.ID AND vhs1.DIRID = directors.ID AND vhs1.ID=prodLookup.VHSID AND prodComp.ID=prodLookup.PRODID";


    if (isset($title) && $title != "") { // a sort order was specified
    $where .= " AND vhs1.TITLE LIKE '%$title%'";// title was specified
    }

    if (isset($dirid) && $dirid != "") { // A director is selected
    $where .= " AND vhs1.DIRID=$dirid";
    }

    if (isset($gid) && $gid != "") { // A category is selected
    $from .= ", genreLookup";
    $where .= " AND vhs1.ID=genreLookup.VID AND GID=$gid";
    }

    if (isset($searchtext) && $searchtext != "") { // Some search text was specified
    $where .= " AND vhs1.plotKeyword LIKE '%$searchtext%'";
    }

    if ($sort != "") { // a sort order was specified
    $where .= " ORDER BY $sort";
    }else{

    $where .= ""; //if no order specified then remain unsorted
    }

    ?>

    <table width="100%" bgcolor="#FFFFFF" border="0" cellspacing="1" cellpadding="1">
    <tr valign="top" bgcolor="#009966">
    <th bgcolor="#009966" valign="top" align="left">
    <div class="search1"><font face="Arial" size="2" color="#FFFFFF"><a href="<?=$PHP_SELF?>?sort=TITLE">Title</a></font></div>
    </th>
    <th bgcolor="#009966" valign="top" align="left">
    <div class="search1"><font face="Arial" size="2" color="#FFFFFF"><a href="<?=$PHP_SELF?>?sort=YEAR">Year</a></font></div>
    </th>
    <th bgcolor="#009966" valign="top" align="left">
    <div class="search1"><font face="Arial" size="2" color="#FFFFFF"><a href="<?=$PHP_SELF?>?sort=NAME">Director</a></font></div>
    </th>
    <th bgcolor="#009966" valign="top" align="left">
    <div class="search1"><font face="Arial" size="2" color="#FFFFFF"><a href="<?=$PHP_SELF?>?sort=PCNAME">Production Company</a></font></div>
    </th>
    </tr>

    <?php

    $result = @mysql_query($select . $from . $where);
    echo("Query: $select $from $where");
    if (!$result) {
    echo("</table>");
    echo("<p>Error retrieving videos from database!<br />".
    "Error: " . mysql_error() . "</p>");
    exit();
    }


    //set alternate row colours
    $alternate = "2";
    while ($row = mysql_fetch_array($result)) {
    echo("<tr valign='top'>\n");
    $id = $row["ID"];
    $title = htmlspecialchars($row["TITLE"]);
    $year = htmlspecialchars($row["YEAR"]);
    $dname = htmlspecialchars($row["NAME"]);
    $prodComp = htmlspecialchars($row["PCNAME"]);
    if ($alternate == "1") {
    $color = "#F7F7EF";
    $alternate = "2";
    }
    else {
    $color = "#F0F0E8";
    $alternate = "1";
    }
    echo("<tr bgcolor=$color>");
    echo("<td>$title</td>\n");
    echo("<td>$year</td>\n");
    echo("<td>$dname</td>\n");
    echo("<td>$prodComp</td>\n");
    echo("</tr>\n");

    }


    echo "</table>";
    ?>

  2. #2
    SitePoint Enthusiast menkes's Avatar
    Join Date
    Jul 2002
    Location
    Channel Islands
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do you specify genre (or director, or whatever)? Is it through a form or an href?

    If it is from a form, then you are not retaining the value when the column is sorted. You must store the appropriate value...in your case you would have to pass those values in the URL (session or cookie would also work, but more complicated).

    If it is through an href (appended to the URL) then your href for the sort is incorrect...it would need to be like:

    <a href="<?=$PHP_SELF?>&sort=NAME">

    (note the ampersand)


    Hope this helps...

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2002
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    thank you so much for replying!

    I obtain the fields though a form on a php script called vhs.php The script that I displayed in my first post is the 'result of the search' vhslist.php. Here is some of vhs.php:

    <?php
    error_reporting(E_ALL);

    $dbcnx = mysql_connect("localhost", "sq3", "student01LA");
    mysql_select_db("multimedia_sq3");

    $directors = mysql_query("SELECT ID, NAME FROM directors");
    $genres = mysql_query("SELECT ID, GNAME FROM genreClass");
    ?>

    <form action="vhslist.php" method="post">
    <p>View videos satisfying the following criteriabr />
    Title: <input type="text" name="title" /><br />
    By Director:
    <select name="dirid" size="1">
    <option selected value="">Any Director</option>
    <?php
    while ($director = mysql_fetch_array($directors)) {
    $dirid = $director["ID"];
    $dname = htmlspecialchars($director["NAME"]);
    echo("<option value='$dirid'>$dname</option>\n");
    }
    ?>
    </select><br />
    By Genre Category:
    <select name="gid" size="1">
    <option selected value="">Any Genre Category</option>
    <?php
    while ($genre = mysql_fetch_array($genres)) {
    $gid = $genre["ID"];
    $gname = htmlspecialchars($genre["GNAME"]);
    echo("<option value='$gid'>$gname</option>\n");
    }
    ?>
    </select><br />
    Containing Plot Texttextarea name="searchtext" rows="3" cols="25" wrap>
    </textarea></p><br />
    <input type="submit" name="submit" value="Search" />
    </form>

    ....I'm not sure exactly what I've got to do. You said '....then you are not retaining the value when the column is sorted. You must store the appropriate value...in your case you would have to pass those values in the URL (session or cookie would also work, but more complicated).....' could you please give me a guide as what to do next.
    Thank you so so much.

  4. #4
    SitePoint Enthusiast menkes's Avatar
    Join Date
    Jul 2002
    Location
    Channel Islands
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, when the user selects the genre and/or the director they are taken to vhslist.php. You then have access to the values they selected through $_POST['gid'] and $_POST['dirid'] ---> no problem so far.

    When the user clicks to sort the column, vhslist.php will reload. A reload causes the $_POST array to reset, so you will lose the $_POST['gid'] and $_POST['dirid'] variables.

    The easiest option (done in vhslist.php) is to change the href values:

    PHP Code:
    <? echo "<a href='vhslist.php?sort=TITLE&gid=$gid&dirid=$dirid'>Title</a>";
    **Note that I changed the $PHP_SELF variable to a static. If you do not do this, you will get some UGLY URL's.

    Hope this helps...

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2002
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    problem solved!!

    Thank you so so much for your help and explanation of my problem!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •