Help with creating MySQL Table

[genieuk]

Hi,

I been having allot of help on here certainly from 'Josh Connerty', who has done a fantastic job of helping me. I am learning php and am enjoying it.

So before i start a big thanks to Josh Connerty and this forum existing as it is a pleasure to be part of a great helpful community.

Ok, back to where i need help.

I basically need to create a MySQL table and fields within the table.

I don't want to create it using php just so i can go to PHPMyAdmin and in the SQL query box enter in the info and let PHPMyAdmin create it that way.

I am having difficulty in knowing what type to use for each field,

I basically need 8 fields in the table. These 8 fields are going to store data about users. I only need one table.

Below is my code, if someone could read it and if they would be kind enough to create me the MySQL info that i need to enter into PHPMyAdmin to create the table.

Things that confuse are things such as the referral url, as if someone was referred from another website where the website that referred them had a very long url which type would be best to use such as CHAR, Text etc... due to possible lenght of url,

I would value and appreciate your time if someone could help.

Thank you,
genieuk

<?php

include("dbconnect.php");

// Change this to however many days until you need to add a new version. Defaults to 31 or a month.
$days = 1;
               
$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'];

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";
}

while( $use == false ) {
    $temp = rand( 100000000 , 999999999 );
    $t = mysql_query("SELECT * FROM visitordata WHERE id='$temp'");
    if( @mysql_num_rows( $t ) > 0 ) {
        return false;
    } else {
        $id = $temp;
        $use = true;
    }   
}

$length = 60*60*24*$days;
$time = time() + $length;

$test = mysql_query("SELECT * FROM visitordata WHERE ip='$ip'");
if( @mysql_num_rows( $test ) == 0 ) {
    $query = "INSERT INTO visitordata VALUES ('$id', '$time', '$ip', '$host', '$agent', '$uri', '$ref', '$oslang')"; //*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.";
} else {
    $row = mysql_fetch_array( $test );
    if( $row['dbtime'] <= time() ) {
       $query = "INSERT INTO visitordata VALUES ('$id', '$time', '$ip', '$host', '$agent', '$uri', '$ref', '$oslang')"; //*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);

?>

I know i need to start off with

CREATE TABLE visitordata

but that's all lol ... is someone could help me finish it off please.

[HITMANOF44th]

depends on what kind of data it is going to be you want to start by makeing a MyISAM database in php and then the index if you are going to be useing it would be int and you would alos put auto increment on and if your going to be storeing data but for a someone new to it i would start with text first

[HITMANOF44th]

i made a database called test1 then to make the tables inside it

CREATE TABLE `test1`.`inside_test` (
`index` INT NOT NULL AUTO_INCREMENT ,
`date` DATE NULL ,
`a` TEXT NULL ,
`b` TEXT NULL ,
`c` TEXT NULL ,
`d` TEXT NULL ,
`e` TEXT NULL ,
`f` TEXT NULL ,
PRIMARY KEY ( `index` )
) TYPE = MYISAM

[genieuk]

Hi,

Thanks for your help. I think there is one or two things thou that should not be in above exmaple, AUTO_INCREMENT firstly in the script that i had help on Josh told me not to auto increment it the vd_id because the script will generate a 9 digit number automatically.

This is what i think it needs to be. Thing is i used a program to help me do it but the problem, it generates it in php way for PHP to create the database and table.

Could someone please convert the php below into a form that i can run the SQL query via PHPMyAdmin?

setupTable("visitordata", "create table if not exists `visitordata` ( `vd_id` INT not null , primary key (`vd_id`), `time` DATETIME not null , `ip` TEXT not null , `host` TEXT not null , `agent` TEXT not null , `uri` TEXT not null , `ref` TEXT not null , `oslang` TEXT not null )", array("ALTER TABLE `visitordata` ADD `field1` INT","ALTER TABLE `visitordata` CHANGE `field1` `vd_id` INT ","ALTER TABLE `visitordata` ADD `field2` INT","ALTER TABLE `visitordata` CHANGE `field2` `time` INT ","ALTER TABLE `visitordata` ADD `field3` INT","ALTER TABLE `visitordata` CHANGE `field3` `ip` INT ","ALTER TABLE `visitordata` ADD `field4` INT","ALTER TABLE `visitordata` CHANGE `field4` `host` INT ","ALTER TABLE `visitordata` ADD `field5` INT","ALTER TABLE `visitordata` CHANGE `field5` `agent` INT ","ALTER TABLE `visitordata` ADD `field6` INT","ALTER TABLE `visitordata` CHANGE `field6` `uri` INT ","ALTER TABLE `visitordata` ADD `field7` INT","ALTER TABLE `visitordata` CHANGE `field7` `ref` INT ","ALTER TABLE `visitordata` ADD `field8` INT","ALTER TABLE `visitordata` CHANGE `field8` `oslang` INT "," ALTER TABLE `visitordata` CHANGE `vd_id` `vd_id` INT not null ","ALTER TABLE `visitordata` ADD UNIQUE (`vd_id`)"," ALTER TABLE `visitordata` CHANGE `vd_id` `vd_id` INT zerofill not null "," ALTER TABLE `visitordata` CHANGE `vd_id` `vd_id` INT not null "," ALTER TABLE `visitordata` CHANGE `vd_id` `vd_id` INT not null "," ALTER TABLE `visitordata` CHANGE `vd_id` `vd_id` INT not null "," ALTER TABLE `visitordata` CHANGE `vd_id` `vd_id` INT unsigned not null "," ALTER TABLE `visitordata` CHANGE `vd_id` `vd_id` INT not null ","ALTER TABLE `visitordata` CHANGE `vd_id` `vd_id` INT not null "," ALTER TABLE `visitordata` CHANGE `ip` `ip` TEXT "," ALTER TABLE `visitordata` CHANGE `ip` `ip` TEXT not null ","ALTER TABLE `visitordata` ADD UNIQUE (`ip`)","ALTER TABLE `visitordata` DROP INDEX `ip`"," ALTER TABLE `visitordata` CHANGE `ip` `ip` TEXT not null "," ALTER TABLE `visitordata` CHANGE `ip` `ip` TEXT not null "," ALTER TABLE `visitordata` CHANGE `ip` `ip` TEXT not null "," ALTER TABLE `visitordata` CHANGE `ip` `ip` TEXT not null "," ALTER TABLE `visitordata` CHANGE `ip` `ip` TEXT not null "," ALTER TABLE `visitordata` CHANGE `ip` `ip` TEXT not null "," ALTER TABLE `visitordata` CHANGE `host` `host` TEXT "," ALTER TABLE `visitordata` CHANGE `host` `host` TEXT not null "," ALTER TABLE `visitordata` CHANGE `agent` `agent` INT not null ","ALTER TABLE `visitordata` ADD UNIQUE (`time`)"," ALTER TABLE `visitordata` CHANGE `time` `time` INT not null ","ALTER TABLE `visitordata` DROP INDEX `time`"," ALTER TABLE `visitordata` CHANGE `time` `time` DATETIME not null "," ALTER TABLE `visitordata` CHANGE `agent` `agent` TEXT not null ","ALTER TABLE `visitordata` ADD PRIMARY KEY (`vd_id`)"));

Thank you,
genieuk