How would I do this?

[Hate]

Hi,

 

I have two tables in my database (d2b_users, d2b_statistics) and I'm trying to do a join of some sort (I'm not entirely sure what I'm doing here, but it should be pretty spectacular in the end if I can get it to work!  )

 

d2b_users: id, username

d2b_statistics: id, requests

 

The id in d2b_statistics is the same unique id from d2b_users (added with a trigger). I'm trying to update the requests number in the d2b_statistics, but I only have the username to work with and that exists in d2b_users. Since they have the same id I should be able to do a JOIN (not sure how) to access the same user in the d2b_statistics.

 

How exactly would I go about this? Also, is it possible to do the JOIN and find the number for the requests and increase it by one in a single query?

[gamesmstr]

Is this for a PHP application?  If so try this.  It isn't a single command, but should get you what you need.

Assume $username is the user name you have access to and $newrequests is the new value you want for requests..

$user=mysql_fetch_array(mysql_query("SELECT id FROM d2b_users WHERE username='$username'"));

mysql_query("UPDATE d2b_statistics SET requests='$newrequests' WHERE id='$user[id]'");

[Hate]

Is this for a PHP application?  If so try this.  It isn't a single command, but should get you what you need.

Assume $username is the user name you have access to and $newrequests is the new value you want for requests..

$user=mysql_fetch_array(mysql_query("SELECT id FROM d2b_users WHERE username='$username'"));

mysql_query("UPDATE d2b_statistics SET requests='$newrequests' WHERE id='$user[id]'");

 

Yes, this is for a php application. That's kind of why I posted it in the php help forums, but apparently that's still not enough reason for it to remain there.

 

I was really hoping we could get this into a single query, but I don't think that's possible so hopefully compacting it down into two queries. As it stands I'm having to do three queries to update the requests. In my three separate queries I first have to get the unique id for the person with the username on table d2b_users. Then I have to query and get the requests from d2b_statistics using the result I got from the first query. Then I need to do some really basic math (adding one to the number). After that I have to do another query to update the requests in d2b_statistics with the new number.

 

It's not really efficient and it would be a lot better to get it all into a single query, or at least two queries if nothing else.

[kickstart]

Hi

 

SQL something like this seems to be what you want:-

 

UPDATE d2b_statistics a
INNER JOIN d2b_users b
ON a.id = b.id
SET a.requests = a.requests + 1
WHERE b.username = ='$username'

 

All the best

 

Keith

[Hate]

Hi

 

SQL something like this seems to be what you want:-

 

UPDATE d2b_statistics a
INNER JOIN d2b_users b
ON a.id = b.id
SET a.requests = a.requests + 1
WHERE b.username = ='$username'

 

All the best

 

Keith

 

Keith, you're a genius.. your code works like a charm! I'm not entirely sure how it works though. Do you have any good resources I can read to better educate myself on the query you just constructed for me?

[kickstart]

Hi

 

It is a JOIN between 2 tables (much the same as in a SELECT statement), but being used to update one of the tables.

 

A JOIN links the rows from 2 (or more) tables together and returns the linked rows as a row.

 

All the best

 

Keith