ansaurus

Question

PHP VLookup (conditional match) Alternative?

Answer 1

+1 A:

Try the following query. Then, you can do it all with one request to the database.

SELECT salaries.id AS salaries_id, salaries.name, salaries.remaining, 
salaries.contract_value, contracts.provision_id AS provision_id 
FROM salaries LEFT JOIN contracts ON contracts.id=salaries.id

scompt.com 2009-07-20 18:28:42

You're dead-on though I left out one important thing. Provision_id is a null field in which some people do not yet have contract provisions. This query did not return those people w/o provisions. There's probably a conditional null workaround to that though I am learning as I go so if you know how to tweak, please let me know. In in the meantime, I'll seek out the answer.Thanks!

Adam 2009-07-20 18:40:04

Or maybe I mean to say that it is pulling only people that have ids in both tables. the contracts table is very incomplete at this point.

Adam 2009-07-20 18:42:06

I just edited the SQL to make it a LEFT JOIN like you had in your original question. That should get all entries in the salaries table.

scompt.com 2009-07-20 18:48:21

So sorry. I think I'm turning delirious after all of this time spent. I have multiple contract provision_ids for each person, each id representing a clause in the contract. Each clause has its own row in contracts table. I was mistaken when saying you were right on before because that solution prints a duplicate row for each person that has more than one provision. I have edited my question w/ Appendix 1 to show what is now happening.

Adam 2009-07-20 19:09:43

I now put everything online: http://beerlyrics.com/jimmy-jones/Trying to get that 1,2 into just JD Drew's row. Now you can see what I'm really up to :/

Adam 2009-07-20 20:48:57

Answer 2

A:

Had to seek professional help for this as I'm still self-teaching. He used a technique called conditional shorthand which made it so that $contracts query only went to work for the rows in which 'id' matched between the two tables.

Winning code snippet:

     foreach ($contracts as $contract)
                    {
                        echo ( $content->id == $contract->id ) ?
                             '<a href="' . $contract->source . '" tooltip="' . $contract->provision . '">' . $contract->provision_id . " " . '</a>' : '';
                     } 
                   ?>
                </td>

<?php };

He sent me this link: http://www.bradino.com/php/ternary-operator-conditional/ You can find him at http://travisballard.com/.

Cheers!

Adam 2009-07-20 22:27:20

ansaurus

tags:

views:

answers:

PHP VLookup (conditional match) Alternative?

related questions