Invalid MySQL resource

Question

I was attempting to use the following if/else statements to build a query, but I'm having a problem evaluating for NULL in the first step.

After some searching online, I can't seem to track down what I'm doing wrong... should I be using empty quotes instead: "" ?
That also gave me an error, though.

So I don't know if the problem is in the first block or the second, which is the while loop.

Any suggestions?

$name = $_POST['Your_name'];

if ($location != "All" && $name == NULL)  $query="SELECT * FROM talent WHERE duty_station='$location')";
else if($location == "All" && $name != "All" ) $query="SELECT * FROM talent WHERE Your_name IN ('$name')";
else if($dutyReq != "All" && $name == "All" ) $query="SELECT * FROM talent WHERE  duty_station='$location'";
else if($location == "All" || $name == "All" ) $query="SELECT * FROM talent";

Then my loop to print out the data gives me this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/html/talent/searchresults.php on line 64

This is the code that the error comes from:

   $result=mysql_query($query); 
   mysql_query($result);

    echo "<table border='1'>
    <tr>
    <th>Your name</th>
    <th>duty station</th>
    <th>first proficiency</th>
    </tr>";
   while($row = mysql_fetch_array($result)){
        echo "<tr>";
        echo "<td>" . $row['Your_name'] . "</td>";
        echo "<td>" . $row['duty_station'] . "</td>";
        echo "<td>" . $row['prof_order_processing'] . "</td>";
        echo "</tr>";
        }
echo "</table>";

Looks like you have an error in SQL statement here "SELECT * FROM talent WHERE duty_station='$location' -----> )" — Roman S, Sep 09 '12 at 21:57
**Your code is vulnerable to SQL injection.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of [Bobby Tables](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). — eggyal, Sep 09 '12 at 21:57
@eggyal -- Oh... Knew what Bobby Tables did, but I never saw that explanation for it. That is great! I'll pass it on. — Jeremy J Starcher, Sep 09 '12 at 21:59
@RomanS, i fixed that, but it still gives me the same error re: mysql_fetch_array() — richfranzen, Sep 09 '12 at 22:14
After the line `$result=mysql_query($query);`, what does `var_dump($result);` output? — uınbɐɥs, Sep 09 '12 at 22:19
@user1658726 That means that the query failed. Can you replace that with `echo "
$query
";`? — uınbɐɥs, Sep 09 '12 at 22:26
@user1658726 That means that none of your `if` conditions evaluate to `true`. Check the variables that you are using in the conditions. — uınbɐɥs, Sep 09 '12 at 22:34
i removed the second mysql_query($result), but that didn't change it. it looks like it now does work, including the $result=mysql_query($query). should there be an issue using that line? — richfranzen, Sep 09 '12 at 22:44

score 1 · Answer 1 · answered Sep 09 '12 at 22:06

1

I think the problem is with this line:

mysql_query($result);

It shouldn't be there. You can not call a mysql_query function and give it a variable which is not a sql script. Delete this line.

answered Sep 09 '12 at 22:06

Shahar

11
1
5

uınbɐɥs · Accepted Answer · 2012-09-09T22:32:46.607

1

You have an error in your first SQL query (an extra right parenthesis):

$query="SELECT * FROM talent WHERE duty_station='$location')";

It should be:

$query="SELECT * FROM talent WHERE duty_station='$location'";

With these lines of code:

$result=mysql_query($query); 
mysql_query($result);

What are you trying to achieve? You are trying to use a MySQL resource as a query string.
Remove the second line.

If $_POST['name'] is not set, you will see

Notice: Undefined index: name in <filename> on line X

To fix that, use this code:

$name = empty($_POST['name']) ? null : $_POST['name'];

And remember to secure your code against SQL injection.

mysql_* functions are discouraged from use, you should use PDO instead.
You can use PDO::prepare to make your code more secure.

edited Sep 09 '12 at 22:32

answered Sep 09 '12 at 22:18

uınbɐɥs

7,236
5
26
42

it looks like it does work with both of these still present: mysql_query($result); $result=mysql_query($query); – richfranzen Sep 09 '12 at 22:55
@user1658726 You do not need `mysql_query($result);`, it will throw an error. Remove that line. – uınbɐɥs Sep 09 '12 at 22:57

score 0 · Answer 3 · answered Sep 09 '12 at 21:59

0

In php you should write something like this

$name = $_POST['Your_name'];
if(isset($name) && !empty($name))
{
  //some code
}

Also you can add more checks like is_array (if you're expecting plain string) and etc...

answered Sep 09 '12 at 21:59

BSen

187
2
16

3

why do you use isset() in combination with empty()? your can only use empty(), because it will not generate a warning if the variable is not set... – Glavić Sep 09 '12 at 22:01
@glavić : does it really? good to know. i never used it because i assumed it would... thx. – Kuro Sep 09 '12 at 22:03
2

I would do the check on the `$_POST['Your_name']`. Also, strictly, you should use `=== null` or `is_null()` as `0 == null` and so does `""` – Philip Whitehouse Sep 09 '12 at 22:03
@PhilipWhitehouse what would that look like exactly? – richfranzen Sep 09 '12 at 22:15

Invalid MySQL resource

3 Answers3