Hates the Infotainment
Join Date: May 2004
Location: NSA Archives
|
OK, if anyone knows how to attain this without me having to read through a 500 page book, doing all the exercises, let me know... here's the situation. I have a web site at work, complete with registration form that loads via our secure host server. What I would like to do, is
a) modify the form with PHP code so that every time someone posts a valid entry, it updates a mySQL database I created on the server. b) periodically pull the information from that server DB into excel or even FileMaker pro, for the purposes of creating contact and mailing lists for our company. alternately, if someone knows how to populate a FileMaker Pro DB from the form that would be even better. The problem AFAICT is, Filemaker web functionality is not designed to be populated from an external web form, it's designed to BE the form and be viewed online. I don't want anyone viewing anything, but rather the DB to be transparent, and only I view the data when I need to, in other formats. ...into the light of a dark black night. |
quote |
Selfish Heathen
Join Date: May 2004
Location: Zone of Pain
|
Quote:
[php] <html> <body> <?php if(isset($_POST['processing'])) { if(isValid($_POST['username'])) { $db = mysql_connect("localhost", "db_username"); mysql_select_db("db_name",$db); $result = mysql_query("INSERT INTO table_name VALUES (". $_POST['username'].")",$db); if(!$result){ echo "Something bad happened."; } else { echo "Okay!"; } } else { echo "Invalid input!"; } }else{ ?> <form action="<?=$PHP_SELF?>" method="post"> Name: <input type="text" name="username" /> <input type="hidden" name="processing" /> <input type="submit" name="submit" value="Submit My Name" /> </form> <?php } ?> </body> </html> [/php] It's a quick and dirty block I just wrote and there may be a mistake or two, but that's generally how it would work. Okay, here's how it works. First, the page checks to see if it's been submitted to itself. If not, it displays the form. If so, it validates the input using your custom function isValid(String). Then, it connects to the database and tries to insert the row. WARNING: This is a VERY simplified example and does not protect against injection attacks. Either you should be very thorough in your isValid(String) method or you should switch to using prepared statements. Quote:
[php] <?php //this MUST be at the start of the "printable" portion //to set up proper downloading to a file header("Cache-Control: ");// leave blank here to avoid IE errors header("Pragma: ");// leave blank here to avoid IE errors header("Content-type: text/plain"); header("Content-Disposition: attachment; ". ."filename=\"YourGreatDownloadFilename.xls\""); header("Expires: Sat, 29 May 1982 19:32:00 GMT"); header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); header("Cache-Control: no-store, no-cache, must-revalidate"); // HTTP/1.1 header("Cache-Control: post-check=0, pre-check=0", false); //insert code to access database //simply use "print" or "echo" to write to this downloading file. ?> [/php] This last chunk of code I actually just copy-pasted from a page that I've reused a bunch of times at work. Yes, the expire date is my birthday. The quality of this board depends on the quality of the posts. The only way to guarantee thoughtful, informative discussion is to write thoughtful, informative posts. AppleNova is not a real-time chat forum. You have time to compose messages and edit them before and after posting. |
||
quote |
Hates the Infotainment
Join Date: May 2004
Location: NSA Archives
|
*scratches head*
Thanks a bunch for the examples; I just wish PHP weren't such a close cousin to C. Seems like I might have to do some real sweating with this stuff to even have a slight understanding of it. HTML and CSS coding are one thing, but this stuff... probably a 1001 ways to fuck it up and leave yourself open to all kinds of data corruption or hackage. Eeesh. Am I asking the wrong question / is there a way to do all this with Filemaker and I just don't realize it? ...into the light of a dark black night. |
quote |
Selfish Heathen
Join Date: May 2004
Location: Zone of Pain
|
It's not so bad, really, is it? Maybe I just threw too much at you at once. Let's break down the meat of my post a little further:
$db = mysql_connect("localhost", "db_username"); That line creates a variable called $db that is going to be our database connection. mysql_select_db("db_name",$db); That line gets $db pointing to the right database. $result = mysql_query("INSERT INTO table_name VALUES (".$_POST['username'].")",$db); That line could be broken up further. Let's replace it with these two lines: $sqlCommand = "INSERT INTO table_name VALUES (".$_POST['username'].")"; $result = mysql_query($sqlCommand,$db); Now, you see that the first part is simply an SQL statement. It can by any SQL statement at all such as INSERT, SELECT, and UPDATE. The second part says to run that SQL statement on the $db database connection and return the result to a variable we made called, of course, $result. This "result" is a pointer to the selected rows in the database. I'll explain what you can do with it in a moment... That $_POST thing in there? That's just a standard variable for anything that's been passed to this page from a form that uses the method=post. As you can see, we have two values that the form passed: username and processing. The latter was just a hidden field in the form that I used so when the page was reloaded, it would know that something has been submitted. It may not be necessary, but I like using it as a handy reminder anyway. Now, you can do your display work from $result. In the above example, I just checked to see if it was valid. If you want to actually do something with the result of your query, you'll need to put in a loop that looks like this: [php]while ($row = mysql_fetch_assoc($result)) { echo "name: ".$row['name']."<br />"; echo "e-mail: ".$row['email']."<br />"; echo "phone: ".$row['phone']."<br />"; }[/php] That loop tries to fetch a row from the results. For each row, it prints out the data (assuming there are fields called name, email, and phone). It repeats as long as there are rows remaining in the result. Of course, you don't have to use a loop. If you're expecting a single value as a result (like from a COUNT query), you can just use that "$row = ..." part alone. After that, well, that's it! It probably just looks scarier up there with all those braces and colors and nested statements than it really is. Or maybe that's just the programmer in me talking and making too many assumptions. Does that help at all? Or are you still wanting to back out of the PHP/MySQL idea? The quality of this board depends on the quality of the posts. The only way to guarantee thoughtful, informative discussion is to write thoughtful, informative posts. AppleNova is not a real-time chat forum. You have time to compose messages and edit them before and after posting. |
quote |
Member
Join Date: Oct 2005
|
$result = mysql_query("INSERT INTO table_name VALUES (" . mysql_escape_string($_POST['username']) . ")",$db);
|
quote |
Hates the Infotainment
Join Date: May 2004
Location: NSA Archives
|
Quote:
I only have a brief moment to scan your explanation right now because I have to get ready for work in a bit, but I will definitely look it over more this afternoon. You are probably right that it's not as difficult as I imagine, but the colors and braces and such give it a very C-like flavor. Any minute now I'm waiting for the infamous "foo" to rear its ugly head. ...into the light of a dark black night. |
|
quote |
Space Pirate
Join Date: May 2004
Location: Atlanta
|
bar
(Brad,how difficult would it be to darken-up the orange color used for comment text?) |
quote |
Hates the Infotainment
Join Date: May 2004
Location: NSA Archives
|
Are you mocking me?
|
quote |
Less than Stellar Member
|
Just remember, if it doesn't work, it's ALWAYS because you forgot a semi-colon or quote. But, of course, you won't find this out until you rip apart your code piece by piece. I hate coding. But I can actually understand what Brad wrote. That's gotta mean something, right? Now if I could remember the code I need to use, I'd be golden (I'm currently implementing a database-based note system for work and it's driving me crazy).
If it's not red and showing substantial musculature, you're wearing it wrong. |
quote |
Space Pirate
Join Date: May 2004
Location: Atlanta
|
not mocking at all... it's just that you have to "bar" when you've been "foo'ed".
I understand most of the logic of what Brad's showing, I just haven't had an opportunity to use the mySQL database option with my new ISP yet and hence have no contributions to make, especially in light of his last line-by-line post. What I don't fully understand about a mySQL database is (and this is a basic question), if the database file is out there and available for modification every time a user accesses the site and POSTs their information (say, their personal contact information), how does the actual database "file" remain intact during those multiple, asynchronous accesses? Is the "insertion" of data associated with unique variables (say the user's username)? This is something I'll probably find out when I go back and finish reading the mySQL portion of the PHP book that I already own, but it's a question that does occur to someone when they're thinking about doing some dB work. Practical, everyday applications of a database is the next thing on the list... trying to figure out how to make it work for you seems to be a logical, basic mySQL question. |
quote |
Selfish Heathen
Join Date: May 2004
Location: Zone of Pain
|
Quote:
Quote:
Quote:
Quote:
edit: Hmm, I may have misread that last part. You can have multiple rows that have identical information; that's what I meant about uniqueness depending on the table structure. To execute an actual INSERT command, you should either enter values in the parentheses in the order that the columns appear in a table or you should explicitly state what you're entering. For example: INSERT INTO MyTable (firstname, email) VALUES ('Brad', 'brad@website.tld'); ...where firstname and email are two (of possibly many) columns in the MyTable table. This will create a new row where any missing column entries will be entered as NULL or some other default value. The quality of this board depends on the quality of the posts. The only way to guarantee thoughtful, informative discussion is to write thoughtful, informative posts. AppleNova is not a real-time chat forum. You have time to compose messages and edit them before and after posting. |
||||
quote |
Senior Member
Join Date: Jan 2005
Location: London, UK
|
Quote:
|
|
quote |
Veteran Member
Join Date: Jun 2004
|
Quote:
I don't know PHP, but a big part of safe C(++) coding is writing everything so you see errors at compile instead of run. |
|
quote |
Member
Join Date: Oct 2005
|
Quote:
|
|
quote |
Space Pirate
Join Date: May 2004
Location: Atlanta
|
Now that's something that I hadn't considered... running a PHP routine that trashes a data file somewhere because it runs badly due to programmer error. Since I haven't yet crossed over into having data that is dynamically updated by visitors I haven't considered the necessity of making backups of datafiles before running updated PHP pages. I'll keep that in mind when/if I ever make it to that stage. Is there any sort of standard operating procedure for making such backups? I figure that it's so straightforward that there's not a lot of need for standard procedures to have been developed by the community... am I incorrect?
|
quote |
monkey with a tiny cymbal
Join Date: Nov 2004
Location: Lost
|
If you're using MySQL, backups are fairly simple. You just use the mysqldump command in the terminal. To streamline things even further, I've made an alias that executes the following so I don't always have to remember the command options.
Code:
mysqldump -h mysql_host -u mysql_username -p db_to_backup | bzip2 -c > /path/to/backup/db.bak.sql.bz2 You could even tie this to cron to get it running daily/weekly/whatever. |
quote |
Posting Rules | Navigation |
|
Thread Tools | |
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Important iBook Question | Robo | Apple Products | 9 | 2005-11-03 03:16 |
MySQL 5.0 Install on OS X v.10.4.1- need help with socket error | DogMom | Genius Bar | 1 | 2005-07-11 17:00 |
Ram & Superdrive Question (Powerbook) | Fooboy | General Discussion | 2 | 2005-02-21 22:23 |
Generating XML for Newsfeed (MovableType and PHP) | drewprops | Genius Bar | 3 | 2005-01-22 00:32 |
HELP! URGENT. DOWNGRADE PHP 5.0.1 to 4.3.6 | hype.it | Genius Bar | 2 | 2004-08-31 19:15 |