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: 01/08/05 (Java Web)    Keywords: mysql, sql

/etc/rc.d/init.d/mysqld start /etc/rc.d/init.d/mysqld stop /etc/rc.d/init.d/mysqld restart And of course there is the brute force way to kill all the processes: $ for i in `ps -ef |grep mysqld |awk '{print $2}'`; do `kill -9 $i`; done cut doesn't cut it in this case.

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

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

Hey! My first post. Just a brief introduction on me: I've been doing PHP for about a year or so and in about 70% of the projects i've done i used PHP.

Anyway, here's my question:Any suggestions on how i can implement a "network" / "connections" algorithm using PHP/mySQL? something like Friendster?

"To steal ideas from one person is plagiarism, to steal ideas from many is research." - Anon.

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

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

alright first post, mysql ver 4.1 installed, running off of a winxppro operating system.

I have phpmyadmin-2.6.0-pl1 on my home computer, and whenever I try to logon as root using the password I specified it says "Error
#1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client"

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

Date: 02/14/05 (PHP Community)    Keywords: php, mysql, css, html, sql, web

I've been working with PHP for several years now in a web-based envoronment and have achieved a moderate level of proficiency - I'm still a little green when it comes to PHP and MySQL though.

Additionally, I'm fluent in XHTML and CSS (though a little out of date with my CSS hacks).

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

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

I'm working on creating some tools (PHP/MySQL) on one of my websites to help a user tell where things are. Generically, I will probably put this to use in several ways at some point - I just need a bit of help to start with. The budget is $0, so there will be no buying of commercial map data.

Scenario:
1) User A inputs location data - say, for SSO, it's a store that sells ShadowRun books. This information includes address, city, state, zip, country, phone, etc.
2) User B is going on a trip to Town Z, and wants to know which of these locations are nearby, so they input address Y and a radius of M miles.
3) The display shows a map of town Z with a radius of M miles centered around addess Y, and a numer N of locations X are indicated with icons on the map. Along with this, there is a table with a list of N locations and a distance between Y and X for each.
*) I would also like to be able to create a state-wide or regional default map to show as a default.

Work In Progress:
What I have been able to do so far, thanks to some links I found, is to use the old US Census Zip data that is floating around to give an approximate distance based on zip code. This is pretty inaccurate at best, but it does at least give some idea.

What I think the next step is would be to find a way to get a more accurate lat/long for each address, and use the zip only as a fallback if that method fails. This would increase the accuracy by a significant amount. Any ideas on where/how to implement such a thing would be welcome.

The big step would be actually mapping into an image. I have looked around quite a bit for information on creating images and maps, but have yet to find anything that I can relate well enough to to work with. I'm looking for an example not too far from this scenario to work from. I magine a basic way to accomplish this task would be to find a state outline map and the lat/long that equate with each corner, then use those as a base to map the icon points... somehow. That would at least be a place to start from, and could probably be done with freely available data. To actually generate M radius maps from address Y would be a bit trickier, I imagine. Any thoughts as to the methods, code, sites, data, or experience in creating such a thing are welcome.

Thanks.

Source: http://www.livejournal.com/community/php/257428.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/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/11/05 (MySQL Communtiy)    Keywords: programming, sql

I basically taught myself programming so majority of times my code isn't as efficient as it could be.

my question is w/ sql statements... if i only need say 1 field from a table should i use

"select X FROM table"

or is

"select * from table" as efficient

i know common sense is pointing me to the first example but it never hurts to ask. also what happens if i need like 4 or 5 fields out of 10... would that be the same situation as if it were just 1 like the example above?

Source: http://www.livejournal.com/community/mysql/48500.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/21/05 (MySQL Communtiy)    Keywords: mysql, sql

Hi all,

I can't seem to change my max_connections variable in mysql.
If I try to do it through the client I get this.
mysql> set global max_connections=500;
ERROR 1064: You have an error in your SQL syntax near 'max_connections=500' at line 1

