The Form Template

This time round I decided that all that cutting and pasting of form tags into the php page (and their layout) was a waste of time so I created a couple of database tables to tell the template which fields to include. So given the form name the template creates the rest of the page.

<?php
$sql="select form_caption, update_file, ok_function, validate_function
  from schema_forms
  where form_name = '$FormName';
select field_name, field_type, field_caption, span_name, needs_validation, can_hide, select_sql, allow_blank,
	block_numbers, allow_decimal, allow_negative
  from schema_form_fields
  where form_name = '$FormName'
  order by field_number;";
if (!isset($link)) {
	include("connectioni.php");
}
if (!mysqli_multi_query($link,$sql)) {
	echo "<p>" . mysqli_error($link) . ". Unable to load property list.</p>";
}
else {
	do {
        	if ($result = mysqli_store_result($link)) {
			$finfo = mysqli_fetch_field($result);
			if ($finfo->name=="form_caption") {
				$row=mysqli_fetch_array($result, MYSQLI_BOTH);
				$FormCaption=$row["form_caption"];
				$UpdateFile=$row["update_file"];
				$OKFunction=$row["ok_function"];
				$ValidateFunction=$row["validate_function"];
			} else {
				?>
				<p class="popuptitle"><?=$FormCaption?></p>
				<form name="<?=$FormName?>">
				<input name="UpdateFile" value="<?=$UpdateFile?>" type="hidden"/>
				<table class="popuptable">
				<?php
				while ($row=mysqli_fetch_array($result, MYSQLI_BOTH)) {
					$FieldName=$row["field_name"];
					$FieldType=$row["field_type"];
					$FieldCaption=$row["field_caption"];
					$SpanName=$row["span_name"];
					$SpanCaptionName=$SpanName."caption";
					$SpanInputName=$SpanName."input";
					$NeedsValidation=$row["needs_validation"];
					$CanHide=$row["can_hide"];
					$SelectSql=$row["select_sql"];
					$AllowBlank=$row["allow_blank"];
					$BlockNumbers=$row["block_numbers"];
					$AllowDecimal=$row["allow_decimal"];
					$AllowNegative=$row["allow_negative"];
					if ($FieldType!="Hidden") {
						echo "<tr>\n";
						echo "<td class=\"popuptable\">\n";
						if ($CanHide=="Y") {
							echo "<span id=\"$SpanCaptionName\">";
						}
						echo $FieldCaption;
						if ($CanHide=="Y") {
							echo "</span>";
						}
						if ($NeedsValidation=="Y") {
							echo "<span id=\"$SpanName\"></span>";
						}
						echo "</td>\n";
						echo "<td class=\"popuptable\">\n";
						if ($CanHide=="Y") {
							echo "<span id=\"$SpanInputName\">";
						}
					}
					switch ($FieldType) {
					case 'Hidden':
						echo "<input name=\"$FieldName\" value=\"".$$FieldName."\" type=\"hidden\"/>";
						break;
					case 'Text':
						echo "<input class=\"popmeinput\" size=\"15\" name=\"$FieldName\" value=\"".$$FieldName."\" type=\"text\"";
						if ($NeedsValidation=="Y") {
							echo " onKeyup=\"$ValidateFunction();\" onblur=\"$ValidateFunction();\"";
						}
						if ($BlockNumbers=="Y") {
							echo " onKeyPress=\"return blockNonNumbers(this, event, $AllowDecimal, $AllowNegative);\"";
						}
						echo "/>";
						break;
					case 'Textarea':
						echo "<textarea class=\"popmeinput\" name=\"$FieldName\"";
						if ($NeedsValidation=="Y") {
							echo " onKeyup=\"$ValidateFunction();\" onblur=\"$ValidateFunction();\"";
						}
						echo ">".$$FieldName."</textarea>";
						break;
					case 'Password':
						echo "<input class=\"popmeinput\" size=\"15\" name=\"$FieldName\" value=\"".$$FieldName."\" type=\"password\"";
						if ($NeedsValidation=="Y") {
							echo " onKeyup=\"$ValidateFunction();\" onblur=\"$ValidateFunction();\"";
						}
						echo "/>";
						break;
					case 'Date':
						$FieldNameD=$FieldName."D";
						$FieldNameM=$FieldName."M";
						$FieldNameY=$FieldName."Y";
						echo "<input class=\"popmeinput\" size=\"1\" name=\"$FieldNameD\" value=\"".$$FieldNameD."\" type=\"text\"";
						if ($NeedsValidation=="Y") {
							echo " onKeyup=\"$ValidateFunction();\" onblur=\"$ValidateFunction();\"";
						}
						echo "/>";
						echo "<select class=\"popmeinput\" name=\"$FieldNameM\"";
						if ($NeedsValidation=="Y") {
							echo " onChange=\"$ValidateFunction();\" onBlur=\"$ValidateFunction();\"";
						}
						echo ">\n";
						echo "<option value=\"\"></option>\n";
						for ($i=1; $i<=12;$i++) {
							if ($i==$$FieldNameM) {
								$Selected="selected=\"selected\"";
							} else {
								$Selected="";
							}
							echo "<option $Selected value=\"$i\">";
							echo date("M",mktime(0,0,0,$i,1,2000));
							echo "</option>\n";
						}
						echo "</select>";
						echo "<input class=\"popmeinput\" size=\"2\" name=\"$FieldNameY\" value=\"".$$FieldNameY."\" type=\"text\"";
						if ($NeedsValidation=="Y") {
							echo " onKeyup=\"$ValidateFunction();\" onblur=\"$ValidateFunction();\"";
						}
						echo "/>";
						break;
					case 'Select':
						echo "<select class=\"popmeinput\" name=\"$FieldName\"";
						if ($NeedsValidation=="Y") {
							echo " onChange=\"$ValidateFunction();\" onBlur=\"$ValidateFunction();\"";
						}
						echo ">\n";
						if ($AllowBlank=="Y") {
							echo "<option></option>\n";
						}
						if (!isset($link2)) {
							include("connection2i.php");
						}
						if (mysqli_multi_query($link2,$SelectSql)) {
							do {
						        	if ($result2 = mysqli_store_result($link2)) {
									while ($row2=mysqli_fetch_array($result2, MYSQLI_BOTH)) {
										$ExistingFieldName=$row2[0];
										if (isset($row2[1])) {
											$ExistingFieldID=$row2[1];
											if ($ExistingFieldID==$$FieldName) {
												$Selected="selected=\"selected\"";
											}
											else {
												$Selected="";
											}
											$ExistingFieldID='value="'.$ExistingFieldID.'"';
										} else {
											unset($ExistingFieldID);
											if ($ExistingFieldName==$$FieldName) {
												$Selected="selected=\"selected\"";
											}
											else {
												$Selected="";
											}
										}
										echo "<option $Selected $ExistingFieldID>$ExistingFieldName</option>\n";
									}
						        	 	mysqli_free_result($result2);
						       		}
							} while (mysqli_next_result($link2));
						}
						echo "</select>";
						break;
					}
					if ($FieldType!="Hidden") {
						if ($CanHide=="Y") {
							echo "</span>";
						}
						echo "</td>\n";
						echo "</tr>\n";
					}
				}
				?>
				<tr>
					<td class="popuptable">
					<span id="aOK"><a class="popuptable" href="#" onclick="<?=$OKFunction?>">OK</a></span>
					</td>
				</tr>
				</table>
				</form>
				<form name="OnloadForm">
				<input name="OnloadFunction" value="<?=$ValidateFunction?>();" type="hidden"/>
				</form>
				<?php
			}
        	 	mysqli_free_result($result);
       		}
	} while (mysqli_next_result($link));
}
?>

Then he tells me that there was probably a tool out there that would have done it for me! So then you just need to set the variable $FormName, include this file and write the appropriate Javascript validation function. I Ajax the lot into a floating box in the middle of the screen (pretending to be a modal dialogue box).

 

It pays to keep an eye on what these guys are doing

Having struggled in the past to upload sourcecode using the pre and code tags and then still having to fiddle it is great to now have this simple method posted in the WordPress news section . I was so excited so I decided I had to blog about it.

My excuse for using it is a slight amendment to my January post on running queries against a MySQL database via PHP. I was finding that if I had a text field it was hard to see where one record ended and the next started.

Record1	Short data	Other data	And here is a cell with loads and loads on stuff written in it trying to look like the output from a text field in my database query. Can you see what it looked like before? It isn’t easy to see where one cell starts and the next finishes is it?
Record2	Short data	Other data	Particularly if there are loads of fields in the output. This by the way is another cell with loads and loads on stuff written in it trying to look like the output from a text field in my database query. Shall we look and see what it all looks like with the alignment changed?

Just do this when looping through the records instead:

					while ($row = mysqli_fetch_row($result)) {
						echo "<tr>";
						for ($counter = 0; $counter &lt; $numcols; $counter++) {
							echo "<td valign=\\"top\\">". $row[$counter] ."</td>";
						}
						echo "</tr>";
					}

