 |
Submit Your Code | |
 |
SEO Monitor | |
 |
UpTime Monitor | |
 |
PHP Code Contest | |
 |
My Favorite Examples | |
|
My Favorite Articles | |
 |
Update Your Profile | |
|
|
| More Weber Sites | ||
| PHP Code Search | ||
| Web Development Forums | ||
| Learn MySQL Playing Trivia | ||
| PHPBB2 Templates | ||
| Web Development Index | ||
| PHP Web Logs (BLogs) | ||
| Web Development Resources | ||
| Web Development Content | ||
| Recommended Links | ||
| PHPClasses | ||
| PHP Editor | ||
| PHP Jobs | ||
| Vision.To Design | ||
| Ajax Tutorials | ||
| PHP Programming Help | ||
| PHP/MySQL Programming | ||
| Webmaster Resources | ||
| Webmaster Forum | ||
| XML meta language | ||
| website builder | ||
| Recommended | ||
| Submit Site | ||
| Forex Trading Online forex trading platform | ||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Code | ||||||||||||||
PHP is great for a lot of things, but report generation is NOT one of them (compared to Crystal Reports or other report generators). I really like Microsoft Access's report environment, but its a royal pain in the butt to tranfer data from a MySQL database to Access, especially if you need to do it multiple times (like you have to with real life reports). Here is the simplest way I know how to do it FULLY AUTOMATICALLY <b>Instructions:</b><br> (1) mount the following script on your PHP server<br> (2) modify it for your particular situation<br> (3) debug it (i.e. invoke it from a browser until it comes back with "100 success")<br> (4) download the zip file reportsynch.zip (can be downloaded from http://www.stanfordsystems.com/downloads/reportsynch.zip)<br> (5) unzip and install on a Microsoft Windows machine<br> (7) run the program ReportSynch.exe and click the Details button; enter the required connection info and Save.<Br> (6) In Microsoft Access, make a database with the table(s) you are transfering.<br> (7) run ReportSynch and click Synch Now.<br> (8) You now have a copy of the MySQL table in your Access table. Make the reports you need in Access or in Crystal, or whatever report designer you have (that will connect to local Access database).<br> (9) Wheneve you want to run a set of reports, run the ReportSynch utility to download a current version of the MySQL data.<br> Here is the PHP script... <? //convert-to-access-date: takes a mysql date and converts it to a format embeddable in Access SQL function ctad($dt) { $yr=strval(substr($dt,0,4)); $mo=strval(substr($dt,5,2)); $da=strval(substr($dt,8,2)); return "$mo/$da/$yr"; } //MS Access escape string function function csql2($s) { for ($a=0;$a<strlen($s);$a++) { $c=substr($s,$a,1); if ($c=="\"") $c="\"\""; if ((ord($c)==10)||(ord($c)==13)) $c=":"; $s2.=$c; } return $s2; } $con = mysql_connect(localhost,"your_username","your_password"); if ($con==NULL) { echo("301 Couldn't connect to MySQL\n\n"); exit(-1); } $db = mysql_select_db("your_database_name",$con); $fptr=fopen("export.txt","w"); //The example table is "po" (or purchase order table). I selected this one from one of many because the filed list is short, but it has all the most common data types (i.e. string, integer, byte, date) //substitute you own values. Also remeber that the Access SQL generates DOES NOT create the table; you must create it yourself from inside Access. fputs($fptr,"delete * from po;\n"); $res=mysql_query("select poid, vendorid, toline1, toline2, toline3, toline4, email, podate, received from po order by poid",$con); $nr=mysql_num_rows($res); if ($nr>0) { for ($a=0;$a<$nr;$a++) { $row=mysql_fetch_row($res); $poid=intval($row[0]); $vendorid=intval($row[1]); $toline1=csql2($row[2]); $toline2=csql2($row[3]); $toline3=csql2($row[4]); $toline4=csql2($row[5]); $email=csql2($row[6]); $podate=ctad($row[7]); $received=intval($row[8]); fputs($fptr,"insert into po (poid, vendorid, toline1, toline2, toline3, toline4, email, podate, received ) select $poid as e1, $vendorid as e2, \"$toline1\" as e3, \"$toline2\" as e4,\"$toline3\" as e5, \"$toline4\" as e6, \"$email\" as e7, #$podate# as e8, $received as e9;\n"); } } mysql_free_result($res); fclose($fptr); echo("100 success"); ?> | ||||||||||||||
| Related Code Examples | ||||||||||||||
| User Comments | ||||||||||||||
|
||||||||||||||
