Need a few pointers with MYSQL Datatypes and a query.

Hi all

Basically I am creating a php script to update my MySQL table that stores server info.

I am getting the servers to automatically run a php file on there side that requests a php file on my webserver, at the same time passing info over in the url variable.

All I need to pass over is a number. But the php file on the web server side will look at the IP the request came from and will need to match it to the row that stores the info for this server.


First of all I need to set the correct data types for the database.
I am crap at SQL and don't understand it to well.

The columns I have are
-ID (id row number and server)
-Name (name of the server node)
-Location (geographic location code)
-IP (ip of the server, this will need to be matched in the query)
-Speed (the network port speed on the server)
-CurrentUsage(the current usage, this is the number we will be passing over in the request)

I am not sure on what datatypes to use, I am guessing that the IP would have to be encoded? Not sure on how to do this..


The second part is the query for updating the rows.

I found a small guide online and have adapted it to what I think should work, would like confirmation and someone to take a look and see if it is what I need.

UPDATE servers
        SET servers_CURRENTUSAGE="NUMBER"
        WHERE IP=SERVERIP

Code (markup):

From what I can guess, this will update the row matching the SERVERIP with the current NUMBER in the "servers" table?

Is this what I am looking for?

Thanks!

Jack,

 

Hi,

here is a link where I have created the table and inserted data and plus added the updates statement if you need any other SQL query complex I can help out let me know : h**p://www.sqlfiddle.com/#!2/28a5c/1 just replace stars with TT and also here is the code for you:

CREATE TABLE servers (
id INT,
name varchar(100),
location INT,
ip varchar(50),
speed varchar(30),
currentusage INT(20));


INSERT INTO servers (id, name, location, ip, speed, currentusage)
VALUES (4, 'DMZ server', 3765, '192.168.0.113','200MB',200);
INSERT INTO servers (id, name, location, ip, speed, currentusage)
VALUES (4, 'domain server', 4765, '192.168.0.114','300MB',400);
INSERT INTO servers (id, name, location, ip, speed, currentusage)
VALUES (4, 'hosting server', 5765, '192.168.0.115','400MB',6000);
INSERT INTO servers (id, name, location, ip, speed, currentusage)
VALUES (4, 'ftp server', 6765, '192.168.0.116','600MB',7000);
INSERT INTO servers (id, name, location, ip, speed, currentusage)
VALUES (4, 'file server', 7765, '192.168.0.117','700MB',9000);

update servers set currentusage='8888'
where ip='192.168.0.117';

Code (markup):

hope it helps.. with the info you provided this should help if anything I missed let me know I will fix it for you.

cheers

 

Here is another example with the joint query as well with two tables: h**p://www.sqlfiddle.com/#!2/f8e38/4

-- main table
CREATE TABLE servers (
id INT,
name varchar(100),
location INT,
ip varchar(50),
speed varchar(30),
currentusage INT(20));

-- other table for join test
CREATE TABLE serversip (
ip varchar(50),
logs varchar(30));

INSERT INTO serversip (ip,logs) VALUES
  ('192.168.0.113','this server is fine');
INSERT INTO serversip (ip,logs) VALUES
  ('192.168.0.114','this server not fine');

-- insert some data

INSERT INTO servers (id, name, location, ip, speed, currentusage)
VALUES (4, 'DMZ server', 3765, '192.168.0.113','200MB',200);
INSERT INTO servers (id, name, location, ip, speed, currentusage)
VALUES (4, 'domain server', 4765, '192.168.0.114','300MB',400);
INSERT INTO servers (id, name, location, ip, speed, currentusage)
VALUES (4, 'hosting server', 5765, '192.168.0.115','400MB',6000);
INSERT INTO servers (id, name, location, ip, speed, currentusage)
VALUES (4, 'ftp server', 6765, '192.168.0.116','600MB',7000);
INSERT INTO servers (id, name, location, ip, speed, currentusage)
VALUES (4, 'file server', 7765, '192.168.0.117','700MB',9000);
INSERT INTO servers (id, name, location, ip, speed, currentusage)
VALUES (4, 'file server', 7765, '192.168.0.117','1000MB',9000);

-- update query
update servers set currentusage='8888'
where ip='192.168.0.117';

-- joint query
select * from servers,serversip
where servers.ip=serversip.ip

Code (markup):

 

Thank you very much Khodem!

That is perfect.

 

you are welcome if that help you out then choose it as answered or else you can ask your issue

 

Got another query I could do with some help with.


I have 3 columns.

The first one is a boolean 0 or 1.
If 1 I need it to add col1 to col2

So lets say if we have 1|50|100 it updates the last to 150.

Basically I am running a timer to count up the total amount of a number every minute but only if it is allowed by the first row.
There are 100s of these not just one row that needs updating to.

I have an idea but not sure about it.

Any help would be appreciated!

 

Hi,

here is the solution for you:

select id,sal,comis,(sal+comis)
from test
where id=1

Code (markup):

and here is the test link

http://www.sqlfiddle.com/#!2/afda7/1


cheers