Inserting into Database (Updating old code)

sirugh · May 27, 2010

Hey guys, I'm pretty new to PHP so fair warning

Anyways, I have bitnami wappstack set up on my windows xp box, running php 5.3, apache, and postgresql. I was given an assignment to update some old code for a conference registration that includes both group and individual registration and tables for both in the database. Both group.php and individual.php (class definitions) have a function to commit the information to the database. In group, this function works properly and inserts whatever info into the db.

	function commit()
{
	global $db_conn;
	// prepare a query to store the basic group info
	$id = md5(uniqid(rand(), true));
	$confirmation = md5(uniqid(rand(), true));
	$fields = array('id'=>'\'' . $id . '\'');
	$fields['paid'] = 'FALSE';
	$fields['confirmation'] = "'$confirmation'";
	foreach (array('institution', 'region', 'first_name', 'last_name', 'title', 'phone', 'fax', 'email') as $val)
	{
		if (!empty($this->data[$val]))
		$fields[$val] = "'" . db_escape_string($this->data[$val]) . "'";
	}
	$query = 'INSERT INTO conf_2010_group_regs(' . implode(', ', array_keys($fields)) . ') VALUES (' . implode(', ', $fields) . ')';
	// the following line generates debug output
	//echo htmlspecialchars($query), "<br/>";
	db_query($query, $db_conn);

	// now add each individual
	$template = 'INSERT INTO conf_2010_individual_regs({{{fields}}}) VALUES ({{{values}}})';
	foreach ($this->data['group_members'] as $key=>$individual)
	{
		$individual['id'] = md5(uniqid(rand(), true));
		// TODO: can remove this line if we change the foreach above to use &$individual instead of $individual
		$this->data['group_members'][$key]['id'] = $individual['id'];

		$fields = array();
		$fields['id'] = '\'' . $individual['id'] . '\'';
		$fields['group_id'] = '\'' . $id . '\'';
		$fields['institution'] = '\'' . db_escape_string($this->data['institution']) . '\'';
		$fields['region'] = '\'' . db_escape_string($this->data['region']) . '\'';
		$fields['room'] = '\'\'';
		foreach (array('first_name', 'last_name', 'title', 'email', 'registrant_type') as $val)
		{
			if (!empty($individual[$val]))
				$fields[$val] = '\'' . db_escape_string($individual[$val]) . '\'';
		}
		$query = str_replace(array('{{{fields}}}', '{{{values}}}'), array(implode(', ', array_keys($fields)), implode(', ', $fields)), $template);
		// The following two lines generate debug output.  Now obsolete since I actually have a decent debugger.
		//echo htmlspecialchars($query), '<br/>';
		//echo db_error($db_conn), '<br/>';
		db_query($query, $db_conn);
	}
	$this->id = $id;
	$this->confirmation = $confirmation;

The above code works properly.

Below, is from the individual.php script and it does not work. Neither the updateDB or insertDB function works.

	function insertDB()
{
	$this->getRegistrationPrice();
	$this->getAccommodationPrice();

	// figure out our database id
	$this->db_key = md5(uniqid(rand(), true));

	// and add this to the database
	$template = 'INSERT INTO conf_2010_individual_regs({{{fields}}}) VALUES ({{{values}}})';
	$elements = array();
	$elements['id'] = '\''. $this->db_key . '\'';
	$elements['confirmation'] = '\'' . md5(uniqid(rand(), true)) . '\'';
	$elements['reg_price'] = $this->registration_fee;
	$elements['room_price'] = $this->accommodation_fee;
	if (!is_null($this->payment_method))
		$elements['payment_method'] = $this->payment_method;
	else
		$elements['payment_method'] = 'unspecified';
	// reg_paid defaults to false
	// room_paid defaults to false
	foreach (array('first_name', 'last_name', 'title', 'region', 'institution', 'registrant_type', 'phone', 'fax', 'email',
	               'room', 'lodging_info',
	               'arrival_date', 'arrival_hour', 'arrival_minute', 'departure_date', 'departure_hour', 'departure_minute', 'carrier', 'meal_type', 'other_food', 'payment_method') as $val)
	{
		$elements[$val] = '\'' . db_escape_string($this->data[$val]) . '\'';
	}

	foreach (array('night5', 'night6', 'night7', 'night8', 'night9', 'night10', 'night11', 'night12', 'night13') as $val)
	{
		$elements[$val] = (($this->data[$val] == 'on') ? 'true' : 'false');
	}

	// TODO: modify to support updating previous entries (i.e., setting accommodation info for group members)
	$fields = implode(', ', array_keys($elements));
	$values = implode(', ', $elements);
	$query = str_replace(array('{{{fields}}}', '{{{values}}}'), array($fields, $values), $template);
	db_query($query, $db_conn);
	if (db_query($query) !== FALSE)
	{
		// now the nights
		mail($this->data['email'], 'Conference registration', "Thank you for your registration.  Please note that you have not paid.  Your itemized receipt is as follows, and will be due upon arriving at the conference: \n $this->receipt");
		return true;
	}
	else
	{
		require_once('views/individual_registration_failed.php');
		return false;
	}
}

function updateDB()
{
	$this->getAccommodationPrice();

	// and add this to the database
	$template = "UPDATE conf_2010_individual_regs SET {{{fields}}} WHERE id = '" . db_escape_string($this->db_key) . "'";
	$elements = array();
	$elements['room_price'] = $this->accommodation_fee;
	if (!is_null($this->payment_method))
		$elements['payment_method'] = $this->payment_method;
	else
		$elements['payment_method'] = 'unspecified';

	// reg_paid defaults to false
	// room_paid defaults to false
	foreach (array('first_name', 'last_name', 'title', 'region', 'institution', 'registrant_type', 'phone', 'fax', 'email',
	               'room', 'lodging_info',
	               'arrival_date', 'arrival_hour', 'arrival_minute', 'departure_date', 'departure_hour', 'departure_minute', 'carrier', 'meal_type', 'other_food', 'payment_method') as $val)
	{
		$elements[$val] = '\'' . db_escape_string($this->data[$val]) . '\'';
	}

	foreach (array('night5', 'night6', 'night7', 'night8', 'night9', 'night10', 'night11', 'night12', 'night13') as $val)
	{
		$elements[$val] = (($this->data[$val] == 'on') ? 'true' : 'false');
	}

	$pairs = array();
	foreach ($elements as $key=>$val)
	{
		$pairs[] = "$key=$val";
	}

	// TODO: modify to support updating previous entries (i.e., setting accommodation info for group members)
	$fields = implode(', ', $pairs);
	$query = str_replace('{{{fields}}}', $fields, $template);
	db_query($query, $db_conn);
	if (db_query($query) !== FALSE)
	{
		// now the nights
		mail($this->data['email'], 'Conference registration', "Thank you for updating your registration details.  Your itemized receipt is as follows, and will be due upon arriving at the conference, unless you chose to pay online: \n $this->receipt");
		return true;
	}
	else
	{
		require_once('views/individual_registration_failed.php');
		return false;
	}
}

If anyone can help, it would be greatly appreciated! If you need more code to get a better understanding, I can put more up just let me know.

-Stephen

-Karl- · May 27, 2010

Have you echoed the query? To see if everything is being set correctly?

echo $query;

sirugh · May 27, 2010

Have you echoed the query? To see if everything is being set correctly?

echo $query;

Where would I do the echo? The individual.php isn't the script that the user sees. I have a view/individual_review.php code that the user sees and once they press the submit button, that code runs $individual->commit(); which is supposed to either update or insert into the db and then direct them forward to the next page.

-Karl- · May 27, 2010

Well, there is one way. I see you send an email, maybe echo the query in the email to check if everything is fine.

sirugh · May 27, 2010

I can try that, just don't have it uploaded to a webserver or have an active mailserver up and running.

Also, ignore the db_query line in the individual.php class, I added that but it didn't fix anything.. just can't edit the php above.

-Karl- · May 27, 2010

I can try that, just don't have it uploaded to a webserver or have an active mailserver up and running.

Also, I added the db_query line in individual.php, I'll remove that since it didn't fix anything.

Well just upload it, simply for debugging purposes.

sirugh · May 27, 2010

I've narrowed the issue down to the following code:

		$fields = implode(', ', array_keys($elements));
	$values = implode(', ', $elements);
	//$query = str_replace(array('{{{fields}}}', '{{{values}}}'), array(implode(', ', array_keys($elements)), implode(', ', $elements)), $template);
	$query = str_replace(array('{{{fields}}}', '{{{values}}}'), array($fields, $values), $template);

	//problem lies here, if below if statement is set to == FALSE, then the mail is sent so db_query is not working properly
	if (db_query($query) !== FALSE)
	{
		// now the nights
		mail($this->data['email'], 'AUDEM Conference registration', "Thank you for your registration.  Please note that you have not paid.  Your itemized receipt is as follows, and will be due upon arriving at the conference: \n $this->receipt");
		return true;
	}
	else if (db_query($query) == FALSE)
	{
		//This method sends the email if query fails, meaning the query did not follow through
		mail($this->data['email'], 'You failed.', "Your query failed.");
		return true;
	}
	else
	{
		require_once('views/individual_registration_failed.php');
		return false;
	}

How do I email the query? The else if statement gets executed and I receive the email saying "You Failed", but what do I need to send to see what the query is trying to do?

If I put:

mail($this->data['email'], '"Here is your query: \n $query");

I get back The query

 INSERT INTO conf_2009_individual_regs(id, confirmation, reg_price, room_price, payment_method, first_name, last_name, title, region, institution, registrant_type, phone, fax, email, room, lodging_info, arrival_date, arrival_hour, arrival_minute, departure_date, departure_hour, departure_minute, carrier, meal_type, other_food, night7, night8, night9, night10, night11, night12, night13) VALUES ('6abb582a33d3b9793640db88e8d5ff88', 'e0161feaf5c773c5ee31ee86c7d2e695', 250, 0, '', 'asdf', 'asdf', '', 'cee', 'Test University', 'regular', 'adsf', 'asdf', '[email protected]', 'std_single', '', '7', '00', '00', '11', '00', '00', '', 'regular', '', true, true, true, true, false, false, false)

sirugh · May 27, 2010

Edit above: I found how to email the query, just don't know how to interpret the info.

sirugh · May 28, 2010

:-\

To clarify, using the following code:

	function insertDB()
{
	$this->getRegistrationPrice();
	$this->getAccommodationPrice();

	// figure out our database id
	$this->db_key = md5(uniqid(rand(), true));

	// and add this to the database
	$template = 'INSERT INTO conf_2010_individual_regs({{{fields}}}) VALUES ({{{values}}})';
	$elements = array();
	$elements['id'] = '\''. $this->db_key . '\'';
	$elements['confirmation'] = '\'' . md5(uniqid(rand(), true)) . '\'';
	$elements['reg_price'] = $this->registration_fee;
	$elements['room_price'] = $this->accommodation_fee;
	if (!is_null($this->payment_method))
		$elements['payment_method'] = $this->payment_method;
	else
		$elements['payment_method'] = 'unspecified';
	// reg_paid defaults to false
	// room_paid defaults to false
	foreach (array('first_name', 'last_name', 'title', 'region', 'institution', 'registrant_type', 'phone', 'fax', 'email',
	               'room', 'lodging_info',
	               'arrival_date', 'arrival_hour', 'arrival_minute', 'departure_date', 'departure_hour', 'departure_minute', 'carrier', 'meal_type', 'other_food', 'payment_method') as $val)
	{
		$elements[$val] = '\'' . db_escape_string($this->data[$val]) . '\'';
	}

	foreach (array('night5', 'night6', 'night7', 'night8', 'night9', 'night10', 'night11', 'night12', 'night13') as $val)
	{
		$elements[$val] = (($this->data[$val] == 'on') ? 'true' : 'false');
	}

	// TODO: modify to support updating previous entries (i.e., setting accommodation info for group members)
	$fields = implode(', ', array_keys($elements));
	$values = implode(', ', $elements);
	$query = str_replace(array('{{{fields}}}', '{{{values}}}'), array($fields, $values), $template);

	if (db_query($query, $db_conn) !== FALSE)
	{
		/*email script
		mail($this->data['email'], 'AUDEM Conference registration', "Thank you for your registration.  Please note that you have not paid.  Your itemized receipt is as follows, and will be due upon arriving at the conference: \n $this->receipt");*/
		$myFile = "querylog.txt";
		$fh = fopen($myFile, 'w') or die("can't open file");
		$stringData = "The query WORKED and sent:\n";
		fwrite($fh, $stringData);
		$stringData = "$query\n";
		fwrite($fh, $stringData);
		fclose($fh);
		return true;
	}
	else if (db_query($query, $db_conn) == FALSE)
	{
		/*email script
		mail($this->data['email'], 'AUDEM Conference registration', "Thank you for your registration.  Please note that you have not paid.  Your itemized receipt is as follows, and will be due upon arriving at the conference: \n $this->receipt");*/
		$myFile = "querylog.txt";
		$fh = fopen($myFile, 'w') or die("can't open file");
		$stringData = "The query FAILED and sent:\n";
		fwrite($fh, $stringData);
		$stringData = "$query\n";
		fwrite($fh, $stringData);
		fclose($fh);			
		return true;
	}
	else
	{
		require_once('views/individual_registration_failed.php');
		return false;
	}
}

Sends the following text to querylog in the root folder:

The query FAILED and sent:
INSERT INTO conf_2010_individual_regs(id, confirmation, reg_price, room_price, payment_method, first_name, last_name, title, region, institution, registrant_type, phone, fax, email, room, lodging_info, arrival_date, arrival_hour, arrival_minute, departure_date, departure_hour, departure_minute, carrier, meal_type, other_food, night5, night6, night7, night8, night9, night10, night11, night12, night13) VALUES ('28b133c1fc406d6d3b61219dae84e761', 'a3ed9e85c273ce114f20b237ef71ad5e', 325, 0, '', 'asdf', 'asdf', 'Dr.', 'cee', 'asdf', 'regular', 'asdf', 'asdf', 'asdf', 'std_single', '', '7', '00', '00', '11', '00', '00', '', 'regular', '', false, false, true, true, true, true, false, false, false)

Just want to clarify that I've narrowed it down but don't really understand why that query will cause db_query($query) to == false...

Please help!

sirugh · May 28, 2010

didn't really get anymore help but I figured the problem out...

Final answer:

use pg_last_error if you can't find the problem.

Syntax?

string pg_last_error ([ resource $connection ] )

Solved. Found out I was missing a column in my database. Thanks anyways.

Sign In

Inserting into Database (Updating old code)

Recommended Posts

sirugh

Link to comment

Share on other sites

-Karl-

Link to comment

Share on other sites

sirugh

Link to comment

Share on other sites

-Karl-

Link to comment

Share on other sites

sirugh

Link to comment

Share on other sites

-Karl-

Link to comment

Share on other sites

sirugh

Link to comment

Share on other sites

sirugh

Link to comment

Share on other sites

sirugh

Link to comment

Share on other sites

sirugh

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information