2. PHPRunner General questions
  3. Insert value of one field into each selected field from list box...
This topic is locked

Insert value of one field into each selected field from list box...

 		8/12/2007 12:46:47 AM
PHPRunner General questions
D
 daveDiamond author

I have a client that would like to record the total number of people for their form submission into a specific field in the database from which items for a list box are called. See the form here. Notice the Total Volunteers field, this will be a numeral, of course. Below this field are the Shifts for which the submitter may choose as many as necessary. These shifts are called from the _decorating_shifts table where the lookup table command will only display those entries WHERE "total_signed_up <= '50' ". This table also contains the finite value of volunteers allowed for each shift. It's column name is total_volunteers.
Here's what needs to happen..
A submitter fills out the form with say 10 Total Volunteers and they pick Saturday December 1st... and Saturday December 15th for example. Before or After the record is added I need the appropriate ID for the selected shifts to be incremented by the Total number of Volunteers (10 in this example). So the _decorating_shifts table would then look like this:
Saturday December 1st = total_signed_up = 10

Saturday December 15th = total_signed_up = 10
and let's say the next person has 5 Total Volunteers and chooses Dec 1st and Dec 28th, it would then look like this:
Saturday December 1st = total_signed_up = 15

Friday December 28th = total_signed_up = 5
Is all that clear. I haven't a clue how to do this. Any help here is appreciated. I shall reciprocate with any assistance I can offer anyone else with their project.
Dave
V
 		Vladimir 8/13/2007

Dave,
use Before record added event on Events tab. Here is a sample code:

global $conn;

$shifts = explode(",", $values["Shifts"])
for ($i=0,$i<count($shifts),$i++)

{

$strQuery = "select * from decorating_shifts where Decorating_Shift='".$shifts[$i]."'";

$rs = db_query($strQuery,$conn);
if ($data=db_fetch_array($rs))

{

$strSQLUpdate = "update decorating_shifts set Total_Signed_Up=Total_Signed_Up+".$values["Total_volunteers"]." where Decorating_Shift='".$shifts[$i]."'";

db_exec($strSQLUpdate,$conn);

}

else

{

$strSQLInsert = "insert into decorating_shifts (Decorating_Shift

, Total_Signed_Up ) values ('".$shifts[i]."', ".$values["Total_volunteers"].")";

db_exec($strSQLInsert,$conn);

}

}
return true;
D
 		daveDiamond author 8/13/2007

Vladimir,
Thanks so much for pointing me down the right path. I edited your sample to make it apply to my project (and corrected some typos too <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=20399&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />) but am now receiving the following error that I believe PHPR support must help with..

function BeforeAdd(&$values)

{
global $conn;

$shifts = explode(",", $values["shifts_1"]);
for ($i=0;$i<count($shifts);$i++)

{

$strQuery = "select * from _decorating_shifts where shift='".$shifts[$i]."'";

$rs = db_query($strQuery,$conn);
if ($data=db_fetch_array($rs))

{

$strSQLUpdate = "update _decorating_shifts set total_signed_up=total_signed_up+".$values["total_volunteers"]." where shift='".$shifts[$i]."'";

db_exec($strSQLUpdate,$conn);

}

else

{

$strSQLInsert = "insert into _decorating_shifts (shift

, total_signed_up ) values ('".$shifts[$i]."', ".$values["total_volunteers"].")";

db_exec($strSQLInsert,$conn);

}

}
return true;

} // function BeforeAdd


PHP error happened

Technical information

Error type 256

Error description You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2

URL davediamondonline.com/dev/kiwanis_rose_parade_float_registration_page_add.php?

Error file /homepages/13/d120330494/htdocs/dev/include/dbconnection.php

Error line 26

SQL query insert into `kiwanis_rose_parade_float_registration_page`

Solution
This is a general error. It occurs when thereis an error in event code or in SQL.
Send your SQL or event code along with full error message tosupport@xlinesoft.com.


Any help here? I'm foaming at the mouth to work this out.
Thanks! Dave
J
 		Jane 8/14/2007

Dave,
Please publish your project on Demo Account and post a URL to your pages here.

I'll find what's wrong with your project inspecting it at Demo account site.
D
 		daveDiamond author 8/14/2007

Dave,

Please publish your project on Demo Account and post a URL to your pages here.

I'll find what's wrong with your project inspecting it at Demo account site.


Hi Jane,
Here's the URL Kiwanis Project
Thanks for any assistance you can offer here.
Dave
J
 		Jane 8/15/2007

Dave,
make sure that you replace all field names used in this code with your actual field names.

For example you need to replace $values["total_volunteers"] with this one:

$values["total_volunteers_1"]
D
 		daveDiamond author 8/15/2007

Doh!
Jane, thanks for pointing that out. I fixed that and corrected the queries with the help of Vladimir (thanks!) and now here's the working code for anyone interested:

function BeforeAdd(&$values)

{

global $conn;

$shifts = explode(",", $values["shifts_1"]);
for ($i=0;$i<count($shifts);$i++)

{

$strQuery = "select * from _decorating_shifts where ID='".$shifts[$i]."'";

$rs = db_query($strQuery,$conn);
if ($data=db_fetch_array($rs))

{

$strSQLUpdate = "update _decorating_shifts set total_signed_up=total_signed_up+".$values["total_volunteers_1"]." where ID='".$shifts[$i]."'";

db_exec($strSQLUpdate,$conn);

}

else

{

/* not needed for this project

$strSQLInsert = "insert into _decorating_shifts (shift

, total_signed_up ) values ('".$shifts[[b]$[/b]i]."', ".$values["total_volunteers_1"].")";

db_exec($strSQLInsert,$conn);

*/

}

}
return true;

} // function BeforeAdd


