Query to search for value in the Database

2

I want to display the results obtained in a form but when saving to DB I did not save as null but saved like this:

Date: 0000-00-00 E Number: 0

Query looks like this:

$sql = mysql_query ("Select *, Count(*) from tabela1, tabela2 where campo1 = 0 and campo2= '0000-00-00' and campo3 is Null and campo4......'")

But he is not showing me the result. I have something wrong with the code?

    
asked by anonymous 07.02.2014 / 10:36

3 answers

5

In your question, you are giving the indication that the difficulty is in the operation of inserting new rows in the database. After that, you have a query to collect results and give indication that the query is not working as intended, we will analyze:

  • How to create the table so that things work as intended;
  • Perform data insertion;
  • Collect existing records.


Create the table

Since you want the columns named campo1 and campo2 to be null by default, you have to give this indication in the table itself:

As you can see, we are indicating that the default value for column campo1 and campo2 is null .

The code for creating the table would be:

CREATE TABLE IF NOT EXISTS 'tabela1' (
  'id' int(13) NOT NULL AUTO_INCREMENT,
  'campo1' int(13) DEFAULT NULL,
  'campo2' date DEFAULT NULL,
  PRIMARY KEY ('id')
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


Enter results

With the table properly prepared to accept null in columns campo1 and campo2 , the insert query would be:

INSERT INTO 'tabela1' ( 'id' , 'campo1' , 'campo2' )
VALUES (NULL , NULL , NULL);

This will insert into the table a line type:


Conduct inquiries

Now that the table is in the desired form, queries looking for null registrations can already be performed.

Query equal to the one you have in the question, that is, the column campo1 equal to 0 and column campo2 equal to null or equal to 0000-00-00 :

SELECT *
FROM 'tabela1'
WHERE 'campo1' =0
AND (
'campo2' IS NULL
OR 'campo2' = '0000-00-00'
)

Which returns us exactly two records where both are with the value in the column campo1 equal to 0 and the value of column campo2 equal to NULL in ID 1 and equal to 0000-00-00 in ID 3 :

    
07.02.2014 / 13:08
1

I've already figured it out. Although it appears in the database 0000-00-00 on the date I put it like this: Thanks to whoever helped me:)

include("conectar.php");
$sql = mysql_query("SELECT * FROM tb_trabalhador WHERE AlvaraNumero = 0 
 AND(AlvaraValidade='' or AlvaraValidade is Null or AlvaraValidade='0000-00-00')
 AND (AlvaraAnexo='' or AlvaraAnexo is Null)
 AND AcidenteNumero = 0
 AND (AcidenteValidade='' or AcidenteValidade is Null or AcidenteValidade='0000-00-00')    
 AND (AcidenteAnexo='' or AcidenteAnexo is Null)
 AND SeguroNumero = 0
 AND (SeguroValidade='' or SeguroValidade is Null or SeguroValidade='0000-00-00' )
 AND (SeguroAnexo='' or SeguroAnexo is Null)
 AND InstaladorNumero = 0
 AND (InstaladorValidade='' or InstaladorValidade is Null or InstaladorValidade='0000-00-00')
 AND (InstaladorAnexo='' or InstaladorAnexo is Null) " ) or DIE( mysql_error());

 while($exibe = mysql_fetch_array($sql)){ 
    
07.02.2014 / 13:18
0

Name the tables to show which table the field you are going to go to on the "WHERE".

Ex:

$sql = mysql_query ("SELECT *, COUNT(*) FROM tabela1 AS tbl1, tabela2 AS tbl2 WHERE tbl1.campo1 = 0 and tbl2.campo2= '0000-00-00' and tbl1.campo3 is Null and tbl2.campo4......'");
    
07.02.2014 / 11:30