1 Replies - 2928 Views - Last Post: 27 November 2007 - 12:23 AM Rate Topic: -----

#1 martini  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 25-November 07

Uploading dynamic data from website forms to Excel

Posted 25 November 2007 - 01:17 PM

Hi - I have a working PHP script for emailing me each contact enquiry from my website which I am now expanding within DW to accomodate dynamic entries to populate a directory from the website. I think this is OK - but what I cannot find anywhere is help with dynamic population of an excel spreadsheet with this data uploaded using a PHP script.

Can this be done? Is Excel open to dynamic entries and does excel have to reside on the server for this? (see - I'm a newbie and coding was a long time ago for me!)

Ideally I'd like the directory to be populated automatically from user entries on the website, send a thank you email automatically and be visible and searchable on the website for anyone whilst being editable by me as admin for removing any entries that aren't suitable manually. I'd also like to deploy it in a widget.

The reason I'd like to upload to excel is because my server only has mysql version 3.x (really out-of-date) and I want a format for the data to be usable in any database right now as I haven't chosen the DB I will use ultimatley.

Thanks for any help and pointers. The code shown here is only for the online form, emailing me each query and is still WIP ....
Martini

 <form action="contactform.php" method="post" name="AGContactForm" id="AGContactForm">
		<p><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong>Name</strong></font></p>
		<p><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong> 
		  <input name="name" type="text" id="name">
		  </strong></font></p>
		<p><strong><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Company</font></strong></p>
		<p><strong><font size="1" face="Verdana, Arial, Helvetica, sans-serif"> 
		  <input name="company" type="text" id="company">
		  </font></strong></p>
		 
		<p><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong> 
		  Address</strong></font></p>
		<p><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong> 
		  <input name="address" type="text" id="address">
		  </strong></font></p>
		<p><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong> 
		  City</strong></font></p>
		<p><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong> 
		  <input name="city" type="text" id="city">
		  </strong></font></p>
		  <p><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong> 
		 Country</strong></font></p>
		<p><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong> 
		  <input name="country" type="text" id="country">
		  </strong></font></p>
		<p><strong><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Telephone</font></strong></p>
		<p><strong><font size="1" face="Verdana, Arial, Helvetica, sans-serif"> 
		  <input name="telephone" type="text" id="telephone">
		  </font></strong></p>
		<p><strong><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Email</font></strong></p>
		<p>
<input name="email" type="text" id="email">
		<p><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong>What 
		  do you provide? </strong></font></p>
		<p> 
		  <select name="What do you provide?" size="5">
			<option>Software development</option>
			<option>Marketing services</option>
			<option>Games or animation development</option>
		  </select>
		</p>
		<p>What is your sector?</p>
		<p> 
		  <select name="select">
		  </select>
		</p>
		<p><strong><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Query</font></strong></p>
		<p><strong><font size="1" face="Verdana, Arial, Helvetica, sans-serif"> 
		  <textarea name="query" id="query"></textarea>
		  </font></strong></p>
		<p><strong><font size="1" face="Verdana, Arial, Helvetica, sans-serif"> 
		  <input type="submit" name="Submit" value="Submit">
		  </font></strong> </p>
	  </form>



Is This A Good Question/Topic? 0
  • +

Replies To: Uploading dynamic data from website forms to Excel

#2 rockstar_  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 34
  • View blog
  • Posts: 189
  • Joined: 16-October 06

Re: Uploading dynamic data from website forms to Excel

Posted 27 November 2007 - 12:23 AM

View Postmartini, on 25 Nov, 2007 - 01:17 PM, said:

Hi - I have a working PHP script for emailing me each contact enquiry from my website which I am now expanding within DW to accomodate dynamic entries to populate a directory from the website. I think this is OK - but what I cannot find anywhere is help with dynamic population of an excel spreadsheet with this data uploaded using a PHP script.

What do you mean by "dynamic" Are the field names changing constantly? If so, this might require a little more code than I'm about to suggest, but not so much that it would kill you.

Quote

The reason I'd like to upload to excel is because my server only has mysql version 3.x (really out-of-date) and I want a format for the data to be usable in any database right now as I haven't chosen the DB I will use ultimatley.

Your best bet would be to save the data in a CSV file and import it into Excel. If you're not familiar with CSV, it stands for Comma Separated Values. You'd have to manipulate it a bit, but you could get the CSV to look like this:
Name,Address,City,State,Zip
Bob,123 Fake St,New York,NY,12345
Mike,321 Real St,Los Angeles,CA,54321


The function you'll need is probably fputcsv()

You won't need Excel on the server for this. If you use fputcsv and append the form data on the end of the file for each submission, you'll get a list of all the people who submitted the form. Then, every day (or week, or hour, or whatever), you download that CSV file to your local computer and open it in Excel (it should autodetect the CSV format and set up the cells for you).

The one caveat I will mention is that this isn't very scalable, and not a good solution if you have a remote chance of any two people submitting the form at the same time. There's a race condition where the first user that opens the CSV file for writing to it won't get the data written to it if a second user submits the form and the file is opened again by the same script running simulataneously (due to threads in Apache). If that sounds confusing, you probably don't need to worry about it.

You can also do it the other way, exporting data from Excel into CSV and parsing it with your PHP. As an example, you could use the first line or the above sample as the data model, and then, as you read each line, create a new associative array based on that first line's names. If you're planning on parsing a CSV file, you'll also want to take a look at fgetcsv()
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1