Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Upload and execute .sql file
- Thread starter overyde
- Start date
do you want them to create a whole new table, or just change the contents of one?
if you want them to create a whole new table, just read out the sql file in a session variable, and execute with mysql_query.. its the same with changing the contents..
hope this helped.
if you want them to create a whole new table, just read out the sql file in a session variable, and execute with mysql_query.. its the same with changing the contents..
hope this helped.
BeRtj's solution will not work if there is more than one query in the sql file as mysql_query cannot handle more than one.
the ideal solution is something like phpmyadmin. if you want to do it yourself you could use exec like this:
note, of course, that this has some pretty grave security issues involved. it also relies on mysql being in the system path (but it usually is). If mysql is not in the system path then you will need to supply the full path to the file.
if for some reason the mysql application does not have read rights to the sql file you are piping in, you might have either to do a chmod on the file or get the file contents in a string (file_get_contents()) and push the string (in quotes) on to the end of the shell_exec instead.
as an alternative you could parse the sql file for semicolons (query delimiters) that are not within quotes (i.e. not part of a string value). I guess a regex would be needed. put the queries into an array and then execute the queries one by one.
as another alternative, if the file were full of text values rather than sql commands you could use mysql_query with the Load Data Infile sql syntax.
the ideal solution is something like phpmyadmin. if you want to do it yourself you could use exec like this:
Code:
$dbuser = "";
$dbpass = "";
$db = "";
$dbhost = "";
$sqlFileName = "" ;// name of uploaded sql file
$command = "mysql --user=$dbuser --password=$dbpass --database=$db --host-$dbhost < $sqlFileName";
$output = shell_exec($command);
echo $output;note, of course, that this has some pretty grave security issues involved. it also relies on mysql being in the system path (but it usually is). If mysql is not in the system path then you will need to supply the full path to the file.
if for some reason the mysql application does not have read rights to the sql file you are piping in, you might have either to do a chmod on the file or get the file contents in a string (file_get_contents()) and push the string (in quotes) on to the end of the shell_exec instead.
as an alternative you could parse the sql file for semicolons (query delimiters) that are not within quotes (i.e. not part of a string value). I guess a regex would be needed. put the queries into an array and then execute the queries one by one.
as another alternative, if the file were full of text values rather than sql commands you could use mysql_query with the Load Data Infile sql syntax.
- Thread starter
- #4
in the command line the --host-$dbhost should be --host=$dbhost (i.e. substitute the = for the -) Have you considered installing phpMyAdmin?
It's a very comprehensive SQL "control panel" for such activities (and LOTS more!).
D.E.R. Management - IT Project Management Consulting
It's a very comprehensive SQL "control panel" for such activities (and LOTS more!).
D.E.R. Management - IT Project Management Consulting
- Status
Similar threads
- Locked
- Question