MySQL Query for values that are not null, where key is part of a string

Question

I have a database setup as follows (simplified):

+----------+---------+----------+
|   path   |  val1   |   val2   |
+----------+---------+----------+
|    a/    |   two   |   cow    |
+----------+---------+----------+
|   a/b/   |   one   |   cat    |
+----------+---------+----------+
|  a/b/c   |   NULL  |   bat    |
+----------+---------+----------+

path is the primary key (in actual use, more like Smith/John/Mark/Jr).

PHP provides a path $mypath = "a/b/c";

Is there a way to build a result set of non null values, choosing from all rows whose path is part of $mypath.
That is, to search for the key that matches most exactly, find all non-null values in it, and if there are any nulls, to get them from the second-"best" key.

Eg. Mysql should check a/b/c and return a val2 = "bat", but see that val1 is set to NULL.
It should therefore check a/b/ and set val1 = "one";
The final result should be array('val1' => "one", 'val2' => "bat").

A friend told me there is a way with "LOCATE", but I couldn't work it out.
Is this doable?

And can someone who knows MySQL help me rewrite the title of this question to be more descriptive?

Thanks.

Nonym · Accepted Answer · 2011-11-15 22:16:39Z

1

Is the filter for the path strictly comparing from left to right, and no in-the-middle matches? It would appear to be the case but I'd like to make certain.

Also, I'm not sure what your logical basis/rule is for determining that III should be chosen, but here is an example that would have worked if val2 was a numeric column:

CREATE PROCEDURE `test`(IN phpPath VARCHAR(50))
BEGIN

SELECT A.val1, B.val2
FROM 
 (SELECT val1 FROM `yourtablehere` 
 WHERE val1 IS NOT NULL AND instr(phpPath, path) = 1 
 ORDER BY length(path) DESC) A
 , (SELECT val2 FROM `yourtablehere` 
 WHERE val2 IS NOT NULL AND instr(phpPath, path) = 1 
 ORDER BY length(path) DESC) B
LIMIT 1

END

Could you test this and let us know if you get desired results? (Again, assuming first that column val2 is numeric)

I also suggest that you use stored procedures than embedded SQL commands in your code, so I included the create syntax as well. Using stored procedures and proper parameter binding helps you fight SQL injection. To note, the SELECT command requires a parameter/variable to contain the basis for including a row.

[edit] I know the code is soooo inelegant, but could you try this one out and see if it does the trick? try to test it with different scenarios...

edited Nov 15, 2011 at 22:16

answered Nov 15, 2011 at 20:12

Nonym

6,2971 gold badge28 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

outis Over a year ago

Prepared statement parameters prevent injection, but procedures don't; they're just as vulnerable, since injection occurs when a query is submitted, which is before a procedure even comes into it.

Nonym Over a year ago

I meant it in conjunction with using PDO prepared statements with bindValue/bindParam --sorry to be unclear :-)

SamGoody Over a year ago

> strictly comparing from left to right, and no in-the-middle matches. I don't understand what you mean? > assuming first that column val2. Sorry, but val2 is NOT numeric. I didn't realize that would change anything. I've run the above query and the result is Min(val1) => NULL, Max(val2) => NULL

Nonym Over a year ago

Sorry! I switched the path and phpPath arguments to INSTR! :( Please try again. Could you tell me how you end up with III instead of the others?

SamGoody Over a year ago

Firstly - thanks alot for helping. I dunno how you grok this stuff. Secondly, I updated the variables in the question for clarity. Thirdly, running your corrected query returns the full row of a/b/c, instead of val2=>bat. Third, to explain - I want bat, because that is the val2 whose key best matches my string, than one because that is the val1 with a key that best matches my string. Assuming with both that NULL is unnacceptable.

|

outis · Accepted Answer · 2011-11-15 20:19:57Z

0

LOCATE(x,y) returns the position of x in y (or 0, if x isn't a substring). If x is a prefix for y, then LOCATE(x,y) = 1, which is what you'd use as a condition. If you don't want 'Smith/Jo' to match 'Smith/John/Mark/Jr', then you'll need to add something to the condition to prevent this match, such as concatenating '/' to the path column value, which will affect performance since MySQL won't be able to use any index on the path column. As for finding the longest path match ("best" is a terribly nondescriptive word and should usually be avoided), you can use ordering to get the longest first and LIMIT to ignore shorter results.

SELECT val2
  FROM tbl
  WHERE (path = ? OR LOCATE(CONCAT(path, '/'), ?) = 1)
    AND val1 IS NOT NULL
  ORDER BY STRLEN(path)
  LIMIT 1

answered Nov 15, 2011 at 20:19

outis

77.7k23 gold badges155 silver badges228 bronze badges

2 Comments

SamGoody Over a year ago

I get a null result set on this query. Not sure what I might be doing wrong

SamGoody Over a year ago

My bad, I had made a mistake when simplifying. I've updated the data set in the question. When correcting [and this has already been helpful], I get the topmost row using your query. But I don't want the topmost row - I want to merge the non-null results together. Is there any way to do that.

SamGoody · Accepted Answer · 2011-11-18 08:52:24Z

In conclusion:
It is probably impossible to do as requested using only SQL.
However, SQL can get an multidimensional array from the matching keys, and PHP can whittle it down to one array.

If anyone can improve this to only use SQL (as in the question posted), it would be much appreciated.

SQL:

SELECT * FROM `mytable` WHERE `path` = '$mypath' 
OR (LOCATE(`path`, '$mypath') = 1 AND RIGHT(`path`, 1) = '/') 
ORDER by length(`page`) desc;

PHP:

$results = mysql_query(... sql ...);
$pg = array();
foreach(array_keys($results[0]) as $key)
    foreach($results as $result)
        if ($result[$key]){ $pg[$key] = $result[$key]; continue 2; }

You can see this in practice at http://codepad.org/UnyEZJ6a

Notes:

locate (used by Outis), and instr (used Nonym) are the same, except for that the order of arguments is reversed.
right() is used in the query to ensure that the path is split only on the forward slash.
The parenthesis used in the OR is technically redundant, as AND is evaluated before OR.
If there is a solution in sql, I have been told it would use the group_by command.

Hope this can help someone else!

Collectives™ on Stack Overflow

MySQL Query for values that are not null, where key is part of a string

3 Answers 3

8 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related