Help with MySQL to $WPDB query

Question

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

Do not use the mysql_ functions directly, continue using the wpdb global. codex.wordpress.org/Class_Reference/wpdb — soulseekah
– soulseekah, Commented Mar 30, 2012 at 19:46

Tom J Nowell · Accepted Answer · 2014-03-06 10:28:52Z

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.

edited Mar 6, 2014 at 10:28

answered Mar 30, 2012 at 19:47

Tom J Nowell♦

61.6k7 gold badges81 silver badges150 bronze badges

Add a comment |

Jared · Accepted Answer · 2012-03-31 17:28:41Z

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?

Stack Exchange Network

Help with MySQL to $WPDB query

2 Answers 2

Your Answer

Hot Network Questions

Help with MySQL to $WPDB query

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions