Date: 10/05/05 (SQL Server)    Keywords: sql

Intellisense® style word completion for MS Query Analyzer, VS.NET 2003, SQL Server Management Studio and VS 2005.

http://www.promptsql.com/

--

This looks like a real slick tool, which would easily pay for itself after a day's use!

(x-posted to various places)

Source: http://community.livejournal.com/sqlserver/36137.html

Date: 09/19/05 (SQL Server)    Keywords: database, sql

Alright, here's the scenario. I have 65 million row table of zip codes... the table just has three columns: zip_cd varchar(10), lattitude, and longitude values. Zip_cd is varchar(10) because we have to store zip + plus 4 values in "xxxxx-xxxx" format... and no, my senior DBA has already overruled me adding two more numeric columns which would offer more optimized indexing. There's a clustered index on the zip_cd and we only ever run lookups on zip_cd, to get lat/long values.

Anyway, we have one simple query as follows:

--

DECLARE @zip_cd varchar(10)
SET @zip_cd = '60440-0001'

IF EXISTS(SELECT zip_cd
FROM serverX.databaseY.dbo.geodata
WHERE zip_cd = @zip_cd)
PRINT 'one'
ELSE
PRINT 'two'

--

Starting late last week, one of our servers running this query started taking forever and a day. Upon running it manually and checking the execution plan, I see that SQL Server, in all of its wisdom, decided to run the "SELECT zip_cd" portion WITHOUT the WHERE clause on the remote server, THEN apply a "filter" step for the WHERE clause. I attempted to replicate this on other servers and 2/3s of our other servers runs the query correctly, by sending everything to the remote server, whereas 2 other servers also just pull all 65 million rows down THEN filters it locally.

So my question is this... how in the hell can I force SQL Server to run the query in one fashion, rather than another? I know about using table and query hints, but the problem is that this is a remote server scenario, which Query Analyzer was complaining that index hints won't work.

Help?

x-posted...

Source: http://community.livejournal.com/sqlserver/34761.html

Date: 09/19/05 (SQL Server)    Keywords: sql

What do smart people do on a Saturday night? Play Smath of course and argue over the validity of using parentheses in mathematical equations like this:

12 = (7) + 5

I still think it's completely valid, see SQL Server confirms it:

SELECT (7) + 5
--yields
--12

So after demonstrating that it was valid in SQL I was basically told that SQL Server is like retarded or something and like it doesn’t adhere to real mathematical principles?

Any opinions out there? Is SQL Server math stupid, is there a real "math property" that supports this? Any opinionated mathematicians out there that can give some insight?

Source: http://community.livejournal.com/sqlserver/34391.html

Date: 10/15/04 (Elite PHP Development)    Keywords: php, mysql, database, sql, java

I was lookin around and found this tonight. Very much kewlness, I am hopeing to join in on the fun in the development community so I can add in my ideas to make PHP even better.
This is a little bit of information I picked up on Creating Custom Exceptions and sending data to the parent class from a function in a subclass. Very spiffy.

query("SELECT NOW()");
var_dump($result->fetch_row());
}
catch(ConnectException $exception) {
echo "Connection Error\n";
var_dump($exception->getMessage());
}
catch(QueryException $exception) {
echo "Query Error\n";
var_dump($exception->getMessage());
}
/* Handle exceptions that we weren't expecting */
catch(Exception $exception) {
echo "Who was that masked exception?\n";
var_dump($exception->getMessage());
}

$result->close();
$my->close();

?>

very spiffy, and I will be posting something using the new MySQLi class. Maybe a whole class system for the generation of graphics pulled from a database. Possibly attach that to a javascript paint proggy, or even better a flash paint proggy..... hmmm the posibilities are endless, but i have to get back to my money making... Gotta love it, but it would be so much more fun if i had a project that really challenged my knowledge and abilities, but well get to that someday.
-=Levi=-

Source: http://community.livejournal.com/php_elite/504.html

Date: 12/14/05 (Javascript Community)    Keywords: php, mysql, database, sql, java

Hi, I have some questions regarding the best way to go about creating dynamic dropdowns in javascript.

I have to create a form with two dropdown lists. One for US states the other for counties within those states. The county dropdown will remain blank until the user selects a state, then the county dropdown will be populated with the correct counties for that state.

