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 ; 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? 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? 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 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. 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? 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. 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. 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'". 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 ) 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. 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 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'? 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. 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
Archived
This topic is now archived and is closed to further replies.