Problem fetching data from different tables.

[Stickie]

Hi,

My name is stefan and I've been trying to develop a php/mysql based CRM for private use.

I've stumbled upon a problem a few days ago and I just can't figure it out, so if you could help me, I'd really appreciate it.

The problem is the following:

I have 1 database which contains 5 tables.
Each table has info in it but the primary key always is 'ID'
4 Tables are named; Zendingen | Klanten | Manden | Bestemmeling
The last table, named 'Combination' has the unique ID of each of those 4 in it. The example will be given below.

What I want to do now is create a page that shows all stored rows in 'Combination'-table, but gets the proper client_name or product_info out of the corresponding table.

I have searched for it myself but I have no clue where to begin and how to define my searches so they all stranded.

This is the piece of code.

$Shipm1 = mysql_query("SELECT * FROM Shipments where Zending_ID = 9") or die(mysql_error());

while($row = mysql_fetch_assoc($Shipm1)) {
echo "";
echo $row["ID"];
echo "";
echo $row["Zending_ID"];
echo "";
echo $row["Klant_ID"];
echo "";
echo $row["Mand_ID"];
echo "";
echo $row["Bestemmeling_ID"];
echo "";

}

This code returns:3 ---- the ID of the 'combination' table and thus primary key
9 ---- Zending_ID
47 --- Klant_ID
17 --- Mand_ID
2 ---- Bestemmeling_ID

4 This is another row from the combinations table,
9 notice that it only returns the Zending_ID = 9.
49
21
4

Now this gives me the info I want, but it doesn't displays them how I need it to.
I want it to search up each ID in the proper table and return me the product name, client name etc...

Anyone who can help or point me in the right direction?

Kind regards

Stefan

[pritaeas]

SELECT *
FROM Combination C, Zendingen Z, Klanten K, Manden M, Bestemmeling B
WHERE C.Zending_ID = Z.ID
AND C.Klant_ID = K.ID
AND C.Mand_ID = M.ID
AND C.Bestemmeling_ID = B.ID

[Stickie]

SELECT *
FROM Combination C, Zendingen Z, Klanten K, Manden M, Bestemmeling B
WHERE C.Zending_ID = Z.ID
AND C.Klant_ID = K.ID
AND C.Mand_ID = M.ID
AND C.Bestemmeling_ID = B.ID

Thank you for replying, but thats where I'm lost.
I need to display all values in combination table, but use a query to replace the ID's by the right name and product name.

will this do the trick?

[PsychicTide]

I'm not sure I totally follow what your trying to do, but I'm guessing you want to somehow link all tables together and then display all data. For this you could join the tables together using the 'combination' table for all attribute links.

IE: you want to link all the columns (IDs) from the combination table with the ID (primary key) of every other table... WHERE firstTable.attribute = secondTable.attribute

SELECT * FROM combination, Zendingen, Klanten, Manden, Bestemmeling WHERE combination.Zending_ID=Zendingen.Zending_ID AND WHERE combination.Klant_ID=Klanten.Klant_ID etc...

[PsychicTide]

pritaeas beat me to it :)

[PsychicTide]

I need to display all values in combination table, but use a query to replace the ID's by the right name and product name.

will this do the trick?

yes, the solution he/she posted pretty much creates one huge table out of all of the tables.... if you want to access a specific column of a table you would type something like...

SELECT attribute FROM table1, table2 WHERE table1.value=table2.ID

If the attribute has the same name on another table... IE: you have a customer table with a column called name and another table you have joined with a column called name then you would have to specify which table to pull the name from...

SELECT table2.attribute FROM table1, table2 WHERE table1.value=table2.ID

[Stickie]

pritaeas beat me to it :)

Thanks for the big support and fast response :)

I will try that out right away and keep you posted!
If anything comes up, can I PM you or do I keep posting in here?

KR

[PsychicTide]

