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?
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'];
}
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 :)
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
Try this:
"SELECT * FROM table1 as t1 Left JOIN table2 as t2 ON t1.id = t2.id WHERE t1.id = ".$somevalue.""
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
join.