MySQL - executing multiple queries

[d_smiff]

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!!!