Another form not working?

I have a form on a site that simply lists a series of items that can be added to a user.
The form is as follows

<?php
$my = JFactory::getUser();
$username=$my->username;
$ynm = '2008';
echo '<p>'.$ynm . ' HIM certified credits that are availabe.<br /></p>';

//create listings of items not to pull
$refs = "";
	$qz = "SELECT * FROM cpe_track WHERE username = '" . $username . "' 
	AND refid is not null";
	$rz = mysql_query($qz);
	$nz = mysql_num_rows($rz);
	if ($nz >0)
	{
		WHILE ($prz = mysql_fetch_array($rz))
		{
			$refs = $refs . " AND progid <> '" . $prz['refid'] . "'";
		}
	}

			//put in the table
		$qa = "SELECT progid, date_format(progdatecomplete, '%b %D, %Y') as dte2, progtitle, progsponsor, progdelmode, 
		progcreditsearned, progmancesection from cpe_refs 
		WHERE YEAR(progdatecomplete) <= '". $ynm . "' 
		AND
		YEAR(progdatecomplete) >= '". $ynm."'" . $refs . " ORDER BY progdatecomplete DESC";
		//print $qa;
		$ra = mysql_query($qa);
		$nra = mysql_num_rows($ra);
		if ($nra>0)
		{

		  echo '<table width="560" border="0" cellspacing="0" cellpadding="0">';
			echo '<tr>';
			  echo '<td width="15%"><strong>Date</strong></td>';
			  echo '<td width="34%"><strong>Program</strong></td>';
			  echo '<td width="30%"><strong>Sponsor</strong></td>';
			  echo '<td width="8%"><strong>Mode</strong></td>';
			  echo '<td width="8%"><strong>Credits</strong></td>';
			  echo '<td width="5%"><strong>Add</strong></td>';
			echo '</tr>';

			WHILE ($pry = mysql_fetch_array($ra))
			{

			echo '<tr>';
			  echo '<td width="15%">' .  $pry['dte2'] . '</td>';
			  echo '<td width="34%">' . $pry['progtitle'] . '</td>';
			  echo '<td width="30%">' .  $pry['progsponsor'] . '</td>';
			  echo '<td width="8%">' .  $pry['progdelmode'] . '</td>';
			  echo '<td width="8%">' . $pry['progcreditsearned'] . '</td>';
			  echo '<td width="5%">
			  <input type="checkbox" name="rid' .  $pry['progid'] . '" value="rid' .  $pry['progid'] . '" />
			  <input type="hidden" name="username" value="' .  $username .'" /></td>';
			echo '</tr>';

			}

			echo '<tr>';
			  echo '<td width="15%"> </td>';
			  echo '<td width="34%"> </td>';
			  echo '<td width="30%"> </td>';
			  echo '<td width="8%"> </td>';
			  echo '<td width="8%"><input name="Submit" type="submit" id="Submit" value="Submit" /></td>';
			  echo '<td width="5%"> </td>';
			echo '</tr>';
		  echo '</table>';

		}
		else
		{
			echo "<br><strong>Sorry, there are not credits available for you to enter in this timeframe</strong>";
		}
?>

My onsubmit codes is as follows:

<?php
foreach($_POST as $name => $value) //Big Al's handy work
{   
	if (substr($value, 0, 3)=="rid")
    {
		//echo substr($value, 0, 3)."<br>";
		//echo substr($value, 3, 100)."<br>";
			//put the query here to add the items
			//first go and get the item variables
			$qz = "SELECT * FROM cpe_refs WHERE progid = '" . substr($value, 3, 100) . "'";
			print $qz;
			$rz = mysql_query($qz);
			$nz = mysql_num_rows($rz);
			$prz = mysql_fetch_array($rz);
			//echo $prz['proglength']."<br>";
			$q4 = "INSERT INTO cpe_track (rid, username, refid, progdate, 
			progtitle, progsponsor, progdelmode, progdatecomplete, proglength, 
			progcreditsearned, progmancesection, dateadded )
			 VALUES ( 
						'',
						'" . $_POST['username'] . "',
						'" . substr($value, 3, 100) . "',
						'" . $prz['progdate']  . "',
						'" . $prz['progtitle'] . "',
						'" . $prz['progsponsor'] . "',
						'" . $prz['progdelmode'] . "',
						'" . $prz['progdatecomplete'] . "',
						'" . $prz['proglength'] . "',
						'" . $prz['progcreditsearned'] . "',
						'" . $prz['progmancesection'] . "',
						'" . date("Y-m-d") . "'
						)";
						print $q4;
			$r4 = mysql_query($q4);			
	}
}
?>

What it is supposed to be doing is take the id of the submitted item, retrieve the particulars about that item and add a record to another table so the user will have this record attached to them.

On running the form with debug on it returns an array with the proper information but does not seem to run code.

Any information or hints would be most appreciated.
Thanks for a fantastic product!

Hi dogfishent,

As I posted to your other message - there's a lot of code here that isn't very easy to read.

