Changing color of text generated in tables from ms sql...

[Soulreaver418]

Hello all, first time site visitor, semi-noob of php here. Trying to self teach for my current job, what I have is a code that takes data from a ms sql database and displays it on a local intranet page for our office coworkers to view.

It deals with rental units and displaying if they are currently rented or occupied. What I would like/need to do to make it easier to, at a glance read, the current status occupied and vacant inside the table that is generated. Occupied would be red text and Vacant would be green.

I wasn't sure if I should try and create a css sheet and call it from the .php file or if I can just add a snippet inside the code itself.

Here is my current code..

Code: [Select]

<?php 

//connect to a DSN "sqlserver" 
$conn = odbc_connect('','',''); 

if ($conn) 
{ 
//the SQL statement that will query the database 
$query = "select PropertyUnitID, Street, City, Status from unit"; 
//perform the query 
$result=odbc_exec($conn, $query); 

echo "<table border=\"1\"><tr>"; 

//print field name 
$colName = odbc_num_fields($result); 
for ($j=1; $j<= $colName; $j++) 
{ 
echo "<th>"; 
echo odbc_field_name ($result, $j ); 
echo "</th>"; 
} 

//fetch tha data from the database 
while(odbc_fetch_row($result)) 
{ 
echo "<tr>"; 
for($i=1;$i<=odbc_num_fields($result);$i++) 
{ 
echo "<td>"; 
echo odbc_result($result,$i); 
echo "</td>"; 
} 
echo "</tr>"; 
} 

echo "</td> </tr>"; 
echo "</table >"; 

//close the connection 
odbc_close ($conn); 
} 
else echo "odbc not connected"; 
?>

Any help is appreciated, probably a simple fix, but I am just not sure how to approach this issue!

Thanks!

[Miss_Rebelx]

Well I'm not quite sure how SQL server works, so I won't bother trying to type up the exact code, but what I would do is:

Change this up a bit:

Code: [Select]

echo "<td>";
echo odbc_result($result,$i);
echo "</td>";
At this part, modify the td tag to something like:

Code: [Select]

$resultfield = odbc_result($result,$i); //I'm assuming you can do this...
echo "<td class='$resultfield'>";
echo $resultfield;
echo "</td>";
Basically, this will assign each TD to a class named after the value of the field. This is a bit messy, but this way, in your CSS file, you can create .occupied and .vacant, and have them set the font color to the appropriate color. All the TDs that contain either of those values will change colors. Anything else will be ignored unless you define a class of their TD values.

Something else you could do is modify this a bit:

Code: [Select]

for($i=1;$i<=odbc_num_fields($result);$i++)
{
echo "<td>";
echo odbc_result($result,$i);
echo "</td>";
}

To make it less messy, you'd have to know which field contains the status value. According to your SQL statement, it should be the fourth field, or value "3". So...

Code: [Select]

for($i=1;$i<=odbc_num_fields($result);$i++)
{
if ($i == 3)
{
$resultfield = odbc_result($result,$i); //I'm assuming you can do this...
echo "<td class='$resultfield'>";
}
else
{
echo "<td>";
}
echo odbc_result($result,$i);
echo "</td>";
}

Again, you will have to set the  .occupied and .vacant classes in CSS but those should be the only two needing to be set.

[Soulreaver418]

/ hug!

This worked out great. I was thinking it could be done with a .css but wasn't sure how to create the variable to call in the .css file.

Now on to the next daunting task...

Trying to generate a conditional if statement according to if data is present or is not present in another table.

Care to lend a hand with this as well mate?

I want to read a table called viewPendingLease.StatusName and check to see if it has a pending or nothing in it.

If it has a pending status, I would like to print a "yes" in a new column I would call future booked. If it has nothing in it, I would like to print a "no" in that column.

Creating the new column shouldn't be too bad, but I am not sure how to read that PendingLease table and have it print the yes or no according to the condition.

I know it would be with conditionals but I am learning as I go here.

Still, cheers for the color issue help. This is a huge step in completing this task at hand.!

 

[Miss_Rebelx]

Glad it worked out for you.

I want to read a table called viewPendingLease.StatusName and check to see if it has a pending or nothing in it.

I take it viewPendingLease is the table name and Status Name is the field?
If so, what kind of field is it? What is in it?
Basically, I want to know: how can you tell if it's a pending status?
Is it a boolean value? Is it a word?

And then you keep referencing "new column". Has this column been made in your database? Or do you not want it to be in the database, just output to screen?

Is this new table and pending status affiliated with the original table you queried?

[Soulreaver418]

Ok, to help clarify, here is a snippet of the current table, colored and all.



What I would like to do is add another Column only on the ouput, not in the database and it would be named something like Future Booked.

In that column would be either a yes or a no next to each properties current status.

Yes, viewPendingLease is the table and the column that holds the data I am after is called StatusName, all it does is show the word Pending or nothing at all if that property has no pending future lease.

The table Unit and viewPendingLease are seperate tables all together. Technically View isn't a table, but it can be called like one.

I hope that clarifies it up a bit.

Thanks again for the help.

[Miss_Rebelx]

Okay, so my next question is: How do you know which row of the viewPendingLease has to do with which row in the Unit table? Do you have an established Foreign Key linking them?

For example:

In table viewPendingLease, you have a propertyID, and in table Unit you have the same propertyID - this identifies the same property within both tables.

(While replying to this thread, and I am fairly sure you have this FK, can you post the updated code so I can give you the code you need with minimum need of editing from you?)

[Soulreaver418]

Code: [Select]

<?php 

