mysql table field types

I want to run a bandwidth test on occasion from one of my test scripts. This will simply help me get some really basic info on how some of our networks are doing.

Script side;

function band_test()
{
        echo "Doing bandwidth 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`
        SPEED=`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 speed="$SPEED" -F dns_time="$DNS_TIME" -F total_time="$TOTAL_TIME"

}


Php Side;

                        elseif ($_POST['function'] === 'bandwidth_monitor') {
                                mysql_query('INSERT INTO band_logs ( band_time, speed, dns_time, total_time, client_id ) VALUES ("' . $_POST['band_time'] . '","' . $_POST
['speed'] . '","' . $_POST['dns_time'] . '","' . $_POST['total_time'] . '","' . $clientid . '")' )
                or die('Query failed: ' . mysql_error());
                        }
                }

Mysql;

  	Field name    	Type    	Allow nulls?    	Key    	Default value    	Extras   
	id 	int(10) unsigned 	No 	Primary 	NULL 	auto_increment
	speed 	int(10) unsigned 	No 	None 	NULL 	
	dns_time 	float(10,4) 	No 	None 	NULL 	
	total_time 	float(10,4) 	No 	None 	NULL 	
	band_time 	datetime 	No 	None 	NULL

Open in new window


When I wasn't using these field types, I was testing using varchar and everything worked fine.
Now, nothing is being entered into the db but I've been starring at it so long that I am probably not seeing the obvious.

I also want to change 'speed' to 'bps' for bytes per second

Help!
projectsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GaryCommented:
Add error reporting to your page, also before your query line

mysql_query('INSERT INTO ...

add
echo('INSERT INTO band_logs ( band_time, speed, dns_time, total_time, client_id ) VALUES ("' . $_POST['band_time'] . '","' . $_POST
 ['speed'] . '","' . $_POST['dns_time'] . '","' . $_POST['total_time'] . '","' . $clientid . '")' )

Open in new window

...so you can check what values are being inserted, it's probably a case of entering characters in a numbers field
0
projectsAuthor Commented:
Actually, I have some typos in my message but I can't edit it anymore.
There is a mismatch of table names which doesn't exist in the real code.
0
GaryCommented:
You mean client_id
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

projectsAuthor Commented:
No, if you look at the function names, there's a mismatch but it's not mismatched in my code.
Anyhow, nothing is getting into the table.
0
projectsAuthor Commented:
I tried adding your error reporting but that kept giving me errors. Can you just post the line exactly into the code I posted? Then I'll just cut and paste it with less chance of errors.

Also, just overlook the client_id, that's just another routine in the php code to know which script is sending stuff since I have a few.
0
GaryCommented:
Did you post your code exactly as you have it in your php page as there appears to be a space between $_POST and ['speed']

At the top of your php page after the opening <?php tag add

error_reporting(E_ALL);

elseif ($_POST['function'] === 'bandwidth_monitor') {
	echo('INSERT INTO band_logs ( band_time, speed, dns_time, total_time, client_id ) VALUES ("' . $_POST['band_time'] . '","' . $_POST['speed'] . '","' . $_POST['dns_time'] . '","' . $_POST['total_time'] . '","' . $clientid . '")' );
	mysql_query('INSERT INTO band_logs ( band_time, speed, dns_time, total_time, client_id ) VALUES ("' . $_POST['band_time'] . '","' . $_POST['speed'] . '","' . $_POST['dns_time'] . '","' . $_POST['total_time'] . '","' . $clientid . '")' )
	or die('Query failed: ' . mysql_error());
}

Open in new window

0
projectsAuthor Commented:
This is what it looks like;


function band_test()
{
        echo "Doing bandwidth Test"

        BAND_TIME="$(date +"%F %T")"
        CSTATS=`curl -w '%{speed_download}\t%{time_namelookup}\t%{time_total}\n' -o /dev/null -s http://servre.com/file`
        SPEED=`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 speed="$SPEED" -F dns_time="$DNS_TIME" -F total_time="$TOTAL_TIME"
}

Open in new window


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

Open in new window

0
GaryCommented:
Did you make the amendments above?
Post the resulting sql that should have been printed on screen.
0
projectsAuthor Commented:
it's just a php page so the only way to see the output would be to send it to a file.

However, I seem to have gotten it working now. There was a field missing in the database table.
0
projectsAuthor Commented:
It's all working now so I'm not sure how to award. Can I award just because you were good enough to try and help? :)
0
projectsAuthor Commented:
This is storing in bytes per second right?
Also, can you tell me how to add a timeout to curl while we're at this?
When dns doesn't respond in time, I don't want the script stalled, I just want curl to time out and try again later.
0
projectsAuthor Commented:
This morning, the data in the database looks different than as I left it last night.

 

band_time 	                bytesps     dns_time   total_time
2014-08-20 01:34:16 	720686 	  0.0030 	     14.5500
2014-08-20 01:34:30 	1111732    0.0030         9.4320
2014-08-20 01:34:43 	2425414    0.0120         4.3230
2014-08-20 03:41:50 	758 	          0.0020 	       0.2780     <changes here...
2014-08-20 03:45:45 	849 	          0.0020 	       0.2480
2014-08-20 04:23:31 	1531 	  0.0020 	       0.1380
2014-08-20 04:29:36 	867 	          0.0020 	       0.2430

Open in new window

0
GaryCommented:
And how should it look
0
projectsAuthor Commented:
Well, it doesn't look right to me...

Look at where it changes overnight... goes from 2425414 bytes per second to 758 and in LESS time!
That's not possible :). It's a 10MB file I am using for testing.

2014-08-20 01:34:43       2425414    0.0120         4.3230
2014-08-20 03:41:50       758                 0.0020              0.2780
0
GaryCommented:
Are you sure there was a connection/file available as the total time is 0.2 seconds
0
projectsAuthor Commented:
Yes, the file is there. I did have dns issues yesterday when one of the fibre storage units died but I got that back up and dns is fine now so it's not that.
0
projectsAuthor Commented:
Ooops, found the problem. A variable calling the file name (for different sizes) was messed up so the file was unreachable.

Thanks for the lead :)

If you think you can answer my previous question, I'll be happy to award you this solution.

This is storing in bytes per second right?

Also, can you tell me how to add a timeout to curl while we're at this?
When dns doesn't respond in time, I don't want the script stalled, I just want curl to time out and try again later.
0
GaryCommented:
You can set a max time for the connection to last

--max-time


or a connection timeout

--connect-timeout

Append the number of seconds

If the example db entries above are for a 10mb file then the speed is bps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
projectsAuthor Commented:
curl --max-time 5 -w '%{speed_download}\t%{time_namelookup}\t%{time_total}\n' -o /dev/null -s http://server.com/file

Ok, bits per second then, thanks.
So, for example, 3934921 bits is my result so 480.3KB is the actual speed.
0
GaryCommented:
Correct, I actually get 491.86kb though
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.