CODEWITHSUNDEEP
phpmysql
ThLiou
ThLiou

Reputation: 17

Select data from table using data from another table

I have 2 tables, one is called companies (with columns: title, finance, address, phone....) and the other is called investments (with columns: title, budget....).

The title value is same in both tables example: google inc as title is stored in both tables.

All I want to do is display data from table "investments" to a page called companies-profile using title as key.

companies-profile page shows data based on id that I get from another page (where all companies are displayed).

I use this code:

<?php

    $var1 = "SELECT title 
            FROM companies 
            WHERE idcompanies='$ID'";

    $result2 = mysqli_query($conn, "SELECT budget 
                                    FROM n4399 
                                    WHERE title='{$var1}'") 
                or die(mysqli_error($conn));

    $row2   = mysqli_fetch_array($result2);
    echo $row2['budget'];

?>

$conn is declared and database conection is ok

i m getting:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2'" at line 1

2 is the id that is selected by the user Using xamp

Upvotes: 0

Views: 1238

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a single query 

SELECT budget 
FROM companies 
INNER JOIN n4399 ON companies.Title=n4399.title
WHERE companies.id = ?

and using a proper prepared statement and binding 

$stmt = $conn->prepare("SELECT budget 
        FROM companies 
        INNER JOIN n4399 ON companies.Title=n4399.title
        WHERE companies.id = ?");
$stmt->bind_param("i", $ID);
$stmt->execute();

// 
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
  echo $row['budget'];
}

Upvotes: 2

Raul Luna
Raul Luna

Reputation: 2046

You are trying to execute the following sql sentence: 

SELECT budget 
  FROM n4399 
 WHERE title='SELECT title 
        FROM companies 
        WHERE idcompanies='$ID''

Either of two: or you first execute the firs sentence (select title from companies...) getting the effective title of the company or you can change your select into this: 

SELECT budget 
  FROM n4399 
 WHERE title in (SELECT title 
        FROM companies 
        WHERE idcompanies='$ID')

So that this select can return all the values that have matching titles in the second select.

Upvotes: 0

Related Questions