Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Who to execute this using DB::query() method
  • AudayashankaraAudayashankara
    Hi 

    I am trying to execute the below query using DB::query() method 

    (SELECT 
        id, category, question_image
    FROM
        `questions`
    WHERE
        `category` = 'COMPUTERS'
    ORDER BY rand()
    LIMIT 6) UNION ALL (SELECT 
        id, category, question_image
    FROM
        `questions`
    WHERE
        `category` = 'ELECTRICAL'
    ORDER BY rand()
    LIMIT 6) UNION ALL (SELECT 
        id, category, question_image
    FROM
        `questions`
    WHERE
        `category` = 'MATHEMATICS'
    ORDER BY rand()
    LIMIT 6) UNION ALL (SELECT 
        id, category, question_image
    FROM
        `questions`
    WHERE
        `category` = 'CHEMISTRY'
    ORDER BY rand()
    LIMIT 6) UNION ALL (SELECT 
        id, category, question_image
    FROM
        `questions`
    WHERE
        `category` = 'ELECTRONICS'
    ORDER BY rand()
    LIMIT 6) UNION ALL (SELECT 
        id, category, question_image
    FROM
        `questions`
    WHERE
        `category` = 'PHYSICS'
    ORDER BY rand()
    LIMIT 6) UNION ALL (SELECT 
        id, category, question_image
    FROM
        `questions`
    WHERE
        `category` = 'CIVIL'
    ORDER BY rand()
    LIMIT 6) UNION ALL (SELECT 
        id, category, question_image
    FROM
        `questions`
    WHERE
        `category` = 'MECHANICAL'
    ORDER BY rand()
    LIMIT 6)

    I am pasting the query above again by removing all the new lines and PHP code 

    $sql = '(SELECT id,category,question_image FROM `questions` WHERE `category` = 'COMPUTERS' ORDER BY rand() LIMIT 6 ) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = 'ELECTRICAL' ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = 'MATHEMATICS' ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = 'CHEMISTRY' ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = 'ELECTRONICS' ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = 'PHYSICS' ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = 'CIVIL' ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = 'MECHANICAL' ORDER BY rand() LIMIT 6)' ; 

    $questions = \DB::query($sql)->execute();

    print_r($questions->as_array()); 

    I am getting an error 

    But when I print 

    print_r($questions) 

    I am getting answer as 16 

    Where I am wrong why fuel framework is not consdering the above as a query 
  • HarroHarro
    I used this code to test your query here (using 1.8/develop of the framework core):
        public function action_union()
        {
            $cats = array('COMPUTERS', 'ELECTRICAL', 'MATHEMATICS', 'CHEMISTRY', 'ELECTRONICS', 'PHYSICS', 'CIVIL', 'MECHANICAL');

            // create test data
            DBUtil::truncate_table('questions');
            for ($i = 0; $i < 500; $i++)
            {
                // create test data
                $cat = $cats[rand(0,7)];
                $query = DB::insert('questions', array('category', 'question_image'))->values(array($cat, 'Question '.$i))->execute();
            }

            $sql = '(SELECT id,category,question_image FROM `questions` WHERE `category` = "COMPUTERS" ORDER BY rand() LIMIT 6 ) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = "ELECTRICAL" ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = "MATHEMATICS" ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = "CHEMISTRY" ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = "ELECTRONICS" ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = "PHYSICS" ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = "CIVIL" ORDER BY rand() LIMIT 6) UNION ALL (SELECT id,category,question_image FROM `questions` WHERE `category` = "MECHANICAL" ORDER BY rand() LIMIT 6)' ;

            $questions = \DB::query($sql)->execute();

            var_dump($questions->as_array());
        }
    and it works fine:

    array (size=48)
      0 => 
        array (size=3)
          'id' => string '108' (length=3)
          'category' => string 'COMPUTERS' (length=9)
          'question_image' => string 'Question 107' (length=12)
      1 => 
        array (size=3)
          'id' => string '86' (length=2)
          'category' => string 'COMPUTERS' (length=9)
          'question_image' => string 'Question 85' (length=11)
      2 => 
        array (size=3)
          'id' => string '488' (length=3)
          'category' => string 'COMPUTERS' (length=9)
          'question_image' => string 'Question 487' (length=12)
      3 => 
        array (size=3)
          'id' => string '500' (length=3)
          'category' => string 'COMPUTERS' (length=9)
          'question_image' => string 'Question 499' (length=12)
      4 => 
        array (size=3)
          'id' => string '176' (length=3)
          'category' => string 'COMPUTERS' (length=9)
          'question_image' => string 'Question 175' (length=12)
      5 => 
        array (size=3)
          'id' => string '341' (length=3)
          'category' => string 'COMPUTERS' (length=9)
          'question_image' => string 'Question 340' (length=12)
      6 => 
        array (size=3)
          'id' => string '219' (length=3)
          'category' => string 'ELECTRICAL' (length=10)
          'question_image' => string 'Question 218' (length=12)
      7 => 
        array (size=3)
          'id' => string '13' (length=2)
          'category' => string 'ELECTRICAL' (length=10)
          'question_image' => string 'Question 12' (length=11)
      8 => ...
  • HarroHarro
    p.s. print_r() works too, but the result is unreadable, so I used var_dump instead...
Add a Comment

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Categories

In this Discussion