Putting it all together...displaying array!

[usmarinenco]

Thanks to the great help from some great people on this message board I'm right at the finish line on this page.

 

I'm trying to mesh all the coding together at this point, and make it display the information. 

 

I keep getting 'mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource....etc..(filename/location) on line 51.

 

Am I way off here???

<?php

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

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

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

        $q_in =  'SELECT * FROM `customer sign-in`';
        $q_out = 'SELECT * 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 );

        $data_in = array(); $data_out = array();

        while( $row = $r_in->fetch_assoc() )
           $data_in[ $row['Ticket #'] ] = $row;
        $r_in->free();
           
        while( $row = $r_out->fetch_assoc() )
           $data_out[ $row['Ticket #'] ] = $row;
        $r_out->free();

        $result = array_diff_key( $data_in, $data_out );

           echo "<table border='1'>
        <tr>
        <th>ID</th>
        <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_assoc($result)) {  // Retrieve data until no more
          {
          echo "<tr>";
          echo "<td>" . $row['ID'] . "</td>";
          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>";
          }
        }
    ?>

[WebStyles]

mysql_fetch_assoc($result)

expects $result to contain a valid sql query. I'm guessing it does not. (didn't read your code, just checked line 51 based on the error you gave us)

[AyKay47]

you will want to use either $q_in or $q_out.. mysql_fetch_array expects mysql resource as the first argument, you are giving it an array

 

Edit: as previously stated..

[PFMaBiSmAd]

You are using mysqli for your database functions.

 

You cannot use any of the mysql_ (non-i) statements. You would need to use the mysqli fetch_assoc method or function.

 

Edit: Also, why on earth do you have related data in two completely separate databases?

[usmarinenco]

You are using mysqli for your database functions.

 

You cannot use any of the mysql_ (non-i) statements. You would need to use the mysqli fetch_assoc method or function.

 

Edit: Also, why on earth do you have related data in two completely separate databases?

 

Ahhhh........the question everyone has asked me so far.

 

I do not own the data.....it's government owned and therefore I cannot change the way it's presented to me for review and presentation on the web page I'm trying to create.

 

All of this PHP is foreign to me in the end.....I spent many a hour on this message board to get to where I am now.....

 

The code below just does the array and returns the data in a web page that is very garbled.....if I could just change this to display the data in a 'table' on the page that would be ideal....unfortunately....with what I'm reading above.....I ummm.....feel dumber than I felt before I started this!

 

<?php 

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

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

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

$q_in =  'SELECT * FROM `customer sign-in`';
$q_out = 'SELECT * 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 );

$data_in = array(); $data_out = array();

while( $row = $r_in->fetch_assoc() )
$data_in[ $row['Ticket #'] ] = $row;
$r_in->free();

while( $row = $r_out->fetch_assoc() )
$data_out[ $row['Ticket #'] ] = $row;
$r_out->free();

$result = array_diff_key( $data_in, $data_out );

print_r( $result );

?>

]

[PFMaBiSmAd]

A) If you have a specific reason for using two databases (or any other conditions you are aware of that defines a problem you are asking someone to help you with), stating those at the START of a request for help or putting a comment in the code you are posting would be prudent.

 

B) If these two different databases are on the same database server and are accessible by the same database user, you can specify the database name and table (the format would be db_name.table_name instead of just table_name) in your queries and it's possible to write one query to do what you are trying to do.

[PFMaBiSmAd]

C) Actually, since $result holds the result of the array_diff_key statement, there's no likely reason to re-retch any of the data from the either query (you have freed up the result resources, so they don't exist anyway.) If you use echo '<pre>',print_r($result,true),'</pre>'; to display what the sample data is and state what output you need from the data, someone can probably help.

[PFMaBiSmAd]

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 #".

 

^^^ Is that an accurate statement? That would imply someone signed something in that is currently not signed out? How would a Ticket # even exist in this case?

[PFMaBiSmAd]

IF the two different databases have the same database user, the following query will get the records that have been signed in, but have not been signed out -

 

SELECT *
FROM sign_in.`customer sign-in` tin
WHERE NOT EXISTS (
SELECT *
FROM sign_out.`customer sign-out` tout
WHERE tin.`Ticket #` = tout.`Ticket #`
)

[usmarinenco]

The Ticket # isn't generated in this system.  It's generated in Remedy.....

[usmarinenco]

Per the poster who stated to run the code and then post what I'm getting as results, here is that!

 

This PHP page is loaded:

<?php 

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

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

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

$q_in =  'SELECT * FROM `customer sign-in`';
$q_out = 'SELECT * 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 );

$data_in = array(); $data_out = array();

while( $row = $r_in->fetch_assoc() )
$data_in[ $row['Ticket #'] ] = $row;
$r_in->free();

while( $row = $r_out->fetch_assoc() )
$data_out[ $row['Ticket #'] ] = $row;
$r_out->free();

$result = array_diff_key( $data_in, $data_out );

print_r( $result );

?>

 

 

THIS is the result of 'print_r'

 

