checking multiple tables have entry not empty

[zimmo]

I have a system which is working well, but  I need to do one final check. I have 5 tables with different information in. Each table should have some information entered from a form, now I need to check that all tables have had data entered to return true, if any return false then it does not allow them to publish. Just like a check to make sure all information is filled in.

 

I have never done this so dont know the correct way.

 

$sql = "SELECT * FROM table_b_details, table_c_info, table_d_tickets, table_e_bank, table_f_publish WHERE fishery_id = '$_SESSION[fishery_id]' AND table_b_details.name = 'NOT NULL' etc.. "; 

 

I know this is wrong, well I think it is, not sure of the correct way to query different data in each table?

[Mchl]

You should've declared columns not to accept NULL values...

[zimmo]

Okay, will check that out, but then how do I still do the query? Basically I need to check all tables to make sure the REQUIRED info is in there and if so allow them to publish from the form.

[zimmo]

I have tried the following but it is giving me an error. If anyone can help as never done this before.

 

$sql = "SELECT * FROM table_b_details, table_c_info, table_d_tickets, table_e_bank, table_f_publish WHERE fishery_id = '$_SESSION[fishery_id]' AND complete = 'YES' "; 
$sql_result = mysql_query($sql); 
if (mysql_num_rows($sql_result) ==0)

[Mchl]

'an error' is not something we can help you with. Post the entire error message.

[zimmo]

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/file.html on line 67

 

[Mchl]

This means your query failed somehow.

 

Change

$sql_result = mysql_query($sql); 

 

to

$sql_result = mysql_query($sql) or die(mysql_error().": $sql"); 

 

and post what error message it shows then.

[zimmo]

Column 'complete' in field list is ambiguous: SELECT complete FROM table_b_details, table_c_info, table_d_tickets, table_e_bank, table_f_publish WHERE fishery_id = '1' AND complete = 'YES'

[Mchl]

Oh.... you probably want this:

(SELECT complete FROM table_b_details WHERE fishery_id = '1' AND complete = 'YES')
UNION
(SELECT complete FROM table_c_info WHERE fishery_id = '1' AND complete = 'YES')
UNION
(SELECT complete FROM table_d_tickets WHERE fishery_id = '1' AND complete = 'YES')
UNION
(SELECT complete FROM table_e_bank WHERE fishery_id = '1' AND complete = 'YES')
UNION
(SELECT complete FROM table_f_publish WHERE fishery_id = '1' AND complete = 'YES')

[zimmo]

It is not throwing up any errors, but it is saying that all of the tables have complete = yes when one of them does not?

[Mchl]

How many rows did you get?

[zimmo]

I did an echo for the sql result and it say resource id 4?

[zimmo]

sorry, I did the following and this is what i get:

$sql_result = mysql_query($sql) or die(mysql_error().": $sql");
$count = mysql_num_rows($sql_result);
{
  echo ($count);
}

 

The result is 1

[Mchl]

Which means there's one row in these tables that matches your criteria. Is there something else you need to know?

[zimmo]

I have slightly changed things, and better explanation at the end. I have created a new table for the terms section so now I want it to query all 5 tables to look for the fishery_id and complete ='YES" it needs to return that all are true, in other words that each of these tables has YES in the complete field. It is not doing that.

 

Example in 4 of the tables they all have complete='YES" the last table does not as it has had no information entered, so it should return as not valid as their is no row in that table, the same can apply for any of the others, there is no particular order that the tables are filled in. It is a form system and they can switch between different tables. hope it makes sense.

 

So here is my new query:

$sql = "
(SELECT complete FROM table_b_details WHERE fishery_id = '$_SESSION[fishery_id]' AND complete = 'YES')
UNION
(SELECT complete FROM table_c_info WHERE fishery_id = '$_SESSION[fishery_id]' AND complete = 'YES')
UNION
(SELECT complete FROM table_d_tickets WHERE fishery_id = '$_SESSION[fishery_id]' AND complete = 'YES')
UNION
(SELECT complete FROM table_e_bank WHERE fishery_id = '$_SESSION[fishery_id]' AND complete = 'YES')
UNION
(SELECT complete FROM table_f_terms WHERE fishery_id = '$_SESSION[fishery_id]' AND complete = 'YES')
";
$sql_result = mysql_query($sql) or die(mysql_error().": $sql");
$count = mysql_num_rows($sql_result);
{
  echo ($count);
}

 

Based on the above query it is returning the result as 1 but if one of the selects does not have complete ='yes' it still returns as valid??