Jump to content

Does TinyInt '0' equal NULL?


frobak

Recommended Posts

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

Link to comment
Share on other sites

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. }

 

Link to comment
Share on other sites

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  :'(

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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 * '';

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 />";
  }

}

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.