abstracting SQL code

[Anon]

I am starting on an online shopping site, using apache, php & mysql...

I have not attempted a project as large as this before in PHP. I have been considering ways of making my code as flexible as possible. One way in which I have heard of doing SQL database work is to abstract the SQL code generation from the actual page code so that SQL is generated on-the-fly. I would be a big fan of doing it this way... however I am not too experienced with SQL at this point in time.

Has anybody got any experience with doing such on-the-fly generation of SQL, who could possibly supply me with some code to demonstrate this. Or just talk about the general structure of such a site.

Thanks in advance!

Adam.

[Anon]

This only works with tables where all the data types are strings unless you are using a loosly (sql) validated database (such as MySQL):

$db->query("insert into $table (" . implode(", ", array_keys($HTTP_POST_VARS)) . ") values ('" . implode("', '", $HTTP_POST_VARS) . "')");

This example will only require that $table be set somewhere. Don't post $table as that would cause a security problem.

[Anon]

I have made something for an intranet solution, that maybe could be inspiring to you.

I can't explain the full context here, but what I've pasted in below is a ecmaScript function, that is partly genereted by php.

It's sort of a double interaction between dHtml and php.
1. PHP puts the right values into some different SQL-strings in an ecmaScript function.
2. When the user makes choices at runtime, the script adds different choices (add or remove items from one list to another) to a form-area. - Note the the script also make changes to the SQL-strings at runtime (depending on the choices the use make).
3. When the user clicks ok, the choicen SQL-strings are executed by php (when the form-results returns to the server).

I hope this is not too confusing.

One more tip to understand the following:
The choices the user can click on is a list with a-tags, where I've put an attribute 'aendring' (means 'change' in Danish) on. The default for aendring is 0, and the I have an onClick-function, that toggles that value on/off.

function fOpdat() {
  //Funktionen løber igennem alle a-mærker i formen og undersøger, om der er ændringer fra det oprindelige
  //Hvis der er, undersøges om det er tilvalg eller fravalg af punker
  //For hver tilvalg eller fravalg skrives en sql i det skjulte felt 'sqler' i hovedformen 'redigStruktur'
  chkColl = redigStruktur.all.tags('A');
  for(i=0;i<chkColl.length;i++) {                         //skan gennem alle a-mærker
    if(chkColl(i).aendring=="1") {                        //Er der forskel fra det oprindelige?
      if(chkColl(i).className == "vlgSkabelonSelected") {  //Der der forskel. Er det tilvalg eller fravalg?
        <?
        if($niveau == "Faser") {
          echo "redigStruktur.sqler.value += \"INSERT INTO Paradigmer (sId,fId,kId) VALUES ($intSkabelon,\" + chkColl(i).Nr + \",1);\\n\";";
        } 
        if($niveau == "Koncepter") {
          echo "redigStruktur.sqler.value += \"INSERT INTO Paradigmer (sId,fId,kId) VALUES ($intSkabelon,$intFase,\" + chkColl(i).Nr + \");\\n\";";
        }
        if($niveau == "Tekster") {
          echo "redigStruktur.sqler.value += \"INSERT INTO Tekster (kId,Tekst,url) VALUES ($intKoncept,'\" + chkColl(i).innerText + \"','\" + chkColl(i).Nr + \"');\\n\";";
        } 
        ?> 
      } else {                            //Der er forskel. Disse skal vælges fra
        <?
        if($niveau == "Faser") {
          echo "redigStruktur.sqler.value += \"DELETE * FROM Paradigmer WHERE ((sId=$intSkabelon) AND (fId=\" + chkColl(i).Nr + \"));\\n\";";
        } 
        if($niveau == "Koncepter") {
          echo "redigStruktur.sqler.value += \"DELETE * FROM Paradigmer WHERE ((sId=$intSkabelon) AND (fId=$intFase) AND (kId=\" + chkColl(i).Nr + \"));\\n\";";
        } 
        if($niveau == "Tekster") {
          echo "redigStruktur.sqler.value += \"DELETE * FROM Tekster WHERE tId=\" + chkColl(i).Nr + \";\\n\";";
        } 
        ?>
      } 
    }
  }
  return false;     
}