Marking every 5 rows

[Canman2005]

Hi all

 

Wondering if you can help;

 

I have a simple table called `mydata`, it has the following fields

 

`id`

`checked`

`date`

 

I have the following INSERT script

 

INSERT INTO `mydata` SET `checked`='1', `date`=date('Y-m-d')

 

What I want to do is for every 5 rows of data INSERTED into my table, 1 of those 5 rows INSERTED to have the field `checked` set to 1 and the other 4 rows set to 0.

 

A history of INSERTS might look like

 

INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='1', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')

 

INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='1', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')

 

INSERT INTO `mydata` SET `checked`='1', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')
INSERT INTO `mydata` SET `checked`='0', `date`=date('Y-m-d')

 

As you can see, the INSERTS are split into 5's, because I want 1 out of every 5 rows INSERTED to have its value of the field `checked` to be set to 1, the other 4 rows should have the value set to 0.

 

This should be a random thing, so it might set the `checked` value of the first row INSERTED to 1 or it might set the last row INSERTED to 1.

 

Does that make much sense?

 

Thanks

 

Dave

[Yesideez]

The only thing I can think of is to check the ID number of the last inserted item and if %5==0 (modular division) set checked to 1 but this won't stand up too well for future use.

 

What would happen if rows were deleted?

[Canman2005]

No rows will be deleted or edited, just inserted

[Yesideez]

mysql_query("INSERT INTO table...");
$lastinserted=mysql_insert_id();
if ($lastinserted%5==0) {
  mysql_query("UPDATE table SET checked='1' WHERE id='".$lastinserted."'");
}

[Mikedean]

Yesideez, that won't make it random.

 

You could store the ID's of the INSERTS in an array and use rand(0,5) to make it random and then UPDATE that row, however posting what you already have would be useful.

[Yesideez]

I thought it was to be every 5th insert?

[Canman2005]

That works cool, but it always does

 

SET checked='1'

 

on the 5th row inserted.

 

I'm trying to get it to add it randomly to one of each block of 5 rows that are inserted.

 

So it might do

 

SET checked='1'

 

on the 2nd row inserted or it might do it on the 4th row

 

but im trying to keep to blocks of 5

[Canman2005]

So it could look like

 

SET checked='0'

SET checked='1'

SET checked='0'

SET checked='0'

SET checked='0'

 

SET checked='0'

SET checked='0'

SET checked='0'

SET checked='0'

SET checked='1'

 

SET checked='1'

SET checked='0'

SET checked='0'

SET checked='0'

SET checked='0'

 

[Mikedean]

<?php
$array[0][0] = 1;
$array[0][1] = 2;
$array[0][2] = 3;
$array[0][3] = 4;
$array[0][4] = 5;

$array[1][0] = 6;
$array[1][1] = 7;
$array[1][2] = 8;
$array[1][3] = 9;
$array[1][4] = 10;

for( $i = 0; $i < count( $array ); $i++ )
{
	$randomNum = rand(0, 4);
	echo "Row ID:  " . $array[$i][$randomNum] . " will have checked='1'<br />";
                // Run SQL update.
}
?>

 

This will let you grab a random ID, but you would need to store the inserted ID's in an array.

[Yesideez]

Ah! I understand the question now

[Canman2005]

So is there no way of automating your suggestion Mikedean?

[kickstart]

Hi

 

Keeping it simple and putting all the inserts into one statement.

 

$RandNum = rand(1,5);
$sql = "INSERT INTO `mydata` (`checked`, `date`)
VALUES (".(($RandNum = 1) ? '1' : '0' ).",date('Y-m-d')),
VALUES (".(($RandNum = 2) ? '1' : '0' ).",date('Y-m-d')),
VALUES (".(($RandNum = 3) ? '1' : '0' ).",date('Y-m-d')),
VALUES (".(($RandNum = 4) ? '1' : '0' ).",date('Y-m-d')),
VALUES (".(($RandNum = 5) ? '1' : '0' ).",date('Y-m-d'))";

 

All the best

 

Keith