hugeness Posted January 20, 2010 Share Posted January 20, 2010 Hello unfortunately having a problem with my first Join for two tables (below) using as a test the following mysql statement: SELECT duplicate.un, upload.pw FROM duplicate, upload WHERE duplicate.un = upload.un i have seeded the tables with the same values in the 'un' field and values in the pw fields to call up.. but the query is still coming up empty.. any ideas what i am doing wrong? thank you! CREATE TABLE IF NOT EXISTS `upload` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, `type` varchar(30) NOT NULL, `size` int(11) NOT NULL, `content` mediumblob NOT NULL, `pw` varchar(50) NOT NULL, `un` varchar(200) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `duplicate` ( `lud` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , `id` int(11) NOT NULL AUTO_INCREMENT , `un` varchar(200) NOT NULL , `pw` varchar(50) NOT NULL , `ttl` varchar(150) NOT NULL , `fn` varchar(250) NOT NULL , `ln` varchar(250) NOT NULL , `tel` varchar(20) NOT NULL , `conteml` varchar(200) NOT NULL , `cnm` varchar(250) NOT NULL , `cyr` year(4) NOT NULL , `add1` varchar(250) NOT NULL, `add2` varchar(250) NOT NULL, `city` varchar(250) NOT NULL, `state` varchar(2) NOT NULL, `zip` varchar(9) NOT NULL, `stel` varchar(20) NOT NULL , `seml` varchar(200) NOT NULL , `usp` varchar(256) NOT NULL , `http` text NOT NULL , `part_note` text NOT NULL , `othf` text NOT NULL , `newRight` text NOT NULL, `ts5` text NOT NULL, `ts7` varchar(250) NOT NULL , `tc1` varchar(100) NOT NULL , `tc2` varchar(100) NOT NULL, `tc3` varchar(100) NOT NULL, `tst1` varchar(2) NOT NULL , `tst2` varchar(2) NOT NULL, `tst3` varchar(2) NOT NULL, `tst4` varchar(2) NOT NULL, `tst5` varchar(2) NOT NULL, `tst6` varchar(2) NOT NULL, `nw` varchar(3) NOT NULL , `pr` varchar(3) NOT NULL , `ps1` varchar(3) NOT NULL , `ps6` varchar(3) NOT NULL, `ps16` varchar(3) NOT NULL, `ps26` varchar(3) NOT NULL, `ps46` varchar(3) NOT NULL, `ps100` varchar(3) NOT NULL, `cent1` tinyint(4) NOT NULL , `cent6` tinyint(4) NOT NULL, `cent16` tinyint(4) NOT NULL, `cent26` tinyint(4) NOT NULL, `cent46` tinyint(4) NOT NULL, `cent100` tinyint(4) NOT NULL, `fos` varchar(3) NOT NULL , `clnt_no` int(11) NOT NULL , `hod_no` int(11) NOT NULL , `more_hod` varchar(3) NOT NULL , `av_bill` int(11) NOT NULL , `clnt_new` int(11) NOT NULL , `fte_no` int(11) NOT NULL , `geek_no` int(11) NOT NULL , `off_yn` varchar(3) NOT NULL , `off_more` varchar(3) NOT NULL , `cre` date NOT NULL, KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/ Share on other sites More sharing options...
akitchin Posted January 20, 2010 Share Posted January 20, 2010 my first guess is that you may have some reserved keywords in there that require backticks, or that your values aren't identical due to the use of VARCHAR for the column types. do you receive any error? Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-998969 Share on other sites More sharing options...
hugeness Posted January 20, 2010 Author Share Posted January 20, 2010 nope, all green on the message front: MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0004 sec ) hmmm.. will check for reserves.. yes i could imagine ' duplicate' could be an issue now you come to mention it! thanks! my first guess is that you may have some reserved keywords in there that require backticks, or that your values aren't identical due to the use of VARCHAR for the column types. do you receive any error? Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-998972 Share on other sites More sharing options...
hugeness Posted January 20, 2010 Author Share Posted January 20, 2010 can't see any reserved keywords from the list here.. http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-1.html Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-998980 Share on other sites More sharing options...
fenway Posted January 20, 2010 Share Posted January 20, 2010 I don't believe there's anything in those tables... prove it. Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-998990 Share on other sites More sharing options...
hugeness Posted January 20, 2010 Author Share Posted January 20, 2010 I don't believe there's anything in those tables... prove it. well theres nothing in the tables i have posted, because i emptied them before posting, but have just been filling with test data, such as putting test in the pw and un fields... so should the code work from what you know? Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-998996 Share on other sites More sharing options...
fenway Posted January 20, 2010 Share Posted January 20, 2010 I mean demonstrate that simple queries work. Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-999003 Share on other sites More sharing options...
hugeness Posted January 20, 2010 Author Share Posted January 20, 2010 I mean demonstrate that simple queries work. they are being populated from 2 seperate forms. INSERT INTO duplicate (un,pw,ttl,fn,ln,tel,conteml,cnm,cyr,add1,add2,city,state,zip,stel,seml,usp,http,othf,newRight,ts5,ts7,tc1,tc2,tc3,tst1,tst2,tst3,tst4,tst5,tst6,nw,pr,ps1,ps6,ps16,ps26,ps46,ps100,cent1,cent6,cent16,cent26,cent46,cent100,fos,clnt_no,hod_no,more_hod,av_bill,clnt_new,fte_no,geek_no,off_yn,off_more,cre) VALUES ( '$un','$pw','$ttl','$fn','$ln','$tel','$conteml','$cnm','$cyr','$add1','$add2','$city','$state','$zip','$stel','$seml','$usp','$http','$othf','$newRight','$ts5','$ts7','$tc1','$tc2','$tc3','$tst1','$tst2','$tst3','$tst4','$tst5','$tst6','$nw','$pr','$ps1','$ps6','$ps16','$ps26','$ps46','$ps100','$cent1','$cent6','$cent16','$cent26','$cent46','$cent100','$fos','$clnt_no','$hod_no','$more_hod','$av_bill','$clnt_new','$fte_no','$geek_no','$off_yn','$off_more','$cre')"; and "INSERT INTO upload (name, size, type, content,pw,un ) ". "VALUES ('$fileName', '$fileSize', '$fileType', '$content','$pw','$un')"; work so the simple stuff works. Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-999005 Share on other sites More sharing options...
akitchin Posted January 20, 2010 Share Posted January 20, 2010 i think what he meant was, show the query and its returned recordset from something simple like "SELECT * FROM duplicate WHERE un='value'" and "SELECT * FROM upload WHERE un='value'". Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-999047 Share on other sites More sharing options...
hugeness Posted January 21, 2010 Author Share Posted January 21, 2010 SELECT * FROM `duplicate` WHERE un = 'jjokoja' LIMIT 0 , 30 Showing rows 0 - 0 (1 total, Query took 0.0005 sec) so that worked fine. but the second table, upload, wont work! very strange! why would a table with the same setup for the same two columns fail a simple select? SELECT * FROM `upload` WHERE un = 'jjokoja' MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0004 sec ) Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-999190 Share on other sites More sharing options...
akitchin Posted January 21, 2010 Share Posted January 21, 2010 have you tried just selecting the whole table? SELECT * from `upload` that should show you. Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-999348 Share on other sites More sharing options...
hugeness Posted January 21, 2010 Author Share Posted January 21, 2010 That actually works, it selects the one row in the table... Showing rows 0 - 0 (1 total, Query took 0.0003 sec) d name type size content pw un Edit Delete 1 ellie2.gif image/gif 10615 [bLOB - 10.4 KiB] truncate truncate Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-999420 Share on other sites More sharing options...
akitchin Posted January 21, 2010 Share Posted January 21, 2010 and did you confirm that the row indeed has a `un` value of 'jjokoja'? Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-999549 Share on other sites More sharing options...
hugeness Posted January 21, 2010 Author Share Posted January 21, 2010 It has a value of 'truncate' now.. but yes, the result is outputted in my last post. Quote Link to comment https://forums.phpfreaks.com/topic/189225-managing-to-botch-my-first-simple-join-in-mysql/#findComment-999550 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.