//connect to a DSN "" 
$conn = odbc_connect('','',''); 

if ($conn) 
{ 
//the SQL statement that will query the database 
$query = "Select unit.Unit_ID, unit.PropertyUnitID, unit.Street, unit.Status from unit";

//perform the query 
$result=odbc_exec($conn, $query); 
echo "<table border=\"2\"><tr>"; 

//print field name 
$colName = odbc_num_fields($result); 
for ($j=1; $j<= $colName; $j++) 
{ 
echo "<th>"; 
echo odbc_field_name ($result, $j ); 
echo "</th>"; 
} 

//fetch tha data from the database 
while(odbc_fetch_row($result)) 
{ 
echo "<tr>"; 
for($i=1;$i<=odbc_num_fields($result);$i++) 
{ 
$resultfield = odbc_result($result,$i); 
echo "<td class='$resultfield'>";
echo $resultfield;
echo "</td>";} 
echo "</tr>"; 
} 
echo "</td> </tr>"; 
echo "</table >"; 

//close the connection 
odbc_close ($conn); 
} 
else{
 echo "odbc not connected"; 
}
?>
The easiest way to determine the same property would to be use the Unit_ID, each table has it. All it is, is a code that starts at number 1 and counts up for each property we have.

[Miss_Rebelx]

Here's what I got, let me know if it doesn't work!


<?php 

//connect to a DSN "" 
$conn = odbc_connect('','',''); 

if ($conn) 
{ 
//the SQL statement that will query the database 
$query = "Select unit.Unit_ID, unit.PropertyUnitID, unit.Street, unit.Status, viewPendingLease.StatusName as \"Future Booked\" from unit INNER JOIN viewPendingLease ON unit.Unit_ID = viewPendingLease.Unit_ID";

//perform the query 
$result=odbc_exec($conn, $query); 
echo "<table border=\"2\"><tr>"; 

//print field name 
$colName = odbc_num_fields($result); 
for ($j=1; $j<= $colName; $j++) 
{ 
echo "<th>"; 
echo odbc_field_name ($result, $j ); 
echo "</th>"; 
} 

//fetch tha data from the database 
while(odbc_fetch_row($result)) 
{ 
echo "<tr>"; 
for($i=1;$i<=odbc_num_fields($result);$i++) 
{ 
$resultfield = odbc_result($result,$i); 
echo "<td class='$resultfield'>";
if ($i == 4)
{
 if ($resultfield == 'Pending')
 {

echo "Yes";
 }
 else
 {

echo "No";
 }
}
else
{
 echo $resultfield;
}
echo "</td>";} 
echo "</tr>"; 
} 
echo "</td> </tr>"; 
echo "</table >"; 

//close the connection 
odbc_close ($conn); 
} 
else{
 echo "odbc not connected"; 
}
?>

[Soulreaver418]

Ok, it's getting there. Right now it did create a column named Future Booked, however there are a few glitches.

it has changed the status section to all no's, with some red and some green. The future booked says pending now all the way down and it does not display all properties now even if it does not have a future booking.

I need the status to say pending and the future booked to say yes or no. Also need it to display all the properties regardless of it is pending lease or not.

So, for the ones that don't have a future booking it would say no all the way down and the ones that do it would say yes.

...and let me say, thank you so much for the assistance you have provided thus far!

[Miss_Rebelx]

Change this:


for($i=0;$i<odbc_num_fields($result);$i++) 
{ 
$resultfield = odbc_result($result,$i); 
echo "<td class='$resultfield'>";
if ($i == 4)


I anticipate this to get rid of atleast the "all 'no's" error, and the "all pending" error.

I'm not sure what's causing the next right off the cuff so I need an update from you once you make this change.

[Soulreaver418]

Warning: odbc_result() [function.odbc-result]: Field index is larger than the number of fields in C:\xampp\htdocs\sqlscript.php on line 74

getting this error multiple times now, I have all no's in the future booked column now, red and green colored.

Lost the status column that shows Occupied or Vacant in red and green text.

[Miss_Rebelx]

Ahh sorry I didn't realize that SQL server resources indexing starts at "1" and not "0".

Code: [Select]

for($i=1;$i<=odbc_num_fields($result);$i++)
{
$resultfield = odbc_result($result,$i);
echo "<td class='$resultfield'>";
if ($i == 5)


[Soulreaver418]

To help you see what it looks like, here are snips from the code. The first one with the working colors, the second one and then the third with this last script you just posted.







Hope this helps. Thanks again for everything!

[Miss_Rebelx]

Ahh thank you for the screenshots. Useful. I'm sorry that I keep swinging and missing at helping you out: I'm prone to make stupid little mistakes!

Apparently I messed up the HTML. Change this please:


 else
 {
     echo "No";
 }
}
else
{
 echo $resultfield;
}
echo "</td>";
} 
echo "</tr>"; 
}
echo "</table >"; 


I'm surprised it compiled before considering there should have been a brace error.

Could you paste the code you have upon next reply as well?
If you keep getting that index error I'm going to have to do some more reading to be sure, and I'd like to be certain you have the code I think you have.

[Soulreaver418]

This is where we are at now,



The only thing missing is to show the units that do not have a pending future lease.

Maybe it causes an issue because if there is no pending lease status the viewPendingLease shows nothing for that unit, it doesn't even show the unit at all... so when you call it, the ones that have no pending lease do not show up.

I did not think of this before, sorry! Is there a way to display the units even though viewPendingLease does not show them if no pending lease is present?

Having issues with the forums atm, keeps telling my last post within 200 seconds, even though it has been 10 minutes...