PHP Autocomplete Search Not Displaying

Question

Possible Duplicate:
mysql_fetch_array() expects parameter 1 to be resource, boolean given in select

Every time I enter a keyword to search; it displays a warning "Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given on line 134". The php successfully displays the autocomplete search while typing but gives this error once I "submit". My relevant code:

               <?php
//allow sessions to be passed so we can see if the user is logged in
session_start();

//connect to the database so we can check, edit, or insert data to our users table
require_once("functions/connection.php"); 
//include out functions file giving us access to the protect() function made earlier
include "functions/functions.php";

?>



<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.5/jquery.min.js"></script>
  <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script>
  <link rel="shortcut icon" href="http://sifeiitd.org/favicon.ico">
<link rel="stylesheet" href="css/style.css" type="text/css" media="all">

<script type="text/javascript">

var _gaq = _gaq || [];
    _gaq.push(['_setAccount', 'UA-30675532-1']);
    _gaq.push(['_setDomainName', 'SIFE IIT Delhi']);
    _gaq.push(['_setAllowLinker', true]);
    _gaq.push(['_trackPageview']);
    (function() {
    var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
    ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
    var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
  })();
</script>
<script>
$(document).ready(function() {
  $("#keywords").autocomplete({
    source: keywordList,
    minLength: 2,

  });
});
</script>
<?php echo keywordArray(); ?>
<?php function keywordArray()
{
  $rsKeywords = mysql_query("SELECT * FROM job");

  $output = '<script>'."\n";
  $output .= 'var keywordList = [';

  while($row_rsKeywords = mysql_fetch_assoc($rsKeywords))
  {
    $output .= '"'.$row_rsKeywords['work'].'",';
  }

  $output = substr($output,0,-1); //Get rid of the trailing comma
  $output .= '];'."\n";
  $output .= '</script>';
  return $output;
}
?> 

</head>

<body>
    <div id="container">
<!-- header -->
        <header class="b_border">
            <h1><a href="index.html"><img src="images/logo_page.png"></a></h1>
        </header>
<!-- / header -->
<!-- content -->
        <section class="content">
            <?php

                //if the login session does not exist therefore meaning the user is not logged in
                if(strcmp($_SESSION['uid'],"") == 0){
                    //display and error message
                    echo "<center>You need to be logged in to user this feature!</center>";
                }else{
                    //otherwise continue the page

                    //this is out update script which should be used in each page to update the users online time
                    $time = date('U')+50;
                    $update = mysql_query("UPDATE `employer` SET `online` = '".$time."' WHERE `id` = '".$_SESSION['uid']."'");
                    $display_query = mysql_query("SELECT * FROM employer WHERE `id` = '".$_SESSION['uid']."'");
                    echo '<div class="col1 lfloat">';
                    echo "<table id='pageTable'><tbody><th>Your Details</th>";
                    echo "<tbody>";
                    while($row = mysql_fetch_array($display_query)){
                        echo "<tr><td>Name&#58;&nbsp;</td><td>".$row['name']."</td><tr>";
                        $currentuser = $row['name'];
                        echo "<title>".$row['name']. "&#124; SIFE IIT Delhi</title>";
                        echo "<tr><td>E&#45;Mail ID&#58;&nbsp;</td><td>".$row['email']."</td><tr>";
                        echo "<tr><td>Contact No&#46;&#58;&nbsp;</td><td>".$row['contact']."</td><tr>";
                        echo "<tr><td>Company&#58;&nbsp;</td><td>".$row['company']."</td><tr>";
                        echo "<tr><td>Designation&#58;&nbsp;</td><td>".$row['designation']."</td><tr>";
                    }

                    echo "</tbody>";
                    echo "</table>";
                    echo "<table><tr><td>";
                    echo '<div class="button"><a href="functions/logout.php">Logout</a></td></tr></table>';
                    echo '</div>';

                    echo '<div class="col1 rfloat">';
                    echo "Dear ".$currentuser." please input the type of job for a potential candidate";

                    echo '<form action="loggedin_employer.php" method="post" id="loginForm">';
                        echo '<table cellpadding="2" cellspacing="0" border="0"><tr><td>';
                            echo '<input class="input" id="keywords" name="keywords" type="text" ></td></tr>';
                            echo '<tr><td colspan="2" align="right"><input class="button" type="submit" name="submit" value="Search" /></td>
                        </tr></table>';
                //make sure you close the check if they are online


                    if(!isset($_POST['submit'])){
                        echo "Your search was invalid";
                        exit;
                    } 

                    $keyword = mysql_real_escape_string($_REQUEST['keywords']);
                    $sql = "SELECT * FROM job WHERE work=$keyword LIMIT 10";

                    $result = mysql_query($sql);
                    $numrows = mysql_num_rows($result);

                    echo "<table id='pageTable'><tbody><th>Your Details</th>";
                    echo "<tbody>";
                    if($numrows == 0){
                        echo "Sorry, your search did not return any results";
                    }

                    else{

                        $i = 0;

                        while($i < $numrows){
                        $row1 = mysql_fetch_array($result);
                        echo "<tr><td>Name&#58;&nbsp;</td><td>".$row1['name']."</td><tr>";
                        echo "<tr><td>E&#45;Age&#58;&nbsp;</td><td>".$row1['age']."</td><tr>";
                        echo "<tr><td>Sex&#46;&#58;&nbsp;</td><td>".$row1['gender']."</td><tr>";
                        echo "<tr><td>Location&#58;&nbsp;</td><td>".$row1['location']."</td><tr>";
                        $i++;
                        }
                    }

                    echo "</tbody>";
                    echo "</table>";


                    echo '</div>';
                    echo '</form>';





                }

            ?>


        </section>
