Reference two SQL databases with a Join via PHP

Question

I have written an SQL statement which works in phpmyadmin. I have two databases on the same server which I have used an INNERJOIN to connect them and im getting the correct results. I am building this up in php and so far have done a few normal queries and got the correct results. I have an include for my db, which is connecting to one database, now I want to connect to two and im not sure how I reference this in in my php sql statement. Can someone please help.

I have this.

<?php
    error_reporting(0);
    include './includes/opendb.php';
    extract($_POST);

    $sql1 = mysql_query("SELECT f_clients.CLIENT_COMPANY,   vtmastertrail.consultant, f_clients.CLIENT_CODE, vtcards.description, vtmastertrail.inspdate_start, vtmastertrail.inspdate_end, vtcards.typeofcard, vtcards.colour, vtcards.frequency, vtcards.priorityon, vtmastertrail.islive, f_clients.CLIENT_DEFAULTINSPECTIONSELL
FROM `f_clients`
INNER JOIN tcards.vtcards ON tcards.vtcards.client_code = f_clients.CLIENT_CODE
INNER JOIN tcards.vtmastertrail ON tcards.vtmastertrail.card_id = tcards.vtcards.id
WHERE tcards.vtmastertrail.consultant = '".$con_name."'
AND tcards.vtmastertrail.inspdate_start >= '".$from_date."'
AND tcards.vtmastertrail.inspdate_start <= '".$to_date."'");

    echo "<table border='1' align='center'>
      <tr>
        <th>Consultant</th>
        <th>Client Code</th>
        <th>Client</th>
        <th>Address</th>
        <th>Inspection Start Date</th>
        <th>Inspection End Date</th>
        <th>Type Of T-Card</th>
        <th>T-Card Colour</th>
        <th>Frequency</th>
        <th>Priority</th>
        <th>Report Sent Out</th>
        <th>Cost</th>
     </tr>";         

    while($row = mysql_fetch_array($sql1))
    {
        echo "<tr>";
        echo "<td>" . $row['consultant'] . "</td>";
        echo "<td>" . $row['client_code'] . "</td>";
        echo "<td>" . $row['title'] . "</td>";
        echo "<td>" . $row['description'] . "</td>";
        echo "<td>" . $row['inspdate_start'] . "</td>";
        echo "<td>" . $row['inspdate_end'] . "</td>";
        echo "<td>" . $row['typeofcard'] . "</td>";
        echo "<td>" . $row['colour'] . "</td>";
        echo "<td>" . $row['frequency'] . "</td>";
        echo "<td>" . ($row['priorityon'] ? 'Yes' : 'No') . "</td>";
        echo "<td>" . ($row['signedoff'] ? 'Yes' : 'No') . "</td>";
        echo "<td>" . $row['CLIENT_DEFAULTINSPECTIONSELL'] . "</td>";
        echo "</tr>";
    }

    echo "</table>";
}

Im basically getting no results from this search.

here is my db file.

<?php
    // ** database connection string
    $conn = mysql_connect("localhost", "#####", "########") or die ('I cannot connect to the database because: ' . mysql_error());

    // ** Tcards database
    mysql_select_db ("tcards");

Ive changed hidden the credentials. I have another database called 'test' which is where the f_clients table is. This is on the same server and has the same login credentials. Can anyone help me out please?

You definitely do not want to do extract($_POST); ever. This is the equivalent to "register globals" which was removed from PHP due to it being a huge security risk. Also, you should never put any data into an SQL query without being properly escaped. — Ian
– Ian, Commented Dec 9, 2015 at 15:42
Thanks, I seemed to have fixed it, errors in my query which seemed to work in phpmyadmin but not in php. Thanks for the advice for the extract function. What would be used in place of this? — user2405796
– user2405796, Commented Dec 11, 2015 at 11:37
You should use the $POST var directly, and instead of putting the values into the SQL query the way you are, use prepare statements. Take a look at this tutorial: markonphp.com/mysqli-select-prepared-statements — Ian
– Ian, Commented Dec 11, 2015 at 14:49

Kevin_Kinsey · Accepted Answer · 2015-12-09 15:49:42Z

1

You won't need to call mysql_select_db if you're planning to select from two separate databases.

Your query will need to use fully-qualified names (that means "test.f_clients.CLIENT_COMPANY", etc.). I think your ON clause will be OK as long as you fully qualify the DB names.

Note that MySQL functions (mysql_query, etc.) are deprecated; please consider switching to MySQLi or PDO. MySQLi is an easy switch.

Also pay attention to all Ian's comment above, about extracting the POST array and escaping data for your queries. You MUST code securely unless this is a company intranet behind a firewall someplace, and even then you cannot be assured it won't be exploited....

answered Dec 9, 2015 at 15:49

Kevin_Kinsey

2,3101 gold badge24 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2405796 Over a year ago

Thanks, for the advice. I will look into changing this to MYSQLi.

Shanka Nuwan · Accepted Answer · 2015-12-09 15:48:43Z

0

one thing you can do is add same user and password for both databases. or you can use view for overcome your problem. in practice i successful with adding same username and password to both databases. If you like try with "mysql workbench" that with easy to work with databases.

answered Dec 9, 2015 at 15:48

Shanka Nuwan

437 bronze badges

Collectives™ on Stack Overflow

Reference two SQL databases with a Join via PHP

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related