Help with MySQL to $WPDB query

2 Views

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.

Read More

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

Post Views: 2

Related posts

Leave a Reply

2 comments

  1. 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.

  2. 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?