Storing mysql row data into php variable name and value

Question

I need help with storing values from a sql query into variables. The problem is in order to get the appropriate values I jad to write a long UNION query. There are only two columns, and not many results. Ideally, I want to create a variable for each row where the variable name is the result from the first column and the corresponding value for that variable would be the result from the second column.

Here is the UNION query:

SELECT 'mon_m' as shift, count(mon_m) as count
FROM schedule
WHERE week_of = '2013-12-23'
AND mon_m <> ''

UNION

SELECT 'mon_a', COUNT(mon_a) 
FROM schedule
WHERE week_of = '2013-12-23'
AND mon_a <> ''

UNION

SELECT 'mon_n', COUNT(mon_n)
FROM schedule
WHERE week_of = '2013-12-23'
AND mon_n <> ''

UNION

SELECT 'tue_m', count(tue_m) 
FROM schedule
WHERE week_of = '2013-12-23'
AND tue_m <> ''

UNION

SELECT 'tue_a', COUNT(tue_a) 
FROM schedule
WHERE week_of = '2013-12-23'
AND tue_a <> ''

UNION

SELECT 'tue_n', COUNT(tue_n)
FROM schedule
WHERE week_of = '2013-12-23'
AND tue_n <> ''

UNION

SELECT 'wed_m', count(wed_m) 
FROM schedule
WHERE week_of = '2013-12-23'
AND wed_m <> ''

UNION

SELECT 'wed_a', COUNT(wed_a) 
FROM schedule
WHERE week_of = '2013-12-23'
AND wed_a <> ''

UNION

SELECT 'wed_n', COUNT(wed_n)
FROM schedule
WHERE week_of = '2013-12-23'
AND wed_n <> ''

UNION

SELECT 'thu_m', count(thu_m) 
FROM schedule
WHERE week_of = '2013-12-23'
AND thu_m <> ''

UNION

SELECT 'thu_a', COUNT(thu_a) 
FROM schedule
WHERE week_of = '2013-12-23'
AND thu_a <> ''

UNION

SELECT 'thu_n', COUNT(thu_n)
FROM schedule
WHERE week_of = '2013-12-23'
AND thu_n <> ''

UNION

SELECT 'fri_m', count(fri_m) 
FROM schedule
WHERE week_of = '2013-12-23'
AND fri_m <> ''

UNION

SELECT 'fri_a', COUNT(fri_a) 
FROM schedule
WHERE week_of = '2013-12-23'
AND fri_a <> ''

UNION

SELECT 'fri_n', COUNT(fri_n)
FROM schedule
WHERE week_of = '2013-12-23'
AND fri_n <> ''

UNION

SELECT 'sat', count(sat) 
FROM schedule
WHERE week_of = '2013-12-23'
AND sat <> ''

UNION

SELECT 'sun', COUNT(sun) 
FROM schedule
WHERE week_of = '2013-12-23'
AND sun <> ''

Here is a small sample of what the data would like:

ColumnA    ColumnB
mon_m      1
mon_a      5
mon_n      6
tue_m      3
tue_a      6
tue_n      2

I need to access these values using php so I can build a table. I'd like to access them using variables. For example:

echo $mon_m
echo $mon_a
echo $mon_n

Where

$mon_m = 1
$mon_a = 5
$mon_n = 6

I have the SQL statement ready to go and need some help with getting these values into php variables.

You should edit the question and add the query to it, so that people can vote for the question to be re-opened. — Alex W
– Alex W, Commented Dec 17, 2013 at 19:58
yep, just figured that out. I didn't realize my last comment would post as soon as I hit enter. Thanks! — PatC
– PatC, Commented Dec 17, 2013 at 20:02

ollieread · Accepted Answer · 2013-12-17 19:30:22Z

2

Once you got the result of the mysql query, you can run it through extract

extract($results);

This will create a variable for each key in the array, so make sure your result is returned as an associative array. See http://www.php.net/manual/en/function.extract.php for more details.

answered Dec 17, 2013 at 19:30

ollieread

6,3211 gold badge22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Calvin K · Accepted Answer · 2013-12-17 21:49:47Z

1

Why not use an array?

$output_array = array(
    "mon_m" => 1,
    "mon_a" => 2,
    "mon_n" => 3
);

Then output them like this

echo $output_array['mon_m'];

And, your results can be even easier to get with mysql in this situation.

$r = mysql_query("select * from table where columnB=" . $var);
while($row = mysql_fetch_assoc($r)){
    $column = $row['columnA'];
    $output_array[$column] = $row['columnB'];
}

Then you can do all kinds of stuff with it, loop through and output a table, check for values, keys, etc. With what you have proposed, I don't think it will be very easy to keep track of the vars created, or very easy to work with them in general.

EDIT::

And here is a better(?) way to do it. Probably exceptionally over-complicated for your needs, but with the comments suggesting you use PDO...

function getObject($sql){
    $dbCon = new object();
        // building data source name 
        $dsn = 'mysql:host=' . DB_HOST .
               ';dbname='    . DB_NAME .
               ';connect_timeout=15';

        $dbCon->dbh = new PDO($dsn, DB_USER, DB_PASS);

    try {

        $stmt = $dbCon->dbh->prepare($sql);

        $stmt->setFetchMode(PDO::FETCH_OBJ);

        if($stmt->execute()){
                return (array) $stmt->fetchObject();
        } else {
            return false;
        }
    }

    catch(PDOException $e) {  
        $_messages[] = $e->getMessage();  
    }
}

And use it like this

$my_array = getObject($your_query);

if(!empty($my_array['mon_m'])){
    echo $my_array['mon_m'];
}

edited Dec 17, 2013 at 21:49

answered Dec 17, 2013 at 19:39

Calvin K

3962 silver badges6 bronze badges

5 Comments

TheCarver Over a year ago

Down-voted because you are answering using PHP's mysql_* functions that are now deprecated and will be removed from PHP versions very soon. Use MySQLi or PDO instead.

Calvin K Over a year ago

Added a different answer, but to me this seems exceptionally complicated for the exact question he asked. Had he expounded on what he needed, it appears we would have been right in our assumption anyway. I would think that your not answering, simply stating how wrong everyone else is, is less constructive than the answers we gave.

TheCarver Over a year ago

No, my constructiveness is to try and prevent new programmers finding this question on Google, and thinking the methods used here are acceptable. If their server administrator decides to update PHP soon, their website will collapse. I answered a question only today where PHP had been updated and their project failed because of the mysql_* functions (check my answers). I'm sorry you couldn't see this as constructive and you couldn't be civil about it. Anyway, I've noticed your edit so I've up-voted again.

Calvin K Over a year ago

I certainly see your point, best practice is always best (pun intended), even though his question wasn't about best practice. So, to me, your answer was constructive in that I won't likely answer another question with mysql_* functions. To the OP, you just didn't solve his problem or offer solutions so it wasn't constructive. And I wasn't intending to be uncivil. I was trying to goad you into providing an answer yourself :)

Calvin K Over a year ago

And thank you for giving a reason for a downvote rather than just leaving it at that. Too often negative feedback isn't explained adequately.

Collectives™ on Stack Overflow

Storing mysql row data into php variable name and value

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related