Couldn't fetch mysqli_result

[rubing]

This is driving me crazy.  I have a query that works fine when I test it using phpmyadmin.  However, when I try to execute it in my php script I receive the following error: 

 

Warning: mysqli_result::fetch_assoc() [function.mysqli-result-fetch-assoc]: Couldn't fetch mysqli_result in /home/public_html/music_serve1.php on line 100

 

Here is the query:

 

 SELECT ad.ad_id, ad.owner_api, ad.file, ad.plays_purchased, ad.times_played, ad_limit.advert_id, ad_log.sess_id, ad_log.date_played, ad_log.ad_played, plays_per_session, COUNT( ad_log.ad_played ) AS num_times_played
FROM ad
LEFT JOIN ad_limit ON ad.ad_id = ad_limit.advert_id
LEFT JOIN ad_log ON ( ad_log.ad_played = ad.ad_id
AND ad_log.sess_id = '927fd56a2dbf9aecd4162711bc159284' )
WHERE ad.owner_api =1
AND ad.plays_purchased > ad.times_played
GROUP BY ad.ad_id
HAVING num_times_played < plays_per_session
ORDER BY num_times_played ASC
LIMIT 1 

[ratcateme]

im crap at mysql but have you tried to use mysqli_error();

 

Scott.

[Bendude14]

can we see some more code like the variable your storing the query in and where your storing the result after you have queried etc...

[rubing]

error reporting indicated that the error was due to not having an index defined.  I defined several indices and it worked.  I don't understand this b/c it was working fine 2 days ago.  And it works fine in phpmyadmin w/out the indices.

[prexep]

The query isn't enough information to tell what the problem is. Please give more information. (The code and line 100)

[rubing]

I am afraid there is some serious bug here!!!  I am seeing these errors intermittenly, sometimes it happens, sometimes it doesn't.  It crops up on a number of different queries.  Here is the query  i have seen it come up for the most often.  [error follows query]

 

 

$sessionid=session_id();
$sessionid=$conn->real_escape_string($sessionid);
//echo $sessionid;
//lookup ad to play
$query="SELECT ad.ad_id, ad.owner_api, ad.file,ad.plays_purchased, ad.times_played, ad_limit.advert_id, ad_log.sess_id, ad_log.date_played, ad_log.ad_played, plays_per_session, COUNT( ad_log.ad_played ) AS num_times_played
FROM ad
LEFT JOIN ad_limit ON ad.ad_id = ad_limit.advert_id
LEFT JOIN ad_log ON ( ad_log.ad_played = ad.ad_id
AND ad_log.sess_id ='".$sessionid."' )
WHERE ad.owner_api =".$api."
AND ad.plays_purchased > ad.times_played
GROUP BY ad.ad_id
HAVING num_times_played < plays_per_session
ORDER BY num_times_played ASC 
LIMIT 1";

//Set the query up to be executed
$result = $conn->query($query);
//*note api is an integer assigned by a get variable:  
//$api= (int) $_GET['api']

 

 

Error:

 

mysqli_sql_exception: No index used in query/prepared statement SELECT ad.ad_id, ad.owner_api, ad.file,ad.plays_purchased, ad.times_played, ad_limit.advert_id, ad_log.sess_id, ad_log.date_played, ad_log.ad_played, plays_per_session, COUNT( ad_log.ad_played ) AS num_times_played FROM ad LEFT JOIN ad_limit ON ad.ad_id = ad_limit.advert_id LEFT JOIN ad_log ON ( ad_log.ad_played = ad.ad_id AND ad_log.sess_id ='dec2316aa286d564e349096574b44861' ) WHERE ad.owner_api =1 AND ad.plays_purchased > ad.times_played GROUP BY ad.ad_id HAVING num_times_played < plays_per_session ORDER BY num_times_played ASC LIMIT 1 in /home/rubing/public_html/citymusicnow.com/public/music_serve.php on line 118

Call Stack

# Time Memory Function Location

1 0.0725 166688 {main}( ) ../music_serve.php:0

2 0.0753 258544 mysqli->query( ) ../music_serve.php:118

[Bendude14]

we need to see more than this...  Can i see the entire script etc...