I suggest that you build up your form a chunk at time and test each piece before you add the next chunk. You can also add debug code to print out interim results so that you can see what is happening and fix the problems.

We try quite hard here to address people's issues and questions using ChronForms and it's sister products but ther forums are getting busier and it really isn't practical to check long pieces of code or to do write or debug code.

Bob

PS If you want to want help with coding like this then I'm sure that Max or I would be happy to give you a quote for doing the work.

Fair enough.
I have shortened things down to try to troubleshoot.

I am not even worrying about what is happening in the actual form anymore.
I am just working on the submit area.
When I click submit with debug on I receive the following:

_POST: Array ( [username] => abob22 [Submit] => Submit [b8777ba43ac56ae6b7dbb43a54b50458] => 1 )

I have put the following code in on submit area

<?php
$q4 = "INSERT INTO cpe_track (rid, username, refid, progdate, 
progdatecomplete, dateadded )
VALUES ( 
'',
'abob22',
7,
'2008-10-13',
'2008-10-13',
'" . date("Y-m-d") . "'
)";
$r4 = mysql_query($q4);			
?>

This is not even adding to the database.
Somehow I am missing something.
It is not even firing the code...
Is this something you can help me troubleshoot?

Hi dogfishent,

Excellent, thank you - I can get my head round the short version.

I don't think that you have a database connection set up. Here's your code changed to demonstrate the Joomla database syntax:

<?php
$db = & JFactory::getDBO();
$sql = "
    INSERT 
        INTO ".$db->nameQuote('cpe_track')." 
        SET 
            username = ".$db->Quote('abob22').",
            refid = ".$db->Quote('7').", 
            progdate  = ".$db->Quote('2008-10-13').",
            progdatecomplete = ".$db->Quote('2008-10-13').", 
            dateadded = ".$db->Quote(date("Y-m-d")).";";
$db->setQuery($sql);
if ( !$db->query() ) {
    global $mainframe;
    $mainframe->enqueuemessage($db->getErrorMessage(), 'error');
}       
?>

[list]

The JFactory line includes the Joomla database object.

I changed the sql variable name to $sql for clarity (some people prefer $query), you can re-use this for each query as long as one executes before you start the next one.

I've switched to the SET syntax here just for clarity, the VALUES syntax will work as well

I've left out the - rid = "", - I'm assuming that this is the primary index which will be set automatically by an INSERT statement, if not then it should be replaced.

$db->nameQuote quotes a db table or fieldname - probably not needed here but is if there are any special characters in the name.

$db->Quote does the same thing for a value - it will escape some special characters (again quite likely not needed here).

$db->setQuery sets the query but does not execute it.

$db->query executes the previously set query and returns true or false.

global $mainframe loads the Joomla mainframe object (don't worry for now).

$mainframe->enqueuemessage creates a standard Joomla message to be shown on the next page display, 'error' indicates the type of message.

[/list]
Hope this helps

Bob

Thanks Bob.
Maybe I am getting somewhere.
I took the liberty of popping your code into the on submit and it did not work.

Then I thought I would just drop the code into a standard article as I am running DirectPHP in my Joomla install.
When I ran the page, it returned the following error

Parse error: syntax error, unexpected T_GLOBAL in /home/echima/public_html/plugins/content/DirectPHP.php(49) : eval()'d code on line 14

I am assuming that it is throwing an error from the code as I can put some pretty standard php code in there and have it run ok.
Sorry to be such a bother.
I am not sure where to go from here. I looked at your code and it makes sense to me for syntax other than the parts that I do not understand like your terms nameQuote and Quote, but I cannot see how that would make the difference.

Again thanks

Hi dogfishent,

Apologies, I missed some brackets - try changing line 14 to

$mainframe->enqueuemessage($db->getErrorMessage(), 'error');

Bob

Sorry, even after changing that line, it is still throwing the error in the article where I pasted it.

Parse error: syntax error, unexpected T_GLOBAL in /home/echima/public_html/plugins/content/DirectPHP.php(49) : eval()'d code on line 14

When I run it in the form it I receive the following blank screen with this code.

_POST: Array ( [username] => abob22 [Submit] => Submit [ccd1fdca1628ca634b7549d4f1345d39] => 1 )

Fatal error: Call to undefined function: geterrormessage() in /home/echima/public_html/components/com_chronocontact/chronocontact.php(533) : eval()'d code on line 15

Yikes! Have I really messed it up?
Could it be having a hard time with global $mainframe?

Hi dogfishent,

Not sure what is happening here. I'd expect this to run OK in ChronoForms - can't speak for DirectPHP.

'global $mainframe;' is standard code, there shouldn't be any problem with it provided that the code has access to the Joomla Framework.

The Joomla database methods ' Quote()', etc are documented here

If I have time later I'll put this code into a test-form and see if I've left any more bugs in there.

Bob

OK, I have it working!
I am pretty sure that the issue resolved around my query structure.
I had my php/mysql guru friend peek at the query structure, we made some changes and it is now working!

Thanks for all your help sir. I appreciate it.

Regards,

Hi dogfishent,

Excellent - well done!

Bob