I'm creating an installation script for an application that I'm developing and need to create databases dynamically from within PHP. I've got it to create the database but now I need to load in several .sql files. I had planned to open the file and mysql_query it a line at a time - until I looked at the schema files and realised they aren't just one query per line.
So, how do I load an sql file from within PHP (as phpMyAdmin does with its import command)?
There is runkit, but you may find it simpler to just call the script over the command line (Use shell_exec), if you don't need any interaction between the master and child processes.
So you want an AND search using each of the words entered, rather than the exact string? Howabout something like this:
$searchTerms = explode(' ', $bucketsearch);
$searchTermBits = array();
foreach ($searchTerms as $term) {
$term = trim($term);
if (!empty($term)) {
$searchTermBits[] = "bucketname LIKE '%$term%'";
}
}
...
$result = mysql_query("SELECT * FROM buckets WHERE ".implode(' AND ', $searchTermBits).");
this will give you a query like:
SELECT * FROM buckets WHERE bucketname LIKE '%apple%' AND bucketname LIKE '%and%' AND bucketname LIKE '%pear%'
change the AND to an OR if you want to match any of the search terms rather than all. Further improvements could involve defining some stop words like 'and' to give better results.
Since (around) PHP 5.2, PHP has had a built in class/object for dealing with Dates and Times, called DateTime. In a void, it's always better to use a built-in than to wrangle with the messy details yourself.
The DateTime constructor (or the date_create function) accepts a date in any format understood by strToTime. All you need to know about strToTime is it's magic voodoo that will correctly recognize a date in almost any string format. When I first encountered strToTime I had the same internal reaction you're having now ("that's bullshit/seems unreliable"). It's not. It Just Works in a way that your own fragile understanding of dates never will (and if you think you understand dates, you don't. Trust Me.)
So, pull the information from MySQL as a Date/Time string, and immediately create a PHP date Object. Use the date_format method (with some handy constants) when/if you need the date again as a string.
I found it's actually faster to read the file in python and execute in batches using pyodbc than it is to use the SQLCMD utility externally (and I don't have to install SQLCMD on every computer I run the scripts on!).
Here is the code I used (because pyodbc doesn't seem to have an executescript() method):
with open(scriptPath, 'r') as inp:
for line in inp:
if line == 'GOn':
c.execute(sqlQuery)
sqlQuery = ''
elif 'PRINT' in line:
disp = line.split("'")[1]
print(disp, 'r')
else:
sqlQuery = sqlQuery + line
inp.close()
I'm getting the feeling that everyone here who's answered this question doesn't know what it's like to be a web application developer who allows people to install the application on their own servers. Shared hosting, especially, doesn't allow you to use SQL like the "LOAD DATA" query mentioned previously. Most shared hosts also don't allow you to use shell_exec.
Now, to answer the OP, your best bet is to just build out a PHP file that contains your queries in a variable and can just run them. If you're determined to parse .sql files, you should look into phpMyAdmin and get some ideas for getting data out of .sql files that way. Look around at other web applications that have installers and you'll see that, rather than use .sql files for their queries, they just package them up in PHP files and just run each string through mysql_query or whatever it is that they need to do.