sql

Insert Group By count results into a table

How do you insert a group by count result into a table? I'm trying to insert a list of names with counts for each. Thanks!! ...

Display resulst from a query with PHP (Wordpress)

Trying to display results from an sql query in PHP: SELECT * FROM wp_celebcount ORDER BY count DESC I'm trying to display two columns: wp_celebcount.name & wp_celebcount.count Having trouble getting the result to show with PHP - I want to show this result on my index.php Wordpress theme file. Thanks for the help... ...

Select average from MySQL table with LIMIT

I am trying to get the average of the lowest 5 priced items, grouped by the username attached to them. However, the below query gives the average price for each user (which of course is the price), but I just want one answer returned. SELECT AVG(price) FROM table WHERE price > '0' && item_id = '$id' GROUP BY username ORDER BY pr...

How to select the latest version of answers given by each answerer in MySQL?

There is a scenario. There is an ask-and-answer website. An answerer can modify his answer, and the history of modification is saved on the server. Be default, only the latest version of each answer is displayed. select * from answers where questionid='$questionid' group by answerer_id So I can group all answers by answerer, then I ...

Wiki Database, is there one?

I was searching the net for something like a wiki database, just like wikipedia but instead stores structured content, editable by users. What I was looking for was an online database accessible by everyone where people can design the schema and data with proper versioning of both schema and data. I couldn't find any such site. I am not ...

Aggregating SQL rows with precedence

I have a table full of items from different sources. Some of the sources might have the same location (in my example, different BBC news feeds would be different sources, but they all come from the BBC). Each item has a "unique" ID which can be used to identify it among others from the same location. This means that items relating to the...

delete data from database where date ... SQL

hey I want to delete some data from database: WHERE created_at < NOW() - 30 min How to wirte the 30 min into sql ...

Build safe search conditions for SQL WHERE clause

I need to build search conditions to be used with WHERE clause. This search condition is then passed to a different application to be executed as a part of SQL query. Because there search conditions can be quite complex (including sub-queries) I don't believe receiving application can intelligently parse them to prevent SQL injection att...

Implementing Wilson Score in SQL

We have a relatively small table that we would like to sort based on rating, using the Wilson interval or a reasonable equivalent. I'm a reasonably smart guy, but my math fu is nowhere near strong enough to understand this: The above formula, I am told, calculates a score for a positive/negative (thumbs up/thumbs down) voting system. ...

How to find a gap in a time ordered table where a given column does not have a certain value for a specified interval.

I have a large table (millions of rows) where I need to find groups of records based on the presence of a certain column value and where a specified 'timeout' has not occurred. I figure one approach would be to find across the entire table where these 'timeout' gaps have occurred. Example table: +----------------+------+ | time ...

Nice way to pass parameters to PDO

Positional parameters become a nightmare when dealing with more than 3 or 4 parameters. Named parameters are verbose. I'm thinking of doing this: query("SELECT * FROM users WHERE username = ", $username, " AND password = ", $password) With dynamic parameters (using func_get_args()), every second one being transformed into a positional...

Using XML in SQL Server Reporting service + Textbox

I am having data in xml as column in my table. I am retriveing the value select * from customer But my customer column value looks like this <customer exception ="Sam"/> <customer exception ="Jam"/> I need to specify the first two letters of this exception in my sql reports textbox as (S,J) How can I achieve this in SSRS reports...

MySQL: Using REPLACE as part of an IN statement

I am trying to use REPLACE to substitute spaces for commas. If I try SELECT REPLACE('1 2 3 4 5 6 7 86 9', ' ', ', '); then I get exactly what I need in a string. However, if I try this as part of an IN statement I only get returned the first match (ie, 1) Here is my entire query: SELECT aa.category_id, aa.yyyymm, ...

[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error - connecting to SQL database in VB script

I have a VB script which connects to a local SQL database to retrieve a value. The exact same script runs on about 100 servers, but a few of the servers produce this error: [DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation Here is the code that runs: Function GetPrimaryServerID On...

Get all entries from one table together with the count of these entries in another table.

I've got 3 tables: users (id, name, ...) items (id, name, ...) downloads (user_id, item_id, ...) How do I get all users together with the number of downloads they have? ...

Intercept/Log all database calls to SQL Server from a .Net Assembly

Is there a way to intercept/log all database calls from a .Net Assembly. The primary purpose is to log all TSQL statements generated and send to SQL Server database from any of the businss logic layer assembly. This mechanism should be available at global level and not required to be applied at each assembly. This mechanism should be unl...

Finding Day in T-SQL

Hi, Please let me know how can I find if it is Sunday on given Date? EDIT: From answers another thing comes to my mind: How to find what is first day of week? ...

Retrieving and Presenting SQL Joined Table Records with PHP, Sqlite (or MySql), and HTML

Hello: Please bare with me while I try to explain my question. I have a Sqlite database that has a table consisting of company information (Companies) and a table consisting of product information (Products). The Companies table id is Company_Name. It is joined with the Products table by Company_Name. For example, Company "A" can ha...

Can I rollback Dynamic SQL in SQL Server / TSQL

Can I run a dynamic sql in a transaction and roll back using EXEC: exec('SELECT * FROM TableA; SELECT * FROM TableB;'); Put this in a Transaction and use the @@error after the exec statement to do rollbacks. eg. Code BEGIN TRANSACTION exec('SELECT * FROM TableA; SELECT * FROM TableB;'); IF @@ERROR != 0 BEGIN ROLL...

[MySQL] Search literal within a word

Is there a way to perform a FULLTEXT search which returns literals found within words? I have been using MATCH(col) AGAINST('+literal*' IN BOOLEAN MODE) but it fails if the text is like: blah,blah,literal,blah blahliteralblah blah,blah,literal Please Note that there is no space after commas. I want all three cases above to be retur...