mysql_fetch_assoc() problem

confused_aswell · February 27, 2010

Hi

I have had this problem suddenly come up without changing any code, and I can only think that due to the constant changes in PHP may have caused this problem.

Anyway, here goes.

The first script allows the user to view all of the records in the db, then if they wish to they can either download all of the records as a csv file by entering zero in the form element. If for example they only want the records from 100 onwards then they would simply enter 99, this then passes to another script which gets the desired number of records for downloading to the csv file.

This is where the error is. Here is the error.

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/phcleani/public_html/shopping/setadmin/quickbooks_download.php on line 54

I have no idea on where to start, so any help would be appreciated.

Thanks

Phil

First script - to view records.

<?php

  require('includes/application_top.php');

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<link rel="stylesheet" type="text/css" href="includes/cssjsmenuhover.css" media="all" id="hoverJS">
<script language="javascript" src="includes/menu.js"></script>
<script language="javascript" src="includes/general.js"></script>
<script type="text/javascript">
  <!--
  function init()
  {
    cssjsmenu('navbar');
    if (document.getElementById)
    {
      var kill = document.getElementById('hoverJS');
      kill.disabled = true;
    }
  }
  // -->
</script>
</head>
<body onload="init()">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->
<?php
// find out how many rows are in the table    
$sql = "SELECT COUNT(*) FROM zen_orders";   
$result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR);   
$r = mysql_fetch_row($result);   
$numrows = $r[0];?>
<div align="center">
<?php   
echo "Here is the information you are about to export to the CSV file, this information is taken from your address book and orders table in your database.<br/> You have $numrows entries in your orders table, please enter the number you would like to start exporting from.<br/><br/>If you want to export all records in the table enter 0, but if you are exporting from entry number 1000 then enter 999.";?>
<form name="form1" method="post" action="quickbooks_download.php">
  <p>
    <input name="limit" type="text" id="limit" size="5">
    <input type="submit" name="Submit" value="Export">
  </p>
</form> 
<?php
// number of rows to show per page   
$rowsperpage = 50;   
// find out total pages   
$totalpages = ceil($numrows / $rowsperpage);   
  
// get the current page or set a default   
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {   
   // cast var as int   
   $currentpage = (int) $_GET['currentpage'];   
} else {   
   // default page num   
   $currentpage = 1;   
} // end if   

// if current page is greater than total pages...   
if ($currentpage > $totalpages) {   
   // set current page to last page   
   $currentpage = $totalpages;   
} // end if   
// if current page is less than first page...   
if ($currentpage < 1) {   
   // set current page to first page   
   $currentpage = 1;   
} // end if   
  
// the offset of the list, based on current page    
$offset = ($currentpage - 1) * $rowsperpage;   
  
// get the info from the db    
$sql = "SELECT zen_orders.orders_id, zen_orders.customers_name, zen_address_book.entry_firstname, zen_address_book.entry_lastname, zen_address_book.entry_street_address, zen_address_book.entry_suburb, zen_address_book.entry_city, zen_orders.customers_state, zen_address_book.entry_postcode, zen_orders.customers_country, zen_orders.customers_telephone, zen_orders.customers_email_address
FROM (zen_address_book INNER JOIN zen_orders ON zen_address_book.customers_id = zen_orders.customers_id) INNER JOIN zen_customers ON (zen_address_book.address_book_id = zen_customers.customers_default_address_id) AND (zen_address_book.customers_id = zen_customers.customers_id) LIMIT $offset, $rowsperpage";   
$result = mysql_query($sql) or trigger_error(mysql_error() . "<br />\n" . $sql, E_USER_ERROR); ?>
</div>

<table align="center"  border="0" >
<?php 
// while there are rows to be fetched...   
while ($list = mysql_fetch_assoc($result)) {   
$color = ($color == '#e8e8e8')? '#cccccc' : '#e8e8e8'; 
?>
<tr><?php echo "<td bgcolor='$color'>" . $list['orders_id'] . " , " . $list['customers_name'] . " , " . $list['entry_firstname'] . " , "  . $list['entry_lastname'] . " , " . $list['entry_street_address'] . " , " . $list['entry_suburb'] . " , " . $list['entry_city'] . " , " . $list['customers_state'] . " , " . $list['entry_postcode'] ." , " . $list['customers_country'] ." , " . $list['customers_telephone'] . " , " . $list['customers_email_address'] . "<br />"; 
}  ?></td>
</tr>
</table><br><div align="center">
<?php  
/******  build the pagination links ******/  
// range of num links to show   
$range = 3;   
  
