Sending Variables to Function...

[Hojo]

I am just getting my chops up on PHP and need help with a problem.

I have a page where users can view their information and add information that doesn't exist in the database. It prints the information on the screen if it is there, and gives a textbox if the appropriate value is Null.

Where my problem is happens on submision of the form. The page reloads itself, then when it goes to add new information to the database, it needs to only have the non-null variables added. Otherwise it coughs up an error saying that variables that were already on the database are null (as they were not entered into the database).

I figured I could add hidden inputs on those lines to add the needed data, but I feel there is a better way to do it. Somehow build an array that only holds the keys and values of the variables that were posted not null, then pass that to the UPDATE statement.

Clear as mud? Hope not...

[tekky]

use aloop for your POST or GET data, then use that loop to build your query if the values are not null

foreach ($_POST as $pkey => $pval)
{
   if !empty($pval)
   {
        if (strlen($columns) == 1) { $columns = "('$pkey')"; }
        else { $columns .= ", '$pkey'"; }
        if (strlen($values) == 1) { $values = "('$pkey')"; }
        else { $values .= ", '$pval'"; }
    }
}
$columns .= ')';
$values .= ')';
echo "INSERT $columns VALUES $values";

something like that should work.... atleast in my head...

[Hojo]

Thanks! That got me 99% of the way there...

I had to obviously edit things a little to be germain to my page, but obviously this will do the trick. I am having a final little problem with it though. Here it is as it stands now:

$columns = '(';
$values = '(';
foreach ($_POST as $pkey => $pval)
	{
	if(!empty($pval))
		{
		if (strlen('$columns') == 1) 
			{ 
			$columns = "('$pkey')"; 
			}
		else 
			{ 
			$columns .= ", '$pkey'";
			}
        if (strlen('$values') == 1) 
		{ 
		$values = "('$pkey')";
		}
	else 
		{ 
		$values .= ", '$pval'";
		}

		}
}
$columns .= ')';
$values .= ')';
$q = "UPDATE table SET $columns = $values WHERE keyfield = '$_SESSION[keyfield]'";
$result = mysql_query( $q, $link );

if ( ! $result)
	die( "Bad things happened" );

As you might assume, I am getting the 'Bad things happened' message. Is it my SQL INPUT statement or the fact that the $columns and $values always have a comma at the beginning (or some other sin I've created)?

Thanks for the help...

[tekky]

$result = mysql_query($q) OR die('mysql error: ' . mysql_error() . ' --- Query was: ' . $q);

try using that for your query it will give you more details... and when it dies and tells you what the query was, check that the query is right... and verify that it works by pasting it into phpMyAdmin or commandline for mySQL

[Hojo]

OK.... Did that and found that the SQL statement was the problem, but even checking syntax with online resources I can't figure out where it is falling down.

Here is the statement that is currently being generated:

UPDATE table SET (, 'field1', 'field2', 'field3') = (, 'Huey', 'Dewey', 'Louie') WHERE field4 = 'Donald'

The leading commas gave me pause, but when I feed the statement in through MySQL Control Center without the commas it still gives a syntax error.

This is probably a "can't see the forest for the trees" situation, but I just don't know which trees I should cut down...