Jump to content

MySQL - executing multiple queries


d_smiff

Recommended Posts

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.