I have this working using PHP (the rest of the application is PHP) but I want to do it without reloading the page. The list of states and counties are stored in 2 tables in a mysql database.

My first instinct is use PHP to write the javascript for me. Do the DB work with PHP and then write the javascript arrays. But that seems wrong.

I would think that the javascript should go something like this and would be called from an onchange handler in the state dropdown. I dont know the exact syntax.

create function setOptions(id)

create array of counties indexed with their id's from the database

if there is an id passed to the function
    iterate through the array of counties
        if a key matches the id then write that to the options tag of the county select list

Source: http://community.livejournal.com/javascript/88041.html

Date: 06/22/05 (Software)    Keywords: php, mysql, sql, security, web, linux, yahoo

I have a group of users (100 or so) that need read/write access to a document (excel) of sensitive information. Since they are all over the place, they requested a web application to be able to access from anywhere whenever they need to modify or look at this document. I'm trying to figure out what is the best way to provide a secure way to only allow these specific users to access this document.
ideas have included:

Plone - complex, looking for simplicity
phpBB - requires me to set up a mysql linux box somewhere which might be possible
Yahoo groups - not very secure but basically what i want

any other ideas? Security of this information is the biggest priority. i currently have both linux/windows available to host.

Source: http://community.livejournal.com/software/54525.html

Date: 01/19/06 (PHP Community)    Keywords: php, mysql, sql, security

Hello all,

I am working on a project to create highly customizable live cds mainly for my peronal use and for friends to try diffrent OSs. At the moment I have several shell scripts that I am looking at converting over to PHP if possiable. The bulk of it i know can be ported easily enough but I have never attempted to call other programs besides MySQL in PHP so I am not quite sure if it is feasable. The server will be located all inhouse with no outside access untill i can be sure of the security implcations involved in the procedure and lower the risks. Can I access other programs through php, spefically can I run "/usr/local/bin/mkisofs -b boot/cdboot -no-emul-boot -c boot/boot.catalog -r -l -L -V LiveCD -o $LIVEISODIR/LiveCD.iso ." and get the intended result? Any thoughts would be appreciated.

Source: http://community.livejournal.com/php/398676.html

Date: 01/19/06 (PHP Community)    Keywords: cms, mysql, database, sql

I am writing an open source cms called Rade (intended for release), and want to store images in the MySQL database; MediaWiki's upload system sounds suspiciously insecure, and makes storing metadata harder. However, I have read that blobs in SQL databases decrease their performance. Should I store the images in another table? Another database?

Source: http://community.livejournal.com/php/398428.html

Date: 01/17/06 (PHP Community)    Keywords: php, mysql, browser, database, sql

Ok, so I have the following code, meant to delete a category in a script I'm working on. It takes the category ID from the $_GET array, checks the database to make sure such a category ID exists, then deletes it if true or shows an error if false.

