SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    problem with query

    i have this code for an array:
    Code:
    if (is_array($lid)) {                    
    		foreach ($lid as $var) {             
    			$pre = explode(", ",$var);       
    			$arr[] = $pre[0];
    		}
    		$lid = implode(',', $arr);         
    	} else {
    		$LotVal = $lid;                        
    	}
    
    $query = "SELECT m.id, DATE_FORMAT(m.mdate,'%m/%d/%Y'), TIME_FORMAT(m.mdate,'%r') as time, m.lid, m.item, m.description, m.status, DATE_FORMAT(m.date_complete,'%m/%d/%Y'), a.assign, c.cost, h.hour, s.serviceby FROM maint m left join assign a on m.aid=a.aid left join cost c on m.id=c.mid left join hours h on m.id=h.mid left join service s on m.sid=s.sid  WHERE m.mdate >= '$StartDate' AND m.mdate <= Date_add('$EndDate', interval 1 day) AND (m.item LIKE '%$search%' OR m.description LIKE '%$search%') AND m.lid IN (" .  $lid . ")";
    	$result = mysql_query($query) or die ("Could not run the query7: " . mysql_error());
    
    }
    in my form if i choose locations 333, 321, 432, except Other
    it will return the mysql results. if my query contains Other it will give me: Unknown column 'Other' in 'where clause'

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's because that where 333, 321 and 432 are integer, "Other" is a string.
    Mysql try to look for a field named 'Other' because in your statement
    Code:
    m.lid IN (" .  $lid . ")
    it waits for an integer.
    You can let myql do a implicit cast from varchar to int by including each values of $li between single quotes.
    That way, '1' will be casted to the integer 1, but 'Other' won't, and as it's between quotes, will not raise an error.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if i were to use a dropdown list and choose the value "other" then i would expect to get a response back which gave me other values, i.e. all the ones not listed in the dropdown

    obviously the m.lid value isn't going to be equal to "other" so nothing would get returned...

    ... which, in my opinion, is wrong

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i would need to return all values....it's a multi-select box so they could select an integer and the string "other".

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,332
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    so (and i'm not trying to be difficult here, but if i'm having trouble understanding this, then maybe your users will too) just what exactly does "other" equate to?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Sep 2004
    Location
    seattle
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i got it to work, i changed it to this:

    m.lid IN ('$lid') in my where clause and put ""
    if (is_array($lid)) {
    foreach ($lid as $var) {
    $pre = explode("','",$var);
    $arr[] = $pre[0];
    }
    $lid = implode("','", $arr);
    } else {
    $LotVal = $lid;
    }


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
  •