EternalSorrow Posted January 7, 2010 Share Posted January 7, 2010 I'm piecing together a code which involves an alphabetized list and the ability to sort the table rows by first letter and series. This page is accessed by a user clicking on a link with the corresponding first letter of the series, a field which is created using the SUBSTRING command in the query statement. The problem I have is I would like to use the first letter (in my code simply named 'letter') to sort the table using the WHERE clause. However, I receive an error message stating the following: Unknown column 'letter' in 'where clause' The above statement is true, as in my table there really isn't a field by the name of letter. However, I still need to sort the query by the first letter so when visitors click the link, they will be shown the page revealing all series beneath the relevant letter. Anyone have any idea how to make the 'imaginary' field $letter usable in a WHERE clause? Here's the code which produces the error effect: <?php if (!is_numeric($_GET["letter"]) && !empty($_GET["letter"]) && $_GET["letter"]!="") { $letter = $_GET["letter"]; } $sql = "SELECT *, UPPER(SUBSTRING(series,1,1)) AS letter FROM design WHERE letter = '$letter' ORDER BY series LIMIT 4"; $query = mysql_query ($sql) or die (mysql_error()); echo '<ul class="alphabet">'; while ($records = @mysql_fetch_array ($query)) { $alpha[$records['letter']] += 1; ${$records['letter']}[$records['series']] = $records['series']; } foreach(range('A','Z') as $i) { echo (array_key_exists ("$i", $alpha)) ? '<li><a href="series.php?letter='.$i.'">'.$i.'</a></li>' : '<li class="no_highlight">'.$i.'</li> '; echo ($i != 'Z') ? ' ':'</ul><h3>'.$letter.'</h3>'; } foreach(range('A','Z') as $i) { if (array_key_exists ("$i", $alpha)) { foreach ($$i as $key=>$value) { $select_design = mysql_query("SELECT * FROM design WHERE series = '$value' ORDER BY id ASC ") or die (mysql_error()); $cat = ""; while ($row2 = mysql_fetch_array($select_design)) { $cat .= "<li><a href=\"graphics/preview.php?id=$row2[id]\"><img src=\"previews/$row2[image]\"></a></li> "; } $value = '<a name="'.$i.'"></a><h3>'.$value.'</h3><ul>'.$cat.'</ul>'; echo ''.$value.''; } } } ?> Quote Link to comment Share on other sites More sharing options...
simshaun Posted January 7, 2010 Share Posted January 7, 2010 WHERE operates on fields that already exist. Any "temporary" fields must go in a HAVING clause. If you wanted to keep it in a where clause, you could do something along the lines of WHERE series LIKE '$letter%' Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted January 7, 2010 Author Share Posted January 7, 2010 The wild card WHERE option worked the best for the problem, as I'm not even sure how I could implement the HAVING clause in this example, but now I've run into another snag concerning the alphabetical listing versus the listing of relevant rows for the first letter. I would like the alphabet listing above where the retrieved rows, but because all letters are then listed and there is a wild card for the WHERE, more than just the single letter in the URL bar is retrieved. Is there any way for the second code (as I've split the alphabetical listing and letter query) to ignore the first snippet, or should I perhaps implement a (currently unknown) HAVING clause? Here's the modified code: <?php $sql = "SELECT *, UPPER(SUBSTRING(series,1,1)) AS letter FROM design ORDER BY series"; $query = mysql_query ($sql) or die (mysql_error()); echo '<ul class="alphabet">'; while ($records = @mysql_fetch_array ($query)) { $alpha[$records['letter']] += 1; ${$records['letter']}[$records['series']] = $records['series']; } foreach(range('A','Z') as $i) { echo (array_key_exists ("$i", $alpha)) ? '<li><a href="series.php?letter='.$i.'">'.$i.'</a></li>' : '<li class="no_highlight">'.$i.'</li> '; echo ($i != 'Z') ? ' ':'</ul><h3>'.$letter.'</h3>'; } ?> <?php if (!is_numeric($_GET["letter"]) && !empty($_GET["letter"]) && $_GET["letter"]!="") { $letter = $_GET["letter"]; } $sql = "SELECT *, UPPER(SUBSTRING(series,1,1)) AS letter FROM design WHERE series LIKE '$letter%' ORDER BY series LIMIT 1"; $query = mysql_query ($sql) or die (mysql_error()); while ($records = @mysql_fetch_array ($query)) { $alpha[$records['letter']] += 1; ${$records['letter']}[$records['series']] = $records['series']; } foreach(range('A','Z') as $i) { if (array_key_exists ("$i", $alpha)) { foreach ($$i as $key=>$value) { $select_design = mysql_query("SELECT * FROM design WHERE series = '$value' ORDER BY id ASC ") or die (mysql_error()); $cat = ""; while ($row2 = mysql_fetch_array($select_design)) { $cat .= "<li><a href=\"graphics/preview.php?id=$row2[id]\"><img src=\"previews/$row2[image]\"></a></li> "; } $value = '<a name="'.$i.'"></a><h3>'.$value.'</h3><ul>'.$cat.'</ul>'; echo ''.$value.''; } } } ?> Quote Link to comment Share on other sites More sharing options...
simshaun Posted January 7, 2010 Share Posted January 7, 2010 I'm not sure I understand what you are asking. Quote Link to comment Share on other sites More sharing options...
EternalSorrow Posted January 7, 2010 Author Share Posted January 7, 2010 I removed the alphabetical listing from the second query, which holds the WHERE statement, because it needs to parse through all results from the 'design' table. The problem I'm now having is that the result from first code, which is all the rows of the table, is being retrieved by the second code, so all rows are being retrieved and displayed in the second code, too. I need only the rows corresponding to the $letter to be shown. Removing the first code (the alphabetical listing) fixes the problem, but deletion isn't a good solution in this case. My question is: how can I get the second code to retrieve the correct $letter from the URL while also being able to display the alphabetical list at the top? Quote Link to comment Share on other sites More sharing options...
simshaun Posted January 8, 2010 Share Posted January 8, 2010 Hmm. Your code was rather confusing <?php // Display a range of letters. $sql = " SELECT * ,UPPER(SUBSTRING(series,1,1)) AS letter FROM design ORDER BY series "; $query = mysql_query($sql) or die(mysql_error()); echo '<ul class="alphabet">'; while ($records = mysql_fetch_assoc($query)) { $alpha[$records['letter']] += 1; ${$records['letter']}[$records['series']] = $records['series']; } foreach(range('A','Z') AS $i) { echo array_key_exists($i, $alpha) ? '<li><a href="series.php?letter='.$i.'">'.$i.'</a></li>' : '<li class="no_highlight">'.$i.'</li>'; echo $i != 'Z' ? ' ':'</ul>'; } // Display records of the letter selected. if (isset($_GET['letter']) && !empty($_GET['letter']) && !is_numeric($_GET['letter'])) { $letter = substr($_GET['letter'], 0, 1); } else { $letter = ''; } $sql = " SELECT * ,UPPER(SUBSTRING(series,1,1)) AS letter FROM design WHERE series LIKE '".mysql_real_escape_string($letter)."%' ORDER BY series LIMIT 1 "; $query = mysql_query($sql) or die (mysql_error()); while ($row = @mysql_fetch_assoc($query)) { $cat = ''; while ($row2 = mysql_fetch_array($select_design)) { $cat .= "<li><a href=\"graphics/preview.php?id=$row2[id]\"><img src=\"previews/$row2[image]\"></a></li>"; } $value = '<a name="'.$i.'"></a><h3>'.$value.'</h3><ul>'.$cat.'</ul>'; echo $value; } ?> See if that is what you want. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.