Using a query to determine the value of a variable (MySQL)

Question

I am trying to get the value of a count into into a variable to be used with another query.

BUT it does not work, it says:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(columnname) FROM field WHERE tabid = 4' at line 1

The query I am using is:

SET @_count = SELECT COUNT(columnname) FROM field WHERE tabid = 4;

Why not use whatever server-side language you're using and fetch the query and store it in a variable for the language for example PHP. Then complete another sql query after you get the result of the first? — ZZZZtop, Feb 26 '16 at 13:55
@ZacharyGover, it is required to be MySQL side on the server, not script side - the point is to be doing this on the MySQL server... — Can O' Spam, Feb 26 '16 at 13:55
Okay then take a look at this question, it explains it in great detail: http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference — ZZZZtop, Feb 26 '16 at 13:57
@ZacharyGover, it doesn't go into what I need, the query parsing the return value to the variable — Can O' Spam, Feb 26 '16 at 13:58

score 1 · Accepted Answer · answered Feb 26 '16 at 14:00

1

You're going to need to wrap the SQL Select Query in parenthesis.

SET @_count = (SELECT COUNT(columnname) FROM field WHERE tabid = 4);

answered Feb 26 '16 at 14:00

ZZZZtop

457
1
5
19

score 0 · Answer 2 · answered Feb 26 '16 at 14:02

0

SELECT COUNT(*) into @_count from  field WHERE tabid = 4; 

select @_count;

for result

answered Feb 26 '16 at 14:02

chenyb999

139
4

score 0 · Answer 3 · answered Feb 26 '16 at 16:22

Did it, this is how:

    /**
     * Query to find and select all the fields with a specific name on any tab id
     */

    -- Prepare the variables for the query
    SET @_tabid = 4; -- Replace with your tab id
    SET @_lowerletter = "%%"; -- Replace with your lower case search
    SET @_upperletter = "%%"; -- Replace with your upper case search
    SET @_upperlimit = (SELECT COUNT(columnname) FROM field WHERE tabid = @_tabid);
    SET @_lowerlimit = 0; -- Set your lower limit
    SET @_query = "SELECT * FROM field 
                                 WHERE tabid = ?
                                 AND (
                                    (columnname LIKE ? OR columnname LIKE ?) OR 
                                    (fieldname LIKE ? OR fieldname LIKE ?) OR 
                                    (fieldlabel LIKE ? OR fieldlabel LIKE ?)
                                 )
                                 ORDER BY tabid, fieldid ASC
                                 LIMIT ?, ?;";
    -- //Prepare

    -- Prepare and execute query
    PREPARE stmt FROM @_query;
            EXECUTE stmt USING 
                @_tabid,
                @_lowerletter, @_upperletter, -- Upper and lower search terms 
                @_lowerletter, @_upperletter, -- Upper and lower search terms 
                @_lowerletter, @_upperletter, -- Upper and lower search terms 
                @_lowerlimit, @_upperlimit; -- Upper and lower limits 
    DEALLOCATE PREPARE stmt;
    -- // Execute

Using a query to determine the value of a variable (MySQL)

3 Answers3