CODEWITHSUNDEEP
php
Satch3000
Satch3000

Reputation: 49384

PHP SQL Query 2 Tables

I need to query the database with a php query.

I need to get the id that matches $somevalue and the with that ID query table 2 so I can get a field value contained in that row where the id is found.

$somevalue = '123';


Select id from table1 where $somevalue = id

...so we have the ID ... now we query table 2

select id, field2 from table2 where id = $id

echo $id;
echo $field2

How can I do this in a php query?

Upvotes: 0

Views: 88

Answers (5)

Keith Bloom
Keith Bloom

Reputation: 2439

You should be able to achieve this using a JOIN, here is an example using the the code from the question

SELECT t2.field2
FROM table1 t1
JOIN table2 t2
   ON t1.id = t2.id
WHERE t1.id = $someValue

What doesn't look right here is joining the tables on their Id columns. This is typically the primary key and unlikely to be used in both sides of a join.

A join is made from one table to another to reconstruct the data model. To make this a little more concrete I will change table1 to People and table2 to Addresses. The following query gets the StreetName for a particular person via the People table. In this case the People table has a column AddressId which holds the Id for this person in the Addresses table

SELECT a.StreetName
FROM People p
JOIN Addresses a
   ON p.AddressId = a.id
WHERE t1.id = $someValue

You can then apply whatever mechanism PHP offers to run the query

Upvotes: 0

Maulik Vora
Maulik Vora

Reputation: 2584

Simply try this. 

 select t2.id, t2.field2 from table2 t2, table1 t1 where t1.id = t2.id and t1.id = $someValue

Full Code is as below

$query = sprintf("select t2.id, t2.field2 from table2 t2, table1 t1 where t1.id = t2.id and t1.id = '%s'",
    mysql_real_escape_string($somevalue));

// Perform Query
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {
    echo $row['id'];
    echo $row['field2'];
}

Upvotes: 1

Sebastjan
Sebastjan

Reputation: 1147

Use inner join

SELECT table2.id table2.field FROM table1
INNER JOIN table2 ON table2.id = table1.id

haven't test the code, and i am fairy new to SQL (joins). But you see the patern :)

Upvotes: 0

Dawid Sajdak
Dawid Sajdak

Reputation: 3084

Try this:

"SELECT * FROM table1 as t1 Left JOIN table2 as t2 ON t1.id = t2.id WHERE t1.id = ".$somevalue.""

Upvotes: 0

Mayukh Roy
Mayukh Roy

Reputation: 1815

Your query syntax should look like:

Select id from table1 where id = $somevalue

But why you want the same id, with which you are searching? You could directly do this:

select id, field2 from table2 where id = $somevalue

Looks like complete mess :(

Anyway, you can Check This link on how to execute queries in PHP

Upvotes: 0

Related Questions