DB API for SQL Insert?

54 Posts

luke.stokes Reply #1, 20 years, 10 months ago

Sorry for such a newbie question... I just started coding with MODx a couple hours ago (been doing php/asp/web stuff for years though). I couldn’t find the API for doing a db insert in the documentation. I tried coding it up just like the update using a $fields array but that didn’t work.

Any help please?

Thanks a bunch.

3,250 Posts

MARKSVIRTUALDESK Reply #2, 20 years, 10 months ago

Here is the URL: http://modxcms.com/dbapi.html

Unfortunately, I don’t know what happended to that bit of documentation, but here is the actual function from the core. Hopefully this will show you what fields you must use when you call it like so:

$modx->db->insert(parameters go here);

	/**
	 *	@name:	insert
	 *	@desc:	returns either last id inserted or the result from the query
	 */
	function insert($fields, $intotable, $fromfields="*", $fromtable="", $where="", $limit="") {
		if(!$intotable) return false;
		else {
			if(!is_array($fields)) $flds = $fields;
			else {
				$keys = array_keys($fields);
				$values = array_values($fields);
				$flds = "(".implode(",",$keys).") ".
						(!$fromtable && $values ? "VALUES('".implode("','",$values)."')":"");
				if($fromtable) {
					$where = ($where != "") ? "WHERE $where" : "";
					$limit = ($limit != "") ? "LIMIT $limit" : "";
					$sql="SELECT $fromfields FROM $fromtable $where $limit";	
				}
			}
			$rt = $this->query("INSERT INTO $intotable $flds $sql;");	
			$lid = mysql_insert_id();
			return $lid ? $lid:$rt;
		}	
	}

54 Posts

luke.stokes Reply #3, 20 years, 10 months ago

Thanks!

I guess I was using it right then, as it seems similar to the update function. My error was before that:

$rows_affected = $this->db->update("value='" . $value . "'", $this->getFullTableName("site_tmplvar_contentvalues") , "tmplvarid=".$row['id']." AND contentid=".$docid);
// if $rows_affected = 0 then this TV value doesn't exist to be updated.
// that means we need to do an insert.
// if ($rows_affected == 0) {
    $fields = array(
       "tmplvarid" => $row['id'],
       "contentid" => $docid,
       "value" => $value
    );
   $rows_affected = $this->db->insert($fields, $this->getFullTableName("site_tmplvar_contentvalues"));
//		   }

The "if ($rows_affected == 0)" didn’t seem to work like I thought it would. When I commented it out, it works just fine. Strange... am I missing something? If the update doesn’t hit any records (because none exist with that where string) shouldn’t rows_affected be zero? Or should I just do if (!$rows_affected)

I’m still playing with it... working on The Man Can’s setting template variables from NewsPublisher.

Thanks again.

3,250 Posts

MARKSVIRTUALDESK Reply #4, 20 years, 10 months ago

There is actually a new API for setting a TV by theo floating around the forums, might want to check that first.

54 Posts

luke.stokes Reply #5, 20 years, 10 months ago

Looking... can’t find it though. Can you point me in the right direction?

Here’s what I’m trying to do:

function setTV($tvname, $value, $docid="") {
if($tvname== "") return false;
   if($docid == "") $docid = $this->documentIdentifier;
   //get id of tv
   $result=$this->db->select("id", $this->getFullTableName("site_tmplvars"), "name = '". $tvname . "'");
   if (!$result) return false;
   $row=$this->fetchRow($result);
   $rows_affected = $this->db->update("value='" . $value . "'", $this->getFullTableName("site_tmplvar_contentvalues") , "tmplvarid=".$row['id']." AND contentid=".$docid);
   if ($rows_affected == 0) {
	$fields = array(
	"tmplvarid" => $row['id'],
	"contentid" => $docid,
	"value" => $value
	);
	$rows_affected = $this->db->insert($fields, $this->getFullTableName("site_tmplvar_contentvalues"));

    }
   return $rows_affected;
}

When you first create a page there are no TV values stored in the database so the update function doesn’t do anything. So... I’m trying to say if the number of affected rows is zero, then do an insert. For some reason this part is failing:

if ($rows_affected == 0) {

Any idea why?

3,250 Posts

MARKSVIRTUALDESK Reply #6, 20 years, 10 months ago

If you are running this in a snippet you still have $this in there instead of $modx!!!