$query = "INSERT INTO shopx_orders SET [everything you want to insert here...]";
$orderdb = mysql_connect($order_database_server,$order_database_user,$order_database_password) or die("cannot connect to database");
$selected = @mysql_select_db($order_dbase_name, $orderdb) or die ("cannot select database");
$result = mysql_query($query) or die ("cannot query the database");
$orderID = mysql_insert_id();
mysql_close($orderdb);
☆ A M B ☆
Now this is great! Combining the powerful eForm system with database manipulation just puts the pieces together
I’m up for altering a different database than the one MODx uses, so I guess I’d have to go around the MODx DBAPI and create a custom mySQL-call instead. Any idea how to accomplish this? Would it be just to replace$dbQuery = $modx->db->insert( $dbTable, $table_prefix . 'insertTableName' );
...With a simple mySQL-query?
Thanks, and keep up the good work!
Quote from: Frank at Aug 30, 2007, 05:10 PM
Any response to this question? Or perhaps a usefull link? Or do I really need to study MySQL to understand what to do?
Fortunately, this will not be required! However, some general knowledge of PHP and MODx’s DBAPI class definitely could not hurt.
eForm2db is mainly exposure to the MODx DBAPI mashed up with eForm’s event functions, illustrating how to combine the 2 into a manageable solution for interfacing with the database via HTML form submissions.
In all reality, familiarizing yourself with the DBAPI should get you pretty close if all you need to do is read/write rows to your MySQL tables...
More on the DBAPI:
http://www.modxcms.com/dbapi.html
What a happy moment was that! And what a great snippet you put together.
<?php
$idpubdetails = $_GET['idpd'];
$idpubmod = $_GET['idpmod'];
$sqlConn = @mysql_connect('localhost','user','pw');
if (!$sqlConn) {
exit('<p>Unable publisher ' .
'database at this time.</p>');
}
$db = mysql_select_db('modx', $sqlConn);
// 001: Database query
$query = "select name from publish order by idpub ASC";
// 002: Execute query
$res = mysql_query($query, $sqlConn);
if (!$res)
{
die ('Could not run query: ' . mysql_error());
}
// everything went ok, so we can display the data
echo "<h4>List of Publishers</h4>";
echo " <table border=\"1\" cols=\"2\" cellpadding=\"0\" cellspacing=\"0\" width=\"400\">";
while ($row = mysql_fetch_array($res, MYSQL_BOTH))
{
$reccnt++;
$idpub = $row[0];
$publisher = $row[8];
$pubmodlink = "<a href=\"/modx/index.php?id=".$idpubmod."&idpub=".$idpub."\">";
$pubmodlink = $pubmodlink . $idpub . "</a>";
$publink = "<a href=\"/modx/index.php?id=".$idpubdetails."&idpub=".$idpub."\">";
$publink = $publink . $name . "</a>";
echo " <tr>";
echo " <td valign=\"top\">" . $pubmodlink . "</td>";
echo " <td valign=\"top\">" . $publink . "</td>";
echo " </tr>";
}
echo " </table>";
mysql_free_result($res);
mysql_close($sqlConn);
?><?php
$idpub = $_GET['idpub'];
$sqlConn = mysql_connect('localhost','user','pw');
if (!$sqlConn)
{
die ('unable to connect' . mysql_error());
}
$db = mysql_select_db('modx', $sqlConn);
// read in the data of the selected record
$query = "select * from publish where idpub =$idpub";
// 002: Execute query
$res = mysql_query($query, $sqlConn);
if (!$res)
{
die ('Could not run query: ' . mysql_error());
}
if ($row = mysql_fetch_array($res, MYSQL_BOTH))
{
$abbreviation = $row[1];
$address = $row[2];
$publisher = $row[3];
$website = $row[4];
$email = $row[5];
$oddities = $row[6];
$telephone = $row[7];
}
echo "<h4>Publisher details</h4>";
echo " <table border=\"0\" cols=\"2\" cellpadding=\"0\" cellspacing=\"0\" width=\"400\">";
echo " <tr>";
echo " <td width=\"60\"></td>";
echo " <td width=\"340\"></td>";
echo " </tr>";
echo " <tr>";
echo " <td width=\"60\" valign=\"top\">Abbreviation:</td>";
echo " <td>".$abbreviation."</td>";
echo " </tr>";
echo " <tr>";
echo " <td width=\"60\" valign=\"top\">Address:</td>";
echo " <td>".$address."</td>";
echo " </tr>";
echo " <tr>";
echo " <td width=\"60\" valign=\"top\">Publisher:</td>";
echo " <td>".$publisher."</td>";
echo " </tr>";
echo " <tr>";
echo " <td width=\"60\" valign=\"top\">Website:</td>";
echo " <td>".$website."</td>";
echo " </tr>";
echo " <tr>";
echo " <td width=\"60\" valign=\"top\">Email:</td>";
echo " <td>".$email."</td>";
echo " </tr>";
echo " <tr>";
echo " <td width=\"60\" valign=\"top\">Telephone:</td>";
echo " <td>".$telephone."</td>";
echo " </tr>";
echo " <tr>";
echo " <td width=\"60\" valign=\"top\">Oddities:</td>";
echo " <td>".$oddities."</td>";
echo " </tr>";
echo " <tr>";
echo " <td width=\"60\" valign=\"top\"> </td>";
echo " <td></td>";
echo " </tr>";
echo " </table>";
mysql_close($sqlConn);
?>
☆ A M B ☆
<h2>Venues Snippet</h2>
[[show-venues]]
<?php
$dbase = $modx->dbConfig[’dbase’];
$table_prefix = $modx->dbConfig[’table_prefix’];
$usernameid = $modx->getLoginUserID();
$sql = "SELECT *,modx_rsvp_venues.id as venue_id FROM $dbase.".$table_prefix."rsvp_venues,".$table_prefix."rsvp_events WHERE modx_rsvp_events.member_id = ’" .$usernameid ."’ AND modx_rsvp_venues.member_id = ’" .$usernameid ."’ ORDER BY venue_order ASC";
$result = mysql_query($sql);
$count = mysql_num_rows($result);
if ($count == "0") {
// Show that nothing was found
$output = "<p>Sorry no venues have been found. You should add one but make sure you add an Event first!</p>";
} else {
// Show the event
$output = "<p>Your Venue Details are as follows: </p>
";
$output .= ’<table summary="Submitted table designs">
<caption>
</caption>
<thead>
<tr>
<th width="125" scope="col"><div align="left">Options</div></th>
<th width="210" scope="col"><div align="left">Venue Name</div></th>
<th width="97" scope="col"><div align="left">Time</div></th>
</tr>
</thead>
<tbody>’;
$ia = "0";
while ($row = mysql_fetch_assoc($result)) {
if ($ia == "1") {
$myclass = "odd";
} else {
$myclass = "";
}
$output .= ’<tr class="’ .$myclass .’" ><th scope="row" id="r86"><a href="[~67~]?id=’ .$row[’venue_id’] .’" border="0"><img src="assets/images/icons/edit.gif" alt="Edit Venue" title="Edit Venue" style="border:0"/></a> | <a href="[~75~]?id=’ .$row[’venue_id’] .’" border="0"><img src="assets/images/icons/delete.gif" alt="Delete Venue" title="Delete Venue" style="border:0"/></a></th>’; $output .= ’<td>’ .$row[’venue_name’] .’</td>’;
$output .= ’<td>’ .$row[’venue_time’] .’</td></tr>’;
if ($ia == "1") {
$myclass = "odd";
$ia = "0";
} else {
$ia = $ia + 1;
}
}
}
$output .= ’</tbody></table>’;
mysql_free_result($result);
return $output;
?>
[[populateVenueForm]]
[[Venues2db]]
[[eForm? &formid=`venue_form` &tpl=`venue_form` &noemail=`1` &eFormOnBeforeMailSent=`Venues2db` &eFormOnBeforeFormParse=`populateVenueForm’]]
<?php
function populateVenueForm(&$fields, &$templates) {
global $modx; // Access to DocumentParser
$usernameid = $modx->getLoginUserID(); // Gets current Web user ID
$venue_id = $_GET[’id’];
// This tells us what message to load from the db (the ’id’ column)
// $mail_id = isset($_REQUEST[’mail_id’]) ? intval($_REQUEST[’mail_id’]) : 0;
$evt =& $modx->event; // The Snippet call event (not eFormOnBeforeFormParse event)
// Replicate $isPostBack variable
$validFormId = ($evt->params[’add_venue’] == $_POST[’add_venue’]);
$isPostBack = ($validFormId && count($_POST) > 0); // (bool)true if the form is being updated
if (!$isPostBack && $venue_id > 0) {
// Check for NOT postback, cuz we want to populate an empty form
// Also, make sure we have an id greater than 0
$rs = $modx->db->select(’`id`,`venue_name`, `venue_address`, `venue_postcode`, `venue_time`’,’modx_rsvp_venues’,’id=’. $venue_id);
$row = $modx->db->getRow($rs); // Get the single row
if (!empty($row)) // Does the row have data?
$fields[venue_name] = $row[venue_name]; // Copy the data over to the fields variable
$fields[venue_address] = $row[venue_address];
$fields[venue_postcode] = $row[venue_postcode];
$fields[venue_time] = $row[venue_time];
$fields[venue_id] = $row[id];
} else {
// The form has beed posted, let’s not disturb the data.
}
return true; // A value of false will stop the eForm parser
}
?>
<?php
function Venues2db( &$fields )
{
/*---------------------------------------------------------------
eForm2db
Version: 0.1 [Beta 1]
Author: pixelchutes
// Bring needed resources into scope
global $modx, $table_prefix;
$usernameid = $modx->getLoginUserID();
$vid = $fields[venue_id];
// Init our array
$dbTable = array(); // key = DB Column; Value = Insert/Update value
$dbTable[member_id] = $usernameid;
$dbTable[venue_name] = $fields[venue_name];
$dbTable[venue_address] = $fields[venue_address];
$dbTable[venue_postcode] = $fields[venue_postcode];
$dbTable[venue_time] = $fields[venue_time];
if ($vid != "") {
$dbQuery = $modx->db->update($dbTable, $table_prefix . ’rsvp_venues’, ’id = "’ .$vid .’"’ );
return true;
} else {
$dbQuery = $modx->db->insert( $dbTable, $table_prefix . ’rsvp_venues’ );
return true;
}
}
// Return empty string
return ’’;
?>
<span style="color:#900;">[+validationmessage+]</span>
<form method="post" name="venue_form" id="venue_form" class="cssform" action="[~[*id*]~]">
<p>
<label for="venue_name">Venue Name:</label>
<input type="text" name="venue_name" id="venue_name" eform="Venue name:string:1" value="[+venue_name+]" size="20" maxlength="30" />
</p>
<p>
<label for="venue_address"> Address :</label>
<textarea name="venue_address" cols="20" rows="5" id="venue_address" eform="Venue address:string:1">[+venue_address+]</textarea>
</p>
<p>
<label for="venue_postcode"> Postcode:</label>
<input type="text" name="venue_postcode" id="venue_postcode" value="[+venue_postcode+]" eform="Venue postcode:string:1" size="20" />
</p>
<p>
<label for="venue_time">Time:</label>
<input type="text" name="venue_time" id="venue_time" value="[+venue_time+]" eform="Venue Time:string:0" size="20" />
<p>
</p>
</p>
<input type="text"style="display:none;" name="venue_id" value="[+venue_id+]" eform="venue_id::0"/>
<p>
<label for="na"></label>
<input type="submit" value="Save" name="cmd_save" />
</fieldset>
</p>
</form>
☆ A M B ☆
So in the end it will be required?
<tr class="[+row_class+]" > <th scope="row" id="[+row_id+]"> <a href="[~67~]?id=[+venue_id+]" border="0"><img src="assets/images/icons/edit.gif" alt="Edit Venue" title="Edit Venue" style="border:0"/></a> | <a href="[~75~]?id=[+venue_id+]" border="0"><img src="assets/images/icons/delete.gif" alt="Delete Venue" title="Delete Venue" style="border:0"/></a> </th> <td>[+venue_name+]</td> <td>[+venue_time+]</td> </tr>
//<?php // For syntax highlighting only // Create the replacement array $chunkArray['venue_name'] = $nameOfVenue; $chunkArray['venue_time'] = $timeAtVenue; // Grab the HTML template, replace placeholders with current row data, and append to $return_html $return_html .= $modx->parseChunk( 'myRowData', $chunkArray, '[+', '+]' ); // ... some more processing // Return the processed template return $return_html;
☆ A M B ☆
☆ A M B ☆