Quickie about a foreach statement?

[Kristoff1875]

Currently i've got this code:

	while($row = mysql_fetch_array($result, MYSQL_ASSOC))
		{
			if (!empty($row['OneDel1'])){ $OneDel1 == 1; }
		}

How would I go about replacing the if statement with a foreach statement that cycles through a number of columns from the database? Basically a shorter way of:

	while($row = mysql_fetch_array($result, MYSQL_ASSOC))
		{
			if (!empty($row['OneDel1'])){ $OneDel1 == 1; }
			if (!empty($row['OneDel2'])){ $OneDel2 == 1; }
			if (!empty($row['OneDel3'])){ $OneDel3 == 1; }
			if (!empty($row['OneDel4'])){ $OneDel4 == 1; }
		}

And so on?

 

Thanks in advanced!

[mac_gyver]

my reply has nothing to do with your question, but your code shows that you are trying to find the first empty column to store a value in. this is a bad database design that results in overly complicated queries and code.

 

you need to store one piece of data per row in your database table. then storing or finding the data becomes simple.

[Kristoff1875]

my reply has nothing to do with your question, but your code shows that you are trying to find the first empty column to store a value in. this is a bad database design that results in overly complicated queries and code.

 

you need to store one piece of data per row in your database table. then storing or finding the data becomes simple.

 

I basically need to find which of the OneDel columns are not empty and then give them a value of 1 (not in the database) just to use them for a bit of calculation. If there's a better way I'm always happy to learn if you can inform me

[mac_gyver]

the better way is to -

you need to store one piece of data per row in your database table. then storing or finding the data becomes simple.

[Kristoff1875]

One piece of data per row? Do you mean rather than having OneDel1 OneDel2 etc, to use OneDel and store them in there as an array? The problem with that for me is that for each OneDel there is also an OneDel1Email and OneDel1Position (number changes for each) and some rows may only have 1 piece of data for each, some may have 4.

[mac_gyver]

without knowing the context and scope of the problem, it's not possible to directly provide an answer.

 

but short-answer - database tables are for storing data, one data item per row. each row holds the relevant - who, what, when, where, why... information about that one piece of data, so that at any time you can query for one or more specific pieces of data out of that table.

 

one of the red flags for a bad (difficult as possible) code/data design is a series of numbered variables/columns/fields. that says that similar/same meaning code/data is being treated separately instead of being treated with the same meaning.

[Kristoff1875]

Well, what i'm inputting is this:

 

Option One

Delegate 1 Name: Example1

Delegate 1 Email: Example1@eg.com

Delegate 1 Position: Manager

 

Delegate 2 Name:

Delegate 2 Email:

Delegate 2 Position:

 

Delegate 3 Name:

Delegate 3 Email:

Delegate 3 Position:

 

Which I was then sending to a unique row, in to the following columns:

 

OneDel1 -> Example1, Manager

OneDel1Email -> Example1@eg.com

 

OneDel2

OneDel2Email

 

OneDel3

OneDel3Email

 

I'm storing the position with the name and for the purpose it's been serving, it's been working fine. But now I just need to check and see which of the columns of the selected row are empty so I can total them up.

[Kristoff1875]

Just noticed a typo of the extra = symbol. Should be:

if (!empty($row['OneDel1'])){ $OneDel1 = 1; }

[cyberRobot]

You could try something like the following:

<?php
while($row = mysql_fetch_assoc($result)) {
     $OneDel = array();  //reset array
     for($i=1; $i<=4; $i++) {
          if(!empty($row["OneDel$i"])){ $OneDel[$i] = 1; }
     }
     print '<pre>' . print_r($OneDel, true) . '</pre>';
}
?>

 

Note that I changed mysql_fetch_array() to mysql_fetch_assoc(). Also be aware that mysql_ functions have been depreciated. At some point, if you're not doing so already, you'll need to look into an alternative:

http://php.net/manual/en/mysqlinfo.api.choosing.php

[kicken]

Which I was then sending to a unique row, in to the following columns:

What you should be doing instead is have a separate table with one row per delagate. eg:

create table delegates (
   delegateId int not null primary key auto_increment
   , name varchar(50)
   , email varchar(50)
   , position varchar(50)
   -- any other columns relevant to individual delegates
)

You would then link the delegates to whatever table they are currently stored in by that row's ID number. You can do this either with a simple ID column in the above delegates table, or via another table to link the two together. Which you choose would depend on if you want to share the delegates among several items or not.

 

 

But now I just need to check and see which of the columns of the selected row are empty so I can total them up.

With the above layout, you could get such a count by simply using the COUNT() function and GROUP BY clause in your query. Eg:

SELECT
  OriginalID
  , COUNT(delegateId) as numberOfDelegates
FROM original_table
LEFT JOIN delegates ON delegates.originalId = original_table.originalId
GROUP BY
   original_table.OriginalId