CODEWITHSUNDEEP
mysqlvariables
anon
anon

Reputation:

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

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;

Upvotes: 0

Views: 54

Answers (3)

anon
anon

Reputation:

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

Upvotes: 0

chenyb999
chenyb999

Reputation: 139

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

select @_count;

for result

Upvotes: 0

ZZZZtop
ZZZZtop

Reputation: 457

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

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

Upvotes: 1

Related Questions