Page 1 of 2
Fetching Array from Query
Posted: Tue Sep 16, 2008 2:45 pm
by mlarson154
I am writing a script for people to automatically unsubscribe from a newsletter. I'm writing a check to the database to verify if the email address is already there, and the mysql_fetch_array line is not executing properly. Here is the basic lines of code where the problem is:
Code: Select all
$query_select = mysql_query("SELECT * FROM newsletter WHERE email='$email'") or die("Query failed.");
$query_array = mysql_fetch_array($query_select) or die("Array failed.");
echo "ID: " . $query_array['id'] . ", Name: " . $query_array['firstName'] . " " . $query_array['lastName'] . ", Email: " . $query_array['email'];
I've included the "die" commands to check the lines and therefore i know that my 2nd line is where the problem is. The first line completes without a problem (e.g. I test this with an email I know exists in the database), but my second line dies.
I believe my syntax is correct, and the code is pretty simplistic. I cannot figure out why the array creation won't work.
Any thoughts?
Re: Fetching Array from Query
Posted: Tue Sep 16, 2008 2:51 pm
by s.dot
try doing echo mysql_num_rows($query_select) before you fetch the array. Perhaps the query is returning 0 rows.
Re: Fetching Array from Query
Posted: Tue Sep 16, 2008 2:59 pm
by mlarson154
mysql_num_rows also fails. Does that indicate that no rows have been returned?
Re: Fetching Array from Query
Posted: Tue Sep 16, 2008 3:30 pm
by Christopher
After queries you should really always do something like:
Code: Select all
$result = mysql_query($sql);
$errmsg = mysql_error();
if (! $errmsg) {
// fetch records
} else {
// handle error
}
Re: Fetching Array from Query
Posted: Tue Sep 16, 2008 3:42 pm
by mlarson154
That is a great suggestion, Arborint. Thanks. It turns out, I had a carriage return in the cell where my data was in the database. This caused all the problems. Thanks for the help, guys! I appreciate the information.
Re: Fetching Array from Query
Posted: Tue Sep 16, 2008 5:22 pm
by mlarson154
I have everything running now, but I'm noticing that when my query returns no value, which it would if it can't find an email in the database equal to what's input, rather than returning an empty query, it's returning "Resource id #3". I've read plenty of posts about what this IS, but what the responses DON'T tell me is how to resolve it.
This is why my fetch_array attempts failed. The variable holding the query results had this as its value.
I just want to see if my query returns an email match, and then run and if/else depending on if there's a match.
Any help on dealing with a return of "Resource id #3" instead of just an empty query?
Re: Fetching Array from Query
Posted: Tue Sep 16, 2008 5:42 pm
by Christopher
Code: Select all
echo "ID: " . $query_array['id'] . ", Name: " . $query_array['firstName'] . " " . $query_array['lastName'] . ", Email: " . $query_array['email'];
You should use mysql_fetch_assoc() if you want values like you show.
Re: Fetching Array from Query
Posted: Tue Sep 16, 2008 6:30 pm
by mlarson154
That actually isn't the issue. I tried _assoc and it still doesn't work. The main problem is if there is no match with the database. I'm checking a submitted email address to see if it matches any email addresses in the database. If there is not a match, I want to return different results than if a match is found.
The problem is, if no match is found, I get the text "Resource id #3" as the returned query result rather than just a blank query. When I try to do any fetch_array or _fetch_assoc, the line fails. It cannot resolve with "Resource id #3" as the result.
Here is what I have:
Code: Select all
$query_select = mysql_query("SELECT * FROM newsletter WHERE email=('$email')") or die("Query failed.");
$query_array = mysql_fetch_assoc($query_select) or die("Array failed.");
if(empty($query_array['id'])) {...
Because $query_select is set to "Resource id #3" if no match is found for email in line 1, the mysql_fetch_assoc on line 2 won't work at all, even in trying to return null.
I've even tried to run an if/else statement with:
Code: Select all
if($query_select =="Resource id #3") {...
but it doesn't work.
Re: Fetching Array from Query
Posted: Tue Sep 16, 2008 9:16 pm
by califdon
The value returned by a mysql_query() call is not the data itself, it is a "resource" or "handle" or "pointer" to the data, if such exists. So you cannot test for its value or (I believe) for it being empty. Try testing for ==False.
Re: Fetching Array from Query
Posted: Tue Sep 16, 2008 10:22 pm
by Christopher
Code: Select all
$email = mysql_real_escape_string($email);
$query_select = mysql_query("SELECT * FROM newsletter WHERE email='$email'");
And change those die()'s to if()'s...
Re: Fetching Array from Query
Posted: Wed Sep 17, 2008 12:42 pm
by mlarson154
I've tried to incorporate the if statements you mentioned before. I don't get an error now, but it doesn't resolve to indicating no record was found. Here is my code:
Code: Select all
$email = mysql_real_escape_string($email);
$query_select = mysql_query("SELECT * FROM newsletter WHERE email=('$email')");
$error_msg = mysql_error();
if(! $error_msg)
{
$delete_query = "DELETE FROM newsletter WHERE email=('$email')";
mysql_query($delete_query);
$page_content = "You have been removed from our newlsetter.";
}
else
{
$page_content = "We're sorry, but your email address was not found...";
}
Do you see anything wrong? the if(! $error_msg) part is not working. Even though the email address is not in the database, I still get the $page_content text in the first section ("You have been removed...").
Thanks for you patience with me. If I had any hair on my head, I'd be pulling it out right now.

Re: Fetching Array from Query
Posted: Wed Sep 17, 2008 1:42 pm
by Christopher
See my example. It should be email='$email' not email=('$email'). If you were printing the error message it would have told you where the problem was.
Re: Fetching Array from Query
Posted: Wed Sep 17, 2008 2:05 pm
by mlarson154
I get the same result when removing the parens. (Meaning it doesn't change anything.)
We are running on an IIS server. Would this cause any of the problem? I have seen weird anomalies when writing for this server in comparison to Linux/Unix servers.
Re: Fetching Array from Query
Posted: Wed Sep 17, 2008 2:54 pm
by Christopher
I am assuming that you have printed the SQL being executed and verified that "
SELECT * FROM newsletter WHERE email='foo@bar.com'" actually fetches a record i.e., there is a newsletter table with a email column containing a "
foo@bar.com" value.
Re: Fetching Array from Query
Posted: Wed Sep 17, 2008 3:02 pm
by mlarson154
When there is a record to match the "SELECT" statement, it does grab the content correctly. For instance, if I locate the record, I delete the table row from the DB, and this executes correctly. It is when no match is found that I have a problem. I want to run my separate content if no record is found, but it doesn't work.
I don't know how to "print the SQL" to see what the output is.