Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Here's my issue. I have three tables I'd like to query - states, points, and state_points.

States contains stateid, and name. Points: pointid, pointlat, pointlng. State_points: Stateid, Pointid.

I need to build an array which displays the States, and then the corresponding points. To do this I figured the best way would be something like this tutorial: http://www.bitsofphp.com/avoid-executing-mysql-queries-within-loops/

Query all the states, make an array using the stateids as the arrayids.
Then select the lat, lng, and stateid from the points and state_points table. Add the points (lat and lng) to the array, using the stateid selected in the 2nd query as the array id

global $wpdb;
$states = $wpdb->query("SELECT * from wp_states");
echo $states;
while ($state = mysql_fetch_array($states)) {
    $arrayState[$state['stateid']]=$state;
}
$points = $wpdb->query("SELECT wp_state_points.statepointid, wp_state_points.stateid, wp_state_points.pointid, wp_points.pointid, wp_points.lat, wp_points.lng FROM wp_state_points, wp_points WHERE wp_state_points.pointid = wp_points.pointid");
while ($point = mysql_fetch_array($points)) {
    $arrayState[$point['stateid']]['points'][]=$point;
}                                                      
foreach ($arrayState as $astate) {                              
    echo $astate['name'];                                                       
    foreach ($astate['points'] as $apoint) {
        echo $apoint['lat'];                                                              
    }                                                           
}

Something like this however I receive "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in..."

I know that my query is returning data (at least the first one). I think that wordpress query simply doesn't support this.

I could use get_results to return an array. But to make this work the arrayids have to be the same as the stateids

What can I do to make this work?

Thanks

share|improve this question
2  
Do not use the mysql_ functions directly, continue using the wpdb global. codex.wordpress.org/Class_Reference/wpdb –  soulseekah Mar 30 '12 at 19:46

2 Answers 2

http://codex.wordpress.org/Class_Reference/wpdb

query returns the number of affected rows. Instead use get_results, which returns all the rows specified by the query. You can use the second parameter to specify how these results will be returned, either as objects or as an array etc

When dealing with sql, you should deal only with wpdb, there is very very rarely ever a reason to deal with the mysql_ prefixed php functions, as the WordPress' database API does all of that.

share|improve this answer

So the way to do it would be use get_results as an OBJECT_K with the stateid being the first column queried.

Then I would use a foreach on the points queried to add the points to the associated state?

What i'm trying to eventually achieve is a JSON file with the following structure:

[
{
    "stateid":"001",
    "name":"Alaska",
    "points":
        {
            "point":
                [
                    {"id":"00001", "lat":"40.4038","lng":"-30.35263"},
                    {"id":"00002", "lat":"40.4013","lng":"-30.31355"},
                    {"id":"00003", "lat":"40.4023","lng":"-30.35235"},
                ]
        }
    "stateid":"002",
    "name":"Alabama",
    "points":
        {
            "point":
                [
                    {"id":"00004", "lat":"41.4038","lng":"-31.35263"},
                    {"id":"00005", "lat":"42.4013","lng":"-32.31355"},
                    {"id":"00006", "lat":"43.4023","lng":"-33.35235"},
                ]
        }

    and so on for all of the states.

}]

Perhaps it's better to simply use one query, then all the points with have a state associated with them, but I only need to reference one to get the state name? I was trying to avoid that as it seems like uneccisary data transfer, but perhaps it's better than running 2 querys and editing the array?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.