0

I am trying to setup a small admin console on my Windows localhost environment which processes a results table on an as needed basis, and while I have most everything else working, am stumped on the following, and while a solution mentioned in this question seems desirable, I had a hard time understanding what's the script path as I had already provided the host, username, password & database in my MySQLi_CONNECT statement before other working queries. If there's another better way I'd appreciate it.

Essentially I need to update a table with a ranking which I know can be simplified on other SQL platforms, but MySQL only seems to work for me when I use a script variable. Here's a snippet of my code from bb2process.sql:

SET @rank := 0;
update bbdata.bb2 SET PTRANK= @rank:= (@rank+1) ORDER BY PTOTAL DESC

This totally works when I run the script in MySQL query browser, but certainly not when I split the commands and use MySQLi_QUERY individually and the solution I linked to suggests using SHELL_EXEC for running a MySQL script but essentially need a clear example.

jfalberg
  • 141
  • 4
  • 16
  • That technically counts as 2 queries so you're running 2 `mysqli_query` calls, yes? You could try using [mysqli::multi-query()](http://php.net/manual/en/mysqli.multi-query.php)... – CD001 Jan 11 '19 at 14:58
  • I tried multi-query but that didn't seem to work. I even echo'd the query to make sure that was the one I was reading from. – jfalberg Jan 11 '19 at 19:16

1 Answers1

0

This actually worked for me though I'm wondering if there's a cleaner way in the event I transfer to a new computer I expect to get soon:

$mycmd = "\"C:\\Program Files\\MySQL\\MySQL Server 5.1\\bin\\mysql.exe\" -h " . $hostname . " --user=";
$mycmd = $mycmd . $mysql_login . " --password=" . $mysql_password . " --database=";
$mycmd = $mycmd . $database . " < bb2-process.sql";
echo '<br/><br/>' . $mycmd;
$output = shell_exec($mycmd);

The only part I was puzzled about was that my $output displayed nothing when I tried to echo it but when I went into the query browser I saw it worked as desired at least.

jfalberg
  • 141
  • 4
  • 16
  • You can always export/import the .sql file. It should import well back into any system, just as long as all of the queries/commands are supported. – Funk Forty Niner Jan 13 '19 at 01:20