// if not on page 1, don't show back links   
if ($currentpage > 1) {   
   // show << link to go back to page 1   
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'>First</a> ";   
   // get previous page num   
   $prevpage = $currentpage - 1;   
   // show < link to go back to 1 page   
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'>Previous</a> ";   
} // end if    
  
// loop to show links to range of pages around current page   
for ($x = (($currentpage - $range) - 1); $x < (($currentpage + $range) + 1); $x++) {   
   // if it's a valid page number...   
   if (($x > 0) && ($x <= $totalpages)) {   
      // if we're on current page...   
      if ($x == $currentpage) {   
         // 'highlight' it but don't make a link   
         echo " [<b>$x</b>] ";   
      // if not current page...   
      } else {   
         // make it a link   
     echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";   
      } // end else   
   } // end if    
} // end for   
            
// if not on last page, show forward and last page links       
if ($currentpage != $totalpages) {   
   // get next page   
   $nextpage = $currentpage + 1;   
    // echo forward link for next page    
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>Next</a> ";   
   // echo forward link for lastpage   
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>Last</a> ";   
} // end if   
/****** end build pagination links ******/  
require(DIR_WS_INCLUDES . 'footer.php');
require(DIR_WS_INCLUDES . 'application_bottom.php');
?>
</div></div>

Second script - to download records.

<?php

   require('includes/application_top.php');

$limit = (int) $_POST['limit']; 

if ($limit > 0){ 
    $limit = ' LIMIT '. $_POST['limit'] .', 98446744073709551615'; 
} else { 
    $limit = null; // Nullify it so there is no way it can affect the SQL. 
} 



$replace = array(
'address_book_id' => '',
'customers_name' => 'Customer Job',
'entry_firstname' => 'First Name',
'entry_lastname' => 'Last Name',
'entry_street_address' => 'Billing Address1',
'entry_suburb' => 'Billing Address2',
'entry_city' => 'Billing Address3',
'customers_state' => 'Billing Address4',
'entry_postcode' => 'Billing Address5',
'customers_country' => 'Country',
'customers_email_address' => 'Email',
'customers_telephone' => 'Phone'

);
function split_num($num){ 
    if (substr($num, 0,1) == 1) { 
    return $num; 
    }
    else {
    $num = substr($num, 0, 5) . ' ' . substr($num, 5); 
    return $num; 
    }
} 

$values = mysql_query("SELECT zen_orders.customers_name, zen_address_book.entry_firstname, zen_address_book.entry_lastname, zen_address_book.entry_street_address, zen_address_book.entry_suburb, zen_address_book.entry_city, zen_orders.customers_state, zen_address_book.entry_postcode, zen_orders.customers_country, zen_orders.customers_telephone, zen_orders.customers_email_address
FROM (zen_address_book INNER JOIN zen_orders ON zen_address_book.customers_id = zen_orders.customers_id) INNER JOIN zen_customers ON (zen_address_book.address_book_id = zen_customers.customers_default_address_id) AND (zen_address_book.customers_id = zen_customers.customers_id)$limit");



$i=0;
while ($rowr = mysql_fetch_assoc($values)) { 
if($rowr['entry_suburb'] ==""){ 
$rowr['entry_suburb'] = $rowr['entry_city']; 
} 

    if(!preg_match('#\x20#', $rowr['customers_telephone'], $match)){ // does not find a space... 
       $rowr['customers_telephone'] = split_num($rowr['customers_telephone']); 
    } 
    if($i==0) { 
        foreach(array_keys($rowr) as $title) 
        $csv_output .= '"'.str_replace(array_keys($replace), $replace, $title).'",'; 
        $csv_output .= "\n"; 
    } 

    foreach ($rowr as $key => $value) { 
        $csv_output .= '"'.$value.'",'; 

    } 
    $csv_output .= "\n"; 
    $i++; 
} 

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
// Footer
require(DIR_WS_INCLUDES . 'footer.php'); 
// End of footer
require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

Line 54 is

while ($rowr = mysql_fetch_assoc($values)) {

PFMaBiSmAd · February 27, 2010

The error message is probably the most common one seen concerning code that is using a database. It means that your query failed to execute and it returned a FALSE value instead of a result resource (i.e. mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource.) It also means that your code does not have any necessary error checking and error recovery logic in it to check for errors and prevent the remainder of the code that is dependent on the result of the query from blindly executing, nor does your code have any error reporting logic to tell you that the query failed and why it failed.

For debugging purposes, get mysql to tell you why the query failed by echoing mysql_error() on the line right after the line with the mysql_query() statement.

confused_aswell · February 27, 2010

Hi

Thanks for you reply and taking the time to help me.

I did what you said and it threw up this message - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '98446744073709551615' at line 2

Does this help us with the problem?

Thanks

Phil

PFMaBiSmAd · February 27, 2010

That's part of the LIMIT term. I would form the query string in a php variable and then echo it so that you can see exactly what the query contains.

confused_aswell · February 27, 2010

Hi

Thanks for that, could you perhaps post something for what I might write? I do not know what or how to write the solution .

Thanks

Phil

jl5501 · February 27, 2010

instead of

$values = mysql_query("SELECT zen_orders.customers_name, zen_address_book.entry_firstname, zen_address_book.entry_lastname, zen_address_book.entry_street_address, zen_address_book.entry_suburb, zen_address_book.entry_city, zen_orders.customers_state, zen_address_book.entry_postcode, zen_orders.customers_country, zen_orders.customers_telephone, zen_orders.customers_email_address
FROM (zen_address_book INNER JOIN zen_orders ON zen_address_book.customers_id = zen_orders.customers_id) INNER JOIN zen_customers ON (zen_address_book.address_book_id = zen_customers.customers_default_address_id) AND (zen_address_book.customers_id = zen_customers.customers_id)$limit");

do

$sql = "SELECT zen_orders.customers_name, zen_address_book.entry_firstname, zen_address_book.entry_lastname, zen_address_book.entry_street_address, zen_address_book.entry_suburb, zen_address_book.entry_city, zen_orders.customers_state, zen_address_book.entry_postcode, zen_orders.customers_country, zen_orders.customers_telephone, zen_orders.customers_email_address
FROM (zen_address_book INNER JOIN zen_orders ON zen_address_book.customers_id = zen_orders.customers_id) INNER JOIN zen_customers ON (zen_address_book.address_book_id = zen_customers.customers_default_address_id) AND (zen_address_book.customers_id = zen_customers.customers_id)$limit";
echo $sql;
$values = mysql_query($sql);

confused_aswell · February 27, 2010

Hi

Thanks for the reply.

I did what you said, but this just echos the sql query - SELECT zen_orders.customers_name,zen_address_book.entry_firstname....98446744073709551615 have I done this right?

Thanks

Phil

jl5501 · February 27, 2010

I am not sure it is valid to have a number that large in the limit of a query.

Do you have a reason for that very large number?

PFMaBiSmAd · February 27, 2010

The point of echoing the actual query so that you can see exactly what it is, was so that you can determine what is wrong with it in order to fix it. Without seeing what the result of doing that is, no one can help you.

confused_aswell · February 27, 2010

No, this was posted by someone else who was helping me put this together, do you suggest a number say something like 20,000?

Thanks

Phil

confused_aswell · February 27, 2010

I see. So if the problem lies with the $limit variable, then it must be this piece of code creating the error - Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/phcleani/public_html/shopping/setadmin/quickbooks_download.php on line 51

Is this correct?

Thanks

Phil

jl5501 · February 27, 2010

The reason you are getting the reported error is because the earlier call to mysql_query() is not producing a valid result. If you show us the entire query that is echoed, then the answer can be provided.

confused_aswell · February 27, 2010

Ah ok, sorry!

SELECT zen_orders.customers_name, zen_address_book.entry_firstname, zen_address_book.entry_lastname, zen_address_book.entry_street_address, zen_address_book.entry_suburb, zen_address_book.entry_city, zen_orders.customers_state, zen_address_book.entry_postcode, zen_orders.customers_country, zen_orders.customers_telephone, zen_orders.customers_email_addressFROM (zen_address_book INNER JOIN zen_orders ON zen_address_book.customers_id = zen_orders.customers_id) INNER JOIN zen_customers ON (zen_address_book.address_book_id = zen_customers.customers_default_address_id) AND (zen_address_book.customers_id = zen_customers.customers_id) LIMIT 50, 98446744073709551615

Thanks

Phil

jl5501 · February 28, 2010

Well you need a space before your FROM, but I still think your limit needs to be a much much smaller number

confused_aswell · February 28, 2010

Hi

All is ok now, it was that large number after all.

Thanks for you help

Phil

Sign In

mysql_fetch_assoc() problem

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information