Solved

php loop, multiple sql updates

Posted on 2009-07-05
15
344 Views
Last Modified: 2013-12-13
Ok, I'm open to suggestions, angel :), on my project.  I've hit another roadblock and might be approaching this whole thing the wrong way.  I was trying to create a small footprint of code and loop it all, but can't seem to get everything to work.

I want to print out 365 checkboxes (see attached image), then update a table in the database with the answer for 1(yes) or 0(no).  I'm not sure if doing 365 updates all at once is the best idea or not, for each of my users.

My end game is this:
#1) print out 365 checkboxes
#2) check the individual users table to see which checkbox is already a yes, and check it for them (maybe with a value=$checked ?)
#3) allow my users to select/deselect one, or all at the same time and click submit, and update accordingly

Here is the code I have so far, which will print the table, create all the answer variables, then it will update the 1st box that is checked and nothing more for some reason.

any ideas would be appreciated!
<?php
 

//GET VARIABLES FROM SUBMISSION ON SAME PAGE

$answer1 = $_POST['answer1']; 

$answer2 = $_POST['answer2']; 

$answer3 = $_POST['answer3']; 

$answer4 = $_POST['answer4']; 

$answer5 = $_POST['answer5'];

$answer6 = $_POST['answer6']; 

$answer7 = $_POST['answer7']; 

$answer8 = $_POST['answer8']; 

$answer9 = $_POST['answer9']; 

$answer10 = $_POST['answer10'];
 
 

//DAILY READINGS

$daily1reading = ', example, read page 1';

$daily2reading = ', example, read page 2';

$daily3reading = ', example, read page 3';

$daily4reading = ', example, read page 4';

$daily5reading = ', example, read page 5';

$daily6reading = ', example, read page 6';

$daily7reading = ', example, read page 7';

$daily8reading = ', example, read page 8';

$daily9reading = ', example, read page 9';

$daily10reading = ', example, read page 10';
 

$date365t = mktime(0, 0, 0, 1, 0, date(Y));

$date365 = date("Y-m-d", $date365t);
 

?>
 

<form method="post" action="365-days">
 

<?php
 

echo '<table width=650><tr>';

for($loop365=1;$loop365<=365;$loop365++) {

  if(($loop365-1)%15==0) echo '</tr><tr>'; ?>

  <td align="center"><span title="<?php 

  echo "Daily reading for ".date('F jS',mktime(0,0,0,1,$loop365)); $dailyreading = "daily{$loop365}reading"; echo $$dailyreading; echo " (Day $loop365)";

$date365t += 86400;

$date365 = date("Y-m-d", $date365t);
 

echo "#"; echo ${$answerloop365}; echo "#"; 

?>"><?php echo $loop365; ?></span></td>

<td align="center">

<input type='checkbox' value="0" title="<?php 

  echo "Daily reading for ".date('F jS',mktime(0,0,0,1,$loop365)); $dailyreading = "daily{$loop365}reading"; echo $$dailyreading; echo " (Day $loop365)"; ?>" value='1' name="answer<?php echo ($loop365+1); ?>" />

</td><?php
 

$answerloop365 = ${'answer'.$loop365};
 

if ( $answerloop365 == '1' ) {

$date1 = date('Y-m-d');

$sql1 = "UPDATE $createtablename SET date='$date1', answer='$answerloop365' WHERE id = ( '$loop365' );";

mysql_query($sql1) or die ("Error in query: $sql1. ".mysql_error());

echo "<b>INSERTED</b>";

} else {

echo "NOT READY TO INSERT";

}
 

}

echo '</tr></table>';
 

?>
 

<table width="650" border="0">

<tr>

<td>

<br>

<input type="submit" value="Update All 365 Days Now!"><input type="Reset">

</form>

</td>

</tr>

</table>

Open in new window

Capture.PNG
0
Comment
Question by:bschwarting
  • 8
  • 7
15 Comments
 
LVL 14

Expert Comment

by:profya
ID: 24783714
Error line 49: $$dailyreading
0
 
LVL 14

Accepted Solution

by:
profya earned 500 total points
ID: 24783997
You need to add a hidden input to handle the case of form submit. I changed the way the check boxes appear, I used control arrays in stead of answer1, answer2 ... there is a simple javascript code to select check boxes selected by the user when the form is postback. I hope this helps.
Try the following code:
<?php

$selectedValues="";

if (isset($_POST['formPosted']) && $_POST['formPosted']==1)

{//Form has been submitted

	$answers=isset($_POST['answer'])?$_POST['answer']:'';

	if (count($answers)>0)

	{

		foreach ($answers as $answer)

		{

			$date1 = date('Y-m-d');

			$sql= "UPDATE $createtablename SET date='$date1', answer='1' WHERE id = ( '".$answer."' );";

			mysql_query($sql) or die ("Error in query: $sql1. ".mysql_error());

			echo "<b>INSERTED</b>";

			} else {

			echo "NOT READY TO INSERT";

			}
 

		}

		$selectedValues= implode (",", $answers);

	}

}
 

$date365t = mktime(0, 0, 0, 1, 0, date('Y'));

$date365 = date("Y-m-d", $date365t);

?>

<form name=form1 method="post" action="">

<input type="hidden" name="formPosted" value="1">

<table width=650><tr>

<?php

	for($loop365=1;$loop365<=365;$loop365++)

	{

		  if(($loop365-1)%15==0)

		  echo '</tr><tr>';

?>

	  <td align="center">

		  <span title="

		  <?php

			echo "Daily reading for ".date('F jS',mktime(0,0,0,1,$loop365)); $dailyreading = "daily{$loop365}reading"; echo $dailyreading; echo " (Day $loop365)";

			$date365t += 86400;

			$date365 = date("Y-m-d", $date365t);
 

			echo "#"; echo $loop365; echo "#";

		  ?>

		  ">

				<?php echo $loop365; ?>

		  </span>

	    </td>

		<td align="center">

			<input type='checkbox' title="

			<?php

			  echo "Daily reading for ".date('F jS',mktime(0,0,0,1,$loop365)); $dailyreading = "daily{$loop365}reading"; echo $dailyreading; echo " (Day $loop365)"; ?>" value='<?php echo ($loop365+1);?>' id='answerNo<?php echo ($loop365+1);?>' name="answer[]"

			 />

		</td>

<?php
 

	}
 

?>
 

<table width="650" border="0">

<tr>

<td>

<br>

<input type="submit" value="Update All 365 Days Now!"><input type="Reset">

</form>

<script type="text/javascript">

var selectedValues="<?php echo $selectedValues;?>";

var selValuesAr=selectedValues.split(',');

for (i=0; i<selValuesAr.length; i++)

{

	id="answerNo"+selValuesAr[i];

	alert(document.getElementById(id));
 

	if (document.getElementById(id))

	{

		alert('dsfjlsdfjsl');

		document.getElementById(id).checked=true;

	}

}

</script>

</td>

</tr>

</table>

Open in new window

0
 
LVL 1

Author Comment

by:bschwarting
ID: 24784197
profya, thanks for this!  when I try to use it, I get the white screen of death.  I've looked through all your code and can't find the error.  maybe you can assist?
0
 
LVL 14

Assisted Solution

by:profya
profya earned 500 total points
ID: 24784262
You are right, I have added the code that saves data to the database later, I didn't check it. There was a syntax error with it. I also liked the expression "the white screen of death", cool!
I am attaching you the revised code along with a screen capture of what I got when I ran it, I hope you find it useful. I have commented out database code and replaced it with an echo statement.


<?php

$selectedValues="";

$createtablename="mytable";

if (isset($_POST['formPosted']) && $_POST['formPosted']==1)

{//Form has been submitted

	$answers=isset($_POST['answer'])?$_POST['answer']:'';

	if (count($answers)>0)

	{

		foreach ($answers as $answer)

		{

			$date1 = date('Y-m-d');

			$sql= "UPDATE $createtablename SET date='$date1', answer='1' WHERE id = ( '".$answer."' );";

			echo "SQL: ", $sql, "<br>";

			/*mysql_query($sql) or die ("Error in query: $sql. ".mysql_error());

			if (mysql_affected_rows()>0)

				echo "<b>INSERTED</b>";

			else

				echo "NOT READY TO INSERT";

				*/
 
 

		}

		$selectedValues= implode (",", $answers);

	}

}
 

$date365t = mktime(0, 0, 0, 1, 0, date('Y'));

$date365 = date("Y-m-d", $date365t);

?>

<form name=form1 method="post" action="">

<input type="hidden" name="formPosted" value="1">

<table width=650><tr>

<?php

	for($loop365=1;$loop365<=365;$loop365++)

	{

		  if(($loop365-1)%15==0)

		  echo '</tr><tr>';

?>

	  <td align="center">

		  <span title="

		  <?php

			echo "Daily reading for ".date('F jS',mktime(0,0,0,1,$loop365)); $dailyreading = "daily{$loop365}reading"; echo $dailyreading; echo " (Day $loop365)";

			$date365t += 86400;

			$date365 = date("Y-m-d", $date365t);
 

			echo "#"; echo $loop365; echo "#";

		  ?>

		  ">

				<?php echo $loop365; ?>

		  </span>

	    </td>

		<td align="center">

			<input type='checkbox' title="

			<?php

			  echo "Daily reading for ".date('F jS',mktime(0,0,0,1,$loop365)); $dailyreading = "daily{$loop365}reading"; echo $dailyreading; echo " (Day $loop365)"; ?>" value='<?php echo ($loop365+1);?>' id='answerNo<?php echo ($loop365+1);?>' name="answer[]"

			 />

		</td>

<?php
 

	}
 

?>
 

<table width="650" border="0">

<tr>

<td>

<br>

<input type="submit" value="Update All 365 Days Now!"><input type="Reset">

</form>

<script type="text/javascript">

var selectedValues="<?php echo $selectedValues;?>";

var selValuesAr=selectedValues.split(',');

