...

View Full Version : mysql between help please



LJackson
05-04-2011, 11:24 PM
Hi All,

i have this query

$getcodes = mysql_query("SELECT UPPER(SUBSTRING(stores.storeName,1,1)) AS letter, storeID, storeName, rating FROM stores WHERE letter BETWEEN '$from' AND '$to' ORDER BY storeName ASC")or die(mysql_error());


but it returns:
Unknown column 'letter' in 'where clause'

now there isnt a letter column in my db but can i not use letter which i defined in my query?

many thanks
Luke

Old Pedant
05-04-2011, 11:35 PM
Hi All,

i have this query

$getcodes = mysql_query("SELECT UPPER(SUBSTRING(stores.storeName,1,1)) AS letter, storeID, storeName, rating FROM stores WHERE letter BETWEEN '$from' AND '$to' ORDER BY storeName ASC")or die(mysql_error());


but it returns:
Unknown column 'letter' in 'where clause'

now there isnt a letter column in my db but can i not use letter which i defined in my query?


No.

Aliases defined in the SELECT are only available to the ORDER BY clause (and maybe the HAVING clause).

YOu have to repeat the expression:


$sql = "SELECT UPPER(LEFT(storeName,1)) AS letter, storeID, storeName, rating "
. " FROM stores "
. " WHERE UPPER(LEFT(storeName,1)) BETWEEN '$from' AND '$to' "
. " ORDER BY storeName ASC";

LJackson
05-04-2011, 11:44 PM
ah ok,

so i changed my query like above withn my function

<?php
#output_codes('0','9',$alpha);
output_codes('A','C',$alpha);
output_codes('D','J',$alpha);
output_codes('K','Q',$alpha);
output_codes('R','Z',$alpha);

function output_codes($from,$to,$alpha)
{
$sql = "SELECT UPPER(LEFT(storeName,1)) AS letter, storeID, storeName, rating "
. " FROM stores "
. " WHERE UPPER(LEFT(storeName,1)) BETWEEN '$from' AND '$to' "
. " ORDER BY storeName ASC";
while ($records = @mysql_fetch_array ($sql))
{
$alpha[$records['letter']] = !isset($alpha[$records['letter']]) ? 1 : $alpha[$records['letter']] + 1;
${$records['letter']}[$records['storeID']] = $records['storeName'];
}
echo "<ul>";
// Create Data Listing
foreach(range($from,$to) as $i)
{
if (array_key_exists ("$i", $alpha))
{
foreach ($$i as $key=>$value)
{
$checkcodes = mysql_query("SELECT codeID,code,codeLink,storeID,details,expireDate
FROM tbl_codes
WHERE storeID = '$key'
ORDER BY expireDate ASC")or die(mysql_error);
$rows = mysql_num_rows($checkcodes);
if($rows >=1)
{
echo "<li>";
$code_count = 1;
//GET STORE LINK
$sql = mysql_query("SELECT * FROM stores WHERE name = '$value'")or die(mysql_error());
$row = mysql_fetch_array($sql);
$link = htmlentities($row['storeLink']);

echo "<b>".htmlspecialchars($value)?></b><br /><?php
while ($row = mysql_fetch_array($checkcodes))
{
$codeLink = htmlspecialchars($row['codeLink']);?>
<a href="<?php echo $codeLink?>" title="<?php echo $row['details']?>"><?php echo $row['code']?></a><?php
if($code_count <> $rows)
{
echo ", ";
}
$code_count++;
}
echo "</li>";
}
}
}
}
echo "</ul>";
}?>


and its now saying

Warning: array_key_exists() expects parameter 2 to be array, null given on line 548

any ideas?
thanks

LJackson
05-05-2011, 12:13 AM
The query is correct because i just tested it in my dbadmin and it returns the expected results.

not sure why it doesnt like the rest?

ok sorted it :D was missing the mysql_query before the sql statment,
thanks very much for your help

Old Pedant
05-05-2011, 12:25 AM
I always create the SQL statement as a separate variable.

So that it's easy to add in a line such as :


echo "DEBUG SQL: " . $sql . "<hr>\n";

for debugging purposes.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum