Problem code query not executed please help !

[Duofilm]

Hi, we are trying to make a marketing wheel. This means we got a database with lots of persons in it each with a individual email adres.

The simel example of how the basic database looks is seen in the picture below.

This table is called "Clicks"

 

 

As you might notice the field "Aan" is either a 0 or a 1. This is how it works, when someone fills in a form on the website asking for more information the form with all the personal information of this person is send to a random email adres found in this table.

When this happens the field "Aan" changes from 0 to 1  this way we can make sure that the same person does not get a email again until everyone else has been used 1 time.

So in other words until that the field "Aan" is value 1 for every person in the table.

 

As you might have guessed this is sadly enough not working ...

 

this is our code :

 

include_once('connection.php');
$data = array();
$i = 0;

$result1 = mysql_query("SELECT * FROM `Clicks`  WHERE Aan = 0");
while ($list1 = mysql_fetch_array($result1,MYSQL_ASSOC))
{
if (empty($list1) ){
mysql_query("UPDATE `Clicks` SET `Aan`= 0 WHERE `Aan` = 1");
}

$result = mysql_query("SELECT * FROM `Clicks`  WHERE Aan = 0 ORDER BY RAND() LIMIT 1");
while ($list = mysql_fetch_array($result,MYSQL_ASSOC))
{ foreach ($list as $key => $value)
        { // Met htmlentities() voorkom je dat html wordt uitgevoert.
                $value = htmlentities($value);
                $data[$i][$keyl] = $value;
        }
        $i++;
}
}



mysql_free_result($result);



$data = array_reverse($data);   // Zet de nieuwste berichten bovenaan
                                //   in plaats van onderaan.


$cnt = count($data);
for($i = 0; $i < $cnt; ++$i)
{ $bericht = $data[$i];

        
     
       
        echo(nl2br($bericht['bericht']));           // Met nl2br() worden alle enters in het 
                                                        //  bericht omgezet naar <br/>.

        echo('</td></tr></table>');

	$naarwie= $bericht['Email'];

}mysql_query("UPDATE `Clicks` SET `Aan`= 1 WHERE `Email` = $naarwie");

mysql_close($Verbinding);          // Sluit de verbinding.
                                //   We hebben hem nu niet meer nodig.

 

So this code in short explained would be.

It checks if all the "Aan" fields are on 0 if the list is empty so it means they are all on 1 he will reset all the fields of every user to 0.

and then he will take a random person out and send the email to that person. And after the mail is send will change that person his value on "Aan" to value 1.

 

Now if the list is not empty, that means there are still persons with value 0 in the list.

Then he will take a random user out of all the persons who have "Aan" with value 0 send the mail to that user and change his "Aan" value to 1.

This process will be repeated every time someone asks for information until all values of all users are on 1. And then again he will change them to 0 for everyone.

 

I hope its clear what we are looking for. And I hope someone can help us out. We are clueless...

[Duofilm]

The link doesn't seem to be working properly.

here you can see the picture.

 

http://imageshack.us/f/525/phpdatabase.jpg/

[batwimp]

What exactly is the problem? I can't tell from your code or your image.

[Drummin]

See how this works.

include_once('connection.php');
$data = array();
$i = 0;

$result1 = mysql_query("SELECT * FROM `Clicks` WHERE Aan = 0");
while ($list1 = mysql_fetch_array($result1,MYSQL_ASSOC))
{
if (empty($list1) ){
mysql_query("UPDATE `Clicks` SET `Aan`= 0 WHERE `Aan` = 1");
}
}

$result = mysql_query("SELECT * FROM `Clicks` WHERE Aan = '0' ORDER BY RAND() LIMIT 1");
while ($list = mysql_fetch_array($result,MYSQL_ASSOC))
{ foreach ($list as $key => $value)
        { // Met htmlentities() voorkom je dat html wordt uitgevoert.
                $value = htmlentities($value);
                $data[$i][$key] = $value;
        }
        $i++;
} 
mysql_free_result($result);	

$data = array_reverse($data);   // Zet de nieuwste berichten bovenaan
                                //   in plaats van onderaan.
$cnt = count($data);
for($i = 0; $i < $cnt; ++$i)
{ $bericht = $data[$i];
       
        echo(nl2br($bericht['bericht']));           // Met nl2br() worden alle enters in het 
                                                        //  bericht omgezet naar <br/>.

        echo('</td></tr></table>');

	$naarwie= $bericht['Email'];

}
mysql_query("UPDATE `Clicks` SET `Aan`= '1' WHERE `Email` = '$naarwie'");

mysql_close($Verbinding);          // Sluit de verbinding.
                                //   We hebben hem nu niet meer nodig.

[Drummin]

Well TOO LATE to Edit Last post.  Try this one.

include_once('connection.php');
$data = array();
$i = 0;

$result1 = mysql_query("SELECT COUNT(*) FROM `Clicks` WHERE Aan = '0'");
$list1 = mysql_fetch_row($result1);
if($list1[0]=='0') {
mysql_query("UPDATE `Clicks` SET `Aan`= '0' WHERE `Aan`='1'");
}

$result = mysql_query("SELECT * FROM `Clicks` WHERE Aan = '0' ORDER BY RAND() LIMIT 1");
while ($list = mysql_fetch_array($result,MYSQL_ASSOC))
{ foreach ($list as $key => $value)
        { // Met htmlentities() voorkom je dat html wordt uitgevoert.
                $value = htmlentities($value);
                $data[$i][$key] = $value;
        }
        $i++;
} 
mysql_free_result($result);	

$data = array_reverse($data);   // Zet de nieuwste berichten bovenaan
                                //   in plaats van onderaan.
$cnt = count($data);
for($i = 0; $i < $cnt; ++$i)
{ $bericht = $data[$i];
       
        echo(nl2br($bericht['bericht']));           // Met nl2br() worden alle enters in het 
                                                        //  bericht omgezet naar <br/>.

        echo('</td></tr></table>');

	$naarwie= $bericht['Email'];

}
mysql_query("UPDATE `Clicks` SET `Aan`= '1' WHERE `Email` = '$naarwie'");

//mysql_close($Verbinding);          // Sluit de verbinding.
                                //   We hebben hem nu niet meer nodig. 

[Duofilm]

@ batwimp

 

Well I'll give it another go explaining.

We have this site. Where we are testing everything, http://test-labs2.be

When you go to the last tab on this site named "INFO AANVRAAG" you can ask for more information about the subject of the site.

Whenever a person asks for this information this form needs to be send to a email adress.

But to avoid it sending the information form always to the same person we made a Wheel( just what we call it).

This Wheel in reality is acctually just the Table "Clicks" in the database with different Email adresses.

When a person fills in the information form it randomly picks a email and sends it to that person the field value "aan" linked to that email adress will then change from value 0 to value 1

This way the program knows that this email adres has been used.

Next time someone fills in the form he will again send it to a random email adress in the table BUT ! not to the person(s) that have the "Aan" value on 1.

So if the table has 10 emails the form will stop sending after 10 persons have asked extra information oon the site.

Thats why after all the emails have their "Aan" value on 1 it needs to check that and reset all of them back to 0 so we can again start from scratch.

I hope this was better explained.

 

 

@ Drummin

I implemented the code. But when I try using the form on the site. The value's of the "Aan" field don't change from 0 to 1 or if they are all already on 1 they don't change to 0 again.

I do get the email so it does find the fields & the querys are all correct since i tested them in the php my admin.

I'm not sure what i'm doing wrong...

 

Hope you guys or someone else got some more idea's been working on it for a while now.

[Drummin]

The second version worked great as far as I could tell.  A random person is picked out of those with the Aan value 0.  Each round, the person picked Aan value is changed to 1.  When all people have 1 as the Aan value, all records are change to 0 and cycles again.  Maybe I missed the point.

[Duofilm]

nope you didn't miss the point ^^ thats exactly what should happen. If it works for you that means i'm doing something wrong :'(

 

[Drummin]

I'm sure you have other fields in your table but this is what I used for testing.

--
-- Table structure for table `clicks`
--

CREATE TABLE IF NOT EXISTS `clicks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bericht` varchar(65) NOT NULL,
  `Email` varchar(65) NOT NULL,
  `Aan` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

I would probably by more reliable to use the `id` instead of `Email` to make update but it should be working as in my last posted code (not the first one).

[Duofilm]

Hi, me again its been a while and i've been trying to make the code more simpel.

 

include_once('verbinding.php');

$result1 = mysql_fetch_array (mysql_query("SELECT COUNT(*) FROM `Clicks` WHERE Aan = '0'")); // controleren hoeveel rijen er zijn met "Aan" op 0
$lijst1 = $result1[0];
if($lijst1=='0') { // als de lijst leeg is wil dit zeggen dat ze allemaal op 1 staan en mogen ze gereset worden.
mysql_query("UPDATE `Clicks` SET `Aan`= '0' WHERE `Aan`='1'");
} // indien er ook maar 1 veld op 0 staat zal er direkt naar hier worden gesprongen

$result = mysql_fetch_array(mysql_query("SELECT Email FROM `Clicks` WHERE Aan = '0' ORDER BY RAND() LIMIT 1"));
$naarwie = $result['Email'];
echo $naarwie;

mysql_query("UPDATE `Clicks` SET `Aan`= '1' WHERE Email = '$naarwie'");



mysql_close($verbinding); 

 

This should work right ? but it seems that the wrong Emails are changing from 0 to 1 or sometimes more then 1 are changing got any idea ?

[Drummin]

Hi, me again its been a while and i've been trying to make the code more simpel.

 

Code: [select]

 

include_once('verbinding.php');

 

$result1 = mysql_fetch_array (mysql_query("SELECT COUNT(*) FROM `Clicks` WHERE Aan = '0'")); // controleren hoeveel rijen er zijn met "Aan" op 0

$lijst1 = $result1[0];

if($lijst1=='0') { // als de lijst leeg is wil dit zeggen dat ze allemaal op 1 staan en mogen ze gereset worden.

mysql_query("UPDATE `Clicks` SET `Aan`= '0' WHERE `Aan`='1'");

} // indien er ook maar 1 veld op 0 staat zal er direkt naar hier worden gesprongen

 

$result = mysql_fetch_array(mysql_query("SELECT Email FROM `Clicks` WHERE Aan = '0' ORDER BY RAND() LIMIT 1"));

$naarwie = $result['Email'];

echo $naarwie;

 

mysql_query("UPDATE `Clicks` SET `Aan`= '1' WHERE Email = '$naarwie'");

 

 

 

mysql_close($verbinding);

 

 

This should work right ? but it seems that the wrong Emails are changing from 0 to 1 or sometimes more then 1 are changing got any idea ?

 

 

Does your table `clicks` have an `id` field AUTO_INCREMENT?

 

It would be much more reliable to use an id to reference which record should be updated in the last update after grabbing the Email.  Just also "grab" the id and use it to define the update.  As far as the code posted, it seems to work fine for me, however as mentioned a primary table key would be better.

[Duofilm]

Thx I did in the meanwhile and it works !

I found my problem. I use a auto generated email form & added that code to the file.

It works without all the code from the auto generated form. But when the code is in the file. It doesn't work properly changes the wrong fields or to many fields

Don't really feel like checking the auto generated code so to bad i guess i'll have to find another way.