I am having trouble trying to figure out how to set up a loop query
that will locate specific data in a table (e.g. pl_stdn.site_id),
then execute queries (DELETE and INSERT) within the loop based on
each site_id the loop query finds in the pl_stdn table.
For example, the process should go something like this.
$query = "SELECT site_id,filepathhist,filepathstdn FROM pl_stdn";
$result = mysql_query($query)
or die ("Could Not Select School Info Data!");
$numrows = mysql_num_rows($result);
while ($row = mysql_fetch_array($result))
{
extract($row);
}
// Store all site id's in an array so that they can be retrieved
during the loop. ???? (Not sure about this part!)
$myarray = array($site_id)
// For each record found, do the following based on site_id...
foreach($myarray)
{
if ($numrows > 0) {
$query = "DELETE FROM pl_stdn WHERE site_id = '$site_id'";
$result = mysql_query($query)
or die("Could Not Delete Student Transaction Records!");
$query = "DELETE FROM itemhist WHERE site_id = '$site_id'";
$result = mysql_query($query)
or die("Could Not Delete Item History Records!");
}
// This query will need to locate and insert a csv file based on
each site_id during the loop as well.
$query = "LOAD DATA INFILE '$filepathstdn' INTO TABLE pl_stdn
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'";
$result = mysql_query($query)
or die("Could Not Load Student History CSV Data In File!");
$query = "LOAD DATA INFILE '$filepathhist' INTO TABLE itemhist
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'";
$result = mysql_query($query)
or die("Could Not Load Item History CSV Data In File!");
}
This process should be repeated (looped) for each Site ID the query
finds.
I've provided some code below, but it obviously does not do what I
need. I am hoping that somewhere among you all is a PHP genious!!!
Here's the code I have so far... Thanks for any helpful information
and/or code you can provide!
<?php
// Query execution timer functions
class BC_Timer
{
var $stime;
var $etime;
function get_microtime(){
$tmp=split(" ",microtime());
$rt=$tmp[0]+$tmp[1];
return $rt;
}
function start_time(){
$this->stime = $this->get_microtime();
}
function end_time(){
$this->etime = $this->get_microtime();
}
function elapsed_time(){
return ($this->etime - $this->stime);
}
}
// Start timer to generate query execution time in seconds
$timer = new BC_Timer;
$timer->start_time();
// Connect to database: dbconnect.php has authentication data
include("dbconnect.php");
$connection = mysql_connect($host,$user,$password)
or die ("Couldn't connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");
$query0 = "SELECT site_id,filepathhist,filepathstdn FROM
school_info";
$result0 = mysql_query($query0)
or die ("Could Not Select School Info Data!");
$numrows0 = mysql_num_rows($result0);
while ($row0 = mysql_fetch_array($result0))
{
extract($row0);
}
#########################################################
# Check for records in the pl_stdn
table #
#########################################################
$query1 = "SELECT site_id FROM pl_stdn WHERE site_id
= '$site_id'";
$result1 = mysql_query($query1)
or die("Could Not Execute Query 1!");
$numrows1 = mysql_num_rows($result1);
// If records exist in the pl_stdn table, do Query
2...
if ($numrows1 > 0) {
#########################################################
# Delete all records from pl_stdn
table #
#########################################################
$query2 = "DELETE FROM pl_stdn WHERE site_id
= '$site_id'";
$result2 = mysql_query($query2)
or die("Could Not Execute Query 2!");
}
#########################################################
# Execute pl_stdn file management
routine #
#########################################################
$query3 = "LOAD DATA INFILE '$filepathstdn' INTO
TABLE pl_stdn FIELDS TERMINATED BY ',' ENCLOSED BY '\"'";
$result3 = mysql_query($query3)
or die("Could Not Execute Query 3!");
#########################################################
# Check for records in the itemhist
table #
#########################################################
$query4 = "SELECT site_id FROM itemhist WHERE
site_id = '$site_id'";
$result4 = mysql_query($query4)
or die("Could Not Execute Query 4!");
$numrows4 = mysql_num_rows($result4);
// If records exist in the itemhist table, do Query
5...
if ($numrows4 > 0) {
#########################################################
# Delete all records from itemhist
table #
#########################################################
$query5 = "DELETE FROM itemhist WHERE site_id
= '$site_id'";
$result5 = mysql_query($query5)
or die("Could Not Execute Query 5!");
}
#########################################################
# Execute itemhist file management
routine #
#########################################################
$query6 = "LOAD DATA INFILE '$filepathhist' INTO
TABLE itemhist FIELDS TERMINATED BY ',' ENCLOSED BY '\"'";
$result6 = mysql_query($query6)
or die("Could Not Execute Query 6!");
#########################################################
# Set the date that the data was
posted #
#########################################################
$dateposted = date("m/d/Y");
$query7 = "UPDATE school_info SET dateposted
= '$dateposted' WHERE site_id = '$site_id'";
$result7 = mysql_query($query7)
or die("Could Not Execute Query 7!");
// Display query execution time in seconds
$timer->end_time();
echo "Query took "; echo number_format($timer-
>elapsed_time(), 2) . " seconds";
?>
------------------------ Yahoo! Groups Sponsor --------------------~-->
Home is just a click away. Make Yahoo! your home page now.
http://us.click.yahoo.com/DHchtC/3FxNAA/yQLSAA/CefplB/TM
--------------------------------------------------------------------~->
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
