sql

PHP: Incorrect SQL syntax error

Hi folks, I have a PHP class that creates a SQL query based on values entered from a form. I'm getting Incorrect syntax near the keyword 'WHERE'. ) ) Here is my code. The problem is occurring around each of the WHERE clauses, (already dealing with SQL injections btw). if($from != ''){ $from = date('Y-m-d H:i:s',strtoti...

combining two sql queries into one?

I have two similar queries on same table and same where condition but different selects on it. Select flatpos from archdetails where version='1.3' AND compname IN ( select distinct compname from svn3 where revno='r270294' ) AND select count(distinct compname), sum(CASE WHEN inFlat=1 THEN 1 ELSE 0 END), min(flatLoopIndex)...

How do you find a missing number in a table field starting from a parameter and incrementing sequentially?

Let's say I have an sql server table: NumberTaken CompanyName 2                      Fred 3                      Fred 4                      Fred 6                      Fred 7                      Fred 8                      Fred 11                    Fred I need an efficient way to pass in a parameter [StartingNumber] an...

Searching numeric strings with Full-Text Search in SQL 2005

I'm using the SQL Full-Text Search and have a stored proceedure that uses the FREETEXTTABLE function. This all works great, however, I have noticed that if I search for something such as 'Chapter 19' the 19 seems as if it is thrown away and the search only searches on 'Chapter'. Also if I search for just '19' I get no results. I know ...

Postgres: checking value before conditionally running an update or delete

I've got a fairly simple table which stores the records' authors in a text field as shown here: CREATE TABLE "public"."test_tbl" ( "index" SERIAL, "testdate" DATE, "pfr_author" TEXT DEFAULT "current_user"(), CONSTRAINT "test_tbl_pkey" PRIMARY KEY("index"); The user will never see the index or pfr_author fields, but I'd like...

SQL Server problems

I have installed SQL Server 2005 but there is no server, I have reinstalled it about 5 times and the same problem never changes. And here is the error message Cannot connect to .. ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server wa...

mysql "group by" very slow query.

hi all, i have this query in a table with about 100k records, it runs quite slow (3-4s), when I take out the group it's much faster (less than 0.5s). I'm quite at loss what to do to fix this : select msg.id, msg.thread_id, msg.senderid,msg.recipientid, from_user.username as from_name, to_user.username as to_name from msgtable as msg le...

Is there a clean way to get table-trigger-column-sequence relation?

I have built a "best guess" query to get the related table.column for a sequence. Is there a better, cleaner method to get this relation? select ut.table_name, ut.table_owner, ut.trigger_name, us.sequence_name ,upper(dbms_metadata.get_ddl('TRIGGER', ut.trigger_name, ut.table_owner )) triger_ddl ,to_char(regexp_substr( upper(d...

build a formatted string in oracle

I need to build a string from some columns into another column, the trailing zeros must be removed from month and day : YEAR=2008;MONTH=1;DAY=1;ID=1021; ... etc For the day piece I've used the TO_CHAR(D_RIC,'D') function to remove leading zeros. Is there a format option for months to remove starting zeros too ? ...

In-memory data cache

All, I have a Windows Service which queries a database (SQL Server) for some data. What is the best way to store that data for later usage and can very accessed very easily? Maybe a DataTable ? Rgds, MK ...

How to set a permission to let a SQL server user to access for certain tables

I just created two tables and they are named as tblA and tblB. I also created a user(Security/Login) who will be used for a remote insert/update. What is the best way to permit this user to access for only those two table out of 50 table. I have tried look for the way from (Security/Login) and (Database/Properties). Can I limit it fro...

SQL and PHP database access

I'm very new to PHP, SQL I've worked with using Coldfusion but only with very simple queries. In coldfusion to access a specific database <cfquery dbname="blah"> I know in PHP I have to use mysql_query() and mysql_connect(), and here is the code I have, so I understand how to access a server and a table, but not the database. How can ...

The riddle of the working broken query.

I was going through some old code that was written in years past by another developer at my organization. Whilst trying to improve this code, I discovered that the query it uses had a very bad problem. OdbcDataAdapter financialAidDocsQuery = new OdbcDataAdapter( @"SELECT a.RRRAREQ_TREQ_CODE, ...

Wordpress - SQL query on two custom fields issue

I've got this query so far: if(!empty($_SESSION['s_property_region'])) { $sqlWHERE .= " AND $wpdb->postmeta.meta_key = 'property_region' AND $wpdb->postmeta.meta_value = '".$_SESSION['s_property_region']."'"; } if(!empty($_SESSION['s_property_bedrooms'])) { $sqlWHERE .= " AND $wpdb->postmeta.meta_key = 'property_bedrooms' AND $...

How to combine two tables, one with 1 row and one with n rows?

I have a database with two tables One with games and one with participants A game is able to have more participants and these are in a different table. Is there a way to combine these two into one query? Thanks ...

How do I manage and publish a database with my MVC2 application on Azure?

I'll start by saying that I'm fairly new to the idea of deploying applications to the cloud, but I recently downloaded the Azure SDK and wanted to build a sample application to get myself up-to-speed. Here's what I did. Download the Azure SDK Create a new solution Add a new ASP.NET MVC2 Web Role Compile Debug Everything seems to work...

I need to find closest date in a rails app.

Hi, I need to find the closest date to a given date using a rails named scope. With MySql I could use DATEDIFF but I want to keep database agnosticism, following doesn't work with Sqlite: named_scope :closest_to, lambda { |*args| date = args.first count = args[1] || 1 {:limit => count, :order => "ABS(DATEDIFF(hour, date_field, #...

MongoDB, Complex Queries, and Performance

Back-Story On a current project, I am using MySQL and SQLite in combination with each other. I'm currently giving each user their own SQLite database to get around my provider's 1GB MySQL DB limit. It's worked out alright and the performance is good, but I know for a fact that continual maintenance of these flat-file databases will be a...

Interpreting SQL's BNF

I'm looking at the syntax of SQL, specifically the character string literal. <character string literal> ::= [ <introducer> <character set specification> ] <quote> [ <character representation> ... ] <quote> [ { <separator> <quote> [ <character representation> ... ] <quote> }... ] Ignoring the [ <introducer> <character set s...

sprintf() Versus mysql_query()

Having trouble formatting my code to execute without error using sprintf() When I run the code I get this error: Parse error: syntax error, unexpected T_VARIABLE in /location on line 16 $query = sprintf('UPDATE `%s` SET `stock` = :amount WHERE `itemname` = '$q'', $tablename); Above is line 16 in my code. I'm assuming it is syntax rel...