Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

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.
0
projects
Asked:
projects
  • 7
  • 5
1 Solution
 
ThomasMcA2Commented:
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.
0
 
projectsAuthor Commented:
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.
0
 
ThomasMcA2Commented:
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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
projectsAuthor Commented:
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.
0
 
projectsAuthor Commented:
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 :)
0
 
ThomasMcA2Commented:
Gotcha. You were close to getting it working. All you were missing was to move the "fi" to the end, like this:

function band_test()
{
runme="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

0
 
projectsAuthor Commented:
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
0
 
projectsAuthor Commented:
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.
0
 
ThomasMcA2Commented:
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.
0
 
projectsAuthor Commented:
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.
0
 
ThomasMcA2Commented:
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.
0
 
projectsAuthor Commented:
I think you are right but your solution could help someone else so I'll award :).

Thanks.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now