how to set an array as a mysql user variable?

[Ashkan_1]

I didn't expect to find this so difficult, but I'm trying to set a user variable in MySQL to contain an array of values. I have no clue how to do this so tried doing some research and was quite suprised to find no answer.

[mmcdonald]

Here's a mock-up function:

  $config = array("host"=>"localhost",
                  "user"=>"root",
                  "pass"=>"pass",
                  "db"=>"database");

    function userInfo(){

        //Global config to call on the database connection values.
        global $config;

        //Global $userInfo so we can use it outside of the function.
        global $userInfo; 

        //Connect to database
        $mysqli = new mysqli($config['host'], $config['user'], $config['pass'], $config['db']);

        //Construct query as a string
        $query = "SELECT * FROM users WHERE id = '50'";

        //Run the query
        $result = $mysqli->query($query);

        //Construct an array based on the results of our query
        $userInfo = $result->fetch_array(MYSQLI_ASSOC);

        //Free up resources by freeing the $result variable
        $result->free();

        //Keep it tidy and close the connection to MySQL
        $mysqli->close();
    }

(Remember to add error checking and such!)

Then just call userInfo(); at the top of your file, or in your config file, and then you can print any table values using column names for the user with the id of 50 using: $userInfo['firstname'] or $userInfo['email'] and so forth.

To get data for the current user simply change '50' in the string above to the session value for that user and so forth... that should be self explanatory :)

Also you should notice that I'm constructing an associative array using fetch_array(MYSQLI_ASSOC) which means that variable names stored in the array are identical to the column the value has been collected from. You can also use fetch_array(MYSQLI_NUM) which would make your variable names relative to the column number the value was collected from and the variable would become $userInfo['0'] or $userInfo['9']. You also have the option of fetch_array(MYSQLI_BOTH) which does what it says on the tin, you can use both assoc and num for calling on array values.

I had to rush this (sory) but I hope it helps!

Michael

[Atli]

Assuming we are talking about User Defined Variables inside a MySQL procedure, as opposed to application variables that use MySQL as as the data source, then what you are attempting is not possible. MySQL doesn't include the concept of arrays. It only has numeric, date-time and string variables.

If you need to store a list of values inside a MySQL proceedure, you can use a Temporary Table. Those will only exist for the current session, and will be automatically dropped once you close the connection. Just be careful not to overuse them; there can be performance issues involved in using such tables.