Then put
set-variable = max_connections=300
in the my.cnf and restarted mysql.

I did
show variables;
and I get
| max_connections | 100

Why aren't these attempts working?

Source: http://www.livejournal.com/community/mysql/46397.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

Date: 01/09/05 (MySQL Communtiy)    Keywords: php, mysql, sql

hey everyone, my name is Phil. i started learning PHP in september and about a month ago started learning mysql and how the two work together.

i have a question regarding the enum data type. any time i try to create a table which has a field which i'd like to specify as enum, i get a syntax error. i've checked the manual and am not sure what i'm doing wrong. here's an example:

create table records(record_id int auto_increment, user varchar(50) not null, status enum("open","closed"), primary key(record_id));

what i've ended up having to do is create the first half of the table, then put in each of the enum fields one at a time, then do the rest. could someone tell me what i'm doing wrong?

thanks

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

Date: 12/29/04 (MySQL Communtiy)    Keywords: php, mysql, html, database, sql

Ok, I figured something out.

When I run the mysql query at a mysql or phpmyadmin prompt I get the proper results...

SELECT * FROM fff_news WHERE month(date)=month(now()) and year(date)=year(now()) ORDER BY date DESC

But when I throw this into an array with php's mysql_fetch_array or mysql_fetch_row everything will display except for the top entry which would be the last date entered into the database. If I add a new entry for yesterday, it won't show until I add a second entry for yesterday(and then that one won't show) or a new entry for today(which won't show).

Why is php mangling my query like this, it's rather simple...

snip...(and I cut out my html which semagic won't let me display as code anyway...)

} else {

$sql = 'SELECT * FROM fff_news WHERE month(date)=month(now()) and year(date)=year(now()) ORDER BY date DESC';
$result = mysql_query($sql);
$row = mysql_fetch_array($result);

if (!$result) {
echo("

Error performing query: " . mysql_error() . "\n");
exit();
}

while ($row = mysql_fetch_array($result)) {
echo $row[date];
echo $row[pagetitle];
echo $row[content];

}

...snip...

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

Date: 12/23/04 (MySQL Communtiy)    Keywords: mysql, sql, web

I upgraded my server today to 4.1.8, both MySQL and MySQL Max. I had to force it as there was a conflict with the client tools. My question is, where are all the client tools? I looked for upgrades on the MyS!QL site and couldn't find them.

Also, any reason why WebMin would still report the old version number?

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

Date: 12/02/04 (MySQL Communtiy)    Keywords: php, mysql, database, sql

Hi guys, i'm a newbie to MYSQL, so excuse me for stupid questions :)
I have the following question:
For instance my database looks like -

+====+============+
| ID | SOME_VALUE |
+====+============+
| 10 |  MY_VALUE  |
+----+------------+
|    |            |
+----+------------+

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

Date: 11/30/04 (MySQL Communtiy)    Keywords: mysql, database, sql

I just received a box with the following specs:

Dual AMD64
8G ram
Two 3ware 2.4 terabyte RAID 5 arrays.

My company has been using Redhat for most of its production machines.

1. Does anyone have any success/horror stories running MySQL 4.0.x on RHES 3/ AMD64?

2. Does anyone have alternate recommendations for running MySQL databases in the terabyte range on AMD64?

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

Date: 02/18/05 (Web Development)    Keywords: php, mysql, rss, xml, sql, java

I’m thinking about putting together a Wiki for some stuff I am doing. Does anyone have any packages that they like and would recommend? Things I would like:

1) PHP (V5 would be a good thing)
2) Data stored in MySQL
3) User names to edit as an option.
4) New/changed Pages RSS feed.
5) Under active development
6) There is no #6
7) Page versioning.
8) Good support for inserting code of various forms (PHP, javascript, xml etc) in pages.

Thanks

--Zach

Source: http://www.livejournal.com/community/webdev/173178.html