Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><a href="https://stackoverflow.com/q/12859942"><strong>Please, don't use <code>mysql_*</code> functions in new code</strong></a>. They are no longer maintained <a href="https://wiki.php.net/rfc/mysql_deprecation" rel="nofollow noreferrer">and are officially deprecated</a>. See the <a href="http://php.net/mysql-connect" rel="nofollow noreferrer"><strong>red box</strong></a>? Learn about <a href="http://en.wikipedia.org/wiki/Prepared_statement" rel="nofollow noreferrer"><em>prepared statements</em></a> instead, and use <a href="http://php.net/pdo" rel="nofollow noreferrer">PDO</a> or <a href="http://php.net/mysqli" rel="nofollow noreferrer">MySQLi</a> - <a href="http://php.net/mysqlinfo.api.choosing" rel="nofollow noreferrer">this article</a> will help you decide which. If you choose PDO, <a href="http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers" rel="nofollow noreferrer">here is a good tutorial</a>.</p> <hr> <p>While it is theoretically possible to join tables from two different databases <em>on the same server</em>, what you are trying to do cannot possibly work because you appear to be accessing two different servers.</p> <p>In order to get the result set you want you will need to combine them manually.</p> <p>For example (using PDO):</p> <pre><code>$dsn1 = "mysql:host=$host;dbname=$db_name"; $dsn2 = "mysql:host=$host2;dbname=$db_name2"; try { // Create the connections $db1 = new PDO($dsn1, $username, $password); $db1-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db1-&gt;setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $db1-&gt;setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db2 = new PDO($dsn2, $username2, $password2); $db2-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db2-&gt;setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $db2-&gt;setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); // Get the initial recordset $sql1 = " SELECT InterestedEntityId, Score FROM `user_interests` WHERE UserId = :userId ORDER BY Score DESC "; $stmt1 = $db1-&gt;prepare($sql1); $stmt1-&gt;bindParam('userId', $userID, PDO::PARAM_INT); $stmt1-&gt;execute(); // Prepare the statement for the second database $sql2 = " SELECT Name FROM entities WHERE Id = :entityId "; $entityId = 0; $stmt2 = $db2-&gt;prepare($sql2); $stmt2-&gt;bindParam('id', $entityId, PDO::PARAM_INT); // Loop the first result set $result = array(); foreach ($stmt1 as $row1) { // Fetch the related data from the second DB $entityId = $row1['InterestedEntityId']; $stmt2-&gt;execute(); $row2 = $stmt2-&gt;fetch(); // Construct the final result row and store it $result[] = array( 'InterestedEntityId' =&gt; $row1['InterestedEntityId'], 'Score' =&gt; $row1['Score'], 'Name' =&gt; $row2['Name'] ); } } catch(PDOException $e) { die($e-&gt;getMessage()); } // The result set you want should now be available var_dump($result); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POJoining two tables from different databases
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USDaveRandom
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI thought of that, but doing so requires that I fetch the name, then append with every row of that table. Which is wasteful, not to mention takes too much time.
      singulars
      1. PO
      1. USShane
    2. CO@Shane If you are dealing with two different database servers, there is literally no other way. If the value of both `$host` and `$host2` is always going to be the same then you can look at a join - but you won't need the two connections in that case. If you are dealing with two different servers as you code suggests, check my edit.
      singulars
      1. PO
      1. USDaveRandom
    3. CO@Shane There are quicker ways to do it than that (eg, your script reads one table, saves the contents in a CSV and then uses LOAD TABLE... to put that CSV into the other DB), but still not fast.
      singulars
      1. PO
      1. USKickstart
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload