frobak Posted February 13, 2012 Share Posted February 13, 2012 Does a TINYINT value of 0 equal NULL when selecting from the cell? I though it would equal false. A little background: I am searching a table for selected checkboxes and then comparing that with posted php data: $sql = "SELECT username FROM candidate_details WHERE acca= '$_POST[acca]' OR msc_bp_mgmt = '$_POST[msc_bp_mgmt]'OR So I want the username pulled out if the cells match. If the checkbox on the form submitted was not selected, '$_POST[acca]' would be null. And then when i compare that to the candidate_details table cell, which has a value of '0' - (zero) - it is still coming up as a match? Is this right? Or is there another data type that actually enters TRUE or FALSE from a checkbox selection? help much appreciated Cheers Quote Link to comment Share on other sites More sharing options...
kicken Posted February 13, 2012 Share Posted February 13, 2012 A variable being NULL in PHP does not mean it is compared to nulls in the sql query. When you sub the variable into your query it will be the empty string so you'd have your query like: WHERE acca = '' I'm not sure if mysql considers the empty string an 0 to be equal or not. What do you want to happen if no checkbox is checked? No results? You likely need to just do a test in PHP and modify your query accordingly. if (!isset($_POST['acca'])){ do something for when checked} else { do something for when not checked. } Quote Link to comment Share on other sites More sharing options...
frobak Posted February 13, 2012 Author Share Posted February 13, 2012 Basically, this is what i want to happen: The posted data is coming from a job that is being posted, so there will be lots of checkboxes with requirements Im then going to check if the candidate has the required skills If there are matching skills, fire off an email to the candidate telling them about the job. Theres 230 checkboxes :'( Quote Link to comment Share on other sites More sharing options...
kicken Posted February 13, 2012 Share Posted February 13, 2012 So I assume you want to do a dbField=1 for which ever ones are checked, and just ignore those that are unchecked? In your PHP you'll have to create all the conditions for the ones that are checked. eg $cond = array(); foreach ($_POST['checkboxes'] as $val){ $cond[] = $val.'=1'; } $sql = '... WHERE '.implode(' AND ', $cond); Of course, you'll want to ensure you prevent against sql injection. Probably use some sort of map array to map the checkboxes on the page to the DB fields they correspond to. Quote Link to comment Share on other sites More sharing options...
frobak Posted February 13, 2012 Author Share Posted February 13, 2012 Kicken, thanks for your help, but that last post went way over my head. Think ill need to do some reading on map arrays etc Quote Link to comment Share on other sites More sharing options...
mikosiko Posted February 13, 2012 Share Posted February 13, 2012 regarding to your original question: Does a TINYINT value of 0 equal NULL when selecting from the cell? I though it would equal false. A little background: I am searching a table for selected checkboxes and then comparing that with posted php data: Code: [select] $sql = "SELECT username FROM candidate_details WHERE acca= '$_POST[acca]' OR msc_bp_mgmt = '$_POST[msc_bp_mgmt]'OR So I want the username pulled out if the cells match. If the checkbox on the form submitted was not selected, '$_POST[acca]' would be null. ..... this may help you to understand why is that happening http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html for testing purposes check what this select return to you SELECT 1 * ''; Quote Link to comment Share on other sites More sharing options...
frobak Posted February 13, 2012 Author Share Posted February 13, 2012 My plan is completely flawed anyway. If i check a column that is 0, and the posted data is 0 also, then it will match, and i only want to pull usernames where there is a match Back to the drawign board i think Quote Link to comment Share on other sites More sharing options...
fenway Posted February 13, 2012 Share Posted February 13, 2012 You know, you can submit null, too. Quote Link to comment Share on other sites More sharing options...
frobak Posted February 13, 2012 Author Share Posted February 13, 2012 In essence what i need to do is compare the 2 tables, which are identical apart from the id and table name. And i want to output the colunmns that match. the columns have a value of either '1' or '0' Is there a way to do this? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 13, 2012 Share Posted February 13, 2012 "all" the columns that match? That's not a job for a database. Quote Link to comment Share on other sites More sharing options...
frobak Posted February 13, 2012 Author Share Posted February 13, 2012 well, i just need to find out if there are any cells that match in the 2 tables Quote Link to comment Share on other sites More sharing options...
fenway Posted February 13, 2012 Share Posted February 13, 2012 Any is different than all. Please give us a real example. Quote Link to comment Share on other sites More sharing options...
frobak Posted February 13, 2012 Author Share Posted February 13, 2012 ok Candidates table (cut down version - there are 200 + columns) CREATE TABLE candidate_details ( candidate_details_id INT(20), acca TINYINT(1), msc_bp_mgmt TINYINT(1), adv_dip_p_mgmt TINYINT(1), cert_pay_prac TINYINT(1), cima TINYINT(1), dip_pay_sup TINYINT(1), cipd_qual TINYINT(1), prac_cert_pay TINYINT(1), cipp_deg_pm TINYINT(1), adv_prac_cert TINYINT(1), username VARCHAR(50)); vacancy_details table CREATE TABLE vacancy_details ( vacancy_details_id INT(20), acca TINYINT(1), msc_bp_mgmt TINYINT(1), adv_dip_p_mgmt TINYINT(1), cert_pay_prac TINYINT(1), cima TINYINT(1), dip_pay_sup TINYINT(1), cipd_qual TINYINT(1), prac_cert_pay TINYINT(1), cipp_deg_pm TINYINT(1), adv_prac_cert TINYINT(1), vacancy_id INT(50)); Now, the recruiter will select what skills they require for the job via check boxes, these are then entered into the database as a '1' for selected, and a '0' for not selected. The candidate will sign up and select their skills, which will be updated into the database with a '1' for selected, and a '0' for not selected I need to compare these tables, as long as there is just 1 matching column, I will fire off an email to the candidate So i just need to know if there is a matching column Need any more info? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 13, 2012 Share Posted February 13, 2012 That's not pretty. I suppose you can treat the entire set of requirements -- binary -- as flags of a bitmask, and then just binary "OR" them together. Quote Link to comment Share on other sites More sharing options...
frobak Posted February 13, 2012 Author Share Posted February 13, 2012 i give up Quote Link to comment Share on other sites More sharing options...
fenway Posted February 13, 2012 Share Posted February 13, 2012 i give up Well, like I said, this isn't what a database is for. But the bitmask method well work -- why give up/ Quote Link to comment Share on other sites More sharing options...
frobak Posted February 13, 2012 Author Share Posted February 13, 2012 I'm at a loss thats all! Ok, so you say that isnt what a databse is for? but i need to store this data, and that has to be in a database, all i need to do is compare the columns to see if any match. To be honest, and as you can tell im no expert, but i thought i would just be able to join the tables and see if the columns match? The bitmask method is something i dont understand yet, and theres a deadline, like tomorrow, so no time to learn what it is and then deploy it in this project Quote Link to comment Share on other sites More sharing options...
fenway Posted February 13, 2012 Share Posted February 13, 2012 If you have a deadline, then just hard-code a bunch of ORs. Quote Link to comment Share on other sites More sharing options...
frobak Posted February 13, 2012 Author Share Posted February 13, 2012 right, ok. So can you please give me a brief example of what you mean by that? Quote Link to comment Share on other sites More sharing options...
frobak Posted February 13, 2012 Author Share Posted February 13, 2012 This is what i have?!?!!? $sql3 = "SELECT candidate_details.username FROM vacancy_details, candidate_details WHERE vacancy_details.vacancy_id = '$_POST[vacancy_id]' AND (vacancy_details.acca=candidate_details.acca OR vacancy_details.msc_bp_mgmt=candidate_details.msc_bp_mgmt OR vacancy_details.ManualCalculations=candidate_details.ManualCalculations OR vacancy_details.PayrollImplementation=candidate_details.PayrollImplementation OR vacancy_details.WorkingTimeRegulations=candidate_details.WorkingTimeRegulations OR vacancy_details.Companycarsandvans=candidate_details.Companycarsandvans OR vacancy_details.PensionAdministration=candidate_details.PensionAdministration OR vacancy_details.InsolvencyandclosingdownaPAYEscheme=candidate_details.InsolvencyandclosingdownaPAYEscheme OR vacancy_details.ADPSurepay=candidate_details.ADPSurepay OR vacancy_details.Moorepay=candidate_details.Moorepay OR vacancy_details.SunAccounting=candidate_details.SunAccounting OR vacancy_details.SageKCS=candidate_details.SageKCS OR vacancy_details.ADPStreamline=candidate_details.ADPStreamline OR vacancy_details.MidlandDelphi=candidate_details.MidlandDelphi OR vacancy_details.Star=candidate_details.Star OR vacancy_details.OpenDoor=candidate_details.OpenDoor OR vacancy_details.Tempaid=candidate_details.Tempaid OR vacancy_details.ADPFreedom=candidate_details.ADPFreedom OR vacancy_details.ADPGlobalview=candidate_details.ADPGlobalview OR vacancy_details.Payrite=candidate_details.Payrite OR vacancy_details.Tempest=candidate_details.Tempest OR vacancy_details.Agresso=candidate_details.Agresso OR vacancy_details.CeridianSource=candidate_details.CeridianSource OR vacancy_details.Peoplesoft=candidate_details.Peoplesoft OR vacancy_details.Trent=candidate_details.Trent OR vacancy_details.Chris21=candidate_details.Chris21 OR vacancy_details.PSEnterprise=candidate_details.PSEnterprise OR vacancy_details.Unipay54=candidate_details.Unipay54 OR vacancy_details.Excel=candidate_details.Excel OR vacancy_details.Resourcelink=candidate_details.Resourcelink OR vacancy_details.Oracle=candidate_details.Oracle OR vacancy_details.Inhouse=candidate_details.Inhouse OR vacancy_details.Sage=candidate_details.Sage OR vacancy_details.PS2000=candidate_details.PS2000 OR vacancy_details.Cintra=candidate_details.Cintra OR vacancy_details.PegasusOpera=candidate_details.PegasusOpera OR vacancy_details.Intex=candidate_details.Intex OR vacancy_details.iTrent=candidate_details.iTrent OR vacancy_details.SAP=candidate_details.SAP OR vacancy_details.Accountancy=candidate_details.Accountancy OR vacancy_details.Housing=candidate_details.Housing OR vacancy_details.Recruitment=candidate_details.Recruitment OR vacancy_details.Advertising=candidate_details.Advertising OR vacancy_details.Insurance=candidate_details.Insurance OR vacancy_details.Retail=candidate_details.Retail OR vacancy_details.Banking=candidate_details.Banking OR vacancy_details.IT=candidate_details.IT OR vacancy_details.Shipping=candidate_details.Shipping OR vacancy_details.BuildersMerchants=candidate_details.BuildersMerchants OR vacancy_details.LeisureIndustry=candidate_details.LeisureIndustry OR vacancy_details.Software=candidate_details.Software OR vacancy_details.Bureaux=candidate_details.Bureaux OR vacancy_details.LocalGovernment=candidate_details.LocalGovernment OR vacancy_details.Solicitors=candidate_details.Solicitors OR vacancy_details.Charity=candidate_details.Charity OR vacancy_details.ManagementConsultancy=candidate_details.ManagementConsultancy OR vacancy_details.TravelTourism=candidate_details.TravelTourism OR vacancy_details.Cleaning=candidate_details.Cleaning OR vacancy_details.Manufacturing=candidate_details.Manufacturing OR vacancy_details.Transport=candidate_details.Transport OR vacancy_details.Construction=candidate_details.Construction OR vacancy_details.Media=candidate_details.Media OR vacancy_details.Wholesalers=candidate_details.Wholesalers OR vacancy_details.Education=candidate_details.Education OR vacancy_details.NHS=candidate_details.NHS OR vacancy_details.Utilities=candidate_details.Utilities OR vacancy_details.Engineering=candidate_details.Engineering OR vacancy_details.Petroleum=candidate_details.Petroleum OR vacancy_details.EstateAgency=candidate_details.EstateAgency OR vacancy_details.Pharmaceuticals=candidate_details.Pharmaceuticals OR vacancy_details.Finance=candidate_details.Finance OR vacancy_details.PrivateMedical=candidate_details.PrivateMedical OR vacancy_details.Hotels=candidate_details.Hotels OR vacancy_details.Publishers=candidate_details.Publishers)"; $result3 = @mysql_query($sql3,$connection) or die(mysql_error()); while($row = mysql_fetch_array($result3)) { echo $row['username']; echo "<br />"; } } Quote Link to comment Share on other sites More sharing options...
fenway Posted February 13, 2012 Share Posted February 13, 2012 OK -- so what doesn't work? Quote Link to comment Share on other sites More sharing options...
frobak Posted February 13, 2012 Author Share Posted February 13, 2012 its returning all usernames from the candidates table, if the option is selected or not Quote Link to comment Share on other sites More sharing options...
fenway Posted February 20, 2012 Share Posted February 20, 2012 Ah yes -- that's because you're checking an equality, and "unset" === "unset". You'd have to add a "field = 1" to each one. Quote Link to comment 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.