;) It may be a better idea to post any other questions here. That way if someone else runs into the same problem maybe this post will help them.

Please make sure to mark this thread as solved if the issue has been fixed.

AND WHERE

in the first post I submitted shouldn't have a 'WHERE' after 'AND'

[Stickie]

I have read your post and it is kinda what I want exept some details which I will try to explain using drawings :p

Combinations table:

ID|Zending_ID|Client_ID|Basket_ID|Destination_ID
----------------------------------------------------------------
1 | 9 | 47 | 13 | 7

now I want to display these values, which I already figured out.
but instead of displaying 9 and 47 and 13 and 7 I need it to go fetch them in different tables.

Table Clients:

ID |Client_Name|Adress |other info
--------------------------------------------------
47| Jeff |5th street| PAYS CASH

So it should lookup the ID 47 from combinations table and switch it with Jeff, I hope this clears my problem a bit.

[PsychicTide]

I'm just going to make up some tables and attributes here...
combinationTable:ID, Table1ID, table2ID
Table1:ID, name, address, email
Table2:ID, payment_method, date, customer_representative

To display all of this you would do something like this...

SELECT combinationTable.ID, name, address, email, payment_method, date, customer_representative
FROM combinationTable, Table1, Table2
WHERE combinationTable.Table1ID = Table1.ID AND combinationTable.Table2ID = Table2.ID

or if you are worried two tables may have the same attribute name, then this...

SELECT combinationTable.ID, Table1.name, Table1.address, Table1.email, Table2.payment_method, Table2.date, Table2.customer_representative
FROM combinationTable, Table1, Table2
WHERE combinationTable.Table1ID = Table1.ID AND combinationTable.Table2ID = Table2.ID

This will output only the things you specify instead of all the attributes from all tables (such as the numbers from the combination table).

Hope it helps

[Stickie]

This is what I came up with, but gives me a syntax error:

$test = mysql_query("SELECT Naam, Bestemming, Aantal_Duiven, Name FROM Shipments, Zendingen, Klanten, Manden, Bestemmeling WHERE Shipments.Zending_ID=Zendingen.Zending_ID AND WHERE Shipments.Klant_ID=Klanten.ID") or die(mysql_error());

What am I doing wrong here?

[PsychicTide]

AND WHERE Shipments.Klant_ID=Klanten.ID"

take out the second WHERE... it should be: SELECT column FROM table WHERE condition AND condition. Stating the obvious: In this particular case... the condition is a table join method

[Stickie]

take out the second WHERE... it should be: SELECT column FROM table WHERE condition AND condition

Okay, I have the feeling we're getting somewhere :)

After removing a dumb WHERE I now have this code:

$test = mysql_query("SELECT Naam, Bestemming, Aantal_Duiven, Name FROM Shipments, Zendingen, Klanten, Manden, Bestemmeling WHERE Shipments.Zending_ID=Zendingen.ID AND Shipments.Klant_ID=Klanten.ID") or die(mysql_error());

echo $test;

This shows me Resource id #2

gonna try showing the values to me :)

EDIT:

while($test2 = mysql_fetch_assoc($test)) {
echo "";
echo $test2["ID"];
echo "";
echo $test2["Bestemming"];
echo "";
echo $test2["Naam"];
echo "";
echo $test2["Aantal_duiven"];
echo "";
echo $test2["Name"];
echo "";
}

This works, but I'm stuck in an endless loop and can't seem to find a way to get around it.

[PsychicTide]

hmm... try...

$num_rows = mysql_num_rows($test);
$i = 0;
while ($i < $num_rows)
{
   //Your Code Here
   $i++;
}

computer scientists start counting at zero :). will start at the first row and go to the last row. let me know if it helps out...

[Stickie]

hmm... try...

$num_rows = mysql_num_rows($test);
$i = 0;
while ($i < $num_rows)
{
   //Your Code Here
   $i++;
}

