INSERT INTO mySQL - from php script?

[genieuk]

Hi,

If you see my other post about writing data to a file i am not doing that now i am using a database instead.

Anyway ....

Before i start i would like to say i am very very new to PHP and no very very little about MySQL writing.

Basically i have a dbconnect.php form, this will connect to a database then i have another form called visitrecorder.php

the visitrecorder.php collects data about user when visiting webpage ( i use

<?php include("visitorrecorder.php"); ?>

in my webpages i want to collect data for) and i want it to after it collects data for it to send to a database. Problem is i don't know how to write the SQL query to insert table into phpMyAdmin and if someone could look at this code and tell me am i doing it rite?

If you read the code you probably know what i am trying to do.

So this is what i want, i am not sure if i done it correctly i want to insert "$dbtime","$ip","$host","$agent","uri","$ref" into a database called visitordata.

Can someone tell me in my code have i done the insert to correctly? and if someone could create the table sql thingy as i dont have a clue. Learning PHP and MySQL at once is hard for me lol.

I am not sure if i also need an ID inserted, i mean everytime data is sent to database i dont want any data that is already there overwritten.

<?php

include("dbconnect.php");

$dtime = date('F jS Y, h:iA');
$ip = $_SERVER['REMOTE_ADDR'];
$host = gethostbyaddr($ip);
$agent = $_SERVER['HTTP_USER_AGENT'];
$uri = $_SERVER['REQUEST_URI'];
$ref = $_SERVER['HTTP_REFERER'];

if($dtime == ""){
$dtime = "None Recorded";
}
if($ip == ""){
$ip = "None Recorded";
}
if($host == ""){
$host = "None Recorded";
}
if($agent == ""){
$agent = "None Recorded";
}
if($uri == ""){
$uri = "None Recorded";
}
if($ref == ""){
$ref = "None Recorded";
}

"INSERT INTO visitordata () VALUES ("$dbtime","$ip","$host","$agent","uri","$ref")"; //*visitordata is the name of the MySQL table where the user data will be saved.
if (!mysql_query($sql,$con)) {
 die('Error: ' . mysql_error());
}
echo "The user data was successfully added to your database.";
mysql_close($conn);

?>

Thank you,
genieuk

[genieuk]

Hi,

I relise already i made a mistake i think in my code where it says:

"INSERT INTO visitordata () VALUES ("$dbtime","$ip","$host","$agent","uri","$ref")"; //*visitordata is the name of the MySQL table where the user data will be saved.
if (!mysql_query($sql,$con)) {
 die('Error: ' . mysql_error());

I think i meant to add the below before "INSERT INTO so it look like $sql=INSERT INTO

$sql=

[Josh Connerty]

Hello again genie :),

I will try my best to format your code...

<?php

include("dbconnect.php");

$dtime = date('F jS Y, h:iA');
$ip = $_SERVER['REMOTE_ADDR'];
$host = gethostbyaddr($ip);
$agent = $_SERVER['HTTP_USER_AGENT'];
$uri = $_SERVER['REQUEST_URI'];
$ref = $_SERVER['HTTP_REFERER'];

if($dtime == ""){
$dtime = "None Recorded";
}
if($ip == ""){
$ip = "None Recorded";
}
if($host == ""){
$host = "None Recorded";
}
if($agent == ""){
$agent = "None Recorded";
}
if($uri == ""){
$uri = "None Recorded";
}
if($ref == ""){
$ref = "None Recorded";
}

$query = "INSERT INTO visitordata VALUES ('$dbtime', '$ip', '$host', '$agent', 'uri', '$ref')"; //*visitordata is the name of the MySQL table where the user data will be saved.
mysql_query( $query ) or die( "MySql Error Occured: " . mysql_error() );
echo "The user data was successfully added to your database.";
mysql_close($conn);

?>

Ok firstly your method on adding the data ia a little out, as you may or may not know PHP does not allow quotes inside quotes unless you have the \ in fron of the contained quotes.

To solve this you ca replace the enclosed double quotes with single quotes.

The more accepted way of confirming a database query was executed properly is by using the or die statement. If there was an error it will output the error but nothing else after. However if it does execute properly it will continue to display the rest of your content.

I find spacing out your code makes it easier for you to understand so I also added an extra space after each of your commas in the query.

