This is driving my crazy. The culprit table seems to be 'Member_WebSkills'.
When I delete a record using phpMyAdmin a Perl script I have shows the updated data.
When I add the same data back, the script shows the updated data.
If I add a *new* record via phpMyAdmin, the script *doesn't* show the updated data.
I emptied the table, and of course the script broke. I dropped the table, and of course the script broke.
I populated the table from a .sql table dump (see below).
So why won't the updates appear? Does MySQL cache views? Can I flush them somehow? I don't have CL access to the remote server, only phpMyAdmin.
I have 3 tables thus:
mysql> describe Web_Skills; +---------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+---------+----------------+ | SkillID | tinyint(3) unsigned | | PRI | NULL | auto_increment | | Skill | varchar(25) | | | | | +---------+---------------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec) [sorry had to paste that as a quote] # # Table structure for table `Web_Skills` #
CREATE TABLE `Web_Skills` ( `SkillID` tinyint(3) unsigned NOT NULL auto_increment, `Skill` varchar(25) NOT NULL default '', PRIMARY KEY (`SkillID`) ) TYPE=MyISAM AUTO_INCREMENT=18 ;
# # Dumping data for table `Web_Skills` #
INSERT INTO `Web_Skills` VALUES (1, 'CSS'); INSERT INTO `Web_Skills` VALUES (2, 'Graphics'); INSERT INTO `Web_Skills` VALUES (3, 'HTML'); INSERT INTO `Web_Skills` VALUES (4, 'JavaScript'); INSERT INTO `Web_Skills` VALUES (5, 'Java'); INSERT INTO `Web_Skills` VALUES (6, 'Multimedia'); INSERT INTO `Web_Skills` VALUES (7, 'XHTML'); INSERT INTO `Web_Skills` VALUES (8, 'XML'); INSERT INTO `Web_Skills` VALUES (9, 'XSL'); INSERT INTO `Web_Skills` VALUES (10, 'PHP'); INSERT INTO `Web_Skills` VALUES (11, 'Cold Fusion'); INSERT INTO `Web_Skills` VALUES (12, 'Perl - CGI'); INSERT INTO `Web_Skills` VALUES (13, 'ASP'); INSERT INTO `Web_Skills` VALUES (14, 'C'); INSERT INTO `Web_Skills` VALUES (15, 'C++'); INSERT INTO `Web_Skills` VALUES (16, 'Python'); INSERT INTO `Web_Skills` VALUES (17, 'Visual Basic');
mysql> describe Member_WebSkills; +----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | MemberID | tinyint(3) unsigned | | | 0 | | | SkillID | tinyint(3) unsigned | | | 0 | | +----------+---------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) # # Table structure for table `Member_WebSkills` #
CREATE TABLE `Member_WebSkills` ( `MemberID` tinyint(3) unsigned NOT NULL default '0', `SkillID` tinyint(3) unsigned NOT NULL default '0' ) TYPE=MyISAM COMMENT='Map Members to Web Skills';
# # Dumping data for table `Member_WebSkills` #
INSERT INTO `Member_WebSkills` VALUES (4, 3); INSERT INTO `Member_WebSkills` VALUES (4, 1); INSERT INTO `Member_WebSkills` VALUES (4, 4); INSERT INTO `Member_WebSkills` VALUES (13, 3); INSERT INTO `Member_WebSkills` VALUES (11, 1); INSERT INTO `Member_WebSkills` VALUES (11, 2); INSERT INTO `Member_WebSkills` VALUES (11, 3); INSERT INTO `Member_WebSkills` VALUES (11, 4); INSERT INTO `Member_WebSkills` VALUES (11, 7); INSERT INTO `Member_WebSkills` VALUES (3, 4); INSERT INTO `Member_WebSkills` VALUES (3, 3); INSERT INTO `Member_WebSkills` VALUES (12, 2); INSERT INTO `Member_WebSkills` VALUES (12, 3); INSERT INTO `Member_WebSkills` VALUES (10, 1); INSERT INTO `Member_WebSkills` VALUES (10, 3); INSERT INTO `Member_WebSkills` VALUES (10, 4); INSERT INTO `Member_WebSkills` VALUES (10, 8); INSERT INTO `Member_WebSkills` VALUES (1, 1); INSERT INTO `Member_WebSkills` VALUES (1, 3); INSERT INTO `Member_WebSkills` VALUES (1, 4); INSERT INTO `Member_WebSkills` VALUES (1, 8); INSERT INTO `Member_WebSkills` VALUES (6, 0); # these last *added* rows don't appear when # run from the script INSERT INTO `Member_WebSkills` VALUES (9, 0); INSERT INTO `Member_WebSkills` VALUES (1, 10); INSERT INTO `Member_WebSkills` VALUES (1, 12); INSERT INTO `Member_WebSkills` VALUES (1, 16); INSERT INTO `Member_WebSkills` VALUES (5, 0);
mysql> describe nbit_members; +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | MemID | int(10) unsigned | | PRI | NULL | auto_increment | | FirstName | varchar(20) | | | | | | LastName | varchar(30) | | | | | | Email | varchar(70) | | | | | | BusName | varchar(60) | YES | | NULL | | | URL | varchar(60) | YES | | NULL | | | Phone | varchar(15) | | | | | | Notify | char(1) | | | Y | | | Visible | char(1) | | | Y | | +-----------+------------------+------+-----+---------+----------------+ 9 rows in set (0.02 sec)
The query:
$sth3=$dbh->prepare(qq{SELECT Lastname,
Web_Skills.SkillID, Member_WebSkills.SkillID
FROM NBIT_Members,Member_WebSkills,Web_Skills
WHERE Member_WebSkills.MemberID=NBIT_Members.MemID
AND Member_WebSkills.SkillID = $ThisSkillID2
OR Member_WebSkills.SkillID = 0
AND Visible='Y'
GROUP BY Lastname
});-- /* All outgoing email scanned by AVG Antivirus /* Amer Neely, Softouch Information Services Home of Spam Catcher & Research Central W: www.softouch.on.ca E: trudge at softouch dot on dot ca Perl | PHP | MySQL | CGI programming for all data entry forms. "We make web sites work!"
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
