muskrat Posted January 13, 2007 Share Posted January 13, 2007 For starters, I'm new to this board, in fact this is my frist post. I've noticed on the post "Read This: PHP Freaks Posting Guidelines" it said;[quote]If you are very new to PHP or programming in general, then post to Newbie Help. People are more likely to be patient with you there. If you post to PHP Help, the assumption is that you have gained a level of competence with PHP and have a specific problem or question that could not be answered by using the resources listed previously.[/quote]I couldn't find the "Newbie Help" section. So please forgive me if I've posted this in the wrong place. I am new to writing PHP from scratch, athough I've been hacking scripts for several years, no big hacks mind you just little stuff.I've writen HTML for sometime now, and use linux CL quite well. I have also conquered Sqlite, I can create and populate a database from the CL, (although I still have a lot to learn about databases) And I have done all the following;[quote]a) http://www.phpfreaks.com/phpmanual.php for any language syntax questions.B) The PHPFreaks tutorials and Quick Code Libraryc) Searched the forums using the Search featured) Googled (http://www.google.com/)e) The pinned topics in the various forums. Often these address FAQ's.[/quote]But what I've found has left me with several things! One more questions than I had to start with. Two Sqlite and PHP seems to be the least documented of combinations, Databases / PHP. Three more ideas of improving my project, the posibilities are awsume.I've labeled this thread "PHP form and Sqlite database", because that is right where all the books and documentation seems to fall down, and that's where I'm stuck.I've bought two books on PHP, Learning PHP5 and PHP Cookbook, Also One Sqlite book. and several Sams teachyourself books. Now I can't saw I've read them all, nor understand all of it. But I'm not finished ether.But I am a person who learns by working out the issues. So I took the code from the sqlite book, and the learning php5 book and started my project. Thier code is setup to work with a example database, but I don't want to waste my time working on a database I'm going to throw away. And putting the two codes together is a bit of puzzle to me.So if nobody minds I'll post my questions and results here as my project builds, maybe then when somebody comes along like me he'll find it. because I've not found any results searching google and here for several weeks. So here is my project in a nutshell; My wife and I own a small Mom/Pop store in Mexico. I need to make a point of sale software, and a inventory search software. My plan was to use Sqlite as my database and PHP as my GUI interface, basicly because I understand buillding web pages and sqlite seems to be a low overhead database system.This will not be a world accessable web server, it'll be a stand alone machine with apache and php5 bundled with sqlite3. Which I have installed here for building it.The question or mental block I have right now is the PHP form to Sqlite issue. Here is the code I have so far, frist the problem page;[code]<html><title>Testing</title><body><p>This pages functions without error except for the form.</p><?php/* * I need this to load module. */ dl('sqlite3.so');/* * create a SQLite3 handle * and connect to database */ $db = sqlite3_open("/home/databases/lirio-sales.db");if (!db) die ("Could not open database.");print<<<_HTML_<center><form method="post" action="$_SERVER[PHP_SELF]"> Date: <input type="text" maxlength="8" name="date"><br> Item Number: <input type="text" maxlength="3" name="item_number"><br> Item: <input type="text" name="item"><br> Sale: <input type="text" maxlength="6" name="sale"><br><input type="submit" value="Submit"></form></center>_HTML_;}/* * Do not forget to release all handles. * */sqlite3_close ($db);?></body></html>[/code]My question is how do I get the form user imput to insert into my data base?I ask this question over on IRC #sqlite and they said it was a php question, I ask it over at IRC ##php and someone said arrays, i've spent the last two days studing the two afore mentioned books about arrays, which they have quite a lot on, but some how I'm missing the connection of dots.Forms,.... PHP,.... Sqlite?Here is a PHP page that communicates with my database and work perfactlly, but it's hard coded, and I need a form where my wife can just enter the data and submit, after which the page should refreash where she can just enter another batch of data and submit. numorous times a day.[code]<?php/* * I need this to load module. */ dl('sqlite3.so');/* * create a SQLite3 handle * and connect to database */ $db = sqlite3_open("/home/databases/lirio-sales.db");if (!db) die ("Could not create database.");/* * create a table "sales" and insert some values.. */$ret = sqlite3_exec ($db, "CREATE TABLE sales (date INTEGER, item_number INTEGER, item TEXT, sale DECIMAL(5,2));");if (!$ret) die (sqlite3_error($db));sqlite3_exec($db, "INSERT INTO sales (date,item_number,item,sale) VALUES (20070102,32,'ribbon','2.50')");sqlite3_exec($db, "INSERT INTO sales (date,item_number,item,sale) VALUES (20070102,33,'thread','1.50')");sqlite3_exec($db, "INSERT INTO sales (date,item_number,item,sale) VALUES (20070102,34,'pen','3.50')");/* * Do not forget to release all handles. * */sqlite3_close ($db);?>[/code] Studing this problem out has raised more questions, and this project will evolove into more later. This is just the frist page. But they should all be simple pages such as this. And I should be able to build on the same principles.The inventory search page will also use a form but instead of insert it'll be select and so on.The next page will be totaly seperate, it'll be a select * from where the dates match some date, echo all the results, and if posible total sales for that day, so we can print a hard copy. Like I said the more I work on it, the more possibilites come up. We are currently using a spreadsheet.Anyway, sorry i'm so longwinded, but I wanted you to see the whole picture, My current question and mental block is changing the insert code in the second code clip above to an array (or other method maybe) to add into the frist code clip so my form will insert into my database.Can anybody help me here? Quote Link to comment Share on other sites More sharing options...
trq Posted January 13, 2007 Share Posted January 13, 2007 Firstly, welcome to the board.Ok... Ive read your post and it eems you pretty well know what you are doing. Because you don't really seem to have any specific problem, Im just going to post a smalll example of posting from a form to an sqlite database. Sqlite is a great choice by the way, I use it for alot of my projects with a few different languages.Anyway.... an example.[code]<?php if (!isset($_POST['submit'])) { // display form. echo '<form method="post">'; // you dont need an action if posting to self. echo ' <input type="text" name="data">'; echo ' <input type="submit" name="submit">'; echo '</form>'; } else { // do database insert. dl('sqlite3.so'); $db = sqlite3_open("sample.db"); if (isset($_POST['data'])) { $sql = "INSERT INTO tbl (fld) VALUES ('{$_POST['data']}')"; // you would normally want to clean $_POST['data'] before using it. if (sqlite3_exec($db,$sql)) { echo "insert success"; } else { echo "insert failed"; } } else { echo "you did not fill in the form"; } }?>[/code]I havent actually used the sqlite3_* functions, normally working with sqlite 2 with php, but they should be much the same.Hope this helps. Quote Link to comment Share on other sites More sharing options...
muskrat Posted January 13, 2007 Author Share Posted January 13, 2007 Yes, that's exactly what I'm needing, it's starting to connect the dots. But in one way you fell off the same way all the tutorials and boxes have fallen off.My DB table has 4 fields, my form has 4 fields I need them all at this time later I'll do different on other pages, but by then maybe I'll understand. Here is the code you gave me, with my modification for the 4 fields, but it didn't work.[code]<html><head> <title></title> <meta name="GENERATOR" content="Quanta Plus"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body><?php if (!isset($_POST['submit'])) { // display form. echo '<form method="post">'; // you dont need an action if posting to self. echo 'Date <input type="text" maxlength="8" name="date"><br>'; echo 'Item Number <input type="text" maxlength="3" name="item_number"><br>'; echo 'Item <input type="text" name="item"><br>'; echo 'Sale <input type="text" maxlength="6" name="sale"><br>'; echo ' <input type="submit" name="submit">'; echo '</form>'; } else { // do database insert. dl('sqlite3.so'); $db = sqlite3_open("/home/databases/lirio-sales.db");if (!db) die ("Could not open database."); if (isset($_POST['date' 'item_number' 'item' 'sale'])) { $sql = "INSERT INTO sales (date,item_number,item,sale) VALUES ('{$_POST['data''item_number''item''sale']}')"; // you would normally want to clean $_POST['data'] before using it. if (sqlite3_exec($db,$sql)) { echo "insert success"; } else { echo "insert failed"; } } else { echo "you did not fill in the form"; } }?></body></html>[/code]I'm getting an error on line 23 which is this line;[code] if (isset($_POST['date' 'item_number' 'item' 'sale'])) {[/code]This is the error I get;[code]Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING, expecting ']' in /var/www/lirio/testing2.php on line 23[/code]Now I tried the ['date' 'item_number' 'item' 'sale'] as such and also as ['date''item_number''item''sale'] both of which failed. All the books and tutorials show examples like yours, with one form box and I need 4, and that's one place I've been getting lost.You mentioned in you "//normally you'd clean data", I realize that but since it's a private deal and only my wife and I will use it, I'm not to concerned about that. I'll tell her just don't enter any special charactorers.But Like I said earlier, the more I learn the more question arise. For exmaple in the segment;[code] if (sqlite3_exec($db,$sql)) { echo "insert success"; } else { echo "insert failed"; } } else { echo "you did not fill in the form";[/code]What would/could I do for a refersh so the form would be ready to use again upon success instead of "echo 'insert success'"?But Thanks a lot that's one step closer anyway, I'm still proceeding in the right direction. Thanks! Quote Link to comment Share on other sites More sharing options...
muskrat Posted January 13, 2007 Author Share Posted January 13, 2007 Ok with your help and the fellows over at IRC##php I've got the php form to function, but now the sqlite doesn't insert. I get the error "insert failed" which is an else statement.[code]<?php if (!isset($_POST['submit'])) { // display form. echo '<form method="post">'; // you dont need an action if posting to self. echo 'Date <input type="text" maxlength="8" name="info[date]"><br>'; echo 'Item Number <input type="text" maxlength="3" name="info[item_number]"><br>'; echo 'Item <input type="text" name="info[item]"><br>'; echo 'Sale <input type="text" maxlength="6" name="info[sale]"><br>'; echo ' <input type="submit" name="submit">'; echo '</form>'; } else { // do database insert. dl('sqlite3.so'); $db = sqlite3_open("/home/databases/lirio-sales.db");if (!db) die ("Could not open database."); if ((isset($_POST['info'])) && ( sizeof( $_POST['info'] ) == 4 ) ) { $sql = "INSERT INTO sales (date,item_number,item,sale) VALUES ('{$_POST['info']}')"; // you would normally want to clean $_POST['data'] before using it. if (sqlite3_exec($db,$sql)) { echo "insert success"; } else { echo "insert failed"; } } else { echo "you did not fill in the form"; } }?>[/code]I have an insert hard codeed on another page which works, I'm not sure exactly where my error lies, because it's a little different when you use a form to insert.[code]<?php/* * I need this to load module. */ dl('sqlite3.so');/* * create a SQLite3 handle * and connect to database */ $db = sqlite3_open("/home/databases/lirio-sales.db");if (!db) die ("Could not create database.");/* * create a table "sales" and insert some values.. */$ret = sqlite3_exec ($db, "CREATE TABLE sales (date INTEGER, item_number INTEGER, item TEXT, sale DECIMAL(5,2));");if (!$ret) die (sqlite3_error($db));sqlite3_exec($db, "INSERT INTO sales (date,item_number,item,sale) VALUES (20070102,32,'ribbon','2.50')");sqlite3_exec($db, "INSERT INTO sales (date,item_number,item,sale) VALUES (20070102,33,'thread','1.50')");sqlite3_exec($db, "INSERT INTO sales (date,item_number,item,sale) VALUES (20070102,34,'pen','3.50')");/* * Do not forget to release all handles. * */sqlite3_close ($db);?>[/code]But I'm making headway, Another thought is I'll need the "sqlite_close" statement should that be an "if" clause or just put it at the end of the page like my hardcoded page? Quote Link to comment Share on other sites More sharing options...
muskrat Posted January 13, 2007 Author Share Posted January 13, 2007 Ok the problem is solved the boys over at IRC##php helped me out but I thought I'd post the finished working form here.[code]<html><head> <title></title> <meta name="GENERATOR" content="Quanta Plus"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body><?php if (!isset($_POST['submit'])) { // display form. echo '<form method="post">'; // you dont need an action if posting to self. echo 'Date <input type="text" maxlength="8" name="info[date]"><br>'; echo 'Item Number <input type="text" maxlength="3" name="info[itemnum]"><br>'; echo 'Item <input type="text" name="info[item]"><br>'; echo 'Sale <input type="text" maxlength="6" name="info[sale]"><br>'; echo ' <input type="submit" name="submit">'; echo '</form>'; } else { // do database insert. dl('sqlite3.so'); $db = sqlite3_open("/home/databases/lirio-sales.db");if (!db) die ("Could not open database."); if ((isset($_POST['info'])) && ( sizeof( $_POST['info'] ) == 4 ) ) { $sql = "INSERT INTO sales (date,itemnum,item,sale) VALUES ('{$_POST['date']}','{$_POST['itemnum']}','{$_POST['item']}','{$_POST['sale']}')"; if (sqlite3_exec($db,$sql)) { echo "insert success"; } else { echo "insert failed"; } } else { echo "you did not fill in the form"; } }?></body></html>[/code]The insert problem was the insert line;[code] $sql = "INSERT INTO sales (date,itemnum,item,sale) VALUES ('{$_POST['date']}','{$_POST['itemnum']}','{$_POST['item']}','{$_POST['sale']}')";[/code]It had to look like that. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.