Now I'm gonna see about adding some email addresses from the database to notify of entries. I may post back here for help. Should I create a new thread or contribute to an existing one?
Dave
D
 		daveDiamond author 8/15/2007

No matter what I try, I can't seem to get this emailing thing right. I wish there were clearer documentation on how to call values from the database. If there is, please point me to it. My code below doesn't insert the db values for each email I want to BCC. Instead it just prints the var name.

//********** Send email with new data ************

global $conn;

$strSQL = "select * from _global_options";

$rs = db_query($strSQL,$conn);

while ($data = db_fetch_array($rs))
$email=$values["email_1"];

$message="We've received your registration. Below is a copy of the form you submitted for your records.\r\n\r\n";

$subject="Your Kiwanis Rose Float Decoration Confirmation";

$bcc1 = $data["global_email1"];

$bcc2 = $data["global_email2"];

$bcc3 = $data["global_email3"];
//************ Additional Headers ****************
$headers = 'From: Kiwanis Rose Float Registration <noreply@kiwanisrosefloat.com>' . "\r\n";

//$headers .= 'Cc: dave@voicedave.com' . "\r\n";

$headers .= 'Cc: $bcc1,$bcc2,$bcc3' ."\r\n";
foreach($values as $field=>$value)

$message.= Label($field)." : ".$value."\r\n";
$message.= "\r\nAll " .$values["total_volunteers_1"] . " of your volunteers must stay for the entire shift for each day listed above.\r\n\r\nThank you again for your registration. We'll see you there!";



mail($email, $subject, $message, $headers);


What am I missing here? It sends email to the $email value but no carbons are sent. I'm missing something silly, huh?
J
 		Jane 8/16/2007

Dave,
try to use this code:

//** Send email with new data ****

global $conn;

$strSQL = "select * from _global_options";

$rs = db_query($strSQL,$conn);

while ($data = db_fetch_array($rs))

{

$bcc1 = $data["global_email1"];

$bcc2 = $data["global_email2"];

$bcc3 = $data["global_email3"];

}

$email=$values["email_1"];

$message="We've received your registration. Below is a copy of the form you submitted for your records.\r\n\r\n";

$subject="Your Kiwanis Rose Float Decoration Confirmation";
//**** Additional Headers ****

$headers = 'MIME-Version: 1.0' . "\r\n";

$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers = 'From: Kiwanis Rose Float Registration <noreply@kiwanisrosefloat.com>' . "\r\n";

//$headers .= 'Cc: dave@voicedave.com' . "\r\n";

$headers .= "Cc: ".$bcc1.",".$bcc2.",".$bcc3."\r\n";
foreach($values as $field=>$value)

$message.= Label($field)." : ".$value."\r\n";
$message.= "\r\nAll " .$values["total_volunteers_1"] . " of your volunteers must stay for the entire shift for each day listed above.\r\n\r\nThank you again for your registration. We'll see you there!";
mail($email, $subject, $message, $headers);
D
 		daveDiamond author 8/16/2007

Dave,

try to use this code:


Jane,
This worked beautifully. Thank you.
I now have two more questions and I can wrap this thing up. I'm trying to use the following code in my AddOnLoad event handler.

function AddOnLoad()

{

//********** Custom code ************

// put your custom code here

global $conn;

$strSQLone = "select * from _global_options";

$rsOne = db_query($strSQLone,$conn);

while ($data = db_fetch_array($rsOne))

{

$global_total = $data["global_total_volunteers"];

}
global $conn;

$vol_total = "select SUM(total_signed_up) from _decorating_shifts";
if ($vol_total >= $global_total) {

header("Location: oops.php");

exit;

} else {
}

} // function AddOnLoad


What I need to do is before displaying the registration page, check to make certain that the sum of the total_signed_up column from the _decorating_shifts is not equal to or greater than the total_volunteers_1 column in the _global_options table. If it is, display another page rather than going to the form. If the sum is not greater, goto the reg form. Is my code close? I think I'm beginning to get it but I still don't have a solid grasp yet.
I also wanted to know if there's a way to suppress empty fields from displaying in the emails. Does that have something to do with field length in the foreach statement?
Any help is appreciated from anyone. Thanks!
Dave
D
 		daveDiamond author 8/20/2007

BUMP
J
 		Jane 8/21/2007

Dave,
first here is the correct code of AddOnLoad event:

global $conn;

$strSQLone = "select * from _global_options";

$rsOne = db_query($strSQLone,$conn);

while ($data = db_fetch_array($rsOne))

$global_total = $data["global_total_volunteers"];
$strTotal = "select SUM(total_signed_up) as sumTotal from _decorating_shifts";

$rsTotal = db_query($strTotal,$conn);

while ($dataTotal = db_fetch_array($rsTotal))

$vol_total = $dataTotal["sumTotal"];
if ($vol_total >= $global_total)

{

?><script>

window.location='oops.php';

</script><?php

exit;

}


Regarding the second question.

To check field length use if clause.

http://php.net/manual/en/language.control-...l-structures.if
D
 		daveDiamond author 8/28/2007

Jane, Vladimir, et al..
Thanks so much to you for your help with this project. I completed it on time and my client was thrilled with the results. Again, you both were very helpful and I thank you.
Dave