asked on

Read mysql record from bash/php

Hopefully, I've provided everything needed.
I am looking for a solution (the code) which picks up a variable on mysql which in turn is used by the bash script to enable or disable a test.

This is the bash side;

function band_test()
{
        BAND_TIME="$(date +"%F %T")"
        CSTATS=`curl -w '%{speed_download}\t%{time_namelookup}\t%{time_total}\n' -o /dev/null -s http://server.com/file`
        BITSPS=`echo $CSTATS | awk '{print $1}' | sed 's/\..*//'`
        DNS_TIME=`echo $CSTATS | awk '{print $2}'`
        TOTAL_TIME=`echo $CSTATS | awk '{print $3}'`
        $CURL -F function=band_test -F band_time="$BAND_TIME" -F bitsps="$BITSPS" -F dns_time="$DNS_TIME" -F total_time="$TOTAL_TIME"

Open in new window

This is the php side;

                        elseif ($_POST['function'] === 'band_test') {
                                 mysql_query('INSERT INTO bandwidth_check ( band_time, bitsps, dns_time, total_time, client_id ) VALUES ("' . $_POST['band_
time'] . '","' . $_POST['bitsps'] . '","' . $_POST['dns_time'] . '","' . $_POST['total_time'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
                        }
                }

Open in new window

The client_id mentioned is found by the php code when the remote script connects. I run the script on several workstations which is why I need this id to give each their own records.

I need to read the 'bandwt' field for a 0 or a 1 in the 'clients' table. Return the value to the script so that function band_test is not run if the result is a 0 and run if the result is a 1.

The reason for this is because on some of our networks, I am not allowed to run bandwidth tests while on others I am and I am using one script to do it all.

ThomasMcA2

Try this:

runme=$(mysql mydatabase -u $user -p$password -se "select bandwt from clients")

if [ "$runme" -eq "1" ]; then
   band_test()
fi

Open in new window

Make sure there is no space between the "-p" and the password.

projects

ASKER

I am assuming this means I would not need to send anything back to the script then as the php function would read the results and either run or not run?

Also, where in the function do I place this?

BTW, the php page has it's mysql credentials at the top of it already so what ever needs to go into the function is what I am looking for.

ThomasMcA2

I'm not sure about the php side of your process. To get bash to see the results of the query, just put your function after my example code, like this:

runme=$(mysql mydatabase -u $user -p$password -se "select bandwt from clients;")

if [ "$runme" -eq "1" ]; then
   band_test()
fi
                                          
function band_test()
{
        BAND_TIME="$(date +"%F %T")"
        CSTATS=`curl -w '%{speed_download}\t%{time_namelookup}\t%{time_total}\n' -o /dev/null -s http://server.com/file`
        BITSPS=`echo $CSTATS | awk '{print $1}' | sed 's/\..*//'`
        DNS_TIME=`echo $CSTATS | awk '{print $2}'`
        TOTAL_TIME=`echo $CSTATS | awk '{print $3}'`
        $CURL -F function=band_test -F band_time="$BAND_TIME" -F bitsps="$BITSPS" -F dns_time="$DNS_TIME" -F total_time="$TOTAL_TIME"
}

Open in new window

projects

ASKER

The query has to be inside, part of the function because there are other functions in the php page.

What I mean is that the script has already been given authentication to read/write from the database at this stage. That is done at the start of the php page which also determines it's id.

So the query to read from the database needs to be from within the function that I posted and it doesn't need mysql credentials as the php page already has access.

projects

ASKER

so basically, something like...

                                          
function band_test()
{
runme="select bandwt from clients;"
if [ "$runme" -eq "1" ]; then
   <continue on with the function, otherwise end>
fi
        BAND_TIME="$(date +"%F %T")"
        CSTATS=`curl -w '%{speed_download}\t%{time<wbr ></wbr>_namelooku<wbr ></wbr>p}\t%{time<wbr ></wbr>_total}\n'<wbr ></wbr> -o /dev/null -s http://server.com/file`
        BITSPS=`echo $CSTATS | awk '{print $1}' | sed 's/\..*//'`
        DNS_TIME=`echo $CSTATS | awk '{print $2}'`
        TOTAL_TIME=`echo $CSTATS | awk '{print $3}'`
        $CURL -F function=band_test -F band_time="$BAND_TIME" -F bitsps="$BITSPS" -F dns_time="$DNS_TIME" -F total_time="$TOTAL_TIME"
}

Open in new window

I am obviously not a programmer however so syntax if perfectly wrong :)

ASKER CERTIFIED SOLUTION

ThomasMcA2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

projects

ASKER

Wait now... so this is actually for the script side and not the php side?
Won't I need something on the php side in order for this to work?

The client is spitting the following error out;

./test: line 99: [: select bandwt from clients;: integer expression expected

projects

ASKER

I don't see how this could be done from the script side without code on the php side.
It seems it would be much easier to simply have the php side do a lookup, if a 0 then don't run the function, if 1, then run the function.

ThomasMcA2

Oops, I pasted your code back in without double-checking it. You modified my "runme" code, which broke it. Here is my version:

runme=$(mysql mydatabase -u $user -p$password -se "select bandwt from clients;")

Open in new window

And here is the complete version:

function band_test()
{
user=yoda
password=jedi

runme=$(mysql mydatabase -u $user -p$password -se "select bandwt from clients;")
if [ "$runme" -eq "1" ]; then
        BAND_TIME="$(date +"%F %T")"
        CSTATS=`curl -w '%{speed_download}\t%{time<wbr ></wbr>_namelooku<wbr ></wbr>p}\t%{time<wbr ></wbr>_total}\n'<wbr ></wbr> -o /dev/null -s http://server.com/file`
        BITSPS=`echo $CSTATS | awk '{print $1}' | sed 's/\..*//'`
        DNS_TIME=`echo $CSTATS | awk '{print $2}'`
        TOTAL_TIME=`echo $CSTATS | awk '{print $3}'`
        $CURL -F function=band_test -F band_time="$BAND_TIME" -F bitsps="$BITSPS" -F dns_time="$DNS_TIME" -F total_time="$TOTAL_TIME"
fi
}

Open in new window

In the above version, $user and $password are variables that need to be populated with your database user and password.

Although php might be able to do the lookup, I don't know how to make that work. However, the above version should work. It doesn't matter if php calls/runs it 100 times. If bandwt is not '1', the script will not do any work.

projects

ASKER

I'm not understanding the mysql login part. The script is running where it sometimes has to go over the internet so this would not be secure.

On the php side, the php page already has access to the mysql database so it doesn't need any credentials. This why I was wondering why not put this on the php side.

It seems that the php side would be the best place for this. Then the function itself could either run or get bypassed. Or, send something back to the script which it can store and only check again in 12hrs, 24hrs, etc instead of wasting the connections.

ThomasMcA2

Gotcha. Let's see if a php expert responds. If not, you may want to close/cancel this question, and submit a new one that is worded a little differently. In the new one, be clear about needing a php solution, and explain the "over the Internet" issue.

projects

ASKER

I think you are right but your solution could help someone else so I'll award :).

Thanks.