loading sql schema from php

[sde]

php/mysql

i'm making a web install script and i'm wondering what the best way to automatically load up sql schema is.

should i read a file into a variable? or is there a function to automatically load sql from a file?

[sdeming]

Mike, try this by executing:

PHP Code:

run_query_batch($dbhandle, "schema.sql"); 


The function:

PHP Code:

function run_query_batch($handle, $filename="")
{
  // --------------
  // Open SQL file.
  // --------------
  if (! ($fd = fopen($filename, "r")) ) {
    die("Failed to open $filename: " . mysql_error() . "<br>");
  }

  // --------------------------------------
  // Iterate through each line in the file.
  // --------------------------------------
  while (!feof($fd)) {

    // -------------------------
    // Read next line from file.
    // -------------------------
    $line = fgets($fd, 32768);
    $stmt = "$stmt$line";

    // -------------------------------------------------------------------
    // Semicolon indicates end of statement, keep adding to the statement.
    // until one is reached.
    // -------------------------------------------------------------------
    if (!preg_match("/;/", $stmt)) {
      continue;
    }

    // ----------------------------------------------
    // Remove semicolon and execute entire statement.
    // ----------------------------------------------
    $stmt = preg_replace("/;/", "", $stmt);

    // ----------------------
    // Execute the statement.
    // ----------------------
    mysql_query($stmt, $handle) ||
      die("Query failed: " . mysql_error() . "<br>");

    $stmt = "";
  }

  // ---------------
  // Close SQL file.
  // ---------------
  fclose($fd);
} 


sde??? Hrm...

[sde]

thanks scott, .. i'll post my results.

wow, just noticed my new name looks like yours =P . sde is a nick i've used for a long time.. short for sandiego electronic, .. ( http://sdelectronic.com/ ) a site i used to maintain. i just wanted to cut down on having a few different nicks on the internet. i'm not so concerned with this site, but other sites concern me with having my first initial , last name on there.

[Admin]

[SDR] SDE kills PrincessMononoke *headshot*

DOH!

[sde]

that is actually what i'm making this install for.. it is a server-status plugin that logs to mysql .. then php retrieves the data every 15 seconds.

the install script will walk the web designer through verifying if the db connection info is valid, then creating tables, inserting data, then creating an administrator account so they can change the colors through a web-based interface. it also supports templates and multiple servers. i've spent way too much time on this thing, but i'm almost done with version .01 =)

still haven't used the code above, .. i'm tweeking the database so i don't have to go back later and change it.

[sde]

Thanks Scott!!

That script worked great!! :rock:

[sdeming]

Woo hoo, glad to hear it!

When you finish your installer, you should write a NUM outlining the steps.

[sde]

great idea .. in the mean time, if anyone is interested, .. you can check out what i have done here: http://mystatus.ocsclan.com

i feel a sense of accomplishment with this one.