On Mon, Nov 14, 2011 at 13:43, Simon Slavin <slav...@bigfraud.org> wrote: > > On 14 Nov 2011, at 10:45am, Gert Van Assche wrote: > >> I would like to understand how a PHP web page communicates with SQLite. > > All my responses to this are general, with a little hand-waving and > simplification. And other posters to this list may have other points of > view. I'm just trying to provide some knowledge that I have. > >> I >> know something about SQLite, but nothing or not a lot about PHP. > > There are two common ways to do this. One is to write your complete program > in PHP: talking to the SQLite database, constructing the web pages and > getting the values entered by the user. The other is to write most of your > code in JavaScript and call PHP code simply for access to the SQLite > database. So you might write one or three tiny PHP programs which just took > some parameters for which database and what command, executed the command, > and returned the results in XML or JSON format. > > People like to write more of their code in JavaScript these days, so they > just write little PHP shims, but if you're really into PHP, or want to put > more load on your server and less on your clients, you can go the other way. > > When using PHP to talk to a SQLite database you have the choice between two > options: the PDO API and the SQLite3 API. > > http://php.net/manual/en/book.pdo.php > http://www.php.net/manual/en/book.sqlite3.php > > The advantage of the PDO API is that the necessary changes, should you ever > need to move from SQLite to another DBMS, are smaller. Unfortunately, it > means that the commands you're issuing, and the results you get back, are > quite a long way from SQLite, and there are certain things you can't do > because it's a standardised interface intended for use with many different > database engines. If you are already familiar with SQLite I'd recommend you > stick with the SQLite3 API which is a very thin conversion of the SQLite3 C > functions into PHP functions. > >> - How does one take input from an input field or a check box and use it >> for doing an "INSERT" for instance? > > Your JavaScript code, or the PHP code you get as a result of the POST, > retrieves the values of the fields and makes up the appropriate SQL command > from it. A trivial JavaScript example plucked from one of my utilities is > > var newValue = prompt('Enter new value for column "'+colName+'" of row > '+rowName+'.', oldValue) > if (newValue != null && newValue != oldValue) { > var theCommand = "UPDATE "+tableName+" SET "+colName+" = > '"+newValue+"' WHERE rowid = "+rowName > doSQLite(theCommand) > clickedCell.innerHTML = newValue > } > >> - How does one do a SELECT query based on what has been selected from a >> dropdown box? > > As above. Retrieve the value which was selected, then construct a string > holding the appropriate SQL command. Then pass that string to SQLite. > >> - How do you fill a dropdown box with values retrieved from a table? >> - ... > > Write some PHP code which does a SELECT which returns the values which should > go into the dropdown box (both the text to be shown in on the screen and the > values to be set). Then write PHP or JavaScript code which uses the result > of this query to construct the dropdown box using HTML DOM components. A > trivial JavaScript example > > var newPopup = document.createElement("select") > newPopup.id = newSelectID > for (i = 0; i < paramArray.length; i = i + 1) { > newOption = document.createElement("option") > newOption.text = paramArray[i] > newPopup.appendChild(newOption, null) > } > newPopup.addEventListener("change", handleFieldChange, true) > >> Does anyone have an example that I could use? I want to see if I can make a >> mockup with PHP & SQLite. > > There are plenty around but you can't have mine. > > Simon.
I recommend against formulating the SQL statements in Javascript. Because if I find that page, I _will_ try to inject my own SQL. Do as much as you can on the server. This particular exercise has no need of javascript. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
