selecting data from a table based on data from another table

[Lambneck]

Hello, can someone please show me how to select all the data from a table based on the id of a row in another table?

 

for example something like this:

 

$sql = "SELECT * FROM $one_table WHERE tag='$id' IN $other_table ORDER BY id DESC LIMIT 25"; 

[abazoskib]

$sql = "SELECT * FROM $one_table as o,$other_table as o2 WHERE o.tag='$id' AND o.tag=o2.tag ORDER BY id DESC LIMIT 25"; 

 

It's called a join. They can be a burden and/or an advantage depending on how you use them.

[TeNDoLLA]

<?php
$idFromOtherTable = 10;
$sql = "SELECT s.* FROM some_table s
	  JOIN another_table a ON a.id = s.some_id  
	  WHERE a.id = $idFromOtherTable  
	  ORDER BY s.some_id DESC  
	  LIMIT 25";
$result = mysql_query($sql);

[Lambneck]

I get this error with the following code:

Error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY s.submission_id DESC LIMIT 25' at line 4 with query SELECT s.* FROM blog_tags s JOIN blog_tags a ON a.id = s.blogging WHERE a.id = ORDER BY s.submission_id DESC LIMIT 25

 

	$id = $_GET['id']; 
$idFromOtherTable = $row['post_id'];
$sql = "SELECT s.* FROM $table s
        JOIN $table1 a ON a.id = s.$id  
        WHERE a.id = $idFromOtherTable  
        ORDER BY s.submission_id DESC  
        LIMIT 25";

[abazoskib]

its sup[posed to be

 

	

$id = $_GET['id']; 



$idFromOtherTable = $row['post_id'];
$sql = "SELECT s.* FROM $table as s
        JOIN $table1 as a ON a.id = s.$id  
        WHERE a.id = $idFromOtherTable  
        ORDER BY s.submission_id DESC  
        LIMIT 25";

[TeNDoLLA]

In this line, do not use the $id variable in it.

 

JOIN $table1 a ON a.id = s.$id // incorrect

JOIN $table1 a ON a.id = s.id // correct

This line just joins the tables together based on the id fields. If you have to get the results for two different id's you will do it in WHERE part.

[Lambneck]

The variable $id is what identifies the row in $table1 that is calling on information in $table to be displayed. So $id must be present in the query. I just dont know where. 

[TeNDoLLA]

Could you please explain what are the two different id's? $_GET['id'] and $row['post_id'] and how they should be used? Maybe also provide the information how you have created your table structures. I don't understand why you have two different id's.

[Lambneck]

$id is the value of the row 'tag' being passed in the url.

'post_id' is the id of the corresponding posts in $table (submission_id)

 

$table1 looks like:

id
post_id
tag

 

$table looks like

submission_id
col_1
col_2
col_3 
col_4
col_5
...

 

[TeNDoLLA]

So you want to get all the submissions related to one post_id right? If so.. (did not test it but should work)

 

<?php
$id = $_GET['id']; // This is the id post_id

// Get submissions related to post_id
$sql = "SELECT s.* FROM post_table p
	  JOIN submissions_table s ON s.submission_id = p.post_id  
	  WHERE p.post_id = $id";
$result = mysql_query($sql);

[Lambneck]

Yeah  thats what I'm trying to do. Only the $_GET['id'] value isnt 'post_id' its the value of the row 'tag'. So the value of the row 'tag' is passed in the url gotten by $_GET['id']. Then this row identified by $_GET['id'] or '$id' has a column called 'post_id' in it which contains the id or the 'submission_id' of the information in the table containing the display data.

 

So the tables are connected via the post_id / submission_id number.

But the row initiating the connection is defined by the value passed in the url (which is contained in the 'tag' column)

 

-(I guess if I knew how to explain this better I wouldnt need the help. Sorry for the confusion)

[Lambneck]

*Bump*