₦airaland Forum

Dear Friends,
i am very new in programming, and i am just learning PHP MYSQL DATABASE.
I need some good programmers to provide me with sql to join and display the information stored in database table in the manner illustrated below:

.........table 1......................................................................table 2...............................................table 3--------------
id----name------age------------------------------------id---food--------drink--------------id-----marks------------position
1.......Ken...........15yrs...............................................1.....rice............coke--------------1.........50%.................1st
2.......John..........20yrs...............................................2 ....beans........fanta--------------2.........30%..................2nd

Now i need an sql statement that when someone clicks on any record from table 1, say on KEN, all record in the corresponding rows in the other two tables (table 2 and table 3) will be displayed. In other words, any record from table 1 (KEN) will serve as a master link to display the other records of Ken in table 2 and tables 3.

clikc on KEN-------------------------->>>display------>> rice, coke, 50%, 1st
click on JOHN ------------------------>>>display------>> beans, fanta, 30%, 2nd
and SO ON

Thanks so much guys.
08036730744

cyan2008:
Dear Friends,
i am very new in programming, and i am just learning PHP MYSQL DATABASE.
I need some good programmers to provide me with sql to join and display the information stored in database table in the manner illustrated below:

table 1 table 2 table 3
id name age id food drink id marks position
1 Ken 15 1 rice coke 1 50% 1st
2 John 20 2 beans fanta 2 30% 2nd

Now i need an sql statement that when someone clicks on any record from table 1, say on KEN, all record in the corresponding rows in the other two tables (table 2 and table 3) will be displayed. In other words, any record from table 1 (KEN) will serve as a master link to display the other records of Ken in table 2 and tables 3.

clikc on KEN-------------------------->>>display------>> rice, coke, 50%, 1st
click on JOHN ------------------------>>>display------>> beans, fanta, 30%, 2nd
and SO ON

Thanks so much guys.
08036730744

Ever heard of "Trigger" in mysql? Google it, you should be able to work something out.

Urine:
Ever heard of "Trigger" in mysql? Google it, you should be able to work something out.

Trigger? In joins?

Op, read on mysql joins

spikesC:
Trigger? In joins?

Op, read on mysql joins

Boss, you can use trigger to manipulate different tables in a database. I find it easier, that said I know it's not the only way.

Urine:
Boss, you can use trigger to manipulate different tables in a database. I find it easier, that said I know it's not the only way.

I think you missed a bit of his question. He is talking about selection here not manipulation.

Trigger is usually used when you need to execute pre-defined queries based on events.
The op just wants to select data from different tables related by a foreign key.

spikesC:
I think you missed a bit of his question. He is talking about selection here not manipulation.

Trigger is usually used when you need to execute pre-defined queries based on events.
The op just wants to select data from different tables related by a foreign key.

I get your point about the FK, but I followed his database design to make the suggestion but with your suggestion he has to redesign his table.

As for the trigger, a couple of months ago I wrote a code for an inventory system that had a sales and product table anytime a sale is registered the quantity in the product table reduces. I will put up the code for you tomorrow to see.

Urine:
I get your point about the FK, but I followed his database design to make the suggestion but with your suggestion he has to redesign his table.

As for the trigger, a couple of months ago I wrote a code for an inventory system that had a sales and product table anytime a sale is registered the quantity in the product table reduces. I will put up the code for you tomorrow to see.

Yes, this is a perfect example of the use of Triggers.

And honestly, I didn't bother to study his schema as it didn't display well

guys

Thanks for the contributions and advice. But i have not been able to solve the problem.
what i need to achieve is to display one or more data from a row in table 1
such that whoever clicks on it will get corresponding rows from the other two tables.

cyan2008:
guys

Thanks for the contributions and advice. But i have not been able to solve the problem.
what i need to achieve is to display one or more data from a row in table 1
such that whoever clicks on it will get corresponding rows from the other two tables.

ehmm, i think You need to work on your table design. Research on KEYS and table Relationship & sql Joins

@op, try this

SELECT t1.name, t1.age, t2.food, t2.drink, t3.marks, t3.position
FROM table1 t1 JOIN (table2 t2, table3 t3)
ON (t1.id=t2.id AND t2.id=t3.id);

or

SELECT t1.name, t1.age, t2.food, t2.drink, t3.marks, t3.position FROM table1 t1, table2 t2, table3 t3 WHERE t1.id=t2.id AND t2.id=t3.id

But Ur table design's a bit poor sha!

cyan2008:
guys

Thanks for the contributions and advice. But i have not been able to solve the problem.
what i need to achieve is to display one or more data from a row in table 1
such that whoever clicks on it will get corresponding rows from the other two tables.

Let's see your new table design.

Where you able to Run The Sample @abdul01 gave to you in phpmyadmin if it works the the remain lies back in HTML/PHP

abdul01:
@op, try this
SELECT t1.name, t1.age, t2.food, t2.drink, t3.marks, t3.position
FROM table1 t1 JOIN (table2 t2, table3 t3)
ON (t1.id=t2.id AND t2.id=t3.id);
or
SELECT t1.name, t1.age, t2.food, t2.drink, t3.marks, t3.position FROM table1 t1, table2 t2, table3 t3 WHERE t1.id=t2.id AND t2.id=t3.id
But Ur table design's a bit poor sha!

=======

Now Try something like this in your PHP file [can save it as "test.php"]

<?php

//NOTE: you must have define you database connection parameter


if(isset($_GET['id'])){

	$_GET['id'] = $myid;

	$slq = "SELECT t1.name, t2.food, t2.drink, t3.marks, t3.position FROM table1 t1, table2 t2, table3 t3 WHERE t1.id='$myid' t2.id='$myid' AND t3.id='$myid' LIMIT 1";
	$db_query = mysql_query($slq ) or die(mysql_error());

	while($get_details = mysql_fetch_array($db_query)){
		
		echo $get_details['food'] .", ". $get_details['drink'] .", ".$get_details['marks'] .", ".$get_details['position'];
	}


}


?>

<html>

<a href="test.php?id=1">KEN</a><br>
<a href="test.php?id=2">JOHN</a>

</html>

Urine:
Boss, you can use trigger to manipulate different tables in a database. I find it easier, that said I know it's not the only way.

triggers is totally unrelated to the issue here

sledjama:
triggers is totally unrelated to the issue here

Ok Sir ,but if you had taken time to read through the thread you will notice that has been clarified. So why did you raise the issue again?

Urine:
Ok Sir ,but if you had taken time to read through the thread you will notice that has been clarified. So why did you raise the issue again?

apologies for not reading through.