Two DBs, Common Field.. Need to check DB2 if field in DB1 matches, do not displa

usmarinenco · September 19, 2011

Okay so the Subject is odd....but here is the deal.

I have the PHP code to display the data from my first database. I have 2 databases, one is for equipment sign_in the other is for equipment sign_out.

I want to only display data on the web page if the equipment is listed in sign_in but not in sign_out based on the common field in both called "Ticket #".

Here is the code for my initial display page. This displays all data in the database "sign_in" with no caveats or validation from the second DB.

***Yes...I know...root and no password is not safe. This is completely internal, and does not reach out to the net. It's in a sandbox environment using XAMPP right now. Once I go live this will all change****

Any help or direction would be greatly appreciated. It's been a while since I've done PHP......

<?php
$db = mysql_connect('localhost', 'root', '');
if (!$db) {
    echo 'Could not connect to MySQL server. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
    exit;
}

// Select the database you want to use – You can use a variable here too instead
if (!mysql_select_db('sign_in', $db)) {   // Did selection fail?
    // Handle error 
    echo 'DB Selection failed. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
    exit;
}

// An example for retrieving zero or more rows
$sql = "SELECT * FROM `customer sign-in`";
$result = mysql_query($sql, $db);
if (!$result) {
    // Handle error 
    echo 'Query failed. SQL: ', $sql, '<br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
    exit;
}
// The while loop stops when there's no data left; it might not even go in loop
// and echo anything when there's no data returned on the first call to
// mysql_fetch_assoc()
echo "<table border='1'>
<tr>
<th>Sign In Date</th>
<th>RANK/CIV</th>
<th>First Name</th>
<th>Last Name</th>
<th>Unit</th>
<th>DSN/Roshan</th>
<th>Classifications</th>
<th>Services Requested</th>
<th>Service Tag/ Serial Number</th>
<th>Ticket #</th>
<th>Make/ Model</th>
</tr>";

while($row = mysql_fetch_array($result)) {  // Retrieve data until no more
  {
  echo "<tr>";
  echo "<td>" . $row['Sign in Date'] . "</td>";
  echo "<td>" . $row['Rank/CIV'] . "</td>";
  echo "<td>" . $row['First Name'] . "</td>";
  echo "<td>" . $row['Last Name'] . "</td>";
  echo "<td>" . $row['Unit'] . "</td>";
  echo "<td>" . $row['DSN/Roshan'] . "</td>";
  echo "<td>" . $row['Classifications'] . "</td>";
  echo "<td>" . $row['Services Requested'] . "</td>";
  echo "<td>" . $row['Service Tag/ Serial Number'] . "</td>";
  echo "<td>" . $row['Ticket #'] . "</td>";
  echo "<td>" . $row['Make/ Model'] . "</td>";
  echo "</tr>";
  }
}
?>

cunoodle2 · September 19, 2011

Couldn't you just use 1 table called equipment and have a field called "status" In that field would either be "checked_in" or "checked_out" ?? If not I don't see the reason to have 2 separate databases. 2 tables would do you just fine. If you are looking at having some form of history on these check ins/outs then I would suggest a transaction table where you log each time a piece of equipment was in/out and whom touched it.

IF you insists on using 2 database then I would query both databases and store results in separate arrays. Then use array intersect to see where they overlap to then get the desired result set you are looking for. This by the way NOT something that I would recommend at all as it is using massive amounts of excess memory and widely inefficient. Either of the first 2 mentioned suggestions that I have would be much much more desirable.

sunfighter · September 19, 2011

The use of a single DB is highly recommended. But if you insist on two for some reason, then I think this will work:

SELECT equipment sign_in.* FROM equipment sign_in
LEFT JOIN table2 ON equipment sign_in.Ticket #=equipment sign_out.Ticket #
WHERE equipment sign_out.Ticket # IS NULL;

Psycho · September 19, 2011

First off, let's use the right terminology. I highly doubt you are using two databases, you are likely taking about two TABLES in a single database. If you are using two databases, then you are doing it wrong.

Your structure is wrong, IMO, but how it should be structured would be dependent on whether you need to track all checkin/checkouts over time or only the current status. I will assume you need the former. You should have one table to describe the equipment. Then you would have a second table to track the checkouts/checkin specific data. You shouldn't put things such as user data into either of these tables - you should instead have a user's table that describes the data about users.

Example table structure (I guessed on some of the data and left out some I had no clue about)

EQUIPMENT
- equip_id
- name
- make_mode
- serial_no

USERS
- user_id
- first_name
- last_name
- unit

CHECKOUT
- user_id
- equip_id
- ticket_no
- checkout
- checkin

You can now get a list of all equipment along with criteria such as whether it is available, or if checked out who checked it out, etc. etc. You could even add a due date to the checkout table.

SELECT * FROM equipment
LEFT JOIN checkout USING (equip_id)
JOIN users USING (user_id)

xyph · September 19, 2011

My solution is exactly like mjdamato's

Table dump

CREATE TABLE IF NOT EXISTS `equip_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `equip_id` int(11) NOT NULL COMMENT 'ID of equipment being rented',
  `renter_id` int(11) NOT NULL COMMENT 'Id of company renting',
  `checked_out` datetime NOT NULL COMMENT 'Date/time checked out',
  `checked_in` datetime DEFAULT NULL COMMENT 'Date/time checked in',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

And to solve your problem in your initial post

SELECT * FROM `equip_status` WHERE `checked_in` IS NULL

What happens here is when you check equipment out, you don't define a `checked_in` time. This will default it to NULL and that can be checked for very easily. You can then get a list of equipment currently checked out.

Using a JOIN similar to what mjdamato's provided, you can reference the equipment ID and renter ID to output-able data.

usmarinenco · September 20, 2011

Considering I inherited this database from another user, and am confined to the restraints of what is already established I will go on about this task on my own.

I am using two databases because we are tracking the equipment that is brought into the service desk for the duration of the time here in Afghanistan.

The fact that anyone would say 'it's wrong' is completely inaccurate. We all do things our own way, and as it is....I will make it work.

Thanks for your replies,

Carl

xyph · September 20, 2011

It's not inaccurate. You are doing it incorrectly. These database systems are designed to work most efficiently using certain relationship methods. Though 'right' and 'wrong' are subjective, there are almost always best practices defined.

We were providing you a solution that would make your problem much easier to solve, and ensure consistency without having to compare multiple data sets.

Here's your solution though

SELECT `ticket`
FROM `sign_in` 
WHERE `ticket` NOT IN (
SELECT `ticket` 
FROM `sign_out`
);

This will return all values of `ticket` in `sign_in` that don't exist in the `ticket` column of `sign_out`

Psycho · September 20, 2011

I am using two databases because we are tracking the equipment that is brought into the service desk for the duration of the time here in Afghanistan.

The fact that anyone would say 'it's wrong' is completely inaccurate. We all do things our own way, and as it is....I will make it work.

No, you are using two TABLES not two DATABASES. There is a very big difference. The fact that you don't know the difference suggest a lack of understanding. I'm not trying to be arrogant, but you came here looking for help. Based upon your description we identified a flaw in the database structure. I am guessing that you may be reluctant to change anything because you are fairly new to development. Your last statement is a common "defensive" statement of those not wanting to hear that something they have built has problems. Learn to take constructive criticism and use it to make what you do better. While there are some shades of gray when it comes to database normalization, I can assure you that what you described would not be considered proper.

By all means, work with what you have. It has problems. But, if it serves your purpose and you are not interested in learning how to improve, so be it.

usmarinenco · September 20, 2011

Okay....let me lay this out very clearly.

DB1 = sign_in

DB2 = sign_out

Both databases are resident inside a XAMPP installation.

If I go to phpmyadmin and click on "Databases" it lists them seperately.

Inside 'sign_in' there is a table called 'customer sign-in'

Inside 'sign_out' there is a table called 'customer sign-out'.

I did not create this structure....however it's what I have to work with.

usmarinenco · September 20, 2011

If you want to create it in MySQL yourself here is the statement.

[uNCLASSIFIED DATA IS ENCLOSED]

-- phpMyAdmin SQL Dump -- version 3.1.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Sep 18, 2011 at 11:13 PM -- Server version: 5.1.30 -- PHP Version: 5.2.8  SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";  -- -- Database: `sign_in` -- CREATE DATABASE `sign_in` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `sign_in`;  -- --------------------------------------------------------  -- -- Table structure for table `customer sign-in` --  CREATE TABLE IF NOT EXISTS `customer sign-in` (   `ID` int(11) NOT NULL AUTO_INCREMENT,   `Sign in Date` datetime NOT NULL,   `Rank/CIV` varchar(255) NOT NULL,   `First Name` varchar(255) NOT NULL,   `Last Name` varchar(255) NOT NULL,   `Unit` varchar(255) NOT NULL,   `DSN/Roshan` varchar(10) NOT NULL,   `Classifications` longtext NOT NULL,   `Services Requested` varchar(255) NOT NULL,   `Service Tag/ Serial Number` varchar(255) NOT NULL,   `Ticket #` varchar(255) NOT NULL,   `Make/ Model` varchar(255) NOT NULL,   PRIMARY KEY (`ID`) ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=614 ;  -- -- Dumping data for table `customer sign-in` --  INSERT INTO `customer sign-in` (`ID`, `Sign in Date`, `Rank/CIV`, `First Name`, `Last Name`, `Unit`, `DSN/Roshan`, `Classifications`, `Services Requested`, `Service Tag/ Serial Number`, `Ticket #`, `Make/ Model`) VALUES (612, '2011-09-17 00:00:00', 'CTR', 'Joe', 'Blow', 'Some Unit', '0794911109', 'NIPR', 'BASELINE', 'D4770D1', '444000', ''), (613, '2011-09-18 00:00:00', 'CPL / E-4', 'Bob', 'Jones', 'Some Unit', '0794440333', 'SIPR', 'ADD TO DOMAIN', '50340', '50505', ''); -- -- Database: `sign_out` -- CREATE DATABASE `sign_out` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `sign_out`;  -- --------------------------------------------------------  -- -- Table structure for table `customer sign-out` --  CREATE TABLE IF NOT EXISTS `customer sign-out` (   `ID` int(11) NOT NULL AUTO_INCREMENT,   `Sign Out Date` datetime NOT NULL,   `Rank/CIV` varchar(255) DEFAULT NULL,   `First Name` varchar(255) NOT NULL,   `Last Name` varchar(255) NOT NULL,   `Unit` varchar(255) NOT NULL,   `DSN/Roshan` varchar(255) NOT NULL,   `Service Tag/ Serial Number` varchar(255) NOT NULL,   `Ticket #` varchar(255) NOT NULL,   `Make/ Model` varchar(255) NOT NULL,   `Sign Out Verified by:` varchar(255) DEFAULT NULL,   PRIMARY KEY (`ID`),   KEY `ID` (`ID`) ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;  -- -- Dumping data for table `customer sign-out` --  INSERT INTO `customer sign-out` (`ID`, `Sign Out Date`, `Rank/CIV`, `First Name`, `Last Name`, `Unit`, `DSN/Roshan`, `Service Tag/ Serial Number`, `Ticket #`, `Make/ Model`, `Sign Out Verified by:`) VALUES (9, '0000-00-00 00:00:00', NULL, '', '', '', '', '', '444000', '', NULL); -- -- Database: `switch_in` -- CREATE DATABASE `switch_in` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `switch_in`;  -- --------------------------------------------------------  -- -- Table structure for table `networks switch sign-in` --  CREATE TABLE IF NOT EXISTS `networks switch sign-in` (   `ID` int(11) NOT NULL AUTO_INCREMENT,   `Date` datetime DEFAULT NULL,   `Ticket #` int(11) DEFAULT NULL,   `Serial Number` varchar(255) DEFAULT NULL,   `Unit Name` varchar(255) DEFAULT NULL,   `Net Ops Technician` varchar(255) DEFAULT NULL,   PRIMARY KEY (`ID`) ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;  -- -- Dumping data for table `networks switch sign-in` --   -- --------------------------------------------------------  -- -- Table structure for table `networks switch sign-out` --  CREATE TABLE IF NOT EXISTS `networks switch sign-out` (   `ID` int(11) NOT NULL AUTO_INCREMENT,   `Date` datetime DEFAULT NULL,   `Ticket #` int(11) DEFAULT NULL,   `Serial Number` varchar(255) DEFAULT NULL,   `Unit Name` varchar(255) DEFAULT NULL,   `Net Ops Technician` varchar(255) DEFAULT NULL,   PRIMARY KEY (`ID`) ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;  -- -- Dumping data for table `networks switch sign-out` --  -- -- Database: `switch_out` -- CREATE DATABASE `switch_out` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `switch_out`;

