building insert query in function

[crawfd]

I'm somewhat confused on how to build an insert query that will take $this->tagPositions and insert it as a record in my db. It's an decrypted block of text.

• Justin

function decompPositions($positions)
{
$this->tagPositions = unserialize(base64_decode(urldecode($positions)));
}

[bubblenut]

Can you give a little more code? I don't quite understand what you need. What is the relavence of the function you provided.

Here's my guess, but I'm feeling in the dark with the amount of information you've provided.

class SomethingWithATag {
  function buildInsert() {
    $sql = "INSERT INTO the_table ".
                "(tag_position) VALUES ('".mysql_real_escape_string($this->tagPosition)."')";
    return mysql_query($sql, $this->dbcon);
  }
}

Obviously this example is assuming a lot

[crawfd]

I'm actually probably using the insert in the wrong area of my whole script.

Below I'd like to add an insert query after $this->replaceTags. The $this->decompPositions function and the $this->replaceTags function creates the final story with words that come from an array that is parsed.

• Justin

function displayMadlib()
{
	$this->getStory();
	if (!isset($_POST['positions']))
	{
		if ($this->inText['story'])
		{
			$this->getTagPositions();
			$this->showForm();
		}
		else
		{
			echo 'No madlib story has been loaded.';
		}
	}
	else
	{
		if ($this->inText['story'])
		{
			$this->decompPositions($_POST['positions']);
			$this->replaceTags($_POST['word']);

[bubblenut]

...but what do you want to insert into the database? What does the table in the database look like and what do you want fields do you want to put in there? What database system are you using? Are you confused on the SQL syntax of the INSERT statement or how to user the database API?

You need to help me help you.

[crawfd]

When someone loads a form and then submits it I need to save the results to a db with a table named 'results'. In this table the fields would be id, title, and story.
I commented the areas below with more detail. Thanks

//displays madlib
function displayMadlib()
{
$this->getStory();
if (!isset($_POST['positions']))
{

if ($this->inText['story'])
{
$this->getTagPositions();
$this->showForm();
}
else
{
echo 'No madlib story has been loaded.';
}
}
else
{
if ($this->inText['story'])
{
$this->decompPositions($_POST['positions']); //function below
$this->replaceTags($_POST['word']); //function below
// sql insert statment here that that takes the result of the these 2 functions
//(the results of these 2 functions is the complete story with replaced tags)
//and places it into a field called 'story' in a table called results. I also need to insert
//it's respective title ($this->inText['title'} and id, $this->inText['id'}).


		}
		else
		{
			echo 'No madlib story has been loaded.';
		}






// does the tag substitution for the finished story
//
function replaceTags($word_list)
{
$this->outText = $this->inText;
if (!empty($this->tagPositions))
{
			$tags = array_reverse($this->tagPositions, TRUE);
			foreach ($tags as $position => $type)
			{
				$tag = '{' . $type . '}';
				$this->outText['story'] = substr_replace($this->outText['story'], '<span class="madlibWord">' . $word_list[$position] . '</span>', $position, strlen($tag));
			}
		}
	}



// decompresses the tag information array from a string
// $positions is the compressed structure
//
	function decompPositions($positions)
{
$this->tagPositions = unserialize(base64_decode(urldecode($positions)));


//retrieves story from db
	function getStory()
	{
		if ($this->useType == 'db')
		{
			$sql = "SELECT * FROM {$this->dbTbl} " .
					($_POST['id'] ? "WHERE id = {$_POST['id']}" : "ORDER BY RAND() LIMIT 1");
			$result = @mysql_query($sql, $this->dbID);
			if (mysql_num_rows($result))
			{
				$data = @mysql_fetch_object($result);
				$this->inText['id']    = $data->id;
				$this->inText['title'] = trim(stripslashes($data->title));
				$this->inText['story'] = trim(stripslashes($data->story));
			}
		}
}

[bubblenut]

From what you've told me the best I can say is this

$sql =  "INSERT INTO results (id, title, story) ".
        "VALUES ('".
        mysql_real_escape_string($this->inText['id'])."', '".
        mysql_real_escape_string($this->inText['title'])."', '".
        mysql_real_escape_string($this->inText['story'])."')";
if(!mysql_query($sql, $this->dbID)) {
    trigger_error("Failed Query: ".$sql." [".mysql_error($this->dbID)."]", E_USER_WARNING);
    return false;
} else {
    return true;
}

however, I doubt this is what you want. If I'm correct I would guess that your ID field is an auto_incrementing field and when you write back to the DB you only want to INSERT if the object has no id otherwise, you want to UPDATE.

if($this->inText['id']) {
    $sql =  "UPDATE results SET ".
            "title = '".mysql_escape_string($this->inText['title'])."', ".
            "story = '".mysql_escape_string($this->inText['story'])."' ".
            "WHERE id=".$this->inText['id'];
} else {
    $sql =  "INSERT INTO results (id, title, story) ".
            "VALUES ('".
            mysql_real_escape_string($this->inText['id'])."', '".
            mysql_real_escape_string($this->inText['title'])."', '".
            mysql_real_escape_string($this->inText['story'])."')";
}
if(!mysql_query($sql, $this->dbID)) {
    trigger_error("Failed Query: ".$sql." [".mysql_error($this->dbID)."]", E_USER_WARNING);
    return false;
} else {
    if(!$this->inText['id']) {
        $this->inText['id'] = mysql_insert_id($this->dbID);
    }
    return true;
}

[crawfd]

Thanks it's inserting but inserting the story without the replaced tags so I'm assuming I'll need to take the result of the replaceTags function and use it as the insert value for the 'story'.....I'm getting a parse error on the fourth line of this with the semi-colon but I don't see the parse error.

//put results into db
$sql =  "INSERT INTO mdresults (id, title, story) ". "VALUES ('". 
        mysql_real_escape_string($this->inText['id'])."', '". 
        mysql_real_escape_string($this->inText['title'])."', '". 
        mysql_real_escape_string($this->replaceTags($_POST['word'])."')";
//end inputting results