Trying to build a query...

Codes here !

Moderators: macek, egami, gesf

indomito
New php-forum User
New php-forum User
Posts: 5
Joined: Thu Oct 31, 2002 2:20 pm

Trying to build a query...

Postby indomito » Thu Oct 31, 2002 2:30 pm

Hi everybody. This is my first post here :P

I'm trying to build a query to get some info from 3 tables at the same time, but I have some problems.

Let's suppose that these are the tables:

table1
id1
userid
id2
name1
...

table2
id2
name2
...

table3
id3
id1
...


Knowing the value of userid...

(1) I want to get all records from table1 with a certain value in the userid field. Ok, I've done this:

SELECT * FROM table1 WHERE userid = '$userid'


(2) I want to get the value of table2.name2 for each of the record I get with the previous query. I've done this:

SELECT * FROM table1, table2.name2 WHERE userid = '$userid' AND table1.id2 = table2.id2


(3) For each record I get with the previous query I want to COUNT all the rows in table3 where table3.id1 = table1.id1

Here is my problem. I tried with COUNT but it doesn't work. Am I doing something wrong or is not possible to do that?

Can anyone help me?

Thanks in advance :)

Sergio

DoppyNL

Postby DoppyNL » Fri Nov 01, 2002 12:33 am

Code: Select all

SELECT table1.*, table2.*, count(table3.id) FROM table1, table2, table 3 WHERE userid = '$userid' AND table1.id2 = table2.id2 AND table1.id1 = table3.id1 group by table1.id1


you may need to add some extra fields to the group by part to make sure you get the results you want.

Greetz Daan

indomito
New php-forum User
New php-forum User
Posts: 5
Joined: Thu Oct 31, 2002 2:20 pm

Postby indomito » Fri Nov 01, 2002 12:48 am

Thank you very much! :D

I don't know why, but I had the feeling it was not possible to use the GROUP BY clausule in PHP :roll:
Last edited by indomito on Fri Nov 01, 2002 12:58 am, edited 2 times in total.

DoppyNL

Postby DoppyNL » Fri Nov 01, 2002 12:50 am

indomito wrote:I don't know why, but I had the feeling it was not possible to use the GROUP BY clausule in PHP :roll:

Every query that mysql accepts can be run with PHP. PHP simply passes it on to mysql.

Greetz Daan

indomito
New php-forum User
New php-forum User
Posts: 5
Joined: Thu Oct 31, 2002 2:20 pm

Postby indomito » Fri Nov 01, 2002 12:58 am

Yes, sorry. I meant MySQL, not PHP. I though I read somewhere that you couldn't use that. Good to know I can use it :)

indomito
New php-forum User
New php-forum User
Posts: 5
Joined: Thu Oct 31, 2002 2:20 pm

Postby indomito » Thu Nov 07, 2002 9:31 am

Hi again.

Daan, I have a little problem with the query you gave me. It works perfectly for what I need except for one thing.

Using the database structure that I post in my first message I would like to get all records in table1 from a single user. I know the userid but my problem is that using this query...

Code: Select all

SELECT table1.*, table2.*, count(table3.id) FROM table1, table2, table 3 WHERE userid = '$userid' AND table1.id2 = table2.id2 AND table1.id1 = table3.id1 group by table1.id1


...I don't get the records that doesn't have a corresponding record in table3. For example, imagine that I store my customers in table1 and table3 is the table where I store each item I sell. If a user haven't bought anything yet, I will not get the user info using the previous query. Is there a way to get the user info even if he haven't bought anything? I've been trying with JOIN clausules but can't get them working. I would like to keep getting all the info I get with the previous query.

Any help would be appreciated :)

Cheers,
Sergio

TotallyInnocent

Postby TotallyInnocent » Thu Nov 07, 2002 10:00 am

indomito wrote:Daan, I have a little problem.......


Daan isn't posting at this forum anymore.

Can't tell you where he went, the moderators and admin of this site don't want that to be public. (they didn't kill him, I can tell you that)

As for your question, I know that removing table3 from your query will return all users. you'll lose the info from table3.
I'm not that good at mysql myself so I can't really help you with that.

indomito
New php-forum User
New php-forum User
Posts: 5
Joined: Thu Oct 31, 2002 2:20 pm

Postby indomito » Thu Nov 07, 2002 10:13 am

OK, I will call the A-Team and try to find him :D Thanks for your interest.

PHP Guru

Postby PHP Guru » Thu Nov 07, 2002 2:57 pm

The question has been answered in under 10 minutes with one happy visitor, if you want proof you can come and see for yourself

TotallyInnocent

Postby TotallyInnocent » Fri Nov 08, 2002 12:34 am

Now THAT'S service!


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 1 guest