if ($_GET['categories'] == "deletecat") {
if (isset($_GET['delid'])) {
$idofd = cleaninput($_GET['delid'], 1);
$idofd = (int)$idofd;
$check_delete = mysql_fetch_assoc(mysql_query("SELECT * FROM table_cats WHERE cat_id = '" . $idofd . "' LIMIT 1", $connect));
if (empty($check_delete)) {
echo "

Error: There is no category with the ID number " . $idofd . ".

Source: http://community.livejournal.com/php/396330.html

Date: 01/13/06 (PHP Community)    Keywords: php, mysql, html, database, sql, web

Hey Guys

I'm new to this community. I am starting a web design company, and although I used to be decent at PHP, that was a couple of years ago and my knowledge is now pretty much non-existent. I need a script coded for a band website and I was wondering if some of you would give me a price estimate on it.

The script is for bands that want to add shows to their website. The script needs to do the following

- Allow a user to add / edit / delete shows to their website. The shows will be in 2 categories (which will be 2 HTML tables) which are Upcoming shows, and past shows.
- The user puts in information in a form and it's stored in a MySQL database. The most important information is displayed in an HTML table, and in the last cell of the table will have a link for more information. When a user clicks on this link it opens up a popup window and displays ALL the information of the show (eg. Date, time, venue, cost, directions, other notes yada yada yada) in another HTML table (within the pop-up window)

I realise that this is a pretty simple script (in comparison to what you guys have probably coded in the past, so I hope someone out there can code it for me at a reasonable price

Thanks in advance

Source: http://community.livejournal.com/php/394319.html

Date: 01/10/06 (PHP Community)    Keywords: sql

Is there some possibility to sort this:
$sql = "SELECT band,cover1,cover1year,cover1title,cover2,cover2year,cover2title,cover3,cover3year,cover3title,cover4,cover4year,cover4title,cover5,cover5year,cover5title FROM referenzen ORDER BY band ASC";

by the count of covers (so the ones with 4+ are on top and the ones with only one are at the bottom)? (see this). Not all of the variables are always in use, I don't have any idea how to do this.

Source: http://community.livejournal.com/php/393297.html

Date: 01/09/06 (PHP Community)    Keywords: php, templates, mysql, css, html, sql, java, spam

I've been hunting all around for a decent photo gallery. There seem to be quite a few different projects, some of which are still active, available. None of them seem to accomplish what I'm looking for, though. I have tested quite a few, but all are lacking. To this end, I thought I'd ask here if anyone has anything similar to what I've been looking for.

Major Features:
Abstracts image URLs - hides the path and filename to prevent direct access
Public/private flags - allows some images to stay hidden
Tags - I'd like to be able to tag the images ala del.icio.us, LJ, Flickr
Does not require gd - auto-thumbnails and watermarks can be disabled
Traverses directories - new images can be dropped in a subfolder or the root image folder and will be picked up automatically
Comments - allows for public (configurable) or private comments, either ties into phpbb or uses some sort of email validation/captcha scheme to reduce spam
EXIF - extracts all image data form the photographs and can display it
Uses simple cacheable CSS/XHTML templates
Does not store images in DB
Uses MySQL or abstraction layer that allows MySQL for comments, tags, etc
Takes reasonable measures to be secure and speedy

Minor Features:
Users can upload images
Temporary/expiring image URLs for sharing
Hotlinking configuration - allow all, deny all, allow specific sites
User-switchable templates
Javascript navigation and image pop-ups optional
Keeps stats on a pre image basis - top keywords, views, etc
All images can be watermarked automatically
Thumbnails may be re-generated automatically
Can assign/rename/describe multiple photos at once

I'm sure there are more details, but that should give a start. The scripts I've found so far are either too simple and don't have many features or too complex and require components and methods of use that I won't be using. There are a few others that store the images in the DB, which is something I'm definately against. I want to be able to FTP a directory of images and know that it will be online and ready to go as soon as the transfer is complete, or that it takes a simple click to do a rescan so they are available.

If I have to, I'll start from scratch... I was just hoping that there would be something available that I have not yet found that could serve as a foundation to build from.

Thanks

Source: http://community.livejournal.com/php/392436.html

Date: 10/15/05 (Web Hosts)    Keywords: sql, web

Free webspace to anyone that knows how to design and wants to build a personal / photographic website.

Just go to the site jenepher.com/x and fill out the form. I will send you your log in information after I set up your FTP account. If you have any questions just email them to me at bratusek@jenepher.com

-20 Mb of space
-unlimited bandwidth
-no pop ups
-no cgi
-no sql

Source: http://community.livejournal.com/webhosts/31628.html

Date: 04/23/05 (Web Hosts)    Keywords: php, mysql, sql, web, hosting

I manage two domains, each under 10MB. For the past two years, they've been hosted with dr2.net, but then service got spotty and then they merged with Mesopia and the reviews got worse.

So, I'm looking to switch both domains to a new shared host. After checking out webhostingjury.com, I like the features of the standard plan of u-hs.com but the price seems a tad too steep. Looking for something in the area of $10 to $25 a year. One of the domains I expect to migrate to PHP and MySQL in the next few months.

Suggestions?

Source: http://community.livejournal.com/webhosts/26292.html

Date: 04/18/05 (Web Hosts)    Keywords: php, mysql, database, sql, hosting

www.subnixus.com now has three easy ways to earn free hosting.

1. Being an active member of the forum. It's that simple, by posting on our forum you can earn free hosting. Read about it here: http://www.subnixus.com/forums/viewtopic.php?id=798

2. By signing up for one of our sponsers, such as DISH Network, you can earn one year of free hosting. Read about it here: http://www.subnixus.com/forums/viewtopic.php?id=799

3. By making a donation of $10 or more you will earn one year of free hosting. Read about that promotion here: http://www.subnixus.com/forums/viewtopic.php?id=802

Your hosting package will be reviewed and given on a case by case basis. The minimum package includes (but you could qualify for more):

www.yourname.com
125MB of Space
1G of bandwidth
1 MySQL Database
POP Email
Control Panel
FTP
(basically anything you need to get your site up and running)


This new program starts this week. All accounts that are earned between April 18 - 22 will be setup on Sat. the 23rd. Please visit www.subnixus.com for all of the details.

Source: http://community.livejournal.com/webhosts/26063.html

Date: 01/13/06 (Web Development)    Keywords: html, sql, java, web, yahoo, google

i'd like to add a calendar to my site. it doesn't need to be editable by visitors to the page, only by me.. but it does need to have functionality that would allow visitors to flip through different months and see what days i'm available. ideally it could also be colorcoded based on different categories (completely available, someone has requested me but nothing is confirmed, not at all available.)

i have come to ask the experts if you know a useful app for this purpose. i'm definitely not a true webdev person... i only know basic html and a little sql/javascript/etc. i googled, but most of the results were for calendars which remain on the site of the calendar's creator (e.g. calendar.yahoo.com) vs. ones which could integrate into my own site's design.

(to give you an idea of my preferences, some of my favorite web-based tools are flickr, allconsuming.net (and 43 things), dodgeball, yelp, etc.)

any advice would be greatly appreciated =]

Source: http://community.livejournal.com/webdev/285230.html

Date: 12/14/05 (Web Development)    Keywords: php, mysql, database, sql, web, apache

I was wondering if anyone could recommend me a webhost that will meet the following requirements:

+ I want as much control as possible. If I decide one week I want to mess around with OracleDB, I want to be able to install it. If I want to use Ruby on Rails, I want to be able to install that too. I basically want to be able to do as much server administration as possible

+ I have the following musts: PHP 5, Apache, MySQL(preferred, but some kind of database)

+ I need a decent amount of space and bandwidth, but not a whole lot.

I have considered a dedicated server, but it seems to be far too expensive for now at least. Any ideas?

Source: http://community.livejournal.com/webdev/279180.html

Date: 12/13/05 (Web Development)    Keywords: database, asp, sql

Amazingly enough, I've *just* started using functions with my ASP code, even though I've been doing ASP for a little while now.

Right now, I'm only using a function to deal with connecting to my database and retrieving information based on the SQL I use.

In several cases, however, I need to use multiple recordsets for part of a page's content. It gets a little tiresome to use the same function over and over.

Is there a way that I could store all of my SQL statements in an array and then use that array in my function so that it would run through however many items there are in the array (this could not be a static number, since one page might use four different statements and another might use three), run all the SQL statements, and then put the results in different recordsets?

Or..would it just be easier to make one huge SQL statement, like

SELECT table1.field1, table2.field1, table2.field2, table3.field5 FROM table1, table2, table2 WHERE

Source: http://community.livejournal.com/webdev/278824.html

Date: 01/19/06 (MySQL Communtiy)    Keywords: php, mysql, sql

** EDIT
turns out the problem is my version of mysql, thanks for the help.



Hi, I am having a problem trying to update ids in a table.

I am using PHPmyAdmin.

I looked at the syntax on mysql.com and I am fairly certain that I have it right but I get the following error:

#1064 - You have an error in your SQL syntax near ' drugs SET synonyms.drug_id = drugs.drug_id WHERE 'synonyms.drug_name' = 'drugs.' at line 1

The two tables are as follows:

drugs
drug_id | drug_name

synonyms
drug_id | synonym | drug_name

This is my SQL statement:
UPDATE synonyms, drugs SET synonyms.drug_id = drugs.drug_id WHERE 'synonyms.drug_name' = 'drugs.drug_name'

Source: http://community.livejournal.com/mysql/83525.html

Date: 01/19/06 (MySQL Communtiy)    Keywords: mysql, database, sql

These tech communities are mostly questions from those that need help which I often do :)
I thought it'd be fun to change the pace a little and post some info I think is good to know if your using mysql.

I've recently had problems with table crashes and running mysql in an environment with very little disk space. This has taught me a few things I did not know before hand. One of which is mysql's binary log (also known as the update log in older versions). It stores all statements that are run that could potentially modify data in the database which allows restores to work as best as possible (i.e. even if your backup is a bit old). For more info check the binary log docs

Source: http://community.livejournal.com/mysql/83396.html