This is just a personal thing but I prefer to do the following with sql queries:

$query = mysql_query( "INSERT INTO visitordata VALUES ('$dbtime', '$ip', '$host', '$agent', 'uri', '$ref')" ) or die( "MySql Error Occured " . mysql_error() );

This will instead make everything on one line and you know that is all there is to the query.

[genieuk]

You again lol

Your so brainy lol ... maybe one day i will be like you :)

Well do you think i done good? i was not that far out i dont think on making errors .

I also added another to detect user OS language

$_SERVER

All done, my hardest part now i reckon is learning mysql so i can create a table, i not got a clue on how to create a table for what i want. i mean like character lenght, well a url etc could possibly be very long etc.

Well i will have to try and find somewhere that explains in detail as everywhere i looked dont explain all the things after the table field names

Any website you know that does the job i want in explaining?

Then my next step it to create a page for admin only to show results etc but one step at a time lol

Thank you,
genieuk

[Josh Connerty]

lol you'll bemaking members scripts in no time. I'd say w3schools is probbably the best place to learn mysql but the PHP & MySql is a bit lacking try the MySql section. As for limiting char lengths I'd say limit it server side.

This way if the user input is too long it doesn't get trimmed by MySql because PHP will tell them.

[genieuk]

IT so confusing. I will see what i am capable of doing.

I just found a program called PHPMagic, not free and limited unless i pay but it also creates the pages to view data from database in a table in browser with filter and search options etc.

I am liking it but again, all those nulls or whatever.

may i ask thou, will i need to create a field called ID or something?

reason why is everytime data is sent to the database without an id will it get overwritten?

example ....

2 visitors visited my site, each visitor had information about them recorded and sent to the mysql database , visitor 1 they was on page first so there information was sent first, lovely stuff! but what about when user two comes along will it overwrite visitor 1 or no?

reason why... i am not sure if i do need an id or something, and if i do not sure what i need to add to mysql and/or that code i posted that sends the data to the database.

Thank you,
genieuk

[Josh Connerty]

You don't have to have an Id but it is an idea. Although a must is to have a primary key. I don't do much sql qith queries I tend to use phpMyAdmin. Do you have a local server?

[genieuk]

You don't have to have an Id but it is an idea. Although a must is to have a primary key. I don't do much sql qith queries I tend to use phpMyAdmin. Do you have a local server?

Hi,

mmm i do have phpMyAdmin on host server and PHP and MySQL.

rite ok then i will use an id i will call it vd_id and that will be the primary key.

What i need to do if you could help me, i will have to figure out how to create table myself but here is my updated code which has vd_id and detection of OS language.

Could you edit the script for me below so it checks the unique IP of the user and if it already exists in database then it wont add it to databse?

reason why is for example if user refreshes any pages that they are on etc then it will keep adding multiple instances of same person. I only want them tracked once no matter what page they on. So something that if user ip is in Database then do not add, and option in code for me to specify when that person is classified as a new visitor.

example: if visitor is still on site after 30 minutes then class visitor as a new visitor

Do you understand me and am i asking you for to much? please tell me to get lost otherwise.

this is the bit that needs checking to see if it in database already:

$ip = $_SERVER;

Thank you,
Mathew

UPDATED CODE below:

<?php
 
 include("dbconnect.php");
  
$dtime = date('F jS Y, h:iA');

$ip = $_SERVER['REMOTE_ADDR'];

$host = gethostbyaddr($ip);

$agent = $_SERVER['HTTP_USER_AGENT'];

$uri = $_SERVER['REQUEST_URI'];

$ref = $_SERVER['HTTP_REFERER'];
	  
$oslang = $_SERVER['HTTP_ACCEPT_LANGUAGE']; // collects user operating system language

if($dtime == ""){

$dtime = "None Recorded";

}

if($ip == ""){

$ip = "None Recorded";

}

if($host == ""){

$host = "None Recorded";

}

if($agent == ""){

$agent = "None Recorded";

}

if($uri == ""){

$uri = "None Recorded";

}

if($ref == ""){

$ref = "None Recorded";

}

if($oslang ==""){
	
$oslang ="None Recorded";

}

$query = "INSERT INTO visitordata VALUES ('vd_id', '$dbtime', '$ip', '$host', '$agent', 'uri', '$ref', '$oslang')"; //*visitordata is the name of the MySQL table.

