PHP mysql_fetch_assoc error

Question

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

I'm trying to fetch a single field from one table and add it to another.

This is my third day using MySQL/PHP so I'm having a little issue.
PHP throws an error expecting a resource but a string was given.
It's the auto incremented ID field from one table.

Basically, I pull the ID where the first name and last name match.

$query5 = "SELECT FROM ".$db_prefix."customer_det (`id`) 
WHERE fname = '".$fname."' 
AND lname = '".$lname."'";

$result5 = mysql_query($query5);
while($row = mysql_fetch_assoc($result5)){
$uid = $row['id'];
echo "$uid";
}

Error:

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given

Please use the [`mysqli_*`](http://php.net/mysqli) functions or [PDO](http://php.net/PDO) instead of the `mysql_*` functions. — nkr, Oct 19 '12 at 12:29
It throws Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given now — smada, Oct 19 '12 at 12:32
This is terrible code and will open you up to SQL Injection attacks. Please use whatever is equivalent to prepared statements in PHP. — Roman, Oct 19 '12 at 18:39

Leri · Answer 1 · 2012-10-19T16:21:08.227

4

$result5 = "mysql_query($query5)";
           ^                    ^

Remove quotes.

If you used var_dump($result5); you'd get that it's string. The reason is that everything in double or single quotes are treated as string in php.

Also you need to handle errors like this:

$result5 = mysql_query($query5) or die(mysql_error());

because mysql_query will return false on failure

If you are interested in string representation take a look at manual.

Also, you have syntax error in your query. You are missing which columns you want to retrieve from db:

$query5 = "select from ".$db_prefix."customer_det (`id`) where fname = '".$fname."' and lname = '".$lname."'";
                 ^

Add * (or names of columns) after select

Note: mysql_* functions are deprecated. Alternatives are: mysqli_* and PDO

edited Oct 19 '12 at 16:21

answered Oct 19 '12 at 12:27

Leri

12,079
5
40
59

You gotta be kidding me...syntax is killing me. It's still throwing the same error. – smada Oct 19 '12 at 12:28
Just to clarify on one of PLB's points. Everything in quotes is treated as a string, BUT, using a variable inside of double quotes will give you the value in place of the variable name. This is because variables within double quotes are evaluated. Single quotes, on the other hand, don't do this, and are therefore faster to use. – Daniel Noel-Davies Oct 19 '12 at 12:30
@user1718270 See updated answer. you need to check what error has occured. – Leri Oct 19 '12 at 12:35
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 'from customer_det (`id`) where fname = 'namehere' and lname = 'namehere'' at line 1 – smada Oct 19 '12 at 12:39

score 1 · Answer 2 · answered Oct 19 '12 at 12:28

1

$query5 = "select from ".$db_prefix."customer_det (`id`) where fname = '".$fname."' and lname = '".$lname."'";
$result5 = mysql_query($query5);
while($row = mysql_fetch_assoc($result5)){
  $uid = $row['id'];
  echo "$uid";
}

answered Oct 19 '12 at 12:28

Jonathan Muller

7,079
2
21
29

score 0 · Answer 3 · answered Oct 19 '12 at 12:28

0

Change--

$result5 = "mysql_query($query5)";

TO

$result5 = mysql_query($query5);

answered Oct 19 '12 at 12:28

swapnesh

25,390
22
93
124

score 0 · Answer 4 · answered Oct 19 '12 at 12:30

0

Use this

$result5 = mysql_query($query5);

Instead of this

$result5 = "mysql_query($query5)";

answered Oct 19 '12 at 12:30

gks

3,444
4
29
47

score 0 · Accepted Answer · answered Oct 19 '12 at 12:34

I'm going to guess that the line throwing the error is this one:

while($row = mysql_fetch_assoc($result5)){

The function mysql_fetch_assoc is "expecting a resource" in that you need to pass it the results of a SQL query and it will fetch from those results. However, $result5 isn't a SQL query, it's a string:

$result5 = "mysql_query($query5)";

I'm not sure why you put quotes around that, but you don't want them there if you want to actually have that code be interpreted:

$result5 = mysql_query($query5);

This would run the mysql_query function and place its return value (which is a resource) into $result5.

Now for the more important stuff...

First and foremost, your code is wide open to SQL injection attacks. There's a lot to know about this, and a single Stack Overflow answer isn't going to cover it. So I'll try to summarize the concept by simply saying that you should never ever use raw string concatenation of user-input values to build a SQL query. The user can enter malicious inputs and modify your query, effectively gaining access to your database. If your database runs with elevated permissions then they have access to more things on the server, and so on.

Basically, always sanitize your inputs and never implicitly trust inputs from the user.

Second, don't use the mysql_ functions. They've been replaced by the mysqli_ functions for, well, reasons not entirely unrelated to the SQL injection stuff I just mentioned. The documentation page for them even very clearly states this.

David I appreciate all the information. It's still throwing the error once the quotes are removed. — smada, Oct 19 '12 at 12:36
@user1718270: Has an error occurred in the SQL query which would result in not returning a valid resource? You can use `mysql_error` to get error information from the database: http://php.net/manual/en/mysqli.error.php — David, Oct 19 '12 at 12:39
@user1718270: I just noticed... You're not actually _selecting_ anything in your SQL query. The problem is in the SQL code, not the PHP code. Notice you go straight from `select` to `from` without specifying what you're selecting. If you want to select everything from that table, use: `select * from` — David, Oct 19 '12 at 12:40
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 'from customer_det (`id`) where fname = 'namehere' and lname = 'namehere'' at line 1 — smada, Oct 19 '12 at 12:40
David, I had the selection ID in front of the customer_det...i feel like an idiot — smada, Oct 19 '12 at 12:41

score 0 · Answer 6 · edited Oct 19 '12 at 13:22

0

You are missing the name of the selected field (id) from your query:

$query = "SELECT id FROM {$db_prefix}customer_det WHERE fname = '{$fname}' AND lname = '{$lname}'";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){
    echo $row['id'];
}

edited Oct 19 '12 at 13:22

aorcsik

14,571
4
38
49

answered Oct 19 '12 at 13:15

adam

21
1

PHP mysql_fetch_assoc error

6 Answers6