SELECT query not working

TobyRT

Hi,

In my database of members there is the option to change whether or not they receive the "jobs" notices we receive. This is done with a drop down menu.

This is successfully updating the database, but for some reason the query I'm using below doesn't work - if I have 4 members, all of which have "Yes" as their answer, I'm only getting 3 emails listed. Even if I "update" them to 'no' and back again, it's still not listing certain emails.

Any ideas what's going on? Here's my select code:

if ($_GET['action'] == 'jobs'){

$result = $connector->query("SELECT email FROM members WHERE jobslist = 'Yes'");
		$row = $connector->fetchArray($result);

	while ($row = $connector->fetchArray($result)){
echo $row['email'].';&nbsp';
}
}

Here's the code for the drop down

<tr>
            <td align="left" valign="middle">Add to Jobs List?</td>
            <td align="left"><SELECT NAME=jobslist id="jobslist" align=top>
              <OPTION value="<?php echo $row['jobslist'] ?>"><?php echo $row['jobslist'] ?>
		  <OPTION value=Yes>Yes
		  <OPTION value=No>No
			  </select>
		</td>
          </tr>

trex005

is it possible that the missing one has a space in front of the YES if the field is a string?

Could you use an enum?

TobyRT

I wondered that, but that entry was actually working OK, I then deleted a couple of entries and that one stopped appearing on the list.

Very bizarre.

I'll look into the enum thing (I've heard of it but don't really know what it does!)

TobyRT

Just altered the table with enum ('Yes','No') but it's still not working 🙁

I've uploaded the page so you can see what happens.

Both myself and Doug are set to 'Yes' for receiving the email, but only Doug appears.

trex005

can you show us a dump of said table?

TobyRT

Here's the dump...

You'll notice the date fields (dob, join & exp) are not 'nix date stamps - I haven't got as far as learning all that syntax yet, but it's on my 'to learn' list 🙂

I've just added another member - somewhere along the line it seems the ID isn't transferring properly - I edit the setting for Toby, but Doug's email appears.

Methinks it's time to not use the back button

phpMyAdmin MySQL-Dump

version 2.2.0rc3

http://phpwizard.net/phpMyAdmin/

http://phpmyadmin.sourceforge.net/ (download page)

Host: localhost

Generation Time: July 31, 2005, 4:43 pm

Server version: 4.2

PHP Version: 4.3.10

Database : thesypuk

--------------------------------------------------------

Table structure for table 'members'

CREATE TABLE members (
ID int(6) unsigned NOT NULL auto_increment,
firstname varchar(25),
surname varchar(40),
dobmonth varchar(10),
dobday int(2),
dobyear int(4),
address1 varchar(50),
address2 varchar(50),
address3 varchar(50),
postcode varchar(10),
email varchar(50),
company varchar(100),
jobrole varchar(100),
jobslist enum('Yes','No'),
eventslist varchar(4),
london varchar(8),
joinmonth varchar(10),
joinday tinyint(2),
joinyear int(6),
expmonth varchar(10),
expday tinyint(2),
expyear int(6),
memnum int(4),
PRIMARY KEY (ID)
);

Dumping data for table 'members'

INSERT INTO members VALUES (16,'Toby','Rhind-Tutt','April',5,1968,'123 Clifton Court','St John\'s Wood','London','NW8 8JA','trhind-tutt@randomhouse.co.uk','Random House','IT Analyst','Yes','Yes','London','May',6,1965,'June',7,1967,879);
INSERT INTO members VALUES (14,'Doug','Wallace','May',8,2003,'address1','address','address','postcode','doug@gmail.com','Taxbriefs','Editor','Yes','Yes','London','May',6,2004,'September',10,2003,456);

trex005

It's working for me :-/

TobyRT

How very weird.

Wonder if I've got something stuck in my cache here?

TobyRT

Nope - still not working for me - I edited my email address in there and Doug's appeared!!

When you say it worked for you, did you have 3 email addresses showing?

Any other ideas? This is driving me nuts now. I think somewhere along the line the ID which is passing is getting rather confused.

The whole thing is running in a php_self if() loop. I'm wondering if perhaps putting the "update" script on a seperate page might be better?

toplay

Well, we don't see the code for the query() and fetchArray() methods within your class (where you use $connector). Could there be a problem with these? Like maybe the fetcharray() is not returning all the values properly.

trex005

TobyRT wrote:
When you say it worked for you, did you have 3 email addresses showing?

There were only 2 in the dump you gave me

TobyRT

Oh yeah, so you would only have had two.

It seems to be going wrong somewhere in the "update" part of the script - i think perhaps the browser is caching the IDs or something.

I'm just about to post the rest of my code

TobyRT

Click here for the full code - it's pretty ugly though - sorry!

I don't think there's anything wrong with the array because it's been pulling results through fine without a problem for ages.