mySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Vietboy
Forum Commoner
Posts: 41
Joined: Mon Dec 01, 2003 10:59 pm

mySQL

Post by Vietboy »

i got mySQL server running..

Create MySQL Table...

#
# Table structure for table 'useronline'
#

CREATE TABLE useronline (
timestamp int(15) DEFAULT '0' NOT NULL,
ip varchar(40) NOT NULL,
file varchar(100) NOT NULL,
PRIMARY KEY (timestamp),
KEY ip (ip),
KEY file (file)
);


how do I create thhose??
User avatar
DuFF
Forum Contributor
Posts: 495
Joined: Tue Jun 24, 2003 7:49 pm
Location: USA

Post by DuFF »

2 ways:

1.) If you have phpMyAdmin then you can do it this way. If you don't have phpMyAdmin skip to the 2nd one.
Ok, open up phpMyAdmin select the database you want from the left frame. Then press the Query Window link at the bottom left. Paste the above code into the query window and press Go.l

2.) You will have to make a PHP page to install it. Replace the db values with whatever you username and password are:

Code: Select all

<?php
$host = "localhost";
// replace these with your username, password and dbname
$user = "dbUsername";
$password = "dbPassword";
$db = "dbName";

@mysql_connect($host,$user,$password) or die("Unable to connect to MySQL database: " . mysql_error());
@mysql_select_db($db) or die( "Unable to select MySQL database.");
$query = "CREATE TABLE useronline (
timestamp int(15) DEFAULT '0' NOT NULL,
ip varchar(40) NOT NULL,
file varchar(100) NOT NULL,
PRIMARY KEY (timestamp),
KEY ip (ip),
KEY file (file)
);"
$result = mysql_query($query) or die("MySQL query failed: " . mysql_error());
echo "MySQL table created successfully."
?>
Vietboy
Forum Commoner
Posts: 41
Joined: Mon Dec 01, 2003 10:59 pm

Post by Vietboy »

THank you.. I found it. Do I need anything after these?

Should I deleted Createmysql table in phpfile?

Code: Select all

Create MySQL Table... 

# 
# Table structure for table 'useronline' 
# 

CREATE TABLE useronline ( 
   timestamp int(15) DEFAULT '0' NOT NULL, 
   ip varchar(40) NOT NULL, 
   file varchar(100) NOT NULL, 
   PRIMARY KEY (timestamp), 
   KEY ip (ip), 
   KEY file (file) 
); 

Create Script... 

<?php 

$server = "xxx"; 
$db_user = "xxx"; 
$db_pass = "xxx"; 
$database = "xxx"; 
$timeoutseconds = 300; 

$timestamp = time(); 
$timeout = $timestamp-$timeoutseconds; 

mysql_connect($server, $db_user, $db_pass); 
$insert = mysql_db_query($database, "INSERT INTO useronline VALUES ('$timestamp','$REMOTE_ADDR','$PHP_SELF')"); 
if(!($insert)) { 
print "Useronline Insert Failed > "; 
} 
$delete = mysql_db_query($database, "DELETE FROM useronline WHERE timestamp<$timeout"); 
if(!($delete)) { 
print "Useronline Delete Failed > "; 
} 
$result = mysql_db_query($database, "SELECT DISTINCT ip FROM useronline WHERE file='$PHP_SELF'"); 
if(!($result)) { 
print "Useronline Select Error > "; 
} 
$user = mysql_num_rows($result); 


mysql_close(); 
if($user == 1) { 
print("<b>$user</b> user online\n"); 
} else { 
print("<b>$user</b> users online\n"); 
} 

?>
User avatar
DuFF
Forum Contributor
Posts: 495
Joined: Tue Jun 24, 2003 7:49 pm
Location: USA

Post by DuFF »

Yes, just delete the text starting with:
Create MySQL Table...
down to
Create Script...

Or, if you want to keep it you can just add a

Code: Select all

/*
above Create MySQL Table... and a

Code: Select all

*/
after Create Script...
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Just adding;

Code: Select all

CREATE TABLE IF NOT EXISTS useronline (
   timestamp int(15) DEFAULT '0' NOT NULL,
   ip varchar(40) NOT NULL,
   file varchar(100) NOT NULL,
   PRIMARY KEY (timestamp),
   KEY ip (ip),
   KEY file (file)
);
Note the 'IF NOT EXISTS'. Probably selfexplainatory.
Vietboy
Forum Commoner
Posts: 41
Joined: Mon Dec 01, 2003 10:59 pm

Post by Vietboy »

Could not connect to MySQL server (muserver.myserver.com)...
Can't connect to MySQL server on myserver.myserver.com' (79)
Error @ line: 40

??
Vietboy
Forum Commoner
Posts: 41
Joined: Mon Dec 01, 2003 10:59 pm

Post by Vietboy »

okie.. I have created the queries for useronline

When I access my useronline.php


I get these:

Useronline Insert Failed > Useronline Delete Failed > Useronline Select Error > users online


I know my server is running, user and password and host are all right. Why do I get those message?
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

Few points:

You've declared key and file to be alternative keys. But I don't think this should be. What if two different users visit the same page. Or what if one user visits more than one page.

Errors would probably occur.

Also, you're inserting $timestamp into an INT field. Are you sure you can do that? Will the INT field accept characters like ":" and "-" and spaces?

As well, you're assigning memory of INT(15) ... that's enough bit space to hold integers up to 256^15 (or some ungodly number like that.. I think it's actually 2^120). Do you really need that much space?
Vietboy
Forum Commoner
Posts: 41
Joined: Mon Dec 01, 2003 10:59 pm

Post by Vietboy »

Post Reply