Record1	Short data	Other data	And here is a cell with loads and loads on stuff written in it trying to look like the output from a text field in my database query. Can you see what it looks like now? Is it easier to see where one cell starts and the next finishes?
Record2	Short data	Other data	Particularly if there are loads of fields in the output. This by the way is another cell with loads and loads on stuff written in it trying to look like the output from a text field in my database query. Not much of a change of code but I’ve found it much more readable particularly when looking to check what the body of a stored procedure does!

 

I’ve just had the expecting T_PAAMAYIM_NEKUDOTAYIM error

When you’ve stopped scratching your head or falling about laughing and calling your mates in to view the screen (‘cos you can’t pronounce the words) just make sure that all your variables have their $s in tact!

Googling for it will come up with interesting etymology when you have the time.

 

PHP page for running queries against a MySQL database

I was complaining to my husband that I couldn’t get the MySQL GUI to create procedures. It threw wobblies about the syntax. So my first stored procedure after installing MySQL 5 was typed line by line into the prompt – not ideal especially if you are prone to typos!

So he said, unsympathetically, you could always write a script and run it from the file on the server. Which is true, I can. Better still I wrote myself a little PHP page that allows me to run the scripts from the comfort of my own PC (or someone else’s for that matter). Just make sure it is in a password protected directory on the server!

<?php
    include("connectioni.php");
    reset ($HTTP_POST_VARS);
    while (list ($key, $val) = each ($HTTP_POST_VARS))
    {
        $$key = stripslashes($val);
    }
    if (isset ($sql)) {
        if (!mysqli_multi_query($link,$sql)) {
            echo $sql . " " . mysqli_error($link);
        }
        else {
            do {
                if ($result = mysqli_store_result($link)) {
                    echo "<table>";
                    $finfo = mysqli_fetch_fields($result);
                    echo "<tr>";
                    foreach ($finfo as $val) {
                        echo "<td>". $val->name ."</td>";
                    }
                    echo "</tr>";
                    $numrows=mysqli_num_rows($result);
                    $numcols=mysqli_num_fields($result);
                    while ($row = mysqli_fetch_row($result)) {
                        echo "<tr>";
                        for ($counter = 0; $counter < $numcols; $counter++) {
                            echo "<td>". $row[$counter] ."</td>";
                        }
                        echo "</tr>";
                    }
                    echo "</table>";
                    mysqli_free_result($result);
                }
                else {
                    echo mysqli_info($link);
                }
            } while (mysqli_next_result($link));
        }
        mysqli_close($link);
    }
    else {
        echo "Enter sql query";
    }
?>
<form action="<?=$HTTP_SERVER_VARS['PHP_SELF']?>" method="POST">
<textarea name="sql" cols="60" rows="30"><?=$sql?></textarea>
<input type="submit"/>
</form>

7 stored procedures later and counting.

This is very useful afterwards:

insert into mysql.procs_priv (host,db,user,routine_name,routine_type,grantor,proc_priv)
values('host','database','user','proc_name','PROCEDURE','dba@%','Execute');
flush privileges;
select * from mysql.procs_priv;

 

As I feared mysqli was the answer

Having banged my head against a brick wall trying to call mysql stored procedures and actually get the results from the output parameters in php we finally installed php5 and mysqli on the server.

$sql="call insert_account(
'$SQLEmailAddress','$SQLPassword','$SQLFirstName',@retval);
select @retval as return_string;";
if (mysqli_multi_query($link,$sql)) {
do {
    if ($result = mysqli_store_result($link)) {
        $row = mysqli_fetch_row($result);
        $ResultString=$row["return_string"];
        if ($ResultString!="S") {
		echo "There is already an account for this email address. ";
	}
	else {
		echo "Account created successfully. You will shortly be receiving an email
			with a link to confirm your registration.";
		include("confirmationemail.php");
      }
      mysqli_free_result($result);
    }
  } while (mysqli_next_result($link));
}
mysqli_close($link);

Job done!