Resource id's returned but still get Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

Question

Possible Duplicate:
php warning mysql_fetch_assoc

I have tried my code and it seems to be working but it is throwing errors.

I'm in a LAMP environment, this is my code.

    <?php
    mysql_select_db($database_conn1, $conn1);

    //DROPING VIEW
    $query_rsDropView = "DROP VIEW attorneyOrder;";
    echo $query_rsDropView . "/////";  //this code outputs nothing, not even the "/////" part.
    $rsDropView = mysql_query($query_rsDropView, $conn1) or die(mysql_error());
    echo $rsDropView . "/////";     //output the value "1" (witout quotes)
    $row_rsDropView = mysql_fetch_assoc($rsDropView);      //line 222
    $totalRows_rsDropView = mysql_num_rows($rsDropView);   //line 223

    //CREATING VIEW
    $query_rsView = "CREATE VIEW attorneyOrder AS SELECT * FROM LewisJohsAttorneys ORDER BY lname ASC;";
    $rsView = mysql_query($query_rsView, $conn1) or die(mysql_error());
    echo $rsView . "/////";     //outputs the value "1" (without quotes)
    $row_rsView = mysql_fetch_assoc($rsView);      //line 228
    $totalRows_rsView = mysql_num_rows($rsView);   //line 229

    //GETTING VALUES FOR SELECT ON VIEW
    $urlStart_rsName = "NULL";
    if (isset($_GET['start'])) {
         $urlStart_rsName = $_GET['start'];
    }
    $urlEnd_rsName = "NULL";
    if (isset($_GET['end'])) {
         $urlEnd_rsName = $_GET['end'];
    }

    //SELECTING DATA FROM VIEW
    $query_rsName = sprintf("SELECT * FROM attorneyOrder WHERE attorneyOrder.lname BETWEEN %s AND %s;", GetSQLValueString($urlStart_rsName, "text"),GetSQLValueString($urlEnd_rsName, "text"));
    $rsSearch = mysql_query($query_rsName, $conn1) or die(mysql_error());
    echo $rsSearch . "/////";  //outputs a resouce id
    $totalRows_rsName = mysql_num_rows($rsName);    //line 249

    //OUTPUTING DATA
    while($rsSearch = mysql_fetch_assoc($rsSearch)){   //line 262
    //do some stuff here
    }

    ?>

The errors I'm getting are:

    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 222

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 223

    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 228

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 229

    Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 249

    Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 262

I'm assuming its all stemming from the same problem and that is why each successive query is returning an invalid value since they all build off of the prior ones output.

But, I'm still getting the correct output. I could just suppress the errors but I want to know why these errors are coming up. After searching online a common cause was not specifying the database, but that's the first thing I'm doing with this line [code]mysql_select_db($database_conn1, $conn1);[/code]

I tried outputing the return result of the queries and commented their output in the code. It seems they are appropriately returning either as "1" or true, or a resource id.

Answer 1

Your errors are there for a reason. You don't need to suppress them but to fix them.

mysql_fetch_assoc() function is used when selecting something from the database. In this case array of selected rows is returned which are fetched with this function. Your error on line 222 and 228 is there because CREATE VIEW OR DROP VIEW is not a select query, and it is totally unnecessary there. The same goes for mysql_num_rows() on lines 223, 229. This function is also used only for SELECT queries. Error on line 249 is there because variable $rsName does not exist in your script. This should be mysql_num_rows($rsSearch); In case you are doing SELECT, SHOW, DESCRIBE, EXPLAIN queries with mysql_query function will return ResultSet if it passed ok or FALSE if it failed. You can fetch Resultset with mysql_fetch_assoc() and loop through it. But if no rows exists for that query then mysql_query will return false which cannot be passed to mysql_fetch_assoc()

This is how it should be written:

<?php
mysql_select_db($database_conn1, $conn1);

//DROPING VIEW
$q_drop = "DROP VIEW attorneyOrder;";
echo $q_drop. "/////"; // prints out string stored in $q
$rsDropView = mysql_query($q_drop, $conn1) or die(mysql_error());
echo $rsDropView . "/////";     // prints TRUE (1) if mysql_query() went well or FALSE(0) if it failed

//CREATING VIEW
$q_view = "CREATE VIEW attorneyOrder AS SELECT * FROM LewisJohsAttorneys ORDER BY lname ASC;";
$rsView = mysql_query($q_view, $conn1) or die(mysql_error());
echo $rsView . "/////";     // prints TRUE (1) if mysql_query() went well or FALSE(0) if it failed


//GETTING VALUES FOR SELECT ON VIEW
$urlStart_rsName = "NULL";
if (isset($_GET['start'])) {
     $urlStart_rsName = $_GET['start'];
}
$urlEnd_rsName = "NULL";
if (isset($_GET['end'])) {
     $urlEnd_rsName = $_GET['end'];
}

//SELECTING DATA FROM VIEW
$query_rsName = sprintf("SELECT * FROM attorneyOrder WHERE attorneyOrder.lname BETWEEN %s AND %s;", GetSQLValueString($urlStart_rsName, "text"),GetSQLValueString($urlEnd_rsName, "text"));
$rsSearch = mysql_query($query_rsName, $conn1) or die(mysql_error());
echo $rsSearch . "/////";  //outputs a resouce id
$totalRows_rsName = mysql_num_rows($rsSearch);    //line 249

//OUTPUTING DATA
if($totalRows_rsName>0){
    while($rsSearch = mysql_fetch_assoc($rsSearch)){   //line 262
        //do some stuff here
    }
}


?>