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 "<br />";
echo $row["ID"];
echo "<br />";
echo $row["Zending_ID"];
echo "<br />";
echo $row["Klant_ID"];
echo "<br />";
echo $row["Mand_ID"];
echo "<br />";
echo $row["Bestemmeling_ID"];
echo "<br />";

}

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

[Psycho]

So, the 'Shipments' table is the combinations table? If I am understanding this correctly, each record in the 'shipments' table has a foreign key reference back to primary IDs in the other four tables. If that's correct, then you simply need to JOIN the tables. However, there would potentially be a problem. You say that the ID fields from each table is labeled ID. Because you have those IDs as foreign key fields int eh shipments table with distinctive names you won't have a problem referencing the IDs in the result set. But, if you have other fields with the same names in those tables it is going to be more difficult in referencing the values. I try to always use unique names in may tables. (E.g. If I have a users and a clients table, I would use user_name and client_name rather than use 'name' in both).  Having said that, it it werdatabase, I would rename the ID fields in each of the tables to be Zending_ID, Klant_ID, etc. It makes it easier to work with the data and JOINing because more logical.

 

Anyway, with what you have you should be able to get the data using:

SELECT *
FROM Shipments    AS s
JOIN Zendingen    AS z ON z.id = s.Zending_ID
JOIN Klanten      AS k ON k.id = s.Klant_ID
JOIN Manden       AS m ON m.id = s.Mand_ID
JOIN Bestemmeling AS b ON b.id = s.Bestemmeling_ID
WHERE Zending_ID = 9

 

If you were to make the ID fields the same as the names in the shipment's table, it becomes even easier

SELECT *
FROM Shipments    
JOIN Zendingen    USING(Zending_ID)
JOIN Klanten      USING(Klant_ID)
JOIN Manden       USING(Mand_ID)
JOIN Bestemmeling USING(Bestemmeling_ID)
WHERE Zending_ID = 9

 

[Stickie]

Thank you for posting!

 

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

 

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 cears my problem a bit.

[Stickie]

Okay

 

I've tried this and came up with:

 

$test = mysql_query("SELECT * FROM Shipments AS s JOIN Zendingen AS z ON z.ID = s.Zending_ID JOIN Klanten AS k ON 

k.ID = s.Klant_ID JOIN Manden AS m ON m.ID = s.Mand_ID JOIN Bestemmeling AS b ON b.ID = s.Bestemmeling_ID WHERE 

Zending_ID = 9") or die(mysql_error());


echo $test;

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

 

EDIT:

Found my error in displaying the values, but now I'm stuck in what seems an endless loop.

What am I missing?

[Stickie]

PROBLEM solved, using this code:

 

$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 "<table><tr>";
echo "<td>Bestemming</td>";
echo "<td>Naam</td>";
echo "<td>Aantal Duiven</td>";
echo "<td>Naam</td>";
echo "</tr>";
echo "<tr>";
echo "<td>" .$test2["Bestemming"]. "</td>";
echo "<td>" .$test2["Naam"]. "</td>";
echo "<td>" .$test2["Aantal_Duiven"]. "</td>";
echo "<td>" .$test2["Name"]. "</td>";
echo "</tr></table>";
$i++;
}

[Psycho]

That is pretty much what I provided to you previously, except:

 

1. I did not list out the individual fields (since you did not provide that information)

2. Your query does not limit the results based upon the Shipments.Zending_ID as you originally showed.

 

I really prefer to explicitly JOIN my tables (as I showed in the query I provided) rather than to do it in the WHERE caluse as you have much more flexibility on how the JOINs work.

 

However, you should not be using $i in that code to process the DB results. Instead, just use

while ($test2 = mysql_fetch_assoc($result))

 

Also, do you really want a new table for each record (with new column headers)? It seems that one set of column headers would be better.

 

Here is a revise of what you had in a more logical format. Note: I guesses on which fields were coming from which tables. Make sure each field name is prefaced with the appropriate table alias

 

$query = "SELECT z.Naam, b.Bestemming, k.Aantal_Duiven, m.Name
          FROM Shipments    AS s
          JOIN Zendingen    AS z ON s.Zending_ID = z.ID
          JOIN Klanten      AS k ON s.Klant_ID = k.ID
          JOIN Manden       AS m ON s.Mand_ID = m.ID
          JOIN Bestemmeling AS b ON s.Bestemmeling_ID = b.ID
          WHERE z.Actief = 1";
$result = mysql_query($query) or die(mysql_error());

echo "<table>\n";
echo "<tr>\n";
echo "  <th>Bestemming</th>\n";
echo "  <th>Naam</th>\n";
echo "  <th>Aantal Duiven</th>\n";
echo "  <th>Naam</th>\n";
echo "</tr>\n";
while ($row = mysql_fetch_assoc($result))
{ 
    echo "<tr>";
    echo "  <td>{$row['Bestemming']}</td>\n";
    echo "  <td>{$row['Naam']}</td>\n";
    echo "  <td>{$row['Aantal_Duiven']}</td>\n";
    echo "  <td>{$row['Name']}</td>\n";
    echo "</tr>\n";
}
echo "</table>\n";

[Stickie]

Thank you for the reply.

 

I've tried your code, works like a charm. 

 

So basically,

 

It fetches the attributes I specify but only if they are in the combinations table and joins the specified tables to get the client_name etc

The where is like an extra filter for the table Zendingen, so if I'd add where Active=1 to E.G. clients table it would ignore all clients with Active=0?

 

pls correct me if I'm wrong, just trying to understand the code here.

[Psycho]

I don't think I could really do an adequate job of explaining how that works without going into detail regarding JOINs. But, suffice it to say that the query is joining the records from the "Shipments" table to the corresponding records in the other four tables. This works for your current need because there is a one-to-one relationship between the "Shipments" table and the other tables. Learning the different types of JOINs and how to use them really unlocks the power of a relational database. I suggest you go and find a tutorial on how to do JOINs. Here is one that seems to have some good examples: http://mysqljoin.com/