Practical PHP

Input from a form into a MySQL-table

The PHP code that is generated by the mysqlphpwriter is very simple. It is simple for a single reason, it is meant as a starting point for your work, and not as finished code. mysqlphpwriter does not generate code that will work at once, you have to make some modifications.

For this example we'll use the same table as the others , the jos_banner from joomla.

The structure is like this:

What we want now is to save the data we have gotten from our form, and store it in the table.

To start off, go to the mysqlphpwriter:

This time we choose "insert" next to the jos_banner. This gives us the following code:

 

function jos_banner_insert($cid, $type, $name, $alias, $imptotal, $impmade, $clicks, $imageurl, $clickurl, $date, $showBanner, $checked_out, $checked_out_time, $editor, $custombannercode, $catid, $description, $sticky, $ordering, $publish_up, $publish_down, $tags, $params){
$sql="INSERT INTO jos_banner ( cid, type, name, alias, imptotal, impmade, clicks, imageurl, clickurl, date, showBanner, checked_out, checked_out_time, editor, custombannercode, catid, description, sticky, ordering, publish_up, publish_down, tags, params) VALUES( '$cid', '$type', '$name', '$alias', '$imptotal', '$impmade', '$clicks', '$imageurl', '$clickurl', '$date', '$showBanner', '$checked_out', '$checked_out_time', '$editor', '$custombannercode', '$catid', '$description', '$sticky', '$ordering', '$publish_up', '$publish_down', '$tags', '$params') ";// practical-php auto-comment://bid has been treated as a autoincremental key and is not included in the insert-sql syntax
$result=mysql_query($sql); }

Well, that saved us quite som time typing, assuming this is what we wanted. This function only supports inserts, and this is rarely what we need. We want updates as well! To get this we have to do some more copy and past'ing.

 

Go to the "Update" link in mysqlphpwriter for the jos_banner-table, and we'll get a full page of code for updating the same table:

function jos_banner_update($bid, $cid, $type, $name, $alias, $imptotal, $impmade, $clicks, $imageurl, $clickurl, $date, $showBanner, $checked_out, $checked_out_time, $editor, $custombannercode, $catid, $description, $sticky, $ordering, $publish_up, $publish_down, $tags, $params){
$sql="UPDATE jos_bannerSET cid = '$cid', type = '$type', name = '$name', alias = '$alias', imptotal = '$imptotal', impmade = '$impmade', clicks = '$clicks', imageurl = '$imageurl', clickurl = '$clickurl', date = '$date', showBanner = '$showBanner', checked_out = '$checked_out', checked_out_time = '$checked_out_time', editor = '$editor', custombannercode = '$custombannercode', catid = '$catid', description = '$description', sticky = '$sticky', ordering = '$ordering', publish_up = '$publish_up', publish_down = '$publish_down', tags = '$tags', params = '$params' WHERE bid = '$bid' ";$result=mysql_query($sql); }

Take the sql -part from this code, and put it into the insert-code within a if-else condition, and we'll have the whole thing solved in a few seconds:

 

function jos_banner_insert($cid, $type, $name, $alias, $imptotal,
$impmade, $clicks, $imageurl, $clickurl, $date, $showBanner,
$checked_out, $checked_out_time, $editor, $custombannercode, $catid,
$description, $sticky, $ordering, $publish_up, $publish_down, $tags,
$params){

if($cid > 0){$sql="
INSERT INTO jos_banner ( cid, type, name, alias, imptotal, impmade, clicks, imageurl, clickurl, date, showBanner, checked_out, checked_out_time, editor, custombannercode, catid, description, sticky, ordering, publish_up, publish_down, tags, params) VALUES( '$cid', '$type', '$name', '$alias', '$imptotal', '$impmade', '$clicks', '$imageurl', '$clickurl', '$date', '$showBanner', '$checked_out', '$checked_out_time', '$editor', '$custombannercode', '$catid', '$description', '$sticky', '$ordering', '$publish_up', '$publish_down', '$tags', '$params') ";}else{ $sql="UPDATE jos_banner
SET cid = '$cid'
, type = '$type'
, name = '$name'
, alias = '$alias'
, imptotal = '$imptotal'
, impmade = '$impmade'
, clicks = '$clicks'
, imageurl = '$imageurl'
, clickurl = '$clickurl'
, date = '$date'
, showBanner = '$showBanner'
, checked_out = '$checked_out'
, checked_out_time = '$checked_out_time'
, editor = '$editor'
, custombannercode = '$custombannercode'
, catid = '$catid'
, description = '$description'
, sticky = '$sticky'
, ordering = '$ordering'
, publish_up = '$publish_up'
, publish_down = '$publish_down'
, tags = '$tags'
, params = '$params' WHERE bid = '$bid' ";
}
// practical-php auto-comment://bid has been treated as a autoincremental key and is not included in the insert-sql syntax
$result=mysql_query($sql); }

Take note of the code in bold .

If there is a unique id with a value of more than 0 ( ie an existing post in hte database) we use the update-block and not the insert block.

So by cutting and pasting , and adding 2 lines of our own, we now have a function that will insert and update the jos_banner table.