Fetching Array from Query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

mlarson154
Forum Newbie
Posts: 15
Joined: Tue Apr 11, 2006 3:00 pm

Fetching Array from Query

Post 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?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Fetching Array from Query

Post by s.dot »

try doing echo mysql_num_rows($query_select) before you fetch the array. Perhaps the query is returning 0 rows.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
mlarson154
Forum Newbie
Posts: 15
Joined: Tue Apr 11, 2006 3:00 pm

Re: Fetching Array from Query

Post by mlarson154 »

mysql_num_rows also fails. Does that indicate that no rows have been returned?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Fetching Array from Query

Post 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
}
(#10850)
mlarson154
Forum Newbie
Posts: 15
Joined: Tue Apr 11, 2006 3:00 pm

Re: Fetching Array from Query

Post 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.
mlarson154
Forum Newbie
Posts: 15
Joined: Tue Apr 11, 2006 3:00 pm

Re: Fetching Array from Query

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Fetching Array from Query

Post 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.
(#10850)
mlarson154
Forum Newbie
Posts: 15
Joined: Tue Apr 11, 2006 3:00 pm

Re: Fetching Array from Query

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Fetching Array from Query

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Fetching Array from Query

Post 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...
(#10850)
mlarson154
Forum Newbie
Posts: 15
Joined: Tue Apr 11, 2006 3:00 pm

Re: Fetching Array from Query

Post 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. :)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Fetching Array from Query

Post 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.
(#10850)
mlarson154
Forum Newbie
Posts: 15
Joined: Tue Apr 11, 2006 3:00 pm

Re: Fetching Array from Query

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Fetching Array from Query

Post 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.
(#10850)
mlarson154
Forum Newbie
Posts: 15
Joined: Tue Apr 11, 2006 3:00 pm

Re: Fetching Array from Query

Post 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.
Post Reply