Page 1 of 2
Selecting items from one table that is not found in another
Posted: Fri Aug 21, 2009 9:04 am
by james20
I have two tables and I want to pull all values from the first table that do not have their ID in the second table.
To get all the items from table1, I do this (which works fine):
$db->CacheGetAll("SELECT * FROM `{$tables['items']['name']}` WHERE {$user_where}");
TO get all the items from table2, I use this query:
$db->CacheGetAll("SELECT * FROM `{$tables['item_edits']['name']}` WHERE {$user_where}");
The table 'items' has a field 'ID' that I want to compare with the field 'ITEM_ID' in the 'item_edits' table. If the 'ITEM_ID' in any row in 'item_edits' equals the 'ID' field from the 'items' table, then I want to exclude those from the query.
I have tried different things, but without any success. Can someone help me on getting this query right?
Re: Selecting items from one table that is not found in another
Posted: Fri Aug 21, 2009 9:15 am
by Eran
you should read up on joins, which allow you to fetch data from multiple tables by stating (or not) the relationships between them (such as the one you gave between the items and item_edits tables).
Re: Selecting items from one table that is not found in another
Posted: Fri Aug 21, 2009 1:49 pm
by james20
I looked at join, but it doesn't seem to do what I want. This is the closest I got, but there's syntax errors I can't figure out:
"SELECT * FROM `{$tables['items']['name']}`,`{$tables['item_edits']['name']}` WHERE
`{$tables['items']['name'].USER_ID}` = '{user_id}' AND
`{$tables['items']['name'].ID}` != `{$tables['item_edits']['name'].ITEM_ID}`";
Re: Selecting items from one table that is not found in another
Posted: Fri Aug 21, 2009 2:05 pm
by Eran
You can't quote both the table and the column together with back-ticks, they need to be quoted separately (this is probably the syntax error you are receiving).
In addition, your second condition is not a good one. Try running this query:
[sql]SELECT * FROM `items`LEFT JOIN `item_edits` ON `items`.`ID` = `item_edits`.`ITEM_ID`WHERE `items`.`USER_ID` = {user_id} AND `item_edits`.`ITEM_ID` IS NULL[/sql]
By the way, in the future please wrap your queries with [sql] tags
Re: Selecting items from one table that is not found in another
Posted: Sat Aug 22, 2009 9:42 am
by james20
Thanks a lot! That works perfectly when I query through phpMyAdmin, but for some reason the EXACT same code will not work when run through php. I know for a fact my syntax is correct and my looping is fine.
Re: Selecting items from one table that is not found in another
Posted: Sat Aug 22, 2009 9:50 am
by Eran
can you paste here the code you have?
Re: Selecting items from one table that is not found in another
Posted: Sat Aug 22, 2009 11:16 am
by james20
query:
Code: Select all
$results = $db->CacheGetAll("SELECT * FROM `items` LEFT JOIN `item_edits` ON
`items`.`ID` = `item_edits`.`ITEM_ID` WHERE
`items`.`USER_ID` = {user_id} AND
`item_edits`.`ITEM_ID` IS NULL");
make the variable available in my template:
Code: Select all
$tpl->assign('results', $results);
loop through each item (smarty code):
Code: Select all
{foreach from=$results item=elem name=items}
{$elem.ID}<br />
{/foreach}
I use the exact same method for all of my queries and things work fine, except for this one. Again, the query you provided works perfect when I run it through phpMyAdmin. I can't spot any reason why it shouldn't be working.
Re: Selecting items from one table that is not found in another
Posted: Sat Aug 22, 2009 11:23 am
by Eran
In your query, you forgot the dollar sign in front of the variable.
should be
This is why I use concatenation instead of the curly brackets to add variables to a string.
Code: Select all
"`items`.`USER_ID` = " . user_id . " AND "
Would have given you a warning
Re: Selecting items from one table that is not found in another
Posted: Sat Aug 22, 2009 11:30 am
by james20
Thanks. That gets me to the point of being able to look the correct number of times, but there's still a slight problem.
I tried:
Code: Select all
{foreach from=$results item=elem name=items}
ID: {$elem.ID}<br />
{/foreach}
and it just prints "ID:" eight times. So it loops the correct amount of times, but I'm not able to pull the data like I used to.
Re: Selecting items from one table that is not found in another
Posted: Sat Aug 22, 2009 11:32 am
by Eran
I don't use smarty, so I can't help you with smarty-specific syntax. perhaps someone else here knows more.
Re: Selecting items from one table that is not found in another
Posted: Sat Aug 22, 2009 11:34 am
by james20
Well, that smart code works fine if I do a regular query. Do you think the Join may be returning a different "style" of record set?
Re: Selecting items from one table that is not found in another
Posted: Sat Aug 22, 2009 11:43 am
by Eran
You are using some sort of database abstraction library, so it's possible. Can't you dump the results to see what they return?
Re: Selecting items from one table that is not found in another
Posted: Sat Aug 22, 2009 12:11 pm
by james20
Yes. I can loop through and print the key, value pairs. The key is the field in the database (such as ID or ITEM_ID) and the value would be what's in there. It prints all of the keys for each iteration just fine, but there's no value to go along with it.
Re: Selecting items from one table that is not found in another
Posted: Sat Aug 22, 2009 12:15 pm
by Eran
Try to change the beginning of the query to indicate the table the columns are selected from:
[sql]SELECT items.* FROM ...[/sql]
If that doesn't work, try to specify the columns you want instead of using the wildcard
Re: Selecting items from one table that is not found in another
Posted: Sat Aug 22, 2009 12:21 pm
by james20
pytrin, Specifying the table before the wild card worked! Thanks, a lot!!