Date: 02/07/05 (Data Management)    Keywords: database

Commentary--Here's why the same features that make databases useful to businesses make them even more attractive to law enforcement.

Source: http://news.zdnet.com/The+flip+side+of+database+snooping/2100-9592_22-5566228.html?part=rss&tag=feed&subj=zdnn

Date: 02/04/05 (Data Management)    Keywords: database

Such a move could trigger displeasure at Oracle but curry favor with open-source advocates.
Image: A Sun database?

Source: http://news.zdnet.com/Sun+floats+open-source+database+idea/2100-9592_22-5562799.html?part=rss&tag=feed&subj=zdnn

Date: 01/11/05 (Data Management)    Keywords: database, sql, postgresql

Pervasive looks to crack the database market with a support offering around open-source database PostgreSQL.

Source: http://news.zdnet.com/Open-source+database+gets+backing/2100-9592_22-5519933.html?part=rss&tag=feed&subj=zdnn

Date: 01/01/70 (Programming)    Keywords: database, sql

The other day I figured out how to access one database while in another with SQL code. It is pretty simple. All you need to do is use DATABASENAME..TABLE name in the SQL Code. For example, we're going to assume...

Source: http://www.coreycollins.com/blog/archives/000054.php

Date: 04/19/04 (Java Web)    Keywords: database

I have started working on creating an Object database supported by RDBMS on backend. No it is not an ORM layer. It is a pure object database to the user. Behind the scenes the actual data is stored in the relational database with a simple schema. Primary object access mechanisms will be through...

Source: http://feeds.feedburner.com/AngsumansBlogOnJavaAndWebTechnologies?m=45

Date: 02/14/05 (Security)    Keywords: database, web

The Phish Report Network aims to thwart fraudsters by creating a central database of information about bogus Web sites.

Source: http://news.zdnet.com/Microsoft%2C+eBay+join+antiphishing+initiative/2100-1009_22-5575106.html?part=rss&tag=feed&subj=zdnn

Date: 02/16/05 (Java Web)    Keywords: database, web

It is a very strong capability to be able to post dynamic content in your blog. You can use this to integrate contents from other websites, fetch data from your database etc. It enables whole plethora of functionalities to your blog posting to make it more lively.

Source: http://feeds.feedburner.com/AngsumansBlogOnJavaAndWebTechnologies?m=153

Date: 02/16/05 (PHP Community)    Keywords: database, security, google

Because xinu is too busy working in production environments, I spent about 15 minutes reading through different articles Google found on the subject. Here's a brief result:

extremeexperts article summary: if the images are small and need security, use a DB. If the images are big or requested frequently, use the filesystem. The article also has a link regarding the TerraServer, which stores all its images (8 terabytes) in a database.

Here are some fetch statistics, too.

Community memories would come in handy, if the admin ever felt so inclined. *hinthint*

Source: http://www.livejournal.com/community/php/260494.html

Date: 02/15/05 (PHP Community)    Keywords: database

I looked through the manual but I did not find a function to help me out.

Two questions
1)
How do I print the name of a variable?
For instance I have a variable name $name

$name = "bob";

I want to print out

print "$name = bob
";

and have the output

$name = bob

2)
I have an upload file form that handles multiple files. When I get the file array from $_FILES each array has the name of the upload field from the form. I want to insert that name into the database as part of the record. I am probably missing something obvious but I cant figure out how to get the name as a string.

Source: http://www.livejournal.com/community/php/260262.html

Date: 02/13/05 (PHP Community)    Keywords: browser, database, java

I have a problem. My current login system works as follows:

The user inputs their username and password and hits Login. Before the browser sends this information, a javascript catches the password and replaces it with an md5 hash (combined with the username). On my server, the username is queried in the database, pulling up the password. The md5 is regenerated, and is compared with the hash that the user sent.

Now, the problem is that this scheme leaves the password unencrypted in my database.

The problem is, I simply have no idea about how verify the login without having the actual password somewhere. I don't want it sent over the network, but I don't want to store it plainly in the database either. Any suggestions?

Source: http://www.livejournal.com/community/php/258899.html

Date: 02/09/05 (PHP Community)    Keywords: php, mysql, browser, database, sql

[Error: Irreparable invalid markup ('

') in entry.  Owner must fix manually.  Raw contents below.]


Lately, I've been adding hidden <img> tags to my posts.  The source attribute points to non-existent URLs on my server, each tagged to the post ID - aka: http://ohsonline.no-ip.com/lj74252.  Simple little way to see whenever someone reads your journal, or whenever somebody checks out a friends page you're listed on.  Not the best solution, but hey, it works.

Last night, I got bored, and still a little out of it from being sick, decided to work on a little PHP/MySQL script to actually collect all of that data coming in and provide a little analysis.  So far it's basic, and only really provides the IP, post ID, timestamp, browser, and platform.  I still need to add the source to pump out page hits based on post, ip, browser, platform, etc.  

Anyways, somebody's probably come up with a similar/better solution before, but as always, feel free to hack away :) 

ljLog - Demo



/**

@author Michael Bommarito
@version 20050208-1
@license LGPL
Keep track of who views your LJ, complete with analysis by post, 
browser, operating system, and referring URL.  Or it will.  Some day.
*/

$db = new mysqli('localhost', 'root', 'password', 'ljlog');
if( mysqli_connect_errno() ) {
	die("Unable to contact database server.  Try again later...");
}

if( isset($_REQUEST['post-id']) ) {
	$post_id = $db->real_escape_string($_REQUEST['post-id']);
	$user_ip = ip2long($_SERVER['REMOTE_ADDR']);
	$user_agent = $_SERVER['HTTP_USER_AGENT'];
	$user_referer = $_SERVER['HTTP_REFERER'];
	
	$query = "SELECT * FROM `ljview_post` WHERE `ljview_post_id` = $post_id";
	$res = $db->query($query);
	if( $res->num_rows > 0 ) {
		$user_post_id = $res->fetch_object()->ljview_post_id;
		$query = "UPDATE `ljview_post` SET `ljview_post_hits` = `ljview_post_hits` + 1 WHERE `ljview_post_id` = $post_id";
		$db->query($query);
	} else {
		$query = "INSERT INTO `ljview_post` VALUES($post_id, 1)";
		$db->query($query);
		$user_post_id = $db->insert_id;
	}
	$res->free();
	
	if( $user_ip > 0 ) {
		$query = "SELECT * FROM `ljview_ip` WHERE `ljview_ip_ip` = $user_ip";
		$res = $db->query($query);
		if( $res->num_rows > 0 ) {
			$user_ip_id = $res->fetch_object()->ljview_ip_id;
			$query = "UPDATE `ljview_ip` SET `ljview_ip_hits` = `ljview_ip_hits` + 1 WHERE `ljview_ip_ip` = $user_ip";
			$db->query($query);
		} else {
			$query = "INSERT INTO `ljview_ip` VALUES(NULL, $user_ip, 1)";
			$db->query($query);
			$user_ip_id = $db->insert_id;
		}
		$res->free();
	}
	
	if( $user_referer != '' ) {
		$query = "SELECT * FROM `ljview_referer` WHERE `ljview_referer_string` = '$user_referer'";
		$res = $db->query($query);
		if( $res->num_rows > 0 ) {
			$user_referer_id = $res->fetch_object()->ljview_referer_id;
			$query = "UPDATE `ljview_referer` SET `ljview_referer_hits` = `ljview_referer_hits` + 1 WHERE `ljview_referer_string` = '$user_referer'";
			$db->query($query);
		} else {
			$query = "INSERT INTO `ljview_referer` VALUES(NULL, '$user_referer', 1)";
			$db->query($query);
			$user_referer_id = $db->insert_id;
		}
		$res->free();
	}
	
	$query = "SELECT * FROM `ljview_user_agent` WHERE `ljview_user_agent_string` = '$user_agent'";
	$res = $db->query($query);
	if( $res->num_rows > 0 ) {
		$user_user_agent_id = $res->fetch_object()->ljview_user_agent_id;
		$query = "UPDATE `ljview_user_agent` SET `ljview_user_agent_hits` = `ljview_user_agent_hits` + 1 WHERE `ljview_user_agent_string` = '$user_agent'";
		$db->query($query);
	} else {
		$query = "INSERT INTO `ljview_user_agent` VALUES(NULL, '$user_agent', 1)";
		$db->query($query);
		$user_user_agent_id = $db->insert_id;
	}
	$res->free();
	
	$ts = time();
	$query = "INSERT INTO `ljview` VALUES (NULL, $user_ip_id, $post_id, $user_user_agent_id, $user_referer_id, $ts)";
	$db->query($query);
} else {
?>
     PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
     "DTD/xhtml1-transitional.dtd">

	
		
		
	
	
		







	$res = $db->query("SELECT * FROM ljview ORDER BY ljview_id DESC");
	while( $view = $res->fetch_object() ) {
		print ("");

		$ip_id = $view->ljview_ip_id;
		$res_ip = $db->query("SELECT * FROM ljview_ip WHERE ljview_ip_id = $ip_id");
		if( $res_ip ) {
			$ip = $res_ip->fetch_object();
			$res_ip->free();
			print ( "" );
		} else {
			print("");
		}

		$post_id = $view->ljview_post_id;
		print("");

		if( $view->ljview_ts ) {
			print ( "" );
		} else {
			print("");
		}

		$user_agent_id = $view->ljview_user_agent_id;
		$res_user_agent = $db->query("SELECT * FROM ljview_user_agent WHERE ljview_user_agent_id = $user_agent_id");
		if( $res_user_agent ) {
			$user_agent = $res_user_agent->fetch_object();
			$res_user_agent->free();
			$browser = get_browser($user_agent->ljview_user_agent_string);
			print ( "" );
			print ( "" );
		} else {
			print("");
		}

		print ("");
	}
?>
			
				
IP
				
Post ID
				
Timestamp
				
Browser
				
Platform
			
		
" . long2ip($ip->ljview_ip_ip) . "
 
		
$post_id
		
" . date("m/d/Y, g:i:s a", $view->ljview_ts) . "
 
		
" . $browser->parent . "
" . $browser->platform . "
 
		
		

		

			
			
		

	


}
$db->close();
?>




  `ljview_post_id` int(10) unsigned NOT NULL default '0',
  `ljview_user_agent_id` int(10) unsigned NOT NULL default '0',
  `ljview_referer` int(10) unsigned NOT NULL default '0',
  `ljview_ts` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_ip`
-- 

DROP TABLE IF EXISTS `ljview_ip`;
CREATE TABLE IF NOT EXISTS `ljview_ip` (
  `ljview_ip_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_ip_ip` int(32) NOT NULL default '0',
  `ljview_ip_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_ip_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_post`
-- 

DROP TABLE IF EXISTS `ljview_post`;
CREATE TABLE IF NOT EXISTS `ljview_post` (
  `ljview_post_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_post_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_referer`
-- 

DROP TABLE IF EXISTS `ljview_referer`;
CREATE TABLE IF NOT EXISTS `ljview_referer` (
  `ljview_referer_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_referer_string` text collate utf8_bin,
  `ljview_referer_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_referer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_user_agent`
-- 

DROP TABLE IF EXISTS `ljview_user_agent`;
CREATE TABLE IF NOT EXISTS `ljview_user_agent` (
  `ljview_user_agent_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_user_agent_string` text collate utf8_bin,
  `ljview_user_agent_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_user_agent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


Source: http://www.livejournal.com/community/php/257133.html
	

Date: 02/08/05 (PHP Community)    Keywords: database, web

OK, I'm trying to write a script that tells you when your next recycling collection is (don't laugh). In my city, your recycling is collected on a certain day (the actual day depends where exactly you live) every two weeks.

What I'd like to do is have a web page with a list of street names and the date of your next collection, which is generated based on the current date (using time() ). I'm running into a bit of trouble generating the next collection day because it's once a fortnight and I can't just (for example) increment the current time() until I get to the right day because it could be more than a week away.

At the moment I've got all the street names in a database with a timestamp of a known collection. Then I just increment the "known collection" timestamp by two weeks until the value exceeds time().

What I'd like to know is - is there an easier way to do this? I figure once the script has been up and running for a while it's a bit silly to start at a date that was years ago and increment it until it passes the current date.

UPDATE: Sorted it (maybe). I don't have server access to set up a cron job, but I can kind of achieve the same effect by querying and updating the database in the script before outputting the values. Cheers! :) You can see the script in action here (woooo).

Source: http://www.livejournal.com/community/php/256785.html

Date: 02/07/05 (PHP Community)    Keywords: php, html, database, web

I'm doing something of a small content management system for a project I'm working on, and had a quick question about stripping html tags from database held text.

I plan on having a "news" section, updated via a web form or phpMyAdmin or something, but I would like the full news entries safe for HTML tags (bolding, italics, email links, etc). Additionally, I would like to display small 100-character or so blurbs of the "news" entries on other pages - my concern is that if a tag is opened before the 100-character cut-off and not closed, this will mess with the following code.

I've dabbled in solving this problem with little effort, and equally little success. Is there an easy way of stripping out HTML tags for a preview such as this? Am I better off not allowing HTML and providing my own tags, such as ::BOLD::here's some bold text::BOLD:: ? If this is the case, how do I prevent HTML tags at all?

Source: http://www.livejournal.com/community/php/256244.html

Date: 02/18/05 (Security)    Keywords: database

Database break-in affects far more consumers than originally acknowledged. Factor in 750 cases of identity theft.

Source: http://news.zdnet.com/ChoicePoint+data+theft+widens+to+145%2C000+people/2100-1009_22-5582144.html?part=rss&tag=feed&subj=zdnn

Date: 02/19/05 (PHP Community)    Keywords: php, mysql, database, sql

I'm attempting to see if I can connect to a database using IIS on a XP Pro machine.



when I go to the document that has that code I get the following error messsage

Warning: mysql_connect(): Client does not support authentication protocol

requested by server; consider upgrading MySQL client in c:\inetpub\wwwroot\PHP\dbconnect.php on line 10
could not connect to database

Source: http://www.livejournal.com/community/php/262496.html

Date: 02/19/05 (PHP Community)    Keywords: database

Please help me with this, I'm trying to create a handler for a 404 error, and i feel like I'm close, but my if statement always returns a false. Code below cut


$user = "MHeath81";
$filename = "http://ws.audioscrobbler.com/rdf/history/$user";
if (is_file($filename)) {
$fp = fopen($filename, "r");
}
else {
Header("Content-type: image/jpeg");
$im = imageCreateFromJPEG("scrobbler.jpg");
$m1 = "Last track played:";
$nomessage = "Tracks database currently unavailable";
$red = ImageColorAllocate($im, 249, 79, 79);
$white = ImageColorAllocate($im, 0, 0, 0);
ImageString($im,2,5,1,$m1,$red);
ImageString($im,3,11,95,$nomessage,$white);
ImageString($im,3,10,94,$nomessage,$red);
ImageJPEG($im, '', 90);
ImageDestroy($im);
}


Any help greatly appreciated, I feel like I'm missing something really stupid, it could even be the way I'm accessing the file using is_file or file_exists.

Source: http://www.livejournal.com/community/php/262722.html

Date: 02/20/05 (PHP Community)    Keywords: php, database, java, security

In building my nice little blog system, I'm running into the following two problems:

Problem 1:
Currently I cannot run image magick as my wonderous site admin has not installed it.  This is - apparently - preventing me from using Text_CAPTCHA from PEAR.  I need a captcha solution that does not require said image manipulation libraries.  Alternatively, if someone knows of a way I can install image magick into my userspace, and NOT have to convince my admin to recompile php, please let me know.

Problem 2:

Additionally, my authentication for administration interface is currently being done via plaintext passwords.  This is - for obvious reasons - insecure.  I was attempting to find a solution using a client-side javascript that would create an md5 hash of the password and a randomly generated "key", send same to php processing form via post, and then the php form processor would use the same randomly generated "key" (I'm currently using "mt_rand()") and the user's password (retreived from the database), combine/hash and compare.  I ran into problems with transmitting the "key" to client side, and maintaining the SAME key (without sending it back, in the clear) on the server side.
The major problems I'm running into, though is the transmission of the key, no matter how I find a way to do it, it still shows up clearly in going to the client side ($rnd = mt_rand() will still show the number client-side when echo $rnd, and therefore it is being clearly transmitted).  Hmm, perhaps I could hash it server side, and then reverse the hash?  But md5 hashes are one-way, yes?  I'd need a crypt for that, and despite my best tries haven't located a good one. ARRGH.
Security is a bitch when you're not using pre-written. ...ESPECIALLY when you're a beginner.

Source: http://www.livejournal.com/community/php/264266.html

Date: 02/10/05 (MySQL Communtiy)    Keywords: php, mysql, browser, database, sql

[Error: Irreparable invalid markup ('

') in entry.  Owner must fix manually.  Raw contents below.]


I've written  little PHP/MySQL script to parse HTTP requests from hidden <img> tags in LJ posts (e.g. <img src="http://ohsonline.no-ip.com/ljview.php?post-id=74765" style="display:none;" />), archiving it all in a MySQL database and providing a little analysis.  So far it's basic, and only really provides the IP, post ID, referring user, timestamp, browser, and platform.  I still need to add the source to pump out page hits based on post, ip, browser, platform, etc.  

Anyways, somebody's probably come up with a similar/better solution before, but as always, feel free to hack away :) 

ljLog - Demo



/**

@author Michael Bommarito
@version 20050209-2
@license GPL
Keep track of who views your LJ, complete with analysis by post, 
browser, operating system, and referring URL.
*/


$db = new mysqli('localhost', 'root', 'password', 'ljlog');
if( mysqli_connect_errno() ) {
	die("Unable to contact database server.  Try again later...");
}

if( isset($_REQUEST['post-id']) ) {
	$post_id = $db->real_escape_string($_REQUEST['post-id']);
	$user_ip = ip2long($_SERVER['REMOTE_ADDR']);
	$user_agent = $_SERVER['HTTP_USER_AGENT'];
	$user_referer = $_SERVER['HTTP_REFERER'];
	
	$query = "SELECT SQL_CACHE * FROM `ljview_post` WHERE `ljview_post_id` = $post_id";
	$res = $db->query($query);
	if( $res->num_rows > 0 ) {
		$user_post_id = $res->fetch_object()->ljview_post_id;
		$query = "UPDATE `ljview_post` SET `ljview_post_hits` = `ljview_post_hits` + 1 WHERE `ljview_post_id` = $post_id";
		$db->query($query);
	} else {
		$query = "INSERT INTO `ljview_post` VALUES($post_id, 1)";
		$db->query($query);
		$user_post_id = $db->insert_id;
	}
	$res->free();
	
	if( $user_ip > 0 ) {
		$query = "SELECT SQL_CACHE * FROM `ljview_ip` WHERE `ljview_ip_ip` = $user_ip";
		$res = $db->query($query);
		if( $res->num_rows > 0 ) {
			$user_ip_id = $res->fetch_object()->ljview_ip_id;
			$query = "UPDATE `ljview_ip` SET `ljview_ip_hits` = `ljview_ip_hits` + 1 WHERE `ljview_ip_ip` = $user_ip";
			$db->query($query);
		} else {
			$query = "INSERT INTO `ljview_ip` VALUES(NULL, $user_ip, 1)";
			$db->query($query);
			$user_ip_id = $db->insert_id;
		}
		$res->free();
	}
	
	if( $user_referer != '' ) {
		$query = "SELECT SQL_CACHE * FROM `ljview_referer` WHERE `ljview_referer_string` = '$user_referer'";
		$res = $db->query($query);
		if( $res->num_rows > 0 ) {
			$user_referer_id = $res->fetch_object()->ljview_referer_id;
			$query = "UPDATE `ljview_referer` SET `ljview_referer_hits` = `ljview_referer_hits` + 1 WHERE `ljview_referer_string` = '$user_referer'";
			$db->query($query);
		} else {
			$query = "INSERT INTO `ljview_referer` VALUES(NULL, '$user_referer', 1)";
			$db->query($query);
			$user_referer_id = $db->insert_id;
		}
		$res->free();
	}
	
	$query = "SELECT SQL_CACHE * FROM `ljview_user_agent` WHERE `ljview_user_agent_string` = '$user_agent'";
	$res = $db->query($query);
	if( $res->num_rows > 0 ) {
		$user_user_agent_id = $res->fetch_object()->ljview_user_agent_id;
		$query = "UPDATE `ljview_user_agent` SET `ljview_user_agent_hits` = `ljview_user_agent_hits` + 1 WHERE `ljview_user_agent_string` = '$user_agent'";
		$db->query($query);
	} else {
		$query = "INSERT INTO `ljview_user_agent` VALUES(NULL, '$user_agent', 1)";
		$db->query($query);
		$user_user_agent_id = $db->insert_id;
	}
	$res->free();
	
	$ts = time();
	$query = "INSERT INTO `ljview` VALUES (NULL, $user_ip_id, $post_id, $user_user_agent_id, $user_referer_id, $ts)";
	$db->query($query);
} else {
?>
     PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
     "DTD/xhtml1-transitional.dtd">

	
		
		
	
	
	$count = array_pop($db->query("SELECT ljview_id FROM ljview ORDER BY ljview_id DESC LIMIT 1")->fetch_row());	
	$limit = isset($_REQUEST['limit']) ? abs($_REQUEST['limit']) : 20;
	$offset = isset($_REQUEST['offset']) ? abs($_REQUEST['offset']) : 0;
	$place = $count - $offset; 
	$query = "SELECT SQL_CACHE * FROM ljview WHERE ljview_id <= $place ORDER BY ljview_ts DESC LIMIT $limit";
	$res = $db->query($query);
	$end = $offset + $limit;
	$back = $offset - $limit;
	print("
Viewing $offset - $end out of $count
");
	print("
");
	if( $offset > 0 ) {
		print("Previous $limit");
	}
	if( $offset < $count ) {
		print("Next $limit
");
	}
?>
		








	while( $view = $res->fetch_object() ) {
		print ("");

		$ip_id = $view->ljview_ip_id;
		$res_ip = $db->query("SELECT SQL_CACHE * FROM ljview_ip WHERE ljview_ip_id = $ip_id");
		if( $res_ip ) {
			$ip = $res_ip->fetch_object();
			$res_ip->free();
			print ( "" );
		} else {
			print("");
		}

		$post_id = $view->ljview_post_id;
		print("");

		if( $view->ljview_ts ) {
			print ( "" );
		} else {
			print("");
		}

		$user_agent_id = $view->ljview_user_agent_id;
		$res_user_agent = $db->query("SELECT SQL_CACHE * FROM ljview_user_agent WHERE ljview_user_agent_id = $user_agent_id");
		if( $res_user_agent ) {
			$user_agent = $res_user_agent->fetch_object();
			$res_user_agent->free();
			$browser = get_browser($user_agent->ljview_user_agent_string);
			print ( "" );
			print ( "" );
		} else {
			print("");
		}

		$referer_id = $view->ljview_referer;
		$res_referer = $db->query("SELECT SQL_CACHE * FROM ljview_referer WHERE ljview_referer_id = $referer_id");
		if( $res_referer ) {
			$referer = $res_referer->fetch_object();
			$res_referer->free();
			$referer_stack = split('[-./]', $referer->ljview_referer_string);
			if( strcasecmp($referer_stack[2], 'livejournal') == 0 ) {
				$lj_user = $referer_stack[5];
			} else if( strcasecmp($referer_stack[3], 'livejournal') == 0 ) {
				$lj_user = $referer_stack[6];
			}

			print("");
		} else {
			print("");
		}

		print ("");
	}
?>
			
				
IP
				
Post ID
				
Timestamp
				
Browser
				
Platform
				
LJ User
			
		
" . long2ip($ip->ljview_ip_ip) . "
&nbsp;
		
$post_id
		
" . date("m/d/Y, g:i:s a", $view->ljview_ts) . "
&nbsp;
		
" . $browser->parent . "
" . $browser->platform . "
&nbsp;
						
$lj_user
&nbsp;
		
		


		

			
			
		

	


}
$db->close();
?>




  `ljview_post_id` int(10) unsigned NOT NULL default '0',
  `ljview_user_agent_id` int(10) unsigned NOT NULL default '0',
  `ljview_referer` int(10) unsigned NOT NULL default '0',
  `ljview_ts` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_ip`
-- 

DROP TABLE IF EXISTS `ljview_ip`;
CREATE TABLE IF NOT EXISTS `ljview_ip` (
  `ljview_ip_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_ip_ip` int(32) NOT NULL default '0',
  `ljview_ip_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_ip_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_post`
-- 

DROP TABLE IF EXISTS `ljview_post`;
CREATE TABLE IF NOT EXISTS `ljview_post` (
  `ljview_post_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_post_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_referer`
-- 

DROP TABLE IF EXISTS `ljview_referer`;
CREATE TABLE IF NOT EXISTS `ljview_referer` (
  `ljview_referer_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_referer_string` text collate utf8_bin,
  `ljview_referer_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_referer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_user_agent`
-- 

DROP TABLE IF EXISTS `ljview_user_agent`;
CREATE TABLE IF NOT EXISTS `ljview_user_agent` (
  `ljview_user_agent_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_user_agent_string` text collate utf8_bin,
  `ljview_user_agent_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_user_agent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


Source: http://www.livejournal.com/community/mysql/47917.html
	

Date: 02/07/05 (MySQL Communtiy)    Keywords: mysql, database, sql, web

For a website, I have to write a suite of scripts interacting with a database of bibliographic references. Some pages will have an embedded request for a set of the latest titles by an author, and some pages will have an embedded list of titles from which to generate full references. Additionally, I have to build a simple search engine that will query by author, title, publication or abstract.

The search engine is a later problem, and I've already got searching for an embedded list of titles working. So right now I have to solve the author problem. I'm a MySQL newbie and am having problems with the following dilemma:

The problem with citations is that there's no fixed number of authors for a book. As a consequence, the database has a table for the papers, a table for the authors, and a link table with columns for a paper's foreign key and author's foreign key. A paper with three authors populates three rows of the link table.

My challenge is that to pull a complete citation when I search by author, I currently have to make two database queries:
SELECT name, id_people, papers_fk FROM papers pa, link li, people pe WHERE name LIKE 'Smith' AND pa.id_paper=li.papers_fk AND pe.id_people=li.people_fk
pulls the IDs of papers that Smith has contributed to.

I take that list of IDs and loop through
SELECT name, id_people, title, id_paper FROM papers pa, link li, people pe WHERE li.papers_fk='$titleid' AND pa.id_paper='$titleid' AND pe.id_people=li.people_fk
entering a different paper ID into $titleid on each loop to generate arrays of citations. A script will distill the arrays into comma-separated lists of authors and format the results.

My question is: Is there a way to distill this into a single query? Which is to say: Can I use a single query to get the complete list of authors for each title that Smith has contributed to?

The approach I have now will work, even if it ends up sending a lot of queries for each web page hit, but being able to streamline this query will both make this page more efficient and give me a leg up on writing the full text (citation + abstract) search section.

Source: http://www.livejournal.com/community/mysql/47657.html

Date: 01/20/05 (MySQL Communtiy)    Keywords: mysql, database, sql

hey, i'm having trouble finding anything about this in the manual. how do you export a database to a file, so that you can import it into mysql on another server?

thanks

Source: http://www.livejournal.com/community/mysql/45945.html