Re: [PHP-DB] Searching many-to-many map tables
Thanks Chris, By the way do you have problems with spammers getting your gmail address from usenet? The user_id is actually what I'm searching for dynamically, so my initial query was relatively correct as it returns one row with user 1. However, I wasn't familiar with the concept of self-joining, so many thanks for that. Still damn ugly so I see. Strange as I would have thought my problem isn't unique. Best practice seems to recommend using many-to-many tables instead of flat-tables yet I've yet to find an explanation of how to convert the flat-table query select * from users where group_id_1=1 and group_id_2=2 into a map-table query in a better way than I've done already. Steve Chris [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Steve McGill wrote: Hello, I am trying to find out how to search a many-to-many map table efficiently. I have an example table: user,user_group 1,1 1,2 2,1 3,2 I want to find out all the users who are a member of BOTH groups 1 AND 2. In this example, this would just be the user with id 1. Until now, I can either do this with multiple queries and using PHP array_intersect, or one really ugly MySQL query: select user, count(user_group) as num_groups_found from users_groups where group IN (1,2) GROUP BY user HAVING num_groups_found=2 Where's your userid check? You should be able to add that in as well. select user, count(user_group) as num_groups_found from users_groups where user='1' and group IN (1,2) GROUP BY user HAVING num_groups_found=2 i.e. narrows down the groups I'm looking for and makes sure that they are all found for a user It works quite reliably I think but it's such a rubbish query that I was hoping that somebody could teach me some syntax that is better. The problem is you want two values from the same table (group is '1' or '2'), so either you need to do the above or a self-join (as far as I know anyway!) :/ -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Searching many-to-many map tables
Hello, I am trying to find out how to search a many-to-many map table efficiently. I have an example table: user,user_group 1,1 1,2 2,1 3,2 I want to find out all the users who are a member of BOTH groups 1 AND 2. In this example, this would just be the user with id 1. Until now, I can either do this with multiple queries and using PHP array_intersect, or one really ugly MySQL query: select user, count(user_group) as num_groups_found from users_groups where group IN (1,2) GROUP BY user HAVING num_groups_found=2 i.e. narrows down the groups I'm looking for and makes sure that they are all found for a user It works quite reliably I think but it's such a rubbish query that I was hoping that somebody could teach me some syntax that is better. Many thanks in advance, Steve -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Copying a set of database records
A sort of similar question to my last one: I have a table with about 100 records all related to one product. I want to create a new product, based on the details from the old product. So in PHP, I would fetch the 100 records, alter the productID, and then insert it 100 in 100 separate SQL queries. Is there any way I can do this in one SQL query, somehow using a combination of insert and select? Thanks in advance! Steve -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Problem with mysql_fetch_array after first loop...
Jeffrey Baumgartner [EMAIL PROTECTED] schreef in bericht
news:[EMAIL PROTECTED]
I've made a little programme that deletes expired records from database
tables. The troublesome bit looks like this...
$query = SELECT ic FROM ic_ic WHERE date = CURDATE();
$result = mysql_query($query) or die(Unable to get old
campaigns because . mysql_error());
while ($row = mysql_fetch_array($result)){
extract($row);
...delete records from tables where ic = ic
}
It works fine for the fitst value of $ic, but on the second time around
I get an error message saying that line [starting with while...] is
not a valid MySQL resource. This really puzzles me because it works one
time around, but not after that.
$ic, incidentally, is a string originally generated from the timestamp
(ie. getdate[0]) - could this be causing a problem?
Why don't you try it without using extract($row), and use $row[variable1]
$row[variable2] instead in your loops.
extract() pulls all the variables into the global namespace and can be quite
dangerous as it might overwrite other variables. (this might be whats
happening with the $result variable for example.
also, how does extract() behave when trying to convert the [0] [1] [2]
variables? you might be better off using mysql_fetch_assoc()
Best wishes,
Steve
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Updating many records at a time
I have a situation where I am doing lots of complex sorting on records that
already exist in a MySQL table. The table has a 'sort_order' field which
means I can do a simple ORDER BY sort_order to keep it nice and quick on
SELECT, it's only UPDATE which is slow.
If I change the order that I want 1 rows to be displayed, I am currently
making 1 SQL queries such as:
UPDATE products SET sort_order=0 WHERE id='5';
UPDATE products SET sort_order=1 WHERE id='2';
UPDATE products SET sort_order=2 WHERE id='32';
and so on.
Obviously I'd love to be able to put all of this into one query.
Is there some way I can combine this with IN() ?
UPDATE products set sort_order= WHERE id IN('5','2','32');
Many thanks in advance for any help / advice.
Best wishes,
Steve
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Updating many records at a time
Steve McGill wrote:
I have a situation where I am doing lots of complex sorting on records
that
already exist in a MySQL table. The table has a 'sort_order' field which
means I can do a simple ORDER BY sort_order to keep it nice and quick on
SELECT, it's only UPDATE which is slow.
If I change the order that I want 1 rows to be displayed, I am
currently
making 1 SQL queries such as:
UPDATE products SET sort_order=0 WHERE id='5';
UPDATE products SET sort_order=1 WHERE id='2';
UPDATE products SET sort_order=2 WHERE id='32';
and so on.
Obviously I'd love to be able to put all of this into one query.
Is there some way I can combine this with IN() ?
UPDATE products set sort_order= WHERE id IN('5','2','32');
Many thanks in advance for any help / advice.
What is the exact operation you're doing? Is there any possibility you
can do UPDATE products SET sort_order=sort_order+1 WHERE sort_order
30 for e.g. - as in you insert an 'item' at spot 30 and want all the
rest below it to be bumped up one.
That's something I've done in the past, and it works well. But indeed, I am
letting the users completely change the order manually, using some
javascript, and letting them do it all in one go.
You can certainly use IN(), but there is a (quite high) limit on how
long the query string can be. It's pretty ridiculously high actually,
but something to consider.
Could you help me with the syntax for that? I said 10,000 as an example, but
it's more like 500. :-)
Personally, I'd look at how you're doing things - see if there isn't an
alternative way to do it like outlined above. I'm assuming these aren't
just some precalculated ordering, they're manually ordered for some
reason? If it's not 'we want to manually order them' - In a products
database, I would think you could just have an ORDER BY product_group,
product_name or some such.
They already get a choice between manual ordering, or using mysql to sort
using 'order by'. Sometimes they need specific control.
Best wishes,
Steve
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
