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. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
