Don't know where to start - MySQL scripts, only need last result

TripletDad

I'm not even sure how to ask this question, so please bear with me. Due to the database structure, I need to run a small sql script for efficiency's sake. If I do everything in one query it takes forever. If I do it in a script, it takes 5 secs. I have the script working in the MySQL query browser and it uses temporary tables, so all this has to be done on one connect to the server. Here is the mysql script that I want to run....

drop temporary table if exists skfgreconcile1;

CREATE TABLE `pointofuse`.`skfgreconcile1` (
  `Lot` VARCHAR(10) NOT NULL DEFAULT '',
  `CustomerPartNumber` VARCHAR(45) NOT NULL DEFAULT '',
  `CustomerPartNumberRevision` VARCHAR(45) NOT NULL DEFAULT '',
  `PiecesUsed` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  `PiecesRemaining` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  `WorkOrder` VARCHAR(45) NOT NULL DEFAULT '',
  INDEX `Index_1`(`Lot`)
)
ENGINE = memory;


insert into skfgreconcile1
(lot, customerpartnumber, customerpartnumberrevision,workorder,piecesused)
   select lot, customerpartnumber, customerpartnumberrevision, workorder, sum(qty)
   from transhist
   where workorder='M833Z00'
   group by lot, customerpartnumber, customerpartnumberrevision, workorder
;


drop temporary table if exists skfgreconcile2;

CREATE TABLE `pointofuse`.`skfgreconcile2` (
  `Lot` VARCHAR(10) NOT NULL DEFAULT '',
  `PiecesRemaining` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  INDEX `Index_1`(`Lot`)
)
ENGINE = memory;


insert into skfgreconcile2
(lot, piecesremaining)
   select i.lot, sum(i.pieces)
   from customerinventory i, skfgreconcile1 r
   where i.lot=r.lot
   group by i.lot
;

update skfgreconcile1, skfgreconcile2
set skfgreconcile1.piecesremaining = skfgreconcile2.piecesremaining
where skfgreconcile1.lot=skfgreconcile2.lot;

What I need to display on the php page after the above sql runs is basically

select * from skfgreconcile1;

I know how to display the results from the single select * from skfgreconile1, but have no idea how to run the preceeding script so I can do the simple select. I don't even know what function in php to use to do this. Just put everything in one big honking $query line and then just do a normal mysql_query($query)? Any suggestions?

Thanks
TD

Houdini

This is not tested so test it first but I believe it should run.

mysql_query("DROP temporary TABLE IF EXISTS skfgreconcile1");

mysql_query("CREATE TABLE `pointofuse`.`skfgreconcile1` (
  `Lot` VARCHAR(10) NOT NULL DEFAULT '',
  `CustomerPartNumber` VARCHAR(45) NOT NULL DEFAULT '',
  `CustomerPartNumberRevision` VARCHAR(45) NOT NULL DEFAULT '',
  `PiecesUsed` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  `PiecesRemaining` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  `WorkOrder` VARCHAR(45) NOT NULL DEFAULT '',
  INDEX `Index_1`(`Lot`)
)
ENGINE = memory");