<!-- / content -->
<!-- footer -->
        <div class="footer" >

        </div>
<!-- / footer -->
    </div>
</body>
</html>

Welcome to Stack Overflow! Please, don't use `mysql_*` functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://goo.gl/KJveJ). See the [**red box**](http://goo.gl/GPmFd)? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide, [this article](http://goo.gl/3gqF9) will help to choose. If you care to learn, [here is good PDO tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Jun 16 '12 at 20:19
Bear in mind there are SQL injection vulnerabilities in the code you've posted - plug those gaps before going live! — halfer, Jun 16 '12 at 20:23
Do you not have connection code? I suspect the problem is that you haven't connected successfully (or even tried connecting at all) to the database. — halfer, Jun 16 '12 at 20:24
To remove the vulnerability, use something like `$keywords = mysql_real_escape_string($_REQUEST['keywords']);` and then use `$keywords` in your query. — halfer, Jun 16 '12 at 20:26
+1 halfer, also don't forget to quote the var in the query if it's a string: `WHERE work='".$_REQUEST['keywords']."' LIMIT 10"` — Fabrício Matté, Jun 16 '12 at 20:29
@FabrícioMatté - indeed, if it's a string. I presume it is an int, however, as the OP says it is currently working. — halfer, Jun 16 '12 at 20:31
Oh I see. It was just my guess as the question starts with `Every time I enter a keyword to search`, also, by the error he is getting, the query must be failing for some reason - either invalid syntax/table/field name or failed db connection it seems. — Fabrício Matté, Jun 16 '12 at 20:33
`$keywords = mysql_real_escape_string($_REQUEST['keywords']);` `$sql = "SELECT * FROM job WHERE work=$keywords LIMIT 10";` Still gives the error. — user1461020, Jun 16 '12 at 20:35
_I haven't close[d] it [the connection]_ - ok, but I'm not convinced you are opening it for every page view. Also, there appears to be no `
` around the input element? Is the code here for the submission handling, rather than the autocomplete? — halfer, Jun 16 '12 at 20:48
@halfer good point, connections are automatically closed by default after each script is done processing. — Fabrício Matté, Jun 16 '12 at 20:49
@FabrícioMatté : Connections are fine as my autocomplete search works good. It displays words as I types from the same table which i'm trying to display. — user1461020, Jun 16 '12 at 20:53
@halfer: I've edited my code. This is what it looks now after modifications from you people. Still it isn't working. — user1461020, Jun 16 '12 at 20:56
Sorry but I'm not finding any autocomplete code above. To be more specific, are you including the connection file in your PHP file above? I can't find any `include` or `require` or connection string in it. — Fabrício Matté, Jun 16 '12 at 20:57
Autocomplete func. in the same page ` '."\n"; $output .= 'var keywordList = ['; while($row_rsKeywords = mysql_fetch_assoc($rsKeywords)) { $output .= '"'.$row_rsKeywords['work'].'",'; } $output = substr($output,0,-1); $output .= '];'."\n"; $output .= ''; return $output; } ?> ` — user1461020, Jun 16 '12 at 21:01
Is your jQuery code included in the PHP file above? I can't find it. If they are different php files, you **must** include the connection string in every PHP file which queries the DB. — Fabrício Matté, Jun 16 '12 at 21:03
Yes. It is. I've edited the question with my entire code of that page. @FabrícioMatté — user1461020, Jun 16 '12 at 21:08
If it's in the same page, do you have access to your server's `error.log`? You may have to make sure that `$_REQUEST['keywords']` is an integer and not a string or null value. — Fabrício Matté, Jun 16 '12 at 21:08
@FabrícioMatté: The words that I write in the search[that appear on autocomplete] are string. — user1461020, Jun 16 '12 at 21:13
Try: `$sql = "SELECT * FROM job WHERE work='$keyword' LIMIT 10";` — Fabrício Matté, Jun 16 '12 at 21:14
@FabrícioMatté: this is my entire code here. http://pastebin.com/BR5hrgFr — user1461020, Jun 16 '12 at 21:20
Your code **is** breaking at the numrows function because your query **is** failing, that's due to either failed connection or SQL syntax error. Try quoting the var as in my last post, if that doesn't solve it, you should check your apache's `error.log`, it should give more insight as to why your query failed. — Fabrício Matté, Jun 16 '12 at 21:26
I figured out the error. missed the quotes in the $sql statement. thanks a lot @FabrícioMatté — user1461020, Jun 16 '12 at 21:37

Madara's Ghost · Answer 1 · 2012-06-16T20:33:34.353

3

Short Answer

Simply don't pass any arguments to the mysql_num_rows() function.

Longer Answer

While mysql_query() returns a resource on success, it returns FALSE on failure, which will mess up your code.

What you want is to capture the result of mysql_connect(), or not to pass arguments at all.

But what you NEED is to use something other than mysql_* functions:

Welcome to Stack Overflow! Please, don't use mysql_* functions for new code. They are no longer maintained and the community has begun the deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide, this article will help to choose. If you care to learn, here is good PDO tutorial.

edited Jun 16 '12 at 20:33

answered Jun 16 '12 at 20:20

Madara's Ghost

165,920
50
255
304

1

Would you delete your comment attached to the original question, to reduce duplication of info? Thanks. – halfer Jun 16 '12 at 20:22
@halfer: Actually, I would prefer that as many people as possible to see that comment. If it helps one more user to stop using `mysql_*`, I'm satisfied with duplicating content. – Madara's Ghost Jun 16 '12 at 20:23
"`mysql_query()` returns a boolean, (TRUE of FALSE) based on whether the query had succeeded or not." Not exactly, it returns a resource on success (which evaluates as truthy) and a boolean false when it fails. Refer OP to the [documentation](http://php.net/manual/en/function.mysql-query.php)'s Return Values. – Fabrício Matté Jun 16 '12 at 20:25
@FabrícioMatté: Corrected, thanks. – Madara's Ghost Jun 16 '12 at 20:26
@Truth: I really appreciate your concern. I just needed this to work out. I'm learning PHP all by myself and it creates confusion by this. Once, I get a hold onto this; I'll get myself accustomed to this version. Can you please figure out the problem with my code? I really worked hard to get it this far. – user1461020 Jun 16 '12 at 20:28
@user1461020: While I **urge** you to learn the correct way using MySQLi or PDO, the simplest solution is to simply not pass `$result` as an argument to the `mysql_num_rows()` function. – Madara's Ghost Jun 16 '12 at 20:32
@Truth: I understand everything. I, myself aspire to be an awesome coder like you people but it takes time. I learnt HTML/CSS all by myself and later mastered the optimization. Just now, I just need to develop a simple page and I desperately need to debug this. – user1461020 Jun 16 '12 at 20:44
I'll +1 your answer Truth as you took your time to write all of this, but one question, why not pass a parameter to the [mysql_num_rows](http://php.net/manual/en/function.mysql-num-rows.php) function? As I see it, it requires a resource to be passed as parameter. – Fabrício Matté Jun 16 '12 at 20:45
@FabrícioMatté: It does not require it. You **may** pass a resource, which is useful in the case where you have 2 mysql resources running at the same time, which isn't the case. – Madara's Ghost Jun 16 '12 at 21:24

PHP Autocomplete Search Not Displaying

1 Answers1

Short Answer

Longer Answer