CODEWITHSUNDEEP
phpmysql
Biker John
Biker John

Reputation: 2711

What is wrong with the mysql clause

TABLE bcompany

companyID | cName | ...

I have the input field, where user searches for the records including the "input" characters:

<input type="text" class="bigblack" name="srch" />

PHP:

$req="%".mysql_real_escape_string($_POST['srch'])."%";

$query = mysql_query("SELECT companyID, cName FROM bcompany WHERE
companyID OR cName LIKE $req ORDER BY companyID LIMIT 10");

OR

$query = mysql_query("SELECT companyID, cName FROM bcompany WHERE
companyID,cName LIKE $req ORDER BY companyID LIMIT 10");

Both queries return an error:

mysql_fetch_array() expects parameter 1 to be resource....

There is probably something wrong with the MYSQL SELECT.

Can you please help me solve this out?

Thanks in advance :)

Rest of the code:

while($res = mysql_fetch_array($query)) {


    echo $res["companyID"];
    echo $res["cName"]."<br>";



}

Upvotes: 1

Views: 83

Answers (3)

AD7six
AD7six

Reputation: 66298

From the docs

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

As such the problem you have is that $queryis false but in your (unshown) code you aren't checking for that.

The cause is the query, or queries, you have are invalid, taking the first one:

SELECT
    companyID, cName 
FROM 
    bcompany
WHERE
    companyID OR cName LIKE $req
ORDER BY
    companyID
LIMIT 10

This is not a valid query, you would need:

SELECT
    companyID, cName 
FROM 
    bcompany
WHERE
    companyID LIKE "$req" OR cName LIKE "$req"
ORDER BY
    companyID
LIMIT 10

You can help identify problems like this by running the queries directly against the db (open a terminal window and mysql -uname -ppass db etc.), and not via php. Doing so with either of the queries in the question will spit out:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that cor...

Making it more obvious where the error is.

Upvotes: 2

powerbuoy
powerbuoy

Reputation: 12838

You need to surround your string with quotes:

mysql_query("SELECT companyID, cName FROM bcompany WHERE companyID,cName LIKE '$req' ORDER BY companyID LIMIT 10");

Should work.

Ah, missed this:

mysql_query("SELECT companyID, cName FROM bcompany WHERE companyID LIKE '$req' OR cName LIKE '$req' ORDER BY companyID LIMIT 10");

You may want to change the OR to AND.

Upvotes: 2

kjones
kjones

Reputation: 1423

You query should be:

$query = mysql_query("SELECT companyID, cName FROM bcompany WHERE
companyID LIKE '$req' OR cName LIKE '$req' ORDER BY companyID LIMIT 10");

Here are the docs for MySQL LIKE:

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

Alternatively you could use the MySQL REGEXP in place of LIKE in your queries.

It looks like mysql_fetch_array is griping when you call it because the query is incorrect and not producing a result which mysql_fetch_array can use.

Upvotes: 3

Related Questions