SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL OR, OR, OR, OR

    It seems like this could be bad with a lotta queries, LOL.
    Im wondering if there is a better way to do this or maybe a easier query to write without so many OR's..

    Heck any Ideas would be good, I just dont like the look of this and Im scaring myself, I think something is very wrong about this.

    PHP Code:
        public function ArmorStats()
        {


            
    $this->armorStr $this->crap('SUM(str)');
            
    $this->armorDex $this->crap('SUM(dex)');
            
    // Lot more of $this->Stuff....
            // Lot more of $this->Stuff....
            // Lot more of $this->Stuff....

            // testing it:
            // echo $this->armorStr;
            // echo $this->armorDex;
            
            
        
    }
        
        private function 
    crap($a)
        {
            
    $this->DB->query("SELECT $a FROM `items`
            WHERE 
            `id` = '
    {$this->weapon}'
            OR `id` = '
    {$this->head}'
            OR `id` = '
    {$this->chest}'
            OR `id` = '
    {$this->arms}'
            OR `id` = '
    {$this->hands}'
            OR `id` = '
    {$this->legs}'
            OR `id` = '
    {$this->feet}'
            OR `id` = '
    {$this->belt}'
            OR `id` = '
    {$this->ring}'
            LIMIT 9"
    );
            
            return 
    $this->DB->get($a);
        } 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    instead of ORs, use an IN list
    Code:
    WHERE id IN 
          ( {$this->weapon}
          , {$this->head}
          , {$this->chest}
          , {$this->arms}
          , {$this->hands}
          , {$this->legs}
          , {$this->feet}
          , {$this->belt}
          , {$this->ring} )
    ORDER
        BY something LIMIT 9
    note that LIMIT doesn't really make sense without ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I am checking my manual to see what this does.

    Does LIMIT make sense if you do an UPDATE?
    Code MySQL:
    UPDATE something SET something2 = 1 LIMIT 1?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by JREAM View Post
    Does LIMIT make sense if you do an UPDATE?
    not to me, but i guess there must be situations where it might be needed (otherwise the mysql engineers wouldn't have come up with it)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    John 8:24 JREAM's Avatar
    Join Date
    Sep 2007
    Location
    Florida
    Posts
    1,508
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fair enough :P
    It could be a protector if you forget the WHERE

    UPDATE users SET name = 'john' LIMIT 1

  6. #6
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,810
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by JREAM View Post
    It could be a protector if you forget the WHERE
    It would still update a record that you probably didn't want updated and if there are more records satisfy the condition than the limit you set you are not defining which of those records to update and which to not update.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


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
  •