query

query works in MySqlWorkbench but not in php mysql

I have the following query that works fine in MySqlWorkbench but not in php. I've copied the query exactly from php into workbench and it works fine there. Any ideas why this is happening? I can't figure it out. SELECT tlpd_games.id FROM tlpd_games LEFT JOIN tlpd_leagues league on tlpd_games.league_id = league.id where league.section_...

SQL Server Query to bring groupwise multiplication

When a table having following value Say TableA **Grp Value** Grp1 2 Grp1 5 Grp1 3 Grp2 3 Grp2 -5 Grp2 -2 Grp3 4 Grp3 0 Grp3 1 Grp4 -2 Grp4 -4 Grp5 7 Grp5 NULL Grp6 NULL Grp6 NULL Grp7 -1 Grp7 ...

Facebook "OR" queries with the graph API

Searching for "OR" queries on facebook is an exercise in futility as "OR" gets filtered. Cannot find a way to search for " X or Y or Z " https://graph.facebook.com/search?q=cats%20OR%20Dogs&type=post Results in it only finding posts which contains both Cats and Dogs: { "id": "100000895865837_129212313793038", "from": { ...

help with sql-server query

hi i have this table: num | class | Prob ---------------------- 1 | 2 | 1 99 | 4 | 2 2 | 5 | 3 99 | 6 | 4 where Prob = 99 i need * else i need to leave empty num | class | Prob ---------------------- | 2 | 1 * | 4 | 2 | 5 ...

TSQL Searching Rows in another table

Two tables are given PilotGroup Table Pilot Plane Jon Smith A1-Fighter Jon Smith B1-Fighter Gravell A2-Fighter Jon Skeet A1-Fighter Jon Skeet B1-Fighter Jon Skeet A4-Fighter Gravell A5-Fighter SouthOperation Table Plane A1-Fighter B1-Fighter The task is to print the pilot names who is trained on ...

Slow PostgreSQL function

Hi, I use this query to select products from my database. Performing this query takes ~0.220ms. If I remove the 3 lines where the number_percentage() function is used everything is fast (~0.07ms or less). SELECT DISTINCT ON (p.id) p.id AS product_id, pv.price, number_percentage(pv.price,t.percentage) AS price_vat, number_percentage...

PHP/PDO: use simple prepared statement with query return/affected rows?

I am new to PDO objects and cannot find a single piece of documentation that will help me. Say I got a simple code to delete a row: $count = $dbh->exec("DELETE FROM fruit WHERE colour = 'red'"); That will return affected rows, but how would I use prepared statements with that? Can use use $dbh->prepare AND $dbh->exec or query !? ...

calculate rank in highscore from 2 tables

i have a trivia game and i want to reward users for 2 events: 1) answering correctly 2) sending a question to the questions pool i want to query for score and rank of a specific player and i use this query: SELECT (correct*10+sent*30) AS score, @rank:=@rank+1 AS rank FROM ( trivia_players JOIN ( SELECT COUNT(*) AS sent,...

Sophisticated JPQL String Query

I am trying to execute a pretty-sophisticated query on a string field in the database. I am not very experienced at JPQL, so I thought I would try to get some help. I have a field in the database called FILE_PATH. Within the FILE_PATH field, there will be values such as: 'C:\temp\files\filename.txt' 'file:\\\C:\testing\testfolder\inn...

query results can't be enumerated more than once - problem with retrieving data from database

Hi I have a strange problem with retrieving data from database. In my form I have a combobox. The combobox contains couple of items - department's name. Everytime I choose an item from combobox I connect to database and fire this public List<User> SelectAllSecurityUsersByDepartment(string departmentId) { var result = DBConne...

Having trouble with MySQL division query (probably simple fix)

I'm trying to figure out the overall ratio for users. There is basically two columns I need to look at to figure out the ratio, total_emails and total_hours. SELECT sum(total_emails/total_hours) as ratio FROM table WHERE id= 1 LIMIT 0, 1; This appears to adding the ratio up multiple times and I get a number like 8.45 when I should be...

How to query wordpress posts by custom field

I am attempting to have lots of posts in a specific category. The post will have a custom field that will be a date. I need the posts to be organized by the category, the custom field, and each page of posts should be separated by month of the custom field. So hard to explain... Page of posts for the current month. Links at the bottom f...

Mysql time stamp queries

I have a column that uses time stamp. My question is I am a bit confused about how to make queries against it,how would I say Where $time is after X date Are queries made in local time or CUT? When I just try to do where andthe post date /time I get an error because of the space and if I quote it I think it takes it as a string : / ...

PHP: fastest hash for non-cryptographic uses?

I'm essentially preparing phrases to be put into the database, they may be malformed so I want to store a short hash of them instead (I will be simply comparing if they exist or not, so hash is ideal). I assume MD5 is fairly slow on 100,000+ requests so I wanted to know what would be the best method to hash the phrases, maybe rolling ou...

PHP/PDO: Prepared statements don't work when creating a table?

When I am using a PDO prepared statement, and use it to plug in a table name to the query it fails, a quick example: $stmt = $dbh->prepare("CREATE TABLE ? (id foo, int bar,...)"); $stmt->execute(Array('table_foobar')); All it does is replaces ? with 'table_foobar', the single quotes don't allow creation of the table for me! I end up ...

Testing a query builder

I am building a query builder that I want to unit test. I don't know how to go about it though. It (currently) consists of two parts: the QueryBuilder itself which provides a fluent interface for constructing queries. And a SqlConstructor that takes care of constructing the actual SQL. So basically, how do I test 'correctness'? Should ...

MySQL event scheduling update query

I am just getting to grips with the event scheduler in MySQL and I am writing a query to update a column on the same date each year. The problem I am having is working out the best way to structure my update query. Every user has a value in column_x that is a varchar field in the format of int/int. These values range from 7/1 to 7/11, ...

PHP/PDO: style of write many queries on one page?

An example of my scenario is a large setup page for an application, the method I use is for example: //query 1 $stmt = $dbh->prepare("..."); $stmt->execute(); //query 2 $stmt = $dbh->prepare("..."); $stmt->execute(); Would this be an accepted method to write more queries? I have no clue how it's supposed to be done (or who does what,...

Getting more details out of a SQL subquery

I have a SQL Query giving me a list of double records in my database. select periodid, itemid from periodscore group by periodid, itemid having count(*) > 1 This works as expected, but now I would like to retrieve additional fields of these records (such as date last updated etc). So I tried: select * from periodscore where period...

SQL: Query a List within a List XML

I'm trying to query some XML in SQL Server but am having difficulties: Here is some sample XML: <BaseReport> <Parties> <Party> <SubjectType> <ListItem Name="SubjectType1Name" /> <ListItem Name="SubjectType2Name" /> </SubjectType> </Party> <Party> <SubjectType> <ListItem Name="SubjectType...