Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Trying to build a query...

Codes here !

Moderators: macek, egami, gesf

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
indomito
New php-forum User
New php-forum User
 
Posts: 5
Joined: Thu Oct 31, 2002 2:20 pm

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
DoppyNL
 

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.
indomito
New php-forum User
New php-forum User
 
Posts: 5
Joined: Thu Oct 31, 2002 2:20 pm

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
DoppyNL
 

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
indomito
New php-forum User
New php-forum User
 
Posts: 5
Joined: Thu Oct 31, 2002 2:20 pm

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.
TotallyInnocent
 

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.
indomito
New php-forum User
New php-forum User
 
Posts: 5
Joined: Thu Oct 31, 2002 2:20 pm

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
PHP Guru
 

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

Now THAT'S service!
TotallyInnocent
 


Return to mySQL & php coding

Who is online

Users browsing this forum: Google [Bot] and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.