Outputting query results on the right place

Question

I have set up the following query based on this question:

"(SELECT users_id, first_name, last_name, 'msg' as type FROM users 
 WHERE users_id LIKE '%" . $searchQuery ."%' 
 OR first_name LIKE '%" . $searchQuery ."%' 
 OR last_name LIKE '%" . $searchQuery ."%') 
 UNION
 (SELECT title, vimeo_id, upload_date, 'topic' as type FROM video 
 WHERE title LIKE '%" . $searchQuery ."%' 
 OR vimeo_id LIKE '%" . $searchQuery ."%')"

After the query is executed it should spit out the results like this:

foreach($row as $field) {
    $details[] = array(
        // Fields uit de users table
        'user_id'       =>  $field['users_id'],
        'first_name'    =>  $field['first_name'],
        'last_name'     =>  $field['last_name'],
        //Fields uit de video table
        'vimeo_id'      =>  $field['vimeo_id'],
        'video_title'   =>  $field['title'],
        'upload_date'   =>  $field['upload_date']
    );
}

It works but I still encounter a problem. When I run the script with a searchterm that should be within the 'user' table there is no problem. When I run the script with a searchterm that should be within the 'video' table I still get results but it moves the results to the first part of the loop. That means I get the following output:

[{"user_id":"Test","first_name":"151125560","last_name":"2016-01-08 10:49:32","vimeo_id":null,"video_title":null,"upload_date":null}]

While the video id should be 151125560, the video title should be test and the upload_date should be 2016-01-08.

I tried to solve this by adding as type but I have no clue on how to proceed from here. Could somebody please help me by moving the results to the right field?

For me this query is weird as the 2 select queries don't return the **same** **columns** at this should be done with UNION. Maybe you should try a **JOIN** of the two tables instead to have the informations on the same row. — Orden, Jan 13 '16 at 18:15

score 1 · Accepted Answer · answered Jan 13 '16 at 18:07

1

Your UNION query:

"(SELECT users_id, first_name, last_name, 'msg' as type FROM users 
 WHERE users_id LIKE '%" . $searchQuery ."%' 
 OR first_name LIKE '%" . $searchQuery ."%' 
 OR last_name LIKE '%" . $searchQuery ."%') 
 UNION
 (SELECT title, vimeo_id, upload_date, 'topic' as type FROM video 
 WHERE title LIKE '%" . $searchQuery ."%' 
 OR vimeo_id LIKE '%" . $searchQuery ."%')"

is going to spit out four fields (users_id,first_name, and last_name and type). It will not spit out a field called title or vimeo_id, upload because those field names are in the second query.

Basically in a UNION we are saying the result sets of the two select statements are the same field-wise. And it always uses the field names specified in the first SELECT statement.

So you should instead do:

foreach($row as $field) {
    $details[] = array(
        // Fields uit de users table
        'user_id'       =>  $field['users_id'],
        'first_name'    =>  $field['first_name'],
        'last_name'     =>  $field['last_name'],
     );
}

And just expect the results to always be in those fields (since they are).

Instead, you could do something like:

foreach($row as $field) {
    if $field['type'] == 'topic'
    {
        $details[] = array(
            // Fields uit de users table
            'user_id'       =>  $field['users_id'],
            'first_name'    =>  $field['first_name'],
            'last_name'     =>  $field['last_name'],
            //Fields uit de video table
            'vimeo_id'      =>  '',
            'video_title'   =>  '',
            'upload_date'   =>  ''
        );

    } else {
        $details[] = array(
            // Fields uit de users table
            'user_id'       =>  '',
            'first_name'    =>  '',
            'last_name'     =>  '',
            //Fields uit de video table
            'vimeo_id'      =>  $field['users_id'],
            'video_title'   =>  $field['first_name'],
            'upload_date'   =>  $field['last_name']
        );
    }

}

Forgive me if my php syntax is off. It's been a year or two and I'm rusty.

answered Jan 13 '16 at 18:07

JNevill

46,980
4
38
63

unbelievable it was actually that easy.. Thanks a lot! – Frank W. Jan 13 '16 at 18:13
1

When in doubt, run your query through phpmyadmin or whatever mysql client you are partial too. Seeing an actual resultset will help out tremendously when dealing with more complex stuff in the DB. – JNevill Jan 13 '16 at 18:15
1

Well, thats what i did, and that made my figure out how to set up the query. But i didn't know how i could use type like you showed! Thanks! – Frank W. Jan 13 '16 at 18:16
Just noticed there still is a small issue. In the else statement you inserted the wrong fields. Could you explain to me why i can only pick up the value with $field['users_id'] instead of $fields['vimeo_id']? – Frank W. Jan 13 '16 at 18:22
1

Because in the result set of your UNION query there are only 4 fields. Those 4 fields are given their name by the first query in the UNION. So you'll find the value for `vimeo_id` that you selected from the `video` table in Result Set's field `users_id`. In essence, a union query for your scenario is somewhat inappropriate, BUT! It's a good solution for your needs, where you want to search two unrelated tables with a single query. Perhaps, for clarity, change your first query to something like `SELECT users_id AS uid_OR_vid, first_name as fn_OR_vidTitle, etc..` so the result set makes sense. – JNevill Jan 13 '16 at 18:27
Just checked it out and works great, isn't union the best way to go for making a search? – Frank W. Jan 13 '16 at 21:14
Yea. For your case, it's the best way to go. Otherwise you would have to submit multiple queries and then stumble around with multiple result sets and yuck. It's just confusing because you get this result set object back with natural language field names that don't necessarily represent the data in those fields. But... you ignore the field names (or name them generically) and pray the poor fool that comes on to the team in a year or two is able to figure out whats what. ;) – JNevill Jan 13 '16 at 21:31
Haha, as i probably would been that poor fool that had to figure it out in two years (as this is my project) i invested a little extra effort in getting the fields names right. Thanks again. – Frank W. Jan 13 '16 at 21:38

Outputting query results on the right place

1 Answers1