Psycho · September 20, 2011

Okay....let me lay this out very clearly.

DB1 = sign_in

DB2 = sign_out

Both databases are resident inside a XAMPP installation.

If I go to phpmyadmin and click on "Databases" it lists them seperately.

Inside 'sign_in' there is a table called 'customer sign-in'

Inside 'sign_out' there is a table called 'customer sign-out'.

I did not create this structure....however it's what I have to work with.

Well, then all I have to say is whoever created that structure had no clue what they were doing. They apparently created a separate database for each table. As to your previous statement

The fact that anyone would say 'it's wrong' is completely inaccurate.

I can unequivocally state that the above DB structure is, without a doubt, wrong. BY creating a separate database for each table you cannot do ANY relational queries which is the whole point of a "relational database".

The good news is that it shouldn't be too hard to fix it. I am going to assume that you have specific pages that each only work with one database/table. Plus, I assume each page has it's own database connection code - which again is poor programming standard. Here is my recommendation to at least get everything into one database that will not require any major edits to your code. It will keep the tables in their current structure, which is also "wrong" but at least you will get more flexibility than you have now.

First, identify the ONE database that you will use. Then, copy each table from the other databases into this database. Then, create a small PHP file that performs the database connection for that one database. E.g.:

$db = mysql_connect('localhost', 'root', '');
if (!$db) {
    echo 'Could not connect to MySQL server. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
    exit;
}