for (i=0; i<selValuesAr.length; i++)

{

	id="answerNo"+selValuesAr[i];

	if (document.getElementById(id))

	{

		document.getElementById(id).checked=true;

	}

}

</script>

</td>

</tr>

</table>

Open in new window

whitescreenofdeath.PNG
0
 
LVL 1

Author Comment

by:bschwarting
ID: 24784810
that works great!

I'm missing two things now:

#1) Yours is doing the same thing, it's updating the next record instead of the record selected (see above where #1 updated id #2, should be id #1)
#2)  Once this is in the database, I need the script when it is first pulled up to see which of these are already a "1" and check them off
0
 
LVL 1

Author Comment

by:bschwarting
ID: 24784858
maybe something like this?

select id, answer from $createtablename where answer = 1

if answer = 1
$checked = checked
} else {
$checked = ' '

then make the input a variable for selected

<input type='checkbox' $checked />
0
 
LVL 14

Assisted Solution

by:profya
profya earned 500 total points
ID: 24785074
#1) You are right, your code was using echo ($loop365+1), now change it to:
<?php echo ($loop365);?>
Line 55 in the code I have submitted, two positions need to change.
#2) hmmm. Theoretically, you get the data from the database and extract it as string, separated with comma, no trailing commas, for example this is good 1,20,40,100 but this is bad 1,20, 40,100,
assign the value you have extracted from the database in $selectedValues variable, the javascript code will then check the corresponding check boxes. Put this code in the ELSE clause of:
if (isset($_POST['formPosted']) && $_POST['formPosted']==1)

To be executed when the page has been loaded for the first time,
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Author Comment

by:bschwarting
ID: 24785263
profya, for some reason i keep getting a bunch of records that won't update.  what is it looking at before it updates?
0
 
LVL 14

Expert Comment

by:profya
ID: 24785338
Excuse me, I didn't get your point. When this happens? When you click on the submit button? Try echoing the sql statement and see the logic, is there anything missing? specifically the WHERE clause.
0
 
LVL 1

Author Comment

by:bschwarting
ID: 24788491
When the date field is blank in the database, it will take the submit fine and update.  If there is a date in the database already, it won't update.
0
 
LVL 1

Author Comment

by:bschwarting
ID: 24788633
can you help me with #2?  not sure I am following.
0
 
LVL 14

Assisted Solution

by:profya
profya earned 500 total points
ID: 24792070
MySQL ignores updating a record if the new values are similar to those values already exist. For example if the date is '1/1/2009', answer=1 and id=100, MySQL ignores
UPDATE myTable SET date='1/1/2009', answer='1' WHERE id=100
because it is none sense to update it to values it is already have.
If mysql_affected_rows()=0 and mysql_error()="" then mysql has ignored the update statement.

Regarding #2:
Add ELSE clause to the following IF statement:
if (isset($_POST['formPosted']) && $_POST['formPosted']==1)

In line 26, this code will be executed when the form is not yet submitted, the first time the page loaded. The code does the following:
1) Accesses the table and get answers where answer=1
2) Store those answers in a string variable comma separated.
Something like:


else

{

	$sql="SELECT id FROM $createtablename WHERE answer='1'";

	$rs=mysql_query($sql);

	$tempVar="";

	$delimiter="";

	if ($rs && mysql_num_rows($rs)>0)

	{

		while ($rec=mysql_fetch_array($rs))

		{

			$tempVar.=$delimiter.$rec['id'];

			$delimiter=",";

		}
 

		echo "Selected values from db: ", $tempVar;//Echo to see if it has been extracted properly, remove it when every this is fine.

	$selectedValues=$tempVar;

	}

}

Open in new window

0
 
LVL 1

Author Comment

by:bschwarting
ID: 24794447
ok, that makes sense on the update routine.  

#2 works great!  you are a genius!

my last issue, #3, is my only remaining issue!  right now, when the page is 1st brought up, and all the boxes are checked, if the user has made a mistake, and wants to deselect the box, it never updates the answer to 0.  what is the best strategy for that?
0
 
LVL 14

Assisted Solution

by:profya
profya earned 500 total points
ID: 24794771
There are tow appraoches you can select one of them:
1) Update all rows to zero first then apply user selections:
before the loop, UPDATE $createtablename SET answer='0' and execute the query, then the loop will run the query that updates user current selections.

2)If your table does not contain an autoincrement, the id is not an auto increment, then you can simply change the type of the update query, you can change it to delete first and then insert:
Before the loop:
DELETE FROM $createtablename and execute the query, then the loop will run the query that updates user current selections.

because your table always contains about 365 records, these addition queries will not affect the performance.

There are several methods also you can use, the above are the most appropriate to your condition as I can see. For example you can collect all ids for answer=1 in a variable similar to the technique I used to preselect on the first load, then run an update query after the loop completes something like
UPDATE $createtablename SET answer='0' WHERE ID NOT IN ('$selectedIdsVariable')

0
 
LVL 1

Author Closing Comment

by:bschwarting
ID: 31600001
Genius, pure genius!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now