The best solution would be to use the $wpdb functions instead. There may be a reason why you’re not using them, but you haven’t said…
If you really have to use the native MySQL functions, firstly, don’t use mysql_query(). You should be using mysqli_query(). The mysql_* functions have been depracated for a very long time, and are something that should never be used in production code these days.
The easy way to get around these errors is to set up the MySQL connection yourself manually, and run your queries against that connection. While that will work, it really is unnessacary overhead when everything can be done through $wpdb anyway.
I’m running the same code that has been working for about 5 years now – hence the deprecated stuff.
I don’t have any reason for using the MySQL functions (except they always worked in the past) and I’d like to bring my site up to date with the more secure $wpdb functions but I’m not entirely sure where I’m going wrong here.
I have this function:
function get_leagues( ) {
// Returns leagues information for network admin page
global $wpdb ;
$leagues_db_table = $wpdb->leagues ;
if ( is_wp_error( $leagues_db_table ) ) {
$leaguesQuery = $leagues_db_table->get_error_message() ;
} else {
$queryStr = "SELECT * FROM $leagues_db_table" ;
$leagues = $wpdb->get_results( $queryStr ) ;
if ( $leagues != null ) {
return $leagues ;
} else {
return new WP_Error( 'Leagues Database Error', __("Could not find any leagues entered .") ) ;
}
}
}
… which seems to work just fine.
Then I try to use the $leagues that is returned from it in this little snippet:
$leaguesQuery = get_leagues( ) ;
if ( is_wp_error( $leaguesQuery ) ) {
$error_string = $leaguesQuery->get_error_message();
echo '<div id="message" class="error"><p>' . $error_string . '</p></div>';
} else {
$leagues = array() ;
$leagueInfo = array() ;
$leagueNum = 0 ;
while ( $leagueData = mysql_fetch_array( $leaguesQuery ) ) {
++$leagueNum ;
$leagueInfo['blog_id'] = $leaguesQuery->blog_id ;
$leagueInfo['league_id'] = $leaguesQuery->league_id ;
$leagueInfo['league_name'] = $leaguesQuery->league_name ;
$leagueInfo['league_short_name'] = $leaguesQuery->league_short_name ;
$leagueInfo['league_db_prefix'] = $leaguesQuery->league_db_prefix ;
$leagueInfo['active'] = $leaguesQuery->active ;
$leagueInfo['leagues_db_version'] = $leaguesQuery->leagues_db_version ;
$leagues[$leagueNum] = $leagueInfo ;
unset ( $leagueInfo ) ;
}
}
… and get nothing!
I’m just a bit of a hacker trying to make my way through – there’s obviously something (obvious!) that I’m missing.
Any help would be appreciated.
Thanks
The first thing to do is check what your actual query is before you pass it to $wpdb. Something like this:
$queryStr = "SELECT * FROM $leagues_db_table" ;
echo "<p>".$queryStr."</p>";
Just an observation, and I coudl be wrong, but I didn’t think that the ~$wpdbobject formatted custom table names in the same way as the default WordPress ones, so when you try to call$wpdb->leagues` I’m thinking that it’s not actually returning anything. You might need to use it like this:
$leagues_db_table = $wpdb->prefix.'leagues' ;
Oh, and when you post code on here you should always use the ‘code’ button above, otherwise you can easily break the formatting and make it unreadable.
Good suggestion – but not where the problem is.
It was echoing the right query, so I took it a step further and did a foreach to echo the leagueID value – which it did. When I get to the next step where I’m populating my list table, that’s where I’m not getting any values.
$leaguesQuery = get_leagues( ) ;
foreach ( $leaguesQuery as $leagueInfo ) {
echo "<p>".$leagueInfo->league_id."</p>";
}
This works and echoes the info in the db. If I put the echo into the following snippet I get nothing!
if ( is_wp_error( $leaguesQuery ) ) {
$error_string = $leaguesQuery->get_error_message();
echo '<div id="message" class="error"><p>' . $error_string . '</p></div>';
} else {
$leagues = array() ;
$leagueInfo = array() ;
$leagueNum = 0 ;
while ( $leagueData = mysql_fetch_array( $leaguesQuery ) ) {
++$leagueNum ;
$leagueInfo['blog_id'] = $leaguesQuery->blog_id ;
$leagueInfo['league_id'] = $leaguesQuery->league_id ;
$leagueInfo['league_name'] = $leaguesQuery->league_name ;
$leagueInfo['league_short_name'] = $leaguesQuery->league_short_name ;
$leagueInfo['league_db_prefix'] = $leaguesQuery->league_db_prefix ;
$leagueInfo['active'] = $leaguesQuery->active ;
$leagueInfo['leagues_db_version'] = $leaguesQuery->leagues_db_version ;
$leagues[$leagueNum] = $leagueInfo ;
unset ( $leagueInfo ) ;
}
}
Instead of the While statement, I tried a foreach that came up with the same result. Obviously I’m not calling it right or something – but it worked just fine in September!
D’oh! I see above that I’m still working with $leaguesQuery – which I’ve already assigned to $leagueData.
I went back in and changed it and it still didn’t work – but once I put it into a foreach it started working.
Thanks for your help!