Array ([DODOIJSDF]=>Array([iD]=>617[sign in Date]=>2011-09-23 00:00:00[RANK/CIV]=>SPC/E4[First Name]=>Sean[Last Name]=>John[unit]=>510th[DSN/Roshan]=>0791110000[Classifications]=>NIPR[service Requested]=>Q-TIP Scan[service Tag/ Serial Number]=>DODOD[Ticket #]=>DODOIJSDF[MAKE/ Model]=>Dell E6500))

 

I need to find a way to format this data (which is the correct data, given the request of the code above....)....into a table for each record.

 

I've tried the numerous other ways that were posted here to get the results I need to get, yet none of them work with what I am trying to do.

[PFMaBiSmAd]

since $result holds the result of the array_diff_key statement

 

Just iterate over the $result array -

 

foreach($result as $row) {  // Retrieve data until no more
    // code to use the elements of $row goes here....
}

[usmarinenco]

PFM!!!

You're awesome!!!!!

That is exactly what I was looking for....I swear....I think every year I get older my 'google skills' get cut in half....I swear I looked for that for at least two days!

 

I really appreciate your patience.....

 

Carl

[usmarinenco]

This ended up being the final code....just in case anyone is ever looking for this type of thing again.

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<meta content="en-us" http-equiv="Content-Language" />
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Removed Service Desk</title>
<style type="text/css">
.auto-style2 {
font-family: "Lucida Sans", "Lucida Sans Regular", "Lucida Grande", "Lucida Sans Unicode", Geneva, Verdana, sans-serif;
font-size: x-large;
}
.auto-style3 {
text-align: center;
color: #FF0000;
}
.auto-style4 {
font-family: Arial, Helvetica, sans-serif;
}
.auto-style5 {
text-align: center;
}
</style>

</head>

<script type="text/javascript">
<!--
function FP_swapImg() {//v1.0
var doc=document,args=arguments,elm,n; doc.$imgSwaps=new Array(); for(n=2; n<args.length;
n+=2) { elm=FP_getObjectByID(args[n]); if(elm) { doc.$imgSwaps[doc.$imgSwaps.length]=elm;
elm.$src=elm.src; elm.src=args[n+1]; } }
}

function FP_preloadImgs() {//v1.0
var d=document,a=arguments; if(!d.FP_imgs) d.FP_imgs=new Array();
for(var i=0; i<a.length; i++) { d.FP_imgs[i]=new Image; d.FP_imgs[i].src=a[i]; }
}

function FP_getObjectByID(id,o) {//v1.0
var c,el,els,f,m,n; if(!o)o=document; if(o.getElementById) el=o.getElementById(id);
else if(o.layers) c=o.layers; else if(o.all) el=o.all[id]; if(el) return el;
if(o.id==id || o.name==id) return o; if(o.childNodes) c=o.childNodes; if(c)
for(n=0; n<c.length; n++) { el=FP_getObjectByID(id,c[n]); if(el) return el; }
f=o.forms; if(f) for(n=0; n<f.length; n++) { els=f[n].elements;
for(m=0; m<els.length; m++){ el=FP_getObjectByID(id,els[n]); if(el) return el; } }
return null;
}
// -->
</script>


<body background="bg.ltr.jpg" onload="FP_preloadImgs(/*url*/'button5.jpg',/*url*/'button6.jpg')">
<div class="auto-style5">
<img alt="" height="177" longdesc="Removes For Security Reasons" src="DesertDawgLOGO.png" width="165" />                            
<span class="auto-style2">Removed for Security Reasons - Service Desk Equipment Sign-In</span>                                        
<img alt="" height="189" longdesc="25th Signal Battalion" src="bnlogo.png" width="102" /><br />
<br />
<?php 
$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';

$db_in = new MySQLi( $db_host, $db_user, $db_pass, 'sign_in' );
$db_out = new MySQLi ( $db_host, $db_user, $db_pass, 'sign_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 * FROM `customer sign-in`';
$q_out = 'SELECT * 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 );

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>Serivces Requested</th>
<th>Service Tag/ Serial Number</th>
<th>Ticket #</th>
<th>Make/ Model</th>
</tr>";

foreach($result as $row) {
{
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>";
}
}
?>
</div>
<p class="auto-style3"><strong><span class="auto-style4">Equipment listed below 
is currently CHECKED IN at the Service Desk</span><br />
<a href="http://removed for security reasons">
<img id="img1" alt="Return to KAF IMO Portal" height="33" onmousedown="FP_swapImg(1,0,/*id*/'img1',/*url*/'button6.jpg')" onmouseout="FP_swapImg(0,0,/*id*/'img1',/*url*/'button4.jpg')" onmouseover="FP_swapImg(1,0,/*id*/'img1',/*url*/'button5.jpg')" onmouseup="FP_swapImg(0,0,/*id*/'img1',/*url*/'button5.jpg')" src="button4.jpg" style="border: 0" width="167" /><!-- MSComment="ibutton" fp-style="fp-btn: Glass Capsule 1" fp-title="Return to KAF IMO Portal" --></a></strong></p>
</body>

</html>