Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,088
    Thanks
    296
    Thanked 12 Times in 12 Posts

    mysql between help please

    Hi All,

    i have this query
    PHP Code:
    $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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Quote Originally Posted by LJackson View Post
    Hi All,

    i have this query
    PHP Code:
    $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:
    Code:
    $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";
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,088
    Thanks
    296
    Thanked 12 Times in 12 Posts
    ah ok,

    so i changed my query like above withn my function
    PHP Code:
            <?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']]) ? $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
    Code:
    Warning: array_key_exists() expects parameter 2 to be array, null given on line 548
    any ideas?
    thanks

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,088
    Thanks
    296
    Thanked 12 Times in 12 Posts
    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 was missing the mysql_query before the sql statment,
    thanks very much for your help
    Last edited by LJackson; 05-04-2011 at 11:16 PM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    I always create the SQL statement as a separate variable.

    So that it's easy to add in a line such as :
    Code:
    echo "DEBUG SQL: " . $sql . "<hr>\n";
    for debugging purposes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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