mysql_query( $query ) or die( "MySql Error Occured:" . mysql_error() );

echo "The table was successfully added to your database."; // Will display success message if table was added to database without problems 

mysql_close($conn);
	  
?>

[Josh Connerty]

Sorry I'm lost, are you trying to refresh their data after 30 mins or making sure that it doesn't get added again.

To make sure it doesn't get added again this woul be the code:

if( $ip != "None Recorded" ) {
$test = mysql_query("SELECT * FROM visitordata WHERE ip='$ip'");
if( @mysql_num_rows( $test ) > 0 ) {
return false;
} else {
$query = "INSERT INTO visitordata VALUES ('vd_id', '$dbtime', '$ip', '$host', '$agent', 'uri', '$ref', '$oslang')"; //*visitordata is the name of the MySQL table.

mysql_query( $query ) or die( "MySql Error Occured:" . mysql_error() );

}
}

This will add them if their is not Ip recorded but will not add them if their was and it is already in the database.

[genieuk]

Hi,

Your a god send in helping me, again thank you! :)

Your rite, basically let say i am a visitor and i visit 10 pages on a website, well ever page i viewed would add me to the database being in this case 10 times added into database, now using that code you gave it will check to see if the ip is already recorded in database if not it gets added if it already exists it wont be added again. So now using that code i will only be added once into database.

Now where that 0 is, am i able to change that? i mean for example i want to be able to decide whether i want that user to be added to database again after let say 24 hours. So for example after 24 hours they are added again as a new visitor.

Does that make sence?

Thank you,
genieuk

[Josh Connerty]

Um no that just checks if there is a row in the mysql table that holds that IP.

To do that you would then have to add a unix timestamp to the database and work off that.

For each visitor you input you must add a unix timestamp that is 24 hours ahead of us and then you would check that if the time is less then now then you must update the values or add a new row etc.

Or you could just update the row everytime they make a move from one page to another.

[genieuk]

mmm, not got a clue lol.

mmm, not sure to stay with original script.

I mean if i add that code then what if at some point whether it be months, years or something that ip was recorded again, it would not add it.

I dont know about IP's but probably at some point the same IP is used again whether it be for same person or someone else.

My option is to either log everything, so every page that is tracking visitor they will be logged which really is silly to have user being logged over and over again or do what you say but not got a clue how to do what you said. I said 24 hours as an example but i was thinking more of using minutes not hours.

If you go here http://daniweb.com/forums/thread187169.html 4th post down they sort of doing same thing.

Thank you,
genieuk

[Josh Connerty]

Similar just without the cookies.

I might be able to write you a script but I don't think it would help you learn.

[genieuk]

mmm, when you say write me a script you mean the script i have posted will no longer work?

or you mean you just need to tweak it abit.

Because i would love it if you did write one so it does what i want, I tend to read the php code like if i was reading a book and if i see something that i dont understand i look it up on php website etc.

Upto you, i don't mind, i just feel rather embarrased as you doing loads for me.

Thank you,
Mathew

[Josh Connerty]

I suppose I learnt PHP the same way, to be honest I learned JavaScript AJAX & MySql the same way too.

I'll work on a script.

[genieuk]

Wow thanks,

I hope what i done already is not going to be completely re done as i was proud of it lol. If so i should print it out and frame it.

Thanks so much,
genieuk

[Josh Connerty]

I haven't tried it you will have to try it out I also added the ID field that will produce a random 9 digit id number that will not be repeated.

[Josh Connerty]

Wow thanks,

I hope what i done already is not going to be completely re done as i was proud of it lol. If so i should print it out and frame it.

Thanks so much,
genieuk

It is quite a big change but yes I'm still using your code.

[genieuk]

Thank you,

I will test it once i figure out the new code, understanding it i mean.

Now i need to create mysql query thing to create the table.

That is very hard, i mean the part that i don't get is the data type, not sure which is the best to use for each information that is going to be stored.

As that code will generate a random id will i still need to use AUTO INCREMENT for the $id ? when i do eventually try and figure out how to create the MySQL table.

Also let say i choose 2 days then it would create a new log for the same user if IP is the same, will it record it on the second day or after the second day?

Thank you,
genieuk

[Josh Connerty]

when they next connect afetr the second day wether it be 1 second after 48 hours or 4 hours after 48 hours.

