New to php-function from multiple mysql queries

[smkncr6]

Hey all I'm new to mysql php, I have a query consisting of multiple queries it doesn't have to be a function but here it is in a nutshell.

 

I have one table I populate, I use this table to fill in all my form drop downs etc... the problem I am having is I need to return only rows that are NOT NULL. Have not been able to figure it out, so instead I was forced to create multiple queries which in my opinion is redundant. I have included the mysql queries, I don't mind using a function but if you have better suggestions, any help would be greatly appreciated.

 

$query_options1 = "SELECT * FROM tblFormFields where fld_Sex is NOT NULL";

$options1 = mysql_query($query_options1) or die(mysql_error());

$row_options1 = mysql_fetch_assoc($options1);

 

$query_options2 = "SELECT * FROM tblFormFields where fld_Month is NOT NULL";

$options2 = mysql_query($query_options2) or die(mysql_error());

$row_options2 = mysql_fetch_assoc($options2);

 

$query_options3 = "SELECT * FROM tblFormFields where fld_Day is NOT NULL";

$options3 = mysql_query($query_options3) or die(mysql_error());

$row_options3 = mysql_fetch_assoc($options3);

 

$query_options4 = "SELECT * FROM tblFormFields where fld_Year is NOT NULL order by fld_Year desc";

$options4 = mysql_query($query_options4) or die(mysql_error());

$row_options4 = mysql_fetch_assoc($options4);

 

Thank you in advance for your help.

[Muddy_Funster]

SELECT * FROM tblFormFields where (fld_Sex is NOT NULL) AND (fld_Month is NOT NULL) AND (fld_Day is NOT NULL) AND (fld_Year is NOT NULL) order by fld_Year desc

should do it.

[PFMaBiSmAd]

You should not even have a table that is laid out like a spreadsheet where you have different columns for each field type with null values in the unused choices. What happens when you need to add more form field types to the design? You need to alter your table and you need to alter all your queries that reference that table because there are now more column names to reference.

 

You should have one row for each possible form field, with a type column (sex, month, day, year) and a value column. The query would just be -

SELECT * FROM tblFormFields ORDER BY type, value

 

If you don't want to output the fields in the actual form in the order that ordering by the type (asc or desc) would result in, you can either add an `order` field to the table so that you can attach a specific ordering to the fields or you can do this in your php code by processing the rows in the result set and store them in an array with the type as the key, then iterate over that array in the order that you want the form fields to be in the actual form.

[PFMaBiSmAd]

Edit to the above: you can also order by the type using -

 

ORDER BY FIELD(type,'sex', 'month', 'day', 'year')

 

to get the fields in the result set to be in the order that you want them for the form. This would actually result in the simplest php code because you can simply iterate over all the rows and detect the change in the type to close out the previous form section and start the next section.

[smkncr6]

so would it be best practice to create one table for each form field

[Muddy_Funster]

Best practice is to propely normalise your data before creating the structure to store it.

[smkncr6]

thanks for the input so far has been a tremendous help. I have restructured my data so that it reads as follows

id_Field, Field_Type, Field_Title, Field_Content. The problem I am running into now is retreiving data from Field_Content WHERE Field_Type = 1. I know how to do this in a Select statement but is it possible to do this within this code

 

 

while ($row_options1 = mysql_fetch_assoc($options1));

  $rows = mysql_num_rows($options1);

  if($rows = 0) {

      mysql_data_seek($options, 0);

  $row_options1 = mysql_fetch_assoc($options1);

  }

[Muddy_Funster]

I have no idea what that code is trying to do.

 

If you only want to display the field content where the field type is 1, but still for some reason retrieve a full record set from the database, then you can use an if condition on what to display:

while ($row = mysql_fetch_array($result)){
  if ($row['Field_Type'] == 1){
    echo $row['Field_Content'];
  }
}

[Morg.]

thanks for the input so far has been a tremendous help. I have restructured my data so that it reads as follows

id_Field, Field_Type, Field_Title, Field_Content. The problem I am running into now is retreiving data from Field_Content WHERE Field_Type = 1. I know how to do this in a Select statement but is it possible to do this

Well I don't know what you're attempting to do but this sounds fishy anyway

Whatever the purpose, you should not be storing field_whatever data in SQL when you can have much simpler more maintainable solutions.