SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysqli_free_result

    I am trying to run two mysqli_query's on the same page. I can't seem to get them to work even on a simple page. Below is my code (any help would be greatly appreciated). I keep getting this error message:

    An error occurred in script '/home/content/j/w/a/jwarrenn1/html/texas-overland-outfitters/deleteme2.php' on line 29:
    mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

    <ul>
    <?php
    $sCM = mysqli_query ($dbc, 'CALL selectCategories_menu()');
    if (mysqli_num_rows($sCM) > 0) {
    while ($row = mysqli_fetch_array($sCM, MYSQLI_ASSOC)) {
    echo '<li>' . $row['category'] . '</li>';
    }
    }
    mysqli_free_result($sCM);
    ?>
    </ul>
    <h2>This is the second list</h2>
    <ul>
    <?php
    $r = mysqli_query ($dbc, 'CALL selectProducts_new()');
    if (mysqli_num_rows($r) > 0) {
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
    echo '<li>' . $row['name'] . '</li>';
    }
    } else {
    echo '<li>Nothing to see here</li>';
    }
    ?>
    </ul>

  2. #2
    SitePoint Wizard bronze trophy chris.upjohn's Avatar
    Join Date
    Apr 2010
    Location
    Melbourne, AU
    Posts
    2,192
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    The easiest way to debug this issue would be to use the mysqli_error() function as it will give you the exact error to correct the problem.

    PHP Code:
    if (!$sCM mysqli_query ($dbc'CALL selectCategories_menu()')) {
        die(
    'MySQL Error: ' mysqli_error($dbc));


  3. #3
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried putting that in the code in various places, and kept getting this response: MySQL Error: Commands out of sync; you can't run this command now.

    I don't understand from everything I have found what this means.

  4. #4
    SitePoint Wizard bronze trophy chris.upjohn's Avatar
    Join Date
    Apr 2010
    Location
    Melbourne, AU
    Posts
    2,192
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

  5. #5
    SitePoint Member
    Join Date
    Feb 2010
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you. I have added this code after the first CALL: $dbc->next_result();

    Is that the best way to move on to the next section, or should I use:

    mysqli_free_result($sCM);
    unset ($sCM);
    $sCM->close();

    Before I run the next Stored Procedure? It seems to be working now with just the first command, I just don't know which way is considered the best technique.

  6. #6
    Non-Member bronze trophy
    Join Date
    Nov 2009
    Location
    Keene, NH
    Posts
    3,760
    Mentioned
    23 Post(s)
    Tagged
    0 Thread(s)
    free only works on prepared sets I thought, you should be using close on result -- and I'd suggest you stop mixing/matching the ancient procedural crutch with the proper use object namespace -- the overhead's a pig. Might also help to stop using string addition in your echo statements since that sucks more memory for nothing. (and can cause issues in procedural calls)... same goes for opening and closing php so much. (I generally consider any .php file that has more than one <?php ?> pairing in it to be rubbish) -- to that end I'd also move the <ul></ul> inside the row conditionals, as you probably don't want the UL in the code if there's no results...

    Also, if you just re-used the same variable for your result handler, it will automatically de-allocate the result set for you so you shouldn't have to call close or free.

    Code:
    <?php
    
    $result=$dbc->query('CALL selectCategories_menu()');
    if ($result->num_rows>0) {
    	echo '
    		<ul>';
    	while ($row=$result->fetch_array(MYSQLI_ASSOC)) {
    		echo '
    			<li>',$row['category'],'</li>';
     	}
     	echo '
     		</ul>';
    }
    
    /*
    	$result->close(); shouldn't be necessary if we re-use $result
    	which is also good since it means less memory overhead/thrashing
    	as we re-use something that already is in the namespace
    */
     		
    $result=$dbc->query('CALL selectProducts_new()');
    
    if ($result->num_rows>0) {
    
    	echo '
     		<h2>This is the second list</h2>
     		<ul>';
     		
    	while ($row=$result->fetch_array(MYSQLI_ASSOC)) {
     		echo '
     			<li>',$row['name'], '</li>';
    	}
    	
    	echo '
    		</ul>';
    		
    } else {
    
    	echo '
    		<h2>Nothing to see here</h2>';
    		
    } 
    
    ?>
    Much simpler. IF the second one bombs try adding the close back in... It shouldn't be necessary, but mysqli can be a bit wonky on that. (part of why I prefer PDO)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •