work with mysql array data in PHP

Question

how to convert array data to work with it in this case? especially in wordpress. when i print the results i get stdClass Object.

Array
(
    [0] => stdClass Object
        (
            [posts_id] => 336
            [value] => 8
            [count(*)] => 2
        )

    [1] => stdClass Object
        (
            [posts_id] => 329
            [value] => 2
            [count(*)] => 1
        )

    [2] => stdClass Object
        (
            [posts_id] => 327
            [value] => 3
            [count(*)] => 1
        )

    [3] => stdClass Object
        (
            [posts_id] => 338
            [value] => 1
            [count(*)] => 1
        )

)

and this is my query

   <?php global $wpdb;

$test = $wpdb->get_results('select
    posts_id,
    value,
    count(*)
From
    wp_mrp_rating_item_entry_value
group by
    posts_id,
    value
order by
    count(*) desc'); 
echo '<pre>';
print_r($test);
echo '</pre>';

How can i get the data out of this array to save into another database table? Or how can i at least save the single values into variables for every id? I tried allot but i have no idea yet so i hope someone is knowing it better than me? thanks for any help.

Do you mean save data in other table ? INSERT maybe?

Raptor
– Raptor

2015-02-11 10:05:01 +00:00
Commented Feb 11, 2015 at 10:05 — Raptor
– Raptor, Commented Feb 11, 2015 at 10:05
@Raptor oh yepp i ment save. my fold. any idea?

jimmy
– jimmy

2015-02-11 10:11:42 +00:00
Commented Feb 11, 2015 at 10:11 — jimmy
– jimmy, Commented Feb 11, 2015 at 10:11

user3225501 · Accepted Answer · 2015-02-11 10:26:54Z

2

foreach($test as $item) :

$ID = $item->posts_id; 
var_dump($ID); //test variable


//if you want to place into the db 

   //DB CODE>>>

$firstName = "Dylan"  //example of field in db to change
$LastName =  "bloggs" //example of second field in db to change

$wpdb->query( $wpdb->prepare( 
	"
		INSERT INTO $wpdb->name_of_wp_table_goes_here
		( name_of_field_goes_here )
		VALUES ( %d, %s, $s ) 
	", 
    $ID,
    $field
) );

   //<<<DB END

endforeach;

Remove the DB code if you want to manually store into a database.

You could also access other keys the same way. e.g., $value = $item->value; -- you would place this in the foreach loop and continue.

edited Feb 11, 2015 at 10:26

answered Feb 11, 2015 at 10:20

user3225501

742 silver badges5 bronze badges

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

1 Comment

jimmy Over a year ago

thanks u so much. thats perfect. there are so many helpful and working answers here. yours is fitting exactly. made my day. thanks to you and the other guys. regards form Berlin

Bang · Accepted Answer · 2015-02-11 10:15:18Z

1

In Wordpress when you use get_results you can choose de hydration mode :

output_type One of three pre-defined constants. Defaults to OBJECT. OBJECT - result will be output as an object. ARRAY_A - result will be output as an associative array. ARRAY_N - result will be output as a numerically indexed array.

So if you do :

$test = $wpdb->get_results('select
posts_id,
value,
count(*)
From
wp_mrp_rating_item_entry_value
group by
posts_id,
value
order by
count(*) desc', ARRAY_N);

You should get an array as result.

answered Feb 11, 2015 at 10:15

Bang

9394 silver badges11 bronze badges

Comments

taliezin · Accepted Answer · 2015-02-11 10:18:54Z

1

As this is array of objects, you can use simple foreach to access your values:

<?php
    foreach ($test as $t) {
        $t->post_id;
        $t->value;
        // You can insert your values in different table here
    }
?>

For count you can change a bit your query to:

SELECT posts_id, value, COUNT(*) AS cnt FROM
    wp_mrp_rating_item_entry_value
GROUP BY
    posts_id,
    value
ORDER BY
    COUNT(*) DESC

so your count in foreach-loop will be accessible $t->cnt.

answered Feb 11, 2015 at 10:18

taliezin

9161 gold badge14 silver badges20 bronze badges

1 Comment

jimmy Over a year ago

have a last question: how would i do to get just the highest value of (cnt)? MAX() seems not to work on count(*) in the query, any idea?? thank u

RiggsFolly · Accepted Answer · 2015-02-11 10:21:35Z

1

Well you have an array of objects, which is a very nice contruct. All you need to do is process over the array.

I cannot help you with the WordPress mechanisms for a table insert but the pseudo code would be something like this.

First it would be a good idea to give the count a more usable name

$test = $wpdb->get_results('select
    posts_id, value,count(*) as how_many
From wp_mrp_rating_item_entry_value
group by
    posts_id, value
order by count(*) desc');

Now process each occurance of the array of objects

foreach ($test as $obj) {
    // Use these fields in an Insert statement
    $sql = "INSERT INTO TableName (f1,f2,f3)
            VALUES ({$obj->post_id}, 
                    {$obj->value}, 
                    {$obj->how_many}");
    // WP command to issue a query
}

answered Feb 11, 2015 at 10:21

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

4 Comments

jimmy Over a year ago

thanks mate this is working great for my purpose with a wp modification.

jimmy Over a year ago

im using it like that now foreach ($test as $obj) { $wpdb->insert( 'Toplist', array( 'postid' => $obj->posts_id, 'category' => $obj->value, 'votingcount' => $obj->how_many ), array( '%d', '%s', '%s' ) ); thanks again u re awesome.

jimmy Over a year ago

have a last question: how would i do to get just the highest value of (how_many)? MAX() seems not to work on count(*) in the query, any idea??

RiggsFolly Over a year ago

JImmy, you should really ask another question if you have another question.

Vineet · Accepted Answer · 2015-02-11 10:30:58Z

1

//Just use type cast brother. Use

$test = (array) $test;

//print_r($test);

foreach($test as $item) :

//if you want to place into the db 
$wpdb->query( $wpdb->prepare( 
    "INSERT INTO $wpdb->name_of_wp_table_goes_here
        ( post_id,value,count )
        VALUES (".$item['post_id'].", '".$item['value']."', ".$item['count'].")
    %d",  
    1
) );

endforeach;

edited Feb 11, 2015 at 10:30

answered Feb 11, 2015 at 10:07

Vineet

4,7153 gold badges25 silver badges42 bronze badges

3 Comments

Raptor Over a year ago

OP wants to save the data to another table.

jimmy Over a year ago

thanks mate.now i get the whole array but how would i use the single values of the array (like $ID = .... ; $value=....; $count =...; now to work with it.i need to update(or save it into) another table. i need this for every id. so i guess its have to be within a loop. !? thanks

jimmy Over a year ago

have a last question: how would i do to get just the highest value of (count)? MAX() seems not to work on count(*) in the query, any idea??

Collectives™ on Stack Overflow

work with mysql array data in PHP

5 Answers 5

1 Comment

Comments

1 Comment

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

1 Comment

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related