Menu
  • HOME
  • TAGS

MySQL matching row values sets

Tag: mysql,matching

I am relatively new with mysql and php. I have developed a hockey stat db. Until now, I have been doing pretty basic queries and reporting of the stats. I want to do a little more advanced query now.
I have a table that records which players were on the ice (shows as a "fk_pp1_id" - "fk_pp5_id") when a goal is scored. here is the table:

pt_id  | fk_gf_id  | fk_pp1_id  | fk_pp2_id  | fk_pp3_id  | fk_pp4_id  |  fk_pp5_id
1      |     1     |     19     |     20     |     68     |     90     |     97
2      |     2     |     1      |     19     |     20     |     56     |     91
3      |     3     |     1      |     56     |     88     |     91     |     93
4      |     4     |     1      |     19     |     64     |     88     |     NULL
5      |     5     |     19     |     62     |     68     |     88     |     97
6      |     6     |     55     |     19     |     20     |     45     |     62
7      |     7     |     1      |     19     |     20     |     56     |     61
8      |     8     |     65     |     68     |     90     |     93     |     97
9      |     9     |     19     |     20     |     45     |     55     |     62
10     |     10    |     1      |     19     |     20     |     56     |     61
11     |     11    |     1      |     19     |     20     |     56     |     61
12     |     12    |     19     |     20     |     68     |     90     |     97
13     |     13    |     19     |     20     |     68     |     90     |     97
14     |     14    |     19     |     20     |     55     |     62     |     91
15     |     15    |     1      |     56     |     61     |     64     |     88
16     |     16    |     1      |     56     |     61     |     64     |     88
17     |     17    |     1      |     19     |     20     |     56     |     61
18     |     18    |     1      |     19     |     20     |     56     |     61
19     |     19    |     1      |     65     |     68     |     93     |     97

I want to do several queries:

  1. Show which of the five players were together on the ice most often when a goal was scored.
  2. Select say 2 players and show which other players were on the ice most often with them when a goal was scored.

I was able to write a query which partially accomplishes query #1 above. 

SELECT 
fk_pp1_id,
fk_pp2_id,
fk_pp3_id,
fk_pp4_id,
fk_pp5_id,
count(*)
FROM TABLE1
group by 
fk_pp1_id,
fk_pp2_id,
fk_pp3_id,
fk_pp4_id,
fk_pp5_id

Here are the results:

fk_pp1_id    fk_pp2_id     fk_pp3_id       fk_pp4_id       fk_pp5_id       count(*)
1               19            20              56              61              4
1               19            20              56              91              1
1               19            64              88            (null)            1
1               56            61              64              88              2
1               56            88              91              93              1
1               65            68              93              97              1
19               1            20              56              61              1
19              20            45              55              62              1
19              20            55              62              91              1
19              20            68              90              97              3
19              62            68              88              97              1
55              19            20              45              62              1
65              68            90              93              97              1             4

See this sqlfiddle:
http://sqlfiddle.com/#!9/e3f5f/1

This seems to work at first, but I realized this query, as written, is sensitive to the order in which the players are listed. That is to say a row with: 1, 19, 20, 68, 90 will not match 19, 1, 20, 68, 90

So to fix this problem, I feel like I have a couple options:

  1. Ensure the data is input into the table in numerical order

  2. Re-write the query so the order of the data in the table doesn't matter

  3. Make the resulting query a sub-query to another query that first orders the column (left to right) in numerical order.

  4. Change the schema to record/store the data in a better way

1, I can do, but would prefer to have the query be fool-proof.
2 or 3 I prefer, but don't know how to do either.
4, I don't know how to do and is least desirable as I already have some complex queries against this table that would need to be totally re-written.

Am i going about this in the wrong way or is there a solution??
Thanks for your help

UPDATE - OK I (hopefully) better normalized the data in the table. Thanks @strawberry. Now my table has a column for the goal_id (foreign key) and a column for the player_id (another foreign key) that was on the ice at the time the goal was scored.

Here is the new fiddle: http://sqlfiddle.com/#!9/39e5a

I can easily get the one player who was on the ice most when goals are scored, but I can't get my mind around how to find the occurrences of a group of players who were on the ice together. For example, how many times were a group of 5 players on the ice together. Then from there, how often a group of 2 players were on the ice together with the 3 other players.

