[SOLVED] passing variables to mysql

[eon201]

Ok so, here is my new problem.

 

My script works out a variable (the users ip) and then needs to pass it to a mysql table.

Quite simple.

 

But the trciky bit is that I need my php to run this style of script before it posts the data to the sql.

 

Does the table for this hour exist? y/n (there will only ever be one hour table which needs to be overwritten each hour)

Does the table for this day exist? y/n (there will be a new table for each day)

Does the table for this week exist? y/n (there will be a new table for each week)

Does the table for this month exist? y/n (there will be a new table for each month)

 

Create the corresponding table if neccesarry.

Post the data to the all the corresponding tables according to the time and date.

 

By doing this I am keeping all the tables amended and can pull them back into another php file which will read the data and post it accordingly.

 

Ok cool. I hope someone has done this before, as im quite unsure how to do this.

 

Thanks in advance. eon201

[eon201]

Has anyone had any experience with this?? ???

[axiom82]

Your goal is to record the timestamp and IP address of a website surfer, right?

 

This should be done with a single table having three fields.

 

Here is the SQL script to create the table:

CREATE TABLE `visitor` (
`id` INT( 10 ) NOT NULL ,
`ip_address` VARCHAR( 15 ) NOT NULL ,
`date` DATETIME NOT NULL ,
PRIMARY KEY ( `id` ) 
) ENGINE = MYISAM ;

 

Here is the SQL query to insert a new visitor's IP and timestamp.

session_start();

if (!isset ($_SESSION['ip_address'])){

     $_SESSION['ip_address'] = $_SERVER['REMOTE_ADDR'];
     mysql_query ("INSERT INTO visitor SET ip_address='{$_SESSION['ip_address']}', date=now()");

}

 

Here is the code to retrieve narrow results such as 1 day or 1 month

// Function: Format Date //
function format_date($time){

     $date = getdate ($time);
     return $date['year'] . '-' . sprintf ('%02d', $date['mon']) . '-' . sprintf ('%02d', $date['mday']) . ' ' . sprintf ('%02d', $date['hours']) . ':' . sprintf ('%02d', $date['minutes']) . ':' . sprintf ('%02d', $date['seconds']);

}

// Retrieve Today's Date Range //
$date['start'] = format_date(strtotime('today'));
$date['end'] = format_date(strtotime('+1 day', strtotime('today'));

// Retrieve Today's Visitor Records //
$query = mysql_query ("SELECT * FROM visitor WHERE date > '{$date['start']}' AND date < '{$date['end']}'");
while ($visitor = mysql_fetch_assoc ($query)){

     print_r ($visitor);

}

[eon201]

Hi, thanks for the response.

 

What im trying to do is only pass the ip to the mysql. Not the timestamp.

 

I need my php to work out (with a timestamp or date function) if a database exists for today,thisweek, and thismonth. If so then plant the variable inside it. If not then create it (naming it after the date eg for today it would be called 15/11/07 or for this week it would be called 3/4-11/07 and for this month it would be 11/07) and add the variable. Does this make sense?? Or am I confusing things further?? 

[axiom82]

The timestamp is your search variable.

 

You retrieve ip addresses based on the record's timestamp.

[axiom82]

I see what you are trying to accomplish, but it is not the best way.

 

I've added to my code above.  It shows a way to search one table for a specific day.

[eon201]

Ok so let me get this straight. (Im a noob so i get confused very easily at the moment!)

 

Pass both the ip and the timestamp into the mysql table (using one table for all data).

 

Then when needed call the data back from the table but only corresponding to time/date selection. eg per hour. day, week or month??

 

Thanks so far!!" 

 

 

[axiom82]

Yes.  You use the PHP strtotime() function to create a unix timestamp.  You pass the unix timestamp through the format_date() function I have created for you.  It will format the date as a MySQL datetime.

 

You need two timestamps though, a start and end timestamp and anything that falls in between is returned

[eon201]

Thanks! Thats great. Very helpfull!!

[axiom82]

You are very welcome.