mysql_query("INSERT INTO skfgreconcile1
(lot, customerpartnumber, customerpartnumberrevision,workorder,piecesused)
   select lot, customerpartnumber, customerpartnumberrevision, workorder, sum(qty)
   from transhist
   where workorder='M833Z00'
   group by lot, customerpartnumber, customerpartnumberrevision, workorder");


mysql_query("DROP temporary TABLE IF EXISTS skfgreconcile2");

mysql_query("CREATE TABLE `pointofuse`.`skfgreconcile2` (
  `Lot` VARCHAR(10) NOT NULL DEFAULT '',
  `PiecesRemaining` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  INDEX `Index_1`(`Lot`)
)
ENGINE = memory");


mysql_query("INSERT INTO skfgreconcile2
(lot, piecesremaining)
   SELECT i.lot, sum(i.pieces)
   FROM customerinventory i, skfgreconcile1 r
   WHERE i.lot=r.lot
   GROUP BY i.lot");

mysql_query("UPDATE skfgreconcile1, skfgreconcile2
SET skfgreconcile1.piecesremaining = skfgreconcile2.piecesremaining
WHERE skfgreconcile1.lot=skfgreconcile2.lot");

TripletDad

So what you're saying is just run it as a bunch of single queries and don't worry about the result sets? 'doh. I'll give it a shot and see what happens. Thanks for the idea.

Houdini

My mistake I thought you just wanted to write a PHP script, but looking at the code some more you can only create one table at a time, but with a temporary tables it might. But when running the query as you have it it will create a table skfgreconcile1 on database pointofuse

I misunderstood your question.

TripletDad

No, you did fine, got me pointed in the right direction. Here's the PHP code that does work...

//
// this is simulating a sql batch
//
if (!mysql_query("DROP temporary TABLE IF EXISTS skfgreconcile1")) {
    die('Invalid drop skfg1 query: ' . mysql_error() . "\n");
    }

if (!mysql_query("CREATE temporary TABLE `pointofuse`.`skfgreconcile1` (
                         `Lot` VARCHAR(10) NOT NULL DEFAULT '',
                         `CustomerPartNumber` VARCHAR(45) NOT NULL DEFAULT '',
                         `CustomerPartNumberRevision` VARCHAR(45) NOT NULL DEFAULT '',
                         `PiecesUsed` INTEGER UNSIGNED NOT NULL DEFAULT 0,
                         `PiecesRemaining` INTEGER UNSIGNED NOT NULL DEFAULT 0,
                         `WorkOrder` VARCHAR(45) NOT NULL DEFAULT '',
                         INDEX `Index_1`(`Lot`))
                         ENGINE = memory")) {
    die('Invalid create skfg1 query: ' . mysql_error() . "\n");
    }

if (!mysql_query("INSERT INTO skfgreconcile1
                     (lot, customerpartnumber, customerpartnumberrevision, workorder, piecesused)
                      select lot, customerpartnumber, customerpartnumberrevision, workorder, sum(qty)
                      from transhist
                      where workorder='M833Z00'
                      group by lot, customerpartnumber, customerpartnumberrevision, workorder")) {
    die('Invalid insert skfg1 query: ' . mysql_error() . "\n");
    }

if (!mysql_query("DROP temporary TABLE IF EXISTS skfgreconcile2")) {
    die('Invalid drop skfg2 query: ' . mysql_error() . "\n");
    }

if (!mysql_query("CREATE temporary TABLE `pointofuse`.`skfgreconcile2` (
                         `Lot` VARCHAR(10) NOT NULL DEFAULT '',
                         `PiecesRemaining` INTEGER UNSIGNED NOT NULL DEFAULT 0,
                          INDEX `Index_1`(`Lot`))
                          ENGINE = memory")) {
    die('Invalid create skfg2 query: ' . mysql_error() . "\n");
    }

if (!mysql_query("INSERT INTO skfgreconcile2 (lot, piecesremaining)
                       SELECT i.lot, sum(i.pieces)
                       FROM customerinventory i, skfgreconcile1 r
                       WHERE i.lot=r.lot
                       GROUP BY i.lot")) {
    die('Invalid insert skfg2 query: ' . mysql_error() . "\n");
    }

if (!mysql_query("UPDATE skfgreconcile1, skfgreconcile2
                       SET skfgreconcile1.piecesremaining = skfgreconcile2.piecesremaining
                       WHERE skfgreconcile1.lot=skfgreconcile2.lot")) {
    die('Invalid update skf1 query: ' . mysql_error() . "\n");
    }


// end of sql batch, results will be output now


// Perform Output Query
$result = mysql_query("SELECT * from skfgreconcile1 order by customerpartnumber,lot");

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}


// Use result

I had to give the php account create_temp_table, insert and update rights in mysql. It started with only select.

Actually it creates 2 temporary tables skfgreconcile1&2, but I'm really only interested in the first one. The 2nd is there just to make the queries run faster because I don't have to join more than 2 tables in one query.

Do you think that's adquate error checking on the queries?