SUBSTRING Field - treat as 'real' field?

[EternalSorrow]

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.'';

}
}
}
?>

[simshaun]

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%'

[EternalSorrow]

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.'';

}
}
}
?>

[simshaun]

I'm not sure I understand what you are asking.

[EternalSorrow]

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?

 

[simshaun]

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.