Retrieve Value From MySQL Table and Save in Variable

Question

I have a table (wp_postmeta), which contains four columns (meta_id, post_id, meta_key, meta_value). meta_key contains a field called neighborhood_value and the values for neighborhood_value are stored under the meta_value column. How do I retrieve the contents of neighborhood_value from my table and use them as a variable? Any support is appreciated

Answer 1

Ok, as everything is meta, thecode will also be "meta" :)

UPDATE - now when I know what you use to connect to DB I can change my answer to be more specific.

global $wpdb;

$sql = "SELECT meta_key, meta_value FROM $wpdb->postmeta WHERE post_id=XXX";

$retMeta = array();
$results = $wpdb->get_results($sql);

foreach ($results as $resultRow)
{
    if (!isset($retMeta[$resultRow['meta_key']]))
    {
        $retMeta[$resultRow['meta_key']] = array();
    }
    $retMeta[$resultRow['meta_key']][] = $resultRow['meta_value'];
}

The resulting $retMeta will be two dimensional array, and then you will be able to access your neighbourhood value by using $retMeta['neighbourhood_value'][0] (of course first you have to check if this is set - isset($retMeta['neighbourhood_value'])).

UPDATE2

For script imporing data, it'll look like that:

$sql = "SELECT meta_key, meta_value FROM wp_postmeta WHERE post_id=XXX";

$retMeta = array();
$result = mysql_query($sql);

while ($resultRow= mysql_fetch_assoc($result))
{
    if (!isset($retMeta[$resultRow['meta_key']]))
    {
        $retMeta[$resultRow['meta_key']] = array();
    }
    $retMeta[$resultRow['meta_key']][] = $resultRow['meta_value'];
}
mysql_free_result($result);

But you should use WP_Query anyway, to maintain portability (the wp_postmeta doesn't have to be named that way, it can be wp2_postmeta etc).

Answer 2

If I understand correctly, you want to key a list of keys from meta_value?

I would do this in php: (assuming you are using some sort of framework that supports this construct. This example works with the codeigniter framework))

$sql = "SELECT distinct meta_value FROM wp_postmeta"
$result = $db->query($sql)
foreach ($result->reult() as $row) {
  $mydata[] = $row->meta_value;
}

$mydata is now array containing all of your meta_value values.

Answer 3

I was doing same as the following.

<?php foreach($values as $value)
{
    $myVarname[$value[valuefield]]=$value[valuefield];

}

$myVarname[field1]="Cow";
$myVarname[field2]="dog";
?>

Answer 4

If you're working with Wordpress template files such as loop.php etc., just use the handy get_post_meta() function.

Answer 5

You don't specify what language you want to use for your variables - but if it happens to be SQL in MySQL, here's how you "Retrieve Value From MySQL Table and Save in Variable":

select @var_name := column_name from table_name where other_column = 5;

This does the select, and as a side-effect assigns the value of the user variable @var_name to the value of column_name.

or

select @var_name := count(*) from other_table where other_column > 5;

which sets @var_name to the number of records that matched the condition.