computer scientists start counting at zero :). will start at the first row and go to the last row. let me know if it helps out...

Okay, I've tried

$num_rows = mysql_num_rows($test);
$i = 0;
while ($i < $num_rows)
{
while($test2 = mysql_fetch_assoc($test)) {
echo "";
echo $test2["Bestemming"];
echo "";
echo $test2["Naam"];
echo "";
echo $test2["Aantal_Duiven"];
echo "";
echo $test2["Name"];
echo "";
}
$i++;
}

But I think I forgot 1 thing.
I ONLY want to display the rows that are in the Shipments table. so if the client_id is not found in the Shipments_table it shouldn't show up.

[PsychicTide]

Once you use the join method the only thing that should be in the retrieved data is what is both in the combination table culomns(through table1ID) and the row (with all columns) in the other table which is associated with that row ID#.

Replace the old while loop with the new one... not combined. put the fetch assoc inside the loop

On a side note... I only know the basics of the difference between mysql_fetch_assoc() and mysql_fetch_array() not sure if its a factor or not...

[Stickie]

Okay, I've tried

$num_rows = mysql_num_rows($test);
$i = 0;
while ($i < $num_rows)
{
while($test2 = mysql_fetch_assoc($test)) {
echo "";
echo $test2["Bestemming"];
echo "";
echo $test2["Naam"];
echo "";
echo $test2["Aantal_Duiven"];
echo "";
echo $test2["Name"];
echo "";
}
$i++;
}

But I think I forgot 1 thing. I ONLY want to display the rows that are in the Shipments table. so if the client_id is not found in the Shipments_table it shouldn't show up.

I found what causes the loop.
I forgot to specify 2 parameters, trying this now... keeping you posted (this looks good btw!) :)

[PsychicTide]

$test = mysql_query("SELECT Naam, Bestemming, Aantal_Duiven, Name FROM Shipments, Zendingen, Klanten, Manden, Bestemmeling WHERE Shipments.Zending_ID=Zendingen.ID AND Shipments.Klant_ID=Klanten.ID") OR die(mysql_error());

    $num_rows = mysql_num_rows($test);
    $i = 0;
    while ($i < $num_rows)
    {
    $test2 = mysql_fetch_assoc($test);
    echo "";
    echo $test2["Bestemming"];
    echo "";
    echo $test2["Naam"];
    echo "";
    echo $test2["Aantal_Duiven"];
    echo "";
    echo $test2["Name"];
    echo "";
    $i++;
    }

[Stickie]

I couldn't thank you more.
It works.

Posting the working code here, strangely removing my loop out of yours didn't work, maybe I did it wrong. If you see room for improvement, feel free to say :)
I couldn't thank you enough, you gave me a better view on mysql and php!
I wish you a life filled with joy!

KR

Stickie

EDIT: Changed the code with your input, works! :)

<?php

$con = mysql_connect("****","****","****");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("****", $con);

$test = mysql_query("SELECT Naam, Bestemming, Aantal_Duiven, Name FROM Shipments, Zendingen, Klanten, Manden, 

Bestemmeling WHERE Zendingen.Actief=1 AND Shipments.Zending_ID=Zendingen.ID AND Shipments.Klant_ID=Klanten.ID AND 

Shipments.Bestemmeling_ID=Bestemmeling.ID AND Shipments.Mand_ID=Manden.ID") or die(mysql_error());

$num_rows = mysql_num_rows($test);
$i = 0;
while ($i < $num_rows)
{
$test2 = mysql_fetch_assoc($test);
echo "";
echo $test2["Bestemming"];
echo "";
echo $test2["Naam"];
echo "";
echo $test2["Aantal_Duiven"];
echo "";
echo $test2["Name"];
echo "";
$i++;
}

[PsychicTide]

Glad to know I could help...

I posted an example that may work as well, but don't fix whats not broken...
Could you please mark this thread as solved?

may the code be with you :)