d_smiff
-
Posts
2 -
Joined
-
Last visited
Never
d_smiff's Achievements
Newbie (1/5)
0
Reputation
-
Hi, I've created a script that generates several SQL statements based on a few different factors. For speed, i've concatenated 50 or so SQL statements together to be executed when the function ends... However, I keep getting MySQL errors... such as: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; UPDATE browser SET m12 = 3, total=3, y2004=3 WHERE id=1; UPDAT' Is it possible to execute the following SQL query through PHP's mysql_query() function? Or doesn't this support multiple queries in one string? UPDATE area SET m12 = 3, total=3, y2004=3 WHERE id=1; UPDATE browser SET m12 = 3, total=3, y2004=3 WHERE id=1; UPDATE click_schoolid SET m12 = 3, total=3, y2004=3 WHERE id=1; UPDATE forum_id SET m12 = 3, total=3, y2004=3 WHERE id=1; UPDATE ind SET m12 = 3, total=3, y2004=3 WHERE id=1; UPDATE ipaddress SET m12 = 3, total=3, y2004=3 WHERE id=1; UPDATE page SET m12 = 3, total=3, y2004=3 WHERE id=1; UPDATE referrer SET m12 = 3, total=3, y2004=3 WHERE id=1; UPDATE school_id SET m12 = 3, total=3, y2004=3 WHERE id=1; UPDATE thread_id SET m12 = 3, total=3, y2004=3 WHERE id=1; Here is the PHP code for the page.... (we are connected to mysql through the include/header.php file): <?php include("includes/header.php"); $database = "bschool-stats"; function getTableName($date) { $month = substr($date, 5, 2); $year = substr($date, 0, 4); $tableName = $month . "_" . $year; return $tableName; } function process_stats($start, $do) { global $conn; global $tableArr; $sql = sprintf("SELECT * FROM log WHERE 1 LIMIT %s, %s", $start, $do); //echo $sql; $res = mysql_query($sql, $conn) or die(mysql_error()); while($row = mysql_fetch_array($res)) { echo "<b>Processing record: ".$row['log_id'] . "</b><BR>\n"; $insTable = getTableName($row['datecreated']); for($i=0;$i<count($tableArr);$i++) { $sql = "SELECT * FROM " . $tableArr[$i] . " WHERE value = '" . $row[$tableArr[$i]] . "'"; $finderQ = mysql_query($sql, $conn) or die(mysql_error()); $finder = mysql_fetch_array($finderQ); $fieldMonth = "m" . substr($row['datecreated'], 5, 2); //get the field name to increment $fieldYear = "y" . substr($row['datecreated'], 0, 4); //get the field name to increment if(mysql_affected_rows()==1) //there is already a row with this option. get it's date and increment the appropriate month { $actionUpd .="UPDATE " . $tableArr[$i] . " SET " . $fieldMonth . " = " . ($finder[$fieldMonth]+1) . ", total=" . ($finder['total']+1) . ", " . $fieldYear . "=" . ($finder[$fieldYear]+1) . " WHERE id=" . $finder['id'] . ";\n"; } else //there is no row with this option yet... create it. { if($row[$tableArr[$i]]=="") { $value = "NULL"; } else { $value = $row[$tableArr[$i]]; } $actionIns .= "INSERT INTO " . $tableArr[$i] . " (value, " . $fieldMonth . ", " . $fieldYear . ", total) VALUES ('" . $value . "', 1, 1, 1);\n"; } //echo nl2br($action); } echo nl2br($action); if($actionUpd!="") { echo nl2br($actionUpd); mysql_query($actionUpd, $conn) or die(mysql_error()); } if($actionIns!="") { echo nl2br($actionIns); mysql_query($actionIns, $conn) or die(mysql_error()); } $a++; } $findNext = "SELECT log_id FROM log WHERE 1 LIMIT " . (($start+$do)+1) . ", 1"; //get the next record ID to process and pass to page refresh $nextRecQ = mysql_query($findNext, $conn) or die(mysql_error()); $nextRec = mysql_fetch_array($nextRecQ); if($nextRec['log_id']!="") { $updateconfig = "UPDATE config SET counter = " . $nextRec['log_id'] . " WHERE type = 'nextrec'"; mysql_query($updateconfig, $conn) or die($updateconfig); //update config table with where we've processed up to } else { $add = "&done"; } echo " <html> <head> <title>Processing stats...</title> </head> <body> <p>The main logfile is currently being processed. Please standby...</p> <script language='javascript'> function MM_goToURL() { //v3.0 var i, args=MM_goToURL.arguments; document.MM_returnValue = false; for (i=0; i<(args.length-1); i+=2) eval(args[i]+\".location='\"+args[i+1]+\"'\"); } MM_goToURL('parent','process.php?mode=process&do=" . $do . "&id=" . ($do+$start) . $done . "'); </script>"; } if(isset($_GET['done'])) { echo "done"; die("Done"); } else { if(!isset($_GET['id'])) //start from the beginning { $start = 1; } else { $start = $_GET['id']; } if(!isset($_GET['do'])) //process 2000 records at a time by default { $do = 1000; } else { $do = $_GET['do']; } if($_GET['mode']=="process") { process_stats($start, $do); } } ?> Any help would be most appreaciated!!!