AppleNova Forums

Moogs · 2005-11-15, 18:58

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.

Brad · 2005-11-15, 20:13

Quote:

Originally Posted by Moogs

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.

Well, here's what I would do, all nicely tucked into one page:

[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:

Originally Posted by Moogs

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.

It's the basic same idea. Change the SQL statement from INSERT to whatever you want to SELECT, loop as necessary, and do something like this:

[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.

Moogs · 2005-11-16, 00:05

*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?

Brad · 2005-11-16, 00:33

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?

deuce868 · 2005-11-16, 08:26

$result = mysql_query("INSERT INTO table_name VALUES (" . mysql_escape_string($_POST['username']) . ")",$db);

Moogs · 2005-11-16, 08:44

Quote:

Originally Posted by Brad

.....

Does that help at all? Or are you still wanting to back out of the PHP/MySQL idea?

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.

drewprops · 2005-11-16, 15:51

bar

(Brad,how difficult would it be to darken-up the orange color used for comment text?)

Moogs · 2005-11-16, 20:11

Are you mocking me?

torifile · 2005-11-16, 20:42

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).

drewprops · 2005-11-17, 00:55

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.

Brad · 2005-11-17, 01:09

Quote:

Originally Posted by torifile

Just remember, if it doesn't work, it's ALWAYS because you forgot a semi-colon or quote.

So true! That's what I hate most of all about PHP; it just dies and displays nothing at all when there's the tiniest syntax error.

Quote:

Originally Posted by drewprops

I just haven't had an opportunity to use the mySQL database option with my new ISP yet

Don't forget that you can easily install PHP and MySQL on your own Mac at home! I use these installers.

Quote:

Originally Posted by drewprops

how does the actual database "file" remain intact during those multiple, asynchronous accesses?

This is all handled by the database server itself. The end user (in this case the PHP programmer) never sees or touches anything low-level like this. The database server internally has a variety of special functions and data structures for buffer managers whose sole purpose is to manage this stuff nicely for you. That's as simple as I can put it without getting into a lot of the nitty gritty inner workings that most people don't care about.

Quote:

Originally Posted by drewprops

Is the "insertion" of data associated with unique variables (say the user's username)?

It can be, but uniqueness is certainly not necessary. It depends entirely on how you've created your tables and what relations or other special characteristics may be set.

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.

mooty · 2005-11-18, 05:49

Quote:

Originally Posted by Brad

So true! That's what I hate most of all about PHP; it just dies and displays nothing at all when there's the tiniest syntax error.

shouldn't you be getting parse errors for these types of things? best to make sure ERRORS_ALL is on for best bug finds

Koodari · 2005-11-18, 08:36

Quote:

Originally Posted by Brad

So true! That's what I hate most of all about PHP; it just dies and displays nothing at all when there's the tiniest syntax error.

The earlier a program dies when things are wrong, the better, lest it screw up the data.
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.

deuce868 · 2005-11-18, 08:44

Quote:

Originally Posted by Koodari

The earlier a program dies when things are wrong, the better, lest it screw up the data.
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.

Php is "compiled" at runtime so if you miss a ; or ' you see it as soon as that file gets loaded.

drewprops · 2005-11-19, 16:40

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?

Majost · 2005-11-19, 16:56

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.

Posting Rules	Navigation
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off	Post Reply Forum Jump

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

deuce868 Member Join Date: Oct 2005	2005-11-16, 08:26 $result = mysql_query("INSERT INTO table_name VALUES (" . mysql_escape_string($_POST['username']) . ")",$db);
	quote

drewprops Space Pirate Join Date: May 2004 Location: Atlanta	2005-11-16, 15:51 bar (Brad,how difficult would it be to darken-up the orange color used for comment text?)
	quote

Moogs Hates the Infotainment Join Date: May 2004 Location: NSA Archives	2005-11-16, 20:11 Are you mocking me?
	quote

User Name		Remember Me?
Password

AppleNova Forums

Basic PHP / mySQL question