WordPress.org

1. zilli
   Member
   Posted 7 months ago #

   Hi guys,

   I would like to gather the data that is stored in wp_postmeta table under meta_key column and put it in variable, so I can manipulate it later. But I don't hava idea how would be the SQL command for that.

   Please, a little help here.

2. Josh
   Member
   Posted 7 months ago #

   You could use something like this:

   <?php
   
       $conn = mysql_connect("localhost", $userName, $passWord);
       mysql_select_db($DB);
       $sql = mysql_query("SELECT * FROM tableName");
   
   ?>

   Then your data would be stored in the $sql variable. This code is a template and would need to be adjusted to suit your specific needs.

3. zilli
   Member
   Posted 7 months ago #

   Thanks Josh,

   I tried your template, but no lucky so far.

4. Josh
   Member
   Posted 7 months ago #

   How did you modify it?

   Post your code... but don't include any sensitive info like username or password. I'm interested in what you changed this to:

   $sql = mysql_query("SELECT * FROM tableName");

5. zilli
   Member
   Posted 7 months ago #

   Thanks for your help.

   "data7" is my meta_key that I want to access and get the data from it. I tried with below.

   $meta_key = 'data7';
   $my_data = $wpdb->get_var( $wpdb->prepare(
   	"
   		SELECT (meta_key)
   		FROM $wpdb->postmeta
   		WHERE meta_key = 'data7'
   	",
   	$meta_key
   ) );
   echo "<p>My data is {$my_data}</p>";

6. Josh
   Member
   Posted 7 months ago #

   How about this?

   $meta_key = 'data7';
   $my_data = $wpdb->get_var( $wpdb->prepare(
   	"
   		SELECT (meta_key)
   		FROM $wpdb->wp_postmeta
   		WHERE meta_key = 'data7'
   	",
   	$meta_key
   ) );
   echo "<p>My data is {$my_data}</p>";

7. zilli
   Member
   Posted 7 months ago #

   Okay, no error now. But the echo is not printing anything. It seems to be empty.

8. Josh
   Member
   Posted 7 months ago #

   Take the brackets out of your bottom line:

   $meta_key = 'data7';
   $my_data = $wpdb->get_var( $wpdb->prepare(
   	"
   		SELECT (meta_key)
   		FROM $wpdb->wp_postmeta
   		WHERE meta_key = 'data7'
   	",
   	$meta_key
   ) );
   echo "<p>My data is $my_data</p>";

9. zilli
   Member
   Posted 7 months ago #

   I'm put this code on my page.php

   <?php
   $meta_key = 'data7';
   $my_data = $wpdb->get_var( $wpdb->prepare(
   	"	SELECT (meta_key)
   		FROM $wpdb->wp_postmeta
   		WHERE meta_key = 'data7'
   	",
   	$meta_key
   ) );
   echo <p>My data is {$my_data}</p>;
   ?>

   If I take the brackets out, I get HTTP Error 500.

10. Josh
    Member
    Posted 7 months ago #

    What is it exactly you are trying to accomplish" Do you just want to pull one record with "data7" or all records with "data7"?

11. zilli
    Member
    Posted 7 months ago #

    Fair enough your question.

    I'm using Job Manager plugin, which store all the candidate info on wp_postmeta on meta_keys called data2, date3, date4, data5, data6 and data7. What I want to do, is to be able to extract those info from the database and show them like a list of candidates on any regular page.

12. Josh
    Member
    Posted 7 months ago #

    I'm using Job Manager plugin, which store all the candidate info on wp_postmeta on meta_keys called data2, date3, date4, data5, data6 and data7.

    Is it date1, date2, date3 or data1, data2, data3?

13. zilli
    Member
    Posted 7 months ago #

    Sorry, my typo. It's dataN

14. Josh
    Member
    Posted 7 months ago #

    Okay, try this:

    <?php
    // set the meta_key to the appropriate custom field meta key
    $meta_key = 'data7';
    $mydata = $wpdb->get_var( $wpdb->prepare(
    	"
    		SELECT sum(meta_value)
    		FROM $wpdb->wp_postmeta
    		WHERE meta_key = %s
    	",
    	$meta_key
    ) );
    echo "<p>My data is {$mydata}</p>";
    ?>

15. zilli
    Member
    Posted 7 months ago #

    I tried above. No error, but the variable still empty.

16. Josh
    Member
    Posted 7 months ago #

    Are you sure you have a table entry with "data7" in the wp-postmeta column??

17. zilli
    Member
    Posted 7 months ago #

    It's like this:

    database 'wordpress' ->
    table 'wp_postmate' ->
    column 'meta_key' with value 'data9'
    column 'meta_value' with value 'Brazil'

    So, the info that I want to get is on 'meta_value' column.

18. Josh
    Member
    Posted 7 months ago #

    Okay.

    But I'm suggesting the reason you are getting an empty return is because you don't have any database tables in your wp_postmeta table which matches the criteria we are searching for.

    First, before we proceed, make sure you have a database entry in the table wp_postmeta which has a column 'meta_key' value of "data7"... or "data9" using your example above.

19. zilli
    Member
    Posted 7 months ago #

    Double checked. I have all the entries.

20. zilli
    Member
    Posted 7 months ago #

    Hi Josh,

    I got it now :-)

    <?php
    $my_data = $wpdb->get_var( $wpdb->prepare(
    	"
    		SELECT (meta_value)
    		FROM $wpdb->postmeta
    		WHERE meta_key = 'data7'
    	"
    ) );
    echo "<p>My data is $my_data</p>";
    ?>

    My next step is to put this in a loop. I want to create a matrix 5x5. Anyway, thank you very much.

    Cheers,

Reply

You must log in to post.