// Select the database you want to use – You can use a variable here too instead
if (!mysql_select_db('sign_in', $db)) {   // Did selection fail?
    // Handle error 
    echo 'DB Selection failed. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
    exit;
}

Now, go to each page that incorporates a database connection and comment out/delete those lines and replace with an include function for the file you created above

include('db_connect.php');

When you moved the tables they should all have their same name. So, when you now connect to the ONE database all the code on those pages should work. Also, you now have ONE place to modify the database connection details when you go to a production environment. You don't have to open every file to change the database username/password.

Depending on how many pages you have the above exercise should take 10-20 minutes. Once you have all the tables in the same database you can do JOINs and other database queries to "relate" the data between tables. As stated above, the current tables are not built in a manner to make what you want to do easy. If you want help in correcting the table structures we can help with that as well.

xyph · September 20, 2011

Okay....let me lay this out very clearly.

DB1 = sign_in

DB2 = sign_out

Both databases are resident inside a XAMPP installation.

If I go to phpmyadmin and click on "Databases" it lists them seperately.

Inside 'sign_in' there is a table called 'customer sign-in'

Inside 'sign_out' there is a table called 'customer sign-out'.

I did not create this structure....however it's what I have to work with.

I would strongly, STRONGLY suggest you change this structure. Perhaps if you explained why you can't change it we wouldn't be hounding you as much. Regardless, here's a solution that will work for you.

<?php 

$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';

$db_in = new MySQLi ( $db_host, $db_user, $db_pass, 'db_in' );
$db_out = new MySQLi( $db_host, $db_user, $db_pass, 'db_out' );

if( $db_in->connect_error || $db_out->connect_error ) trigger_error( 'Unable to initiate database connections', E_USER_ERROR );

// Grab whatever information you need from the tables
$q_in =  'SELECT `ticket`, `equipment` FROM `customer sign-in`';
$q_out = 'SELECT `ticket`, `equipment` FROM `customer sign-out`';

if( ($r_in = $db_in->query($q_in)) === FALSE || ($r_out = $db_out->query($q_out)) === FALSE )
trigger_error( 'Unable to grab ticket information from databases', E_USER_ERROR );

// Initialize the arrays that will hold our data
$data_in = array(); $data_out = array();

// Build the in array, using the ticket as the key
while( $row = $r_in->fetch_assoc() )
$data_in[ $row['ticket'] ] = $row;
$r_in->free();

// Build the out array, using the ticket as the key
while( $row = $r_out->fetch_assoc() )
$data_out[ $row['ticket'] ] = $row;
$r_out->free();

// Use array_diff_key() to automatically grab keys that don't exist in out
$result = array_diff_key( $data_in, $data_out );

// Output teh result
print_r( $result );

?>

usmarinenco · September 21, 2011

Thank you for the above explanation.

I cannot change the structure because I am not the information holder.

Some of the data ends up on a classified network, but is unclassified in it's present state.

I'm not a DBA, nor am I a Web Designer for this company....however my experience in the past has given me a basic understanding of what's required to complete these things....

If I was the information owner, I would have started this entire thing from scratch.

The DB's that are setup are that way because when this is complete, the existing data in the DB's the information owner has will be imported into this system.

usmarinenco · September 21, 2011

Also wanted to add the code you provided worked with a few changes.

"ticket' was changed to 'Ticket #'....table names were changed to the correct ones and everything now displays on the PHP page.

Now I just gotta format it to display without all the:

Array ([444000]=>Array([iD]=>612[sign in Date]...etc...etc...etc....

Thank you again,

Carl

Sign In

Two DBs, Common Field.. Need to check DB2 if field in DB1 matches, do not displa

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information