For the id because when you suto increment it will give you a one digit number wich is predictable I use a 9 digit random number so I wouldn't use auto_increment. Set it as an INT with 9 char length.

[genieuk]

Great, thank you for all your help.

Not sure shall i now start a new thread maybe about MySQL.

I think i may have it but not sure at all. Reason what confuses me is URL for example. I cannot use some data types due to they limit the amount of characters etc. Same for date and time, do i use data type date and time or not etc.

Also that new code does it log both date and time? and if so together or seperate fields when entered into MySQL, as unless i am reading it wrong it only seems to add time not date unless i am not reading the new code correctly.

This program PHPMagic seems good as it explains things and creates the files to be able to view database in a user friendly and browser friendly format where i can then update, delete, modiy stuff from database , filter save as .csv etc ....so it good but it not free so unless i fork out over $50 i cannot create my full database as it is limited so 4 fields in free version.

It save me allot of time thou as it generates a hell of allot of php code that would work wonders with what i am trying to do, infact it has everything i need for it to do what i want.

do you know any free alternatives ?

[Josh Connerty]

Honestly I wouldn't bother, I would just download xampp and then you could do all the work locally and then just type in something like localhost/phpmyadmin and access phpmyadmin and you can just go to your browser and type in localhost/page and see the results.

As for the timestamp basicly what it does is produce a number that is the amount of seconds since 1970 so that would give you the time and date the the second.

I good peice of software that is free is Notepad++ or I have just recently bought Nusphere's PHPED that does similar to what you were describing it basicly runs the script but I don't think it would access the database.

[genieuk]

Thanks, i am downloading now, so what does it do allow me to test on my machine first before having to upload?

That why it called local server as it acts like a server but on the computer not a server? , first saving time on having to upload etc..

Will check both softwares out.

Once again thanks bud! you have done more than enough helping, honestly without you i would not have got anywhere.

You have really done a fantastic job.

Regards,
genieuk

So the time and date will be in the same field in the database?

[Josh Connerty]

Yes when you retreive it use the date function to display it in a readable format.

Xammp & Wamp are almost identical to your web server but it wont have a DNS server thus the mail server wont work and it wont have a domain name.

[genieuk]

Thank you,

I got XAMPP Installed much easier for testing etc, thanks for the advice of XAMPP, i did try WAMP before hand but got allot of .dll errors and it would not load etc. XAMPP works great without problems.

Thanks for everything, rest assured i will probably be wanting help again soon.

Because this MySQL thing is not very easy for me, i am not sure the best way to use the type for the field.

Thank you,
genieuk

[Josh Connerty]

For most it will probably INT or VARCHAR. I will PM you my MSN address if you like, it seems to work alot quicker in a live chat as apose to a forum thread.

[genieuk]

Hi,

I managed to sort it, i am having a problem thou.

In the code when data is added to database it tells it to echo out that data was added etc and to give an error if not added.

Problem i am having is the webpage when i view it it brings up the text to say it was added to database and error if it was not added. Problem with that is the visitor can see that text,

Could you alter it so it does not echo out anything whether it be data added or not added please?

Also even thou i dont get errors for some reason even thou i set to not add person again until another 2 days it is still added the data and not checking the IP information in the datbase to see if it already exists.

and last of the problem is am i suppose to set the $time field to both date and time or just time? because i set it to date and time field as i thought it would give date and time when user was recorded, but when viewing database it comes out like this:

0000-00-00 00:00:00

Hope you can help me solve the last of the problems then it all done!

I am on MSN so yes please do. :)

Thank you,
Mathew

[Josh Connerty]

Hmmm it sounds like a few small issues. I will have to play with it on monday as I am currently building a new PC sooo I'll see what I can do on my laptop.

[genieuk]

Hi, ok, if you want still send me your MSN address.

I think they are very minor.

I was hoping i could sort it today as i got excited seeing it all, don't mean to sound rude. At the end of the day you done everything for free and been a pleasure to have on hand for help. Your rock.

Ok no problem. One thing thou, is it suppose to log both date and time like it was originally before you tweaked the script for me? reason why i set the db type for time to both date and time so if it not meant to record both date and time then it maybe that because of the field type i used wish was both date and time. If i could thou have both date and time logged.

Thank you,
genieuk