Keep Server Online
If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.
or
A donation makes a contribution towards the costs, the time and effort that's going in this site and building.
Thank You! Steffen
Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
| |
|
 Topic: Creating and Using Temporary Tables? |
|
| Author |
|
kr33
Joined: 19 Sep 2006
Posts: 64
Location: South Africa
|
 Posted: Mon 06 Nov '06 11:24 Post subject: Creating and Using Temporary Tables? |
 |
|
Hi all,
I'm approaching the final(almost) stage of my web application. There are a few "loose ends" if you will, which I need to sort out, so here goes.
I need to create Temporary tables in which I will populate data that comes from uploaded CSV files, I need 8 or more of these tables, the reason is, that I will take only certain bits of data, which will be used to update a table/s which my "front end" page will read from to draw graphs.
I would like to know how I'd do this.
this is how I would create the temp table:
| Code: |
$tmp_tbl = "CREATE TEMPORARY TABLE IF NOT EXISTS '$tbl_nme' ".
"(Depot varchar(255) NOT NULL, ".
"Key varchar(10) NOT NULL, ".
"YTD_Target decimal(8,2) NOT NULL, ".
"Daily decimal(8,2) NOT NULL, ".
"WTD decimal(8,2) NOT NULL, ".
"MTD decimal(8,2) NOT NULL, ".
"YTD decimal(8,2) NOT NULL, ".
"YTD_Var decimal(8,2) NOT NULL, ".
"Max_Score int(11) NOT NULL, ".
"YTD_Score int(11) NOT NULL)";
$result = mysql_query($tmp_tbl)
or die(mysql_error());
#import the data from CSV file (posted from upload page)
$sql_u = "LOAD DATA LOCAL
INFILE 'C:\\\WINDOWS\\\TEMP\\\\$tmp_name' ".
"INTO TABLE '$tbl_nme' FIELDS TERMINATED BY ',' ".
"OPTIONALLY ENCLOSED BY '\"' ".
"ESCAPED BY '\\\\' ".
"LINES TERMINATED BY '\r\n' IGNORE 4 LINES";
$result_u = mysql_query($sql_u);
if (!$result_u) {
die (mysql_error());
} else {
echo "File import successful!<br/>";
}
|
Would the above be a step in the right direction? And if theres a better way, I'd love to know. Keep in mind I need to do this for 8 or more different files.
Once i've done the above, I need to extract certain bits of data from each file and the replace/update the main/permanent table that is used by the main page for drawing the graphs.
Anyone, please shed some light on this.
Thanks.
Ciao |
|
| Back to top |
|
kr33
Joined: 19 Sep 2006
Posts: 64
Location: South Africa
|
| Posted: Tue 14 Nov '06 17:31 Post subject: |
|
|
I've decided that since the temp tables will only be used for updating purposes, i created the tables in my database and just imported the data via an upload page and did the update, taking the data that I need and replacing the old data in the 'main' table.
Once the update is complete, I issue the following query:
| Code: |
$sql = "TRUNCATE <table_name>";
$result = mysql_query($);
if (!$result) {
die ("Error in emptying temporary table! : ".mysql_error());
} else {
echo "Query succeeded!";
}
|
This ensures that immediately after the neccesary updating has completed, the temporary table are emptied, then the next temporary table is populated and the update process repeats as well as the above sql query upon completion of the update.
This is the most feasible way of achieving my goal, and therefore works pretty well. I do realize that MUCH BETTER ways exist, but due to my time constraint, the way i've chosen to do it works reliably and efficiently.
Hope this gives some of the even "newer" php/Mysql newbies an idea of how to achieve such a task. |
|
| Back to top |
|
Brian
Joined: 21 Oct 2005
Posts: 209
Location: Puyallup, WA USA
|
| Posted: Tue 14 Nov '06 20:29 Post subject: |
|
|
Something that MySQL 5 offers that I have not used is TRIGGERS. You could set up a trigger to peform certain actions when some specified event takes place. I am trying a very handy tool out called SQLyog, the Enterprise edition actually, and it makes it easier to create MySQL functions, triggers, and stored procedures.
You don't here much about TRIGGERS and such in the PHP circles, but I am starting to see some value here, it puts some of the repetivie burdens onto the MySQL server, reducing the amount of code some may have to write, at least in some situations.
Does anyone else here use Functions, Triggers, and/or Stored Procedures in MySQL 5 and PHP? |
|
| Back to top |
|
kr33
Joined: 19 Sep 2006
Posts: 64
Location: South Africa
|
| Posted: Thu 16 Nov '06 8:45 Post subject: |
|
|
I've come across stored procedures and triggers, they are very useful and makes things ALOT easier.
Being that as it may, I have dwelled much on them seeing that I havent had the time currently, to work with them.
I do have a few tutorials on sp's and triggers. Once I have my site up and running, I'll create a link to download them.
But other than Brian and myself, does anyone know how the creation of the above mentioned would be achieved.
Thanks |
|
| Back to top |
|
|
|
|
|
|
