Multiple Database Queries |
[eluser]joshvermaire[/eluser]
I'm doing the following queries and can't figure out how to get the second query to work. How do I get an array of just the values from my first query and use that in my second? Code: function application() If I comment out the second query and Code: var_dump($row); array(2) { [0]=> array(1) { ["app_id"]=> string(1) "2" } [1]=> array(1) { ["app_id"]=> string(1) "3" } } I decided to do multiple queries instead of a join because I will be adding additional columns to select from the second query.
[eluser]Jaketoolson[/eluser]
Personally, I'd rather have a robust SQL query than excessive code with multiple database queries. Maybe try setting $query to NULL after the first query? Code: $user_id = $this->tank_auth->get_user_id();
[eluser]joshvermaire[/eluser]
Jaketoolson, thanks for getting back to me on this. The problem is that in the second query, Code: $row['app_id'] Code: $row[0]['app_id'] Maybe a join is better, like you said. Could you help me with the code? Would I add the following to the first query? Code: $this->db->join('applications', 'applications.id = using.app_id'); Can I use method chaining to just select app_name from the join? Thanks.
[eluser]Jaketoolson[/eluser]
Code: $sql = "SELECT app_id a, app_name a FROM applications a Also, the reason you had to add the [key] to $row in the beginning, is because $row was returning multiple arrays of results (2). See -> Code: array { So your original query returned more than one app_id which is why the 2nd query bombs
[eluser]Jaketoolson[/eluser]
Also if you expect to return more than one app_id, you can rewrite the 2nd query to use a 'WHERE IN' statement. Code: foreach ($query->result() as $row) Which should read "SELECT app_name FROM `applications` WHERE id IN('1','2','3', etc).
[eluser]joshvermaire[/eluser]
When I use the 'WHERE IN' statement it only returns one value even though there is more than one app_id. I did this query and it works well: Code: SELECT `app_name` If there are a hundred different app_id values that I'm looking for, I'm guessing there's a more efficient way to process the query than a hundred OR statements. I'm assuming that's where you want somehting like a 'WHERE IN' statement, but unfortunately it wasn't working. Thoughts?
[eluser]Jaketoolson[/eluser]
WHERE x IN (1,2,3,4) should return the same results as WHERE x = 1 OR x = 2 OR x = 3 OR x = 4.... jaketoolson = msn PSJakeT = aim jaketoolson = yahoo
[eluser]Jaketoolson[/eluser]
FYI the following was occuring: Example: Code: $values = array( 1,2,3); CI returns the query as written: SELECT app_name FROM `applications` WHERE id IN('1,2,3'); Ok, we need to add apostrophes. Code: $values = implode(" ' , ' " ,$values); This then returns: SELECT app_name FROM `applications` WHERE id IN ('1\', '2\', '3\') Turns out this was reported as a bug, only to be rejected as not a bug but user error. Same problem here. And here with a weird workaround, assuming you wish the keys to be the 'searched' values. Is there something I'm missing as it pertains to preparing the array to be used in the 'where_in()' Active record class? |
Welcome Guest, Not a member yet? Register Sign In |