Any other clues???

Best How To :

I find a similar problem here and based on that i come up with this solution.

For the first part of your problem to select how many time same five player were on the ice when the goal is scored your query could look like this:

SELECT GROUP_CONCAT(t1.fk_gf_id) AS MinOfGoal, 
       t1.players AS playersNumber,
       COUNT(t1.fk_gf_id) AS numOfTimes
FROM (SELECT fk_gf_id, GROUP_CONCAT(fk_plyr_id ORDER BY fk_plyr_id) AS players
      FROM Table1
      GROUP BY fk_gf_id) AS t1
GROUP BY t1.players
ORDER BY numOfTimes DESC;

And for your second part of the question where you want to select two players and find three more player which were on the ice when goal were scored you should extend previous query whit WERE clause like this

SELECT GROUP_CONCAT(t1.fk_gf_id) AS MinOfGoal, 
       t1.players AS playersNumber,
       COUNT(t1.fk_gf_id) AS numOfTimes
FROM (SELECT fk_gf_id, GROUP_CONCAT(fk_plyr_id ORDER BY fk_plyr_id) AS players
      FROM Table1
      WHERE fk_gf_id IN (SELECT fk_gf_id
                        FROM Table1
                        WHERE fk_plyr_id = 19)
      AND fk_gf_id IN (SELECT fk_gf_id
                       FROM Table1
                       WHERE fk_plyr_id = 56)
      GROUP BY fk_gf_id) AS t1
GROUP BY t1.players
ORDER BY numOfTimes DESC;

You can see how it's work here in SQL Fiddle...

Note: I added some data in Table1 (don't be confused with more date counted).

GL!

MySQL Query returning strange values

php,mysql

You need to join by account_id and also question_id SELECT * FROM `quiz_questions` INNER JOIN `quiz_answers` ON `quiz_questions`.`account_id` = `quiz_answers`.`account_id` AND `quiz_questions`.`question_id` = `quiz_answers`.`question_id` WHERE `quiz_questions`.`account_id` = '1840979156127491' ORDER BY `quiz_questions`.`question_id` ASC LIMIT 5 ...

MySQL trigger help, capturing a variable from php insert into

php,mysql,triggers

You are on the right path: IF (SELECT true FROM redcap_encryption WHERE ProjectID=NEW.project_id AND FieldName=NEW.field_name).... ...

MySQLdb Python - Still getting error when using CREATE TABLE IF NOT EXISTS

python,mysql

MySQL is actually throwing a warning rather that an error. You can suppress mysql warnings like this : import MySQLdb as mdb from warnings import filterwarnings filterwarnings('ignore', category = mdb.Warning) Now the mysql warnings will be gone. But mysql errors will be shown as usual Read more about warnings at...

How do I display my mysql table column headers in my php/html output?

php,html,mysql,table,data

Note: You can just make a single file out of it to achieve your wanted output Use mysql_real_escape_string() to sanitize the passed-on value to prevent SQL injections You should use mysqli_* instead of the deprecated mysql_* API Form them in a single file like this (display.php): <html> <form method="post" name="display"...

Loop through database table and compare user input

mysql,c

If you are only looking for fields that match the input, you'll want to search the database using the input string. In other words, write your query string so that it only gives you results that match the user input. This will be much faster than searching through every returned...

Can Rails deal with DB uniqueness without index?

mysql,ruby-on-rails,rdbms

Because there is no need for other ways. Under the hood it's all the same: when you define a UNIQUE constraint, a UNIQUE index is created on that table to enforce it. Question from DBA.SE: When should I use a unique constraint instead of a unique index? So for a...

Having two arrays in variable php

php,mysql,arrays,variables,multidimensional-array

The explode function is being used correctly, so your problem is further up. Either $data[$i] = mysql_result($result,$i,"data"); isn't returning the expected string "2015-06-04" from the database OR your function $data[$i] = data_eng_to_it_($data[$i]); isn't returning the expected string "04 June 2015" So test further up by echo / var_dump after both...

C# - Can't connect to remote MySQL server

c#,mysql

When connecting to a MySQL-Database I always used the MySQL Connector you can get here: https://dev.mysql.com/downloads/connector/net/6.9.html You have to import the MySQL namespaces to your project and then you can use the MySQLConnection instead of the SQLConnection that is, as far as I know, only for MSSQL servers. http://www.codeproject.com/Tips/423233/How-to-Connect-to-MySQL-Using-Csharp...

Ignore Group if LIMIT is not reached in MySQL

mysql

You can use the having clause to filter out the groups you don't need, keeping only the groups where there are more than 4 dates: SELECT Fruits, SUM(Ordered), Date FROM table GROUP BY Date HAVING COUNT(Date) > 4 ...

Is it possible to connect to remote DB while working in localhost?

php,mysql,git,phpmyadmin

Yes, of course it is. You'll need to whitelist your development servers on the remote server's firewall and allow remote access to the database. If you're using cPanel, there are options there to do so, but you may need to speak to your host as well.

MySQL: Select several rows based on several keys on a given column

mysql,sql,database

If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ...

timestamp SQL to Excel

php,mysql,sql,excel

The ###### is shown in MS Excel when the data in a cell is too long for the column width.... the data inside the cell is still correct, as you can see if you select one of those cells and look at the value displayed in the cell content bar...

how will i order this query by DoctorName

php,mysql

Add an ORDER BY statement to your query: $q=mysqli_query($link,"Select * from doctor where status='". $stat ."' ORDER BY `doctor_last_name`"); ...

How can I create missing date records for each employee based off a limited calendar?

mysql,sql,tsql

Create a table that contains all the possible dates in the range, @alldates Then insert your missing records with something like this: Query INSERT INTO dbo.timeclock SELECT id ,d.punchtime ,'no time entered' FROM ( SELECT DATE ,id FROM @alldates d CROSS JOIN ( SELECT DISTINCT id FROM dbo.timeclock )...

Comparing two values in the same row and change if needed

php,mysql,loops

Below query will work, unless you need to do query optimization and reduce the locking period UPDATE Product SET Voorraad = Minvoorraad WHERE Minvoorraad > Voorraad ...

Query how often an event occurred at a given time

mysql,sql

This could be done using user defined variable which is faster as already mentioned in the previous answer. This needs creating incremental variable for each group depending on some ordering. And from the given data set its user and date. Here how you can achieve it select user, date, purchase_count...

Retrieve Values As Column

mysql,sql

If types are fixed (just IMPRESSION and CLICK), you could use a query like this: SELECT headline, SUM(tracking_type='IMPRESSION') AS impressions, SUM(tracking_type='CLICK') AS clicks FROM tracking GROUP BY headline ...

Php Mysql Query not working properly

php,mysql

No need to use union as it will give a lots of duplicate data What you want to achieve can be done with simple left join or inner join SELECT m.issue_name ,m.issue_type , m.priority ,m.status,m.description , m.start_date,m.end_date,m.duration, s.name as server_name,p.name as product_name from mod_networkstatus as m LEFT JOIN tblservers as...

If I export my database with phpmyadmin will it lock my tables or take my database down?

mysql,database,phpmyadmin

The answer is no, tables won't be locked, database won't be down. But, if your database is large and it takes long time to backup it, you can sometimes expect performance degradation(slow SQL queries from your application).

Select all none null values from all columns in a table

mysql,sql

select @variable will just return you a value of variable. You need to use some dynamic SQL I believe. Maybe smth like exec('select ' + @colname + ' from ' etc) will work for you (at least it will work in MS SQL server).

How do I find records in one table that do exist in another but based on a date?

mysql,sql

select d.`name` from z_dealer d where (select count(*) from z_order o WHERE o.promo_code = d.promo_code AND o.date_ordered > '2015-01-01') = 0 ...

mysql detect field value change

mysql

Here's a way to do it using variables: SELECT id, `timestamp`, ver_fw FROM ( SELECT id, `timestamp`, ver_fw, IF ( @prev_ver <> ver_fw, IF (@prev_ver := ver_fw, 1, 1), IF (@prev_ver := ver_fw, 0, 0)) AS IsDifferent FROM tbl_geodata CROSS JOIN (SELECT @prev_ver := '-1') AS var WHERE imei LIKE...

How to post time value in database in PHP

php,mysql

It is very simple: You code here: $id_time = date("d-m-Y",time()); Please UPDATE THAT INTO; $id_time = date("Y-m-d",time()); Your sql is ok. And I hope that will work....

MySQL substring match using regular expression; substring contain 'man' not 'woman'

mysql,regex

A variant of n-dru pattern since you don't need to describe all the string: SELECT '#hellowomanclothing' REGEXP '(^#.|[^o]|[^w]o)man'; Note: if a tag contains 'man' and 'woman' this pattern will return 1. If you don't want that Gordon Linoff solution is what you are looking for....

Notice: Array to string conversion in “path of php file” on line 64

php,mysql,arrays,oracle

Curly brackets are your friend when inserting variables into double quoted strings: $main_query=oci_parse($connection,"INSERT INTO ROTTAN(NAME,ROLLNO) VALUES('{$array[$rs][0]}','{$array[$rs][1]}')"); ...

IBM Cognos _days_between function not working

mysql,database,date,cognos

The Cognos _days_between function works with dates, not with datetimes. Some databases, like Oracle, store all dates with a timestamp. On a query directly to the datasource, try using the database's functions to get this data instead. When possible, this is always preferable as it pushes work to the database,...

How to program a recurring billing/invoice system using PHP and MySQL

php,mysql

Cron sounds good. (Also it is worth to mention the MySQL Event Scheduler, but again I would go for a Cronjob) A copy would be something like this SQLFIDDLE: create table t ( id int, d date ); insert into t values( 0, CURDATE() ); insert into t values( 2,...

Symfony2 creating and persisting entity relationships

php,mysql,symfony2,doctrine2

When you create two entities with a one-to-one relationship, both entities need to be persisted either explicitly or by using cascade persist on one side of the relationship. You also need to explicitly set both sides of the relationship. Doctrine - Working with Associations - Transitive persistence / Cascade Operations...

I Want to fetch SQL Records in MySQL of current Year

mysql

Try this: SELECT count(enq.`enquiryId`), Month(enq.`date`), Year(enq.`date`) FROM enquiry enq WHERE Year(enq.date)=somevalue --2015 for example GROUP BY MONTH(enq.`date`) ...

How to create multiple jquery form fields and insert in mysql database, without using mysql_real_escape_string

javascript,php,jquery,mysql

Your problem has nothing to do with jQuery and the form. It is just highly recommended to prevent SQL injection, an attack in which an attacker injects SQL commands into your DB Query by posting it in your form. That's why any data that comes from an untrusted source (eg...

Temporarily Number Rows to Group by in MYSQL

mysql

You can do this using variables: select s.*, floor( ((@rn := @rn + 1) - 1) / 5) as group_number from sample s cross join (select @rn := 0) params order by date; ...

PHP / MySQLi: How to prevent SQL injection on INSERT (code partially working)

php,mysql,mysqli,sql-injection,sql-insert

In the New PHP code snippet, you are still vulnerable to injections. You are using a prepared statement in the insert part, but you are not actually using the preparations strengths correctly. When creating a prepared statement, you create a query in which you add placeholders instead of the raw...

Use SQL output 1 by 1

php,mysql

Store your results arrays in an array: <?php $RankSql = 'SELECT * FROM games WHERE Genre LIKE "%'.$Genre.'%" ORDER BY Score DESC limit 5'; $Rankresult = mysql_query($RankSql); $games = array(); while($RankOrder = mysql_fetch_array($Rankresult)) { $games[] = $RankOrder; }; // ex: third game (remember, arrays start at zero) echo $games[2]['Game']; ?>...

Trying to rewrite mysql_* to pdo

php,mysql,pdo

I don't know the source of the array $arr = array();, but it is assigned to null before the insert query. So it means, literally you are inserting nothing into the database. So check your array well, maybe it was to be like $arr = array('name'=>'My Name', 'url'=>'url', 'email'=>'my email',...

How to search images by name inside a folder?

php,mysql,image

This looks like a job for glob, which returns an array of file names matching a specified pattern. I'm aware of the other answer just posted, but let's provide an alternative to regex. According to the top comment on the docs page, what you could do is something like this:...

Inner Join 3 tables pl/sql

mysql,sql

The issue is that you are using the alias C where you should not, with the count function. This: C.Count(C.column6) should be: Count(C.column6) and the same change applies in the order by clause (which might be counting the wrong column - should it not be column6?): order by C.count(column5) desc...

Fatal error in if stament [duplicate]

php,mysql,mysqli

Just change the condition to: if(isset($_REQUEST['userid']) && $_REQUEST['userid'] > $user_hack) isset tells is a variable is set, while this statement may be true or false, on which you cannot call isset function. Until you check if(isset($_REQUEST['userid'])), you cannot assign it to $userid variable....

php mysql select insert on different DBs in same server

php,mysql,insert-into

If your connection has rights to both databases, there is no need to have two connections. A database connection is a connection to the server, not to a specific database (although you can select a default database within the connection). What you can do is normal INSERT INTO SELECT within...

Using Sum in If in Mysql

mysql,sql,select,sum

Using least would be much easier: SELECT LEAST(SUM(my_field), 86400) FROM my_table ...

Using VLOOKUP formula or other function to compare two columns

mysql,excel,vba,date

If data in your first table starts at A2, and your other column starts at D2, then use in E2 =VLOOKUP(D2,$A$2:$B$17,2,0) Copy down as needed....

concatenate field names in a mysql update with inner join

php,mysql

You can try as per below- UPDATE products pr INNER JOIN sub_categories sc ON sc.id = pr.sub_category SET slug = REPLACE(TRIM(LOWER(CONCAT(sc.subcat_name,'.',products.product_name))),' ', '-'); ...

Select most recent entry where timestamp is from the past hour

mysql

select * from table where TIMESTAMPDIFF(HOUR, `timestamp`, NOW())=0 ORDER BY `timestamp` DESC LIMIT 1 ...

Purging Database - Count purged/not-purged tables

mysql,sql,sql-server,database,stored-procedures

The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...

SQL only one cell from column can have X value

mysql,sql

Have a separate table with one row that contains the mainOfferName of the row that you want. Then query as: select yt.*, (nt.mainOfferName is not null) as valueFlag from yourtable yt left join newtable nt on yt.mainOfferName = nt.mainOfferName; When you want to change the value, change the value in...

C# MySQL Parameters.AddWithValue

c#,mysql

You try to add all your 52 parameter and their values with one AddWithValue method. You can't do that. First of all, you need to define all your parameters in your command with your column names like; command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname, id, projectnummber....) VALUES (?name, ?id, ?projektnummer....)"; Then...

MySQL - How can I know my query is tuned?

mysql,performance,explain

Except for trivial queries, there is no way to know if you have the optimal query & indexes. Nor can you get a metric for how well designed the schema and application are. 3 seconds on a cold system for a 3-way JOIN with "Rows" of 409, 45, 1 is...

SQL Group By multiple categories

php,mysql,sql,mysqli

Just include a case statement for the group by expression: SELECT (CASE WHEN Categories.name like 'Cat3%' THEN 'Cat3' ELSE Categories.name END) as name, sum(locations.name = 'loc 1' ) as Location1, sum(locations.name = 'loc 2') as Location2, sum(locations.name = 'loc 3') as Location3, count(*) as total FROM ... GROUP BY (CASE...

How to call MySQL view in Struts2 or Hibernate

java,mysql,hibernate,java-ee,struts2

You can simply create an Entity, that's mapping the database view: @Entity public class CustInfo { private String custMobile; private String profession; private String companyName; private Double annualIncome; } Make sure you include an @Id in your view as well, if that's an updatable view. Then you can simply use...

compare today's date with unix timestamp value in database

php,mysql

If I understand correctly you have a unix timestamp in a varchar field and you can't change this. If you compare the unix timestamp directly you will only get results that match the exact second of the timestamp. You can use FROM_UNIXTIME() to convert the timestamp in a date value...

PHP: While loop not working after adjusting SELECT for SQL injection prevention

php,mysql,select,sql-injection,associative-array

You cannot bind column and table names, only data. You need to specify the table and then bind for your '%calendar weekday%'. $stmt = $conn->prepare("SELECT " . $selectLang . " FROM `TranslationsMain` WHERE `location` LIKE ? ORDER BY `sortOrder`, " . $selectedLang); $stmt->bind_param('s', $calendar_weekday); ...