Adding "Filler" Data to a DB

Morning,

I inherited a PHP series of web pages and need to bounce something off a real programmer. I have a chunk of code that inserts some information into a DB when we get an on-line applicant. It is here:

/**
 *
 * @@param string full name
 * @@param string first name
 * @@param string last name
 * @@param string email
 * @@param string program code
 * @@param password
 *
 * @@return int application number
 */
function myapplication_create($name, $first_name, $last_name, $email, $program, $password) {
	// create the applicant record
	$db = myapplication_db();

	$id = myapplication_insert_query(
		$db->query("INSERT INTO applicant (name_common,name_given,name_family,email,program_code,password,status,ts_create)
					VALUES (?,?,?,?,?,?,'draft',CURRENT_TIMESTAMP)",
					array($name,$first_name,$last_name,$email,$program,md5($password)))
	);

PHP:

This part works fine. However, I need to add a chunk of code that will run after this. We have two new programs called "PDAS" and "PDHP". When those programs are being applied for I need to put additional data into some other DB fields to fool other parts of the code into believing it has already been done. I wrote this bit of code here:

/**
 * Adding new course programs. Writes additional fields into DB if PDAS or PDHP program
 *Added 4 July 07
 *
 * Unneeded data is entered as "N.A."
 */

if(substr($application['program_code'], 0, 4) == "PDAS" | "PDHP")

	$id = myapplication_insert_query(
		$db->query("INSERT INTO applicant (employment_status,cv_file_id,appfee_payment_ref,appfee_payment_date,essay_file_id)
					VALUES ('N.A.','999999','N.A.',CURRENT_TIMESTAMP,'999999')",
					array(What goes here if anything???))
	);

PHP:

... and am unsure what goes in the "array" brackets as this is just filler info that does not exist in the system. Do I even need it? I just need to insert the same data into the fields regardless of the applicant ID, but only if the programs are PDAS or PDHP...!

The system is live and I cannot play with it as much as I would like. If I can have both pieces of code integrated then that would be best I suspect.

Any suggs appreciated...

 

No, you don't need that array at all in your second example assuming all those attributes in your table are nullable.

It's there in the first one because it populates the latter fields in the expression. But in your second example, you are explicitly populating all your fields.

In other words, in your query, the number of named attributes must match the number of supplied values. You don't have to explicitly state every attribute on the table, but if the attributes don't allow nulls, the statement will fail.

So, in summary, depends on your table structure mate!

 

Good response. Thanks. Now the only other Q is how do I preface the code. Here is the example of the working piece...

function myapplication_create($name, $first_name, $last_name, $email, $program, $password) {
	// create the applicant record
	$db = myapplication_db();

PHP:

So for the other chunk do I use:

function myapplication_addonstuff() {
	// modify the applicant record with filler info
	$db = myapplication_db();
 $id = myapplication_insert_query(
        $db->query("INSERT INTO applicant (employment_status,cv_file_id,appfee_payment_ref,appfee_payment_date,essay_file_id)
                    VALUES ('N.A.','999999','N.A.',CURRENT_TIMESTAMP,'999999')",)
    );

PHP:

... Syntax checks out, but will this work?

 

Looks fine to me!

But, if in doubt, clone the database! Really, you shouldn't mess with a live database anyway.

Or if that's not possible, simply create a similar table in the same database and try it on that table first, then alter the code to reference the original table when you're sure it's ok.

 

Yuck database inserts, they're such a pain keeping the params to match up with values. This should make things a whole lot cleaner once you make the slight changes you need to make it work with your system.

function insert($table,$record)
	{
		
		// Setup fields and values
		$fields ="(";
		$values ="(";
		// Insert the fields and the values
		foreach ($record as $k => $v)
		{
			$fields .= '`'.$k.'`,';
            $values .= "'".mysql_escape_string($v)."',";
		}
		// Trim the extra , then add the remaining ) to make them valid sql
		$fields = substr($fields,0,-1);
		$values = substr($values,0,-1);
		$fields .= ")";
		$values .= ")";
		$sql = "INSERT INTO ".$table."".$fields."VALUES ".$values."";
		
		return $sql;
	}

PHP:

This should all work assuming you're values can take null / default in the insert. Any required fields you will need to specify in the array but that's about it. A valid array for this setup would be

$array = array 
(
      'param' => 'value'
);

PHP:

 

Agreed on the database diddling, but the guy who wrote this mess over-engineered it to the Nth degree and a change on one page will screw up another page three levels down. No long term planning and almost impossible to amend the code without killing the system. Talk about a house of cards. As soon as a job opens up in Bermuda I am so there...

I keep hitting the wall with code changes. Everytime I include the new code I get a blank white screen. Will keep at it. Thanks guys...

 

Sounds like you have error reporting disabled or set to ~E_ALL. If you enable the errors it's a lot easier to figure out what's going on.