Need help with outer join command

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • edefikh
    New Member
    • Sep 2007
    • 5

    #1

    Need help with outer join command

    I have a database called inventory, In it I have tables called network and environment. In the network table there is a row called ipaddr. I would like to be able to write a query that can find ip address, lets say 10.0.0.1 in the table called network, row called ipaddr, join the information with environment table and display the search result. I want to be able to search the inventory database by ip address and be able to see the complete result, based on data in both tables. What would be the statement ?
  • Atli
    Recognized Expert Expert
    • Nov 2006
    • 5062

    #2
    Hi. Welcome to TSDN!

    Consider this example...

    Say we have a database for a simple blog system:
    [code=mysql]
    CREATE TABLE User (
    UserID Int Unsigned Auto_Increment Primary Key,
    Name VarChar(150) Not Null
    );

    CREATE TABLE Blogg (
    BloggID Int Unsigned Auto_Increment Primary Key,
    Title VarChar(255),
    Body Text,
    Owner BigInt References User(UserID)
    );
    [/code]

    Now if I wanted to find all Blogs written by a single user, along with the user info, I could do this:
    [code=mysql]
    SELECT
    Blog.Title, Blog.Body,
    User.UserID, User.Name
    FROM User
    INNER JOIN Blog
    ON User.UserID = Blog.Owner
    [/code]

    Comment

    • edefikh
      New Member
      • Sep 2007
      • 5

      #3
      Thank you for that reply. Now I would like to add a search box to my wiki page and have users query the data by ipaddr string. Somehow I need to pass the string from the webpage to mysql and display the results back. Any ideas

      Comment

      • Atli
        Recognized Expert Expert
        • Nov 2006
        • 5062

        #4
        What kind of API are you using on your page?
        PHP?... ASP?... Something else?

        Comment

        • edefikh
          New Member
          • Sep 2007
          • 5

          #5
          Originally posted by Atli
          What kind of API are you using on your page?
          PHP?... ASP?... Something else?

          I am using PHP
          999999999999999 999999999999999 999999999999999

          Comment

          • Atli
            Recognized Expert Expert
            • Nov 2006
            • 5062

            #6
            Then you simply need to create a HTML form which sends the data to you PHP script, have your PHP script execute a query on your database and display the results.

            If you are having difficulties passing the data from the form, check out this article I wrote on the subject.

            If you need to know how to query the database, check out the MySQL Functions, or better yet, the Improved MySQL Extension at php.net

            Comment

            • edefikh
              New Member
              • Sep 2007
              • 5

              #7
              Originally posted by Atli
              Then you simply need to create a HTML form which sends the data to you PHP script, have your PHP script execute a query on your database and display the results.

              If you are having difficulties passing the data from the form, check out this article I wrote on the subject.

              If you need to know how to query the database, check out the MySQL Functions, or better yet, the Improved MySQL Extension at php.net
              here is the script I created, but when I execute it, nothing comes back in a webpage, it still displays the search button, I am thinking something is not correct in the way I try to display results.
              [PHP]<?php

              if($_GET["field1"]):

              // Connecting, selecting database
              $link = mysql_connect(' 10.10.17.247', 'edefikh', 'changeme')
              or die('Could not connect: ' . mysql_error());
              echo 'Connected successfully';
              mysql_select_db ('InventoryDev' ) or die('Could not select database');

              // Performing SQL query
              $query = 'SELECT network.hostnam e, server.domain, server.manufact urer, server.model, server.chassis, server.slot, server.os, server.osver, server.notes
              FROM network
              LEFT OUTER JOIN server
              ON network.serveri d = server.serverid
              WHERE ipaddr = '.mysql_escape_ string($_GET["ipadd"]);

              $result = mysql_query($qu ery) or die('Query failed: ' . mysql_error());

              // Printing results in HTML
              echo "<table>\n" ;
              while ($line = mysql_fetch_arr ay($result, MYSQL_ASSOC)) {
              echo "\t<tr>\n";
              foreach ($line as $col_value) {
              echo "\t\t<td>$col_v alue</td>\n";
              }
              echo "\t</tr>\n";
              }
              echo "</table>\n";

              // Free resultset
              mysql_free_resu lt($result);

              // Closing connection
              mysql_close($li nk);

              else:
              ?>

              <!-- comments -->
              <form method="post" action="test.ph p">
              <input type ="text" name ="ipadd"/>
              <input name="button" value="Search" type="SUBMIT"/>
              </form>

              <?php endif;?>[/PHP]
              Last edited by mwasif; Sep 14 '07, 06:14 PM. Reason: Added code tags.

              Comment

              • mwasif
                Recognized Expert Contributor
                • Jul 2006
                • 802

                #8
                Use CODE tags when posting souce code.

                Comment

                • Atli
                  Recognized Expert Expert
                  • Nov 2006
                  • 5062

                  #9
                  PHP does not use : like python and other such languages do...

                  Your if statements should be structured like this:
                  [code=php]
                  if($something > 1) {
                  echo "It's bigger than 1";
                  }
                  else if($something == 1) {
                  echo "It is 1";
                  }
                  else {
                  echo "It's less than 1";
                  }
                  [/code]

                  PHP shows error messages when you make syntax errors like these. You just need to turn them on.
                  Last edited by Atli; Sep 14 '07, 08:10 PM. Reason: Add error message article link

                  Comment

                  • edefikh
                    New Member
                    • Sep 2007
                    • 5

                    #10
                    I need to be able to query a set of 7 tables, pull the right data from it and then display the result in a table. Here is what I have so far, I am not sure how to display the results nicely:


                    <?php
                    if($_POST["ipadd"]):
                    $_POST["my_dropdow n"];
                    echo mysql_escape_st ring($_POST['text']);
                    echo $_POST['my_dropdown'];

                    // Connecting, selecting database
                    $link = mysql_connect(' 10.10.17.247', 'inventory', 'inventory')
                    or die('Could not connect: ' . mysql_error());
                    echo 'Connected successfully';
                    mysql_select_db ('InventoryDev' ) or die('Could not select database');


                    // Performing SQL query
                    $query = "SELECT network.hostnam e, server.domain, server.manufact urer, server.model, server.chassis, server.slot, server.os, server.osver, server.notes
                    FROM network
                    LEFT OUTER JOIN server
                    ON network.serveri d = server.serverid
                    WHERE ipaddr = '" . mysql_escape_st ring($_POST["ipadd"]) . "'";

                    print "<p>$query<p>\n ";

                    $result = mysql_query($qu ery) or die('Query failed: ' . mysql_error());

                    // Printing results in HTML
                    echo "<table>\n" ;
                    while ($line = mysql_fetch_arr ay($result, MYSQL_ASSOC)) {
                    echo "\t<tr>\n";
                    foreach ($line as $col_value) {
                    echo "\t\t<td>$col_v alue</td>\n";
                    }
                    echo "\t</tr>\n";
                    }
                    echo "</table>\n";

                    // Free resultset
                    mysql_free_resu lt($result);

                    // Closing connection
                    mysql_close($li nk);

                    else:
                    ?>

                    <!-- comments -->
                    <form method="post" action="test.ph p">
                    <input type ="text" name ="ipadd"/>
                    <p><select size="1" name="my_dropdo wn">
                    <option value="one">cpu </option>
                    <option value="two">dis k</option>
                    <option value="three">e nvironment</option>
                    <option value="four">me mory</option>
                    <option value="five">ne twork</option>
                    <option value="six">pro duct</option>
                    <option value="seven">s erver</option>
                    </select></p>
                    <input name="button" value="Search" type="SUBMIT" name="btm_submi t" onclick="valida te()"/>
                    </form>



                    <?php endif;?>

                    Comment

                    Working...