Query output looks pretty nasty 2

[skicamel]

Is there any way to format output from a sql query? The fields are only as long as the longest word. The columns are always centered, but the rows are left aligned. There's what looks to be less than a single space between columns. This is very unappetizing asthetically (Although I could care less, the people that will be using the site are bit less forgiveable on such heinous crimes ). I'm hoping maybe in the form of a table? Maybe in a grid form like in SQL Query Analyzer? Or even just a way to add some spaces between columns? Any information would be helpful. Thank you, all.

 

[JimEkleberry]

The way this is typically done is by writting the query results into an html table.

 

[skicamel]

Any ideas how to do this?

 

[JimEkleberry]

Typically it would be done like:

<table width=&quot;560&quot; border=&quot;0&quot; cellspacing=&quot;3&quot; cellpadding=&quot;1&quot;>
<?
$query = &quot;select name, title, department from $db_table order by id&quot;;
$result = mysql_query($query, $connection)
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
// Print the column names as table column headers.
print &quot;<th>&quot;.ucfirst(mysql_field_name($result, $i)).&quot;</th>\n&quot;;
}

// Now output each column of the query result as a table cell.
while ($row = mysql_fetch_array($result, mysql_num))
{
echo &quot;<tr>\n&quot;;
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
echo &quot;\t<td>&quot;.$row[$i].&quot;</td>\n&quot;;
}
echo &quot;</tr>\n&quot;;
}
?>
</table>

Let me know if that works out for you.

 

[skicamel]

Sorry for the delay... Unrelated things blew up at work...

I really appreciate your help, Jim. It's still not working. I've learned a lot breaking down your script though. The FOR line kept giving me errors. After breaking it down time and time again until I was sure I understood it was right, I realized a semi-colon was needed on the line above (doh). For some reason, I also got an error with the $i = 0... it gave a 'Field numbering starts at 1' error (?), so I changed that to 1 testing it and the error went away, but I'm a little unnerved until I see the
results. I really thought that was supposed to be 0.

Anyway, where I'm hanging up now is the line:

while ($row = mysql_fetch_array($result, mysql_num))

Most of the places I could simply change 'mysql' to 'odbc' and have no troubles. This one just isn't going to do 'er. All the other mysql commands had similar functions in the manual. I can't find mysql_num or odbc_num in the manual. Not sure where to go from here.

Here's what I've got if you want to check it over (note: I haven't changed the second $1 = 0 to a 1 yet...one line at a time, you know):

<table width=&quot;560&quot; border=&quot;0&quot; cellspacing=&quot;3&quot; cellpadding=&quot;1&quot;>
<?
$query = &quot;select Num, Name from Staff&quot;;
$result = odbc_exec($conn_id,$query);
for ($i = 1; $i < odbc_num_fields($result); $i++)
{
// Print the column names as table column headers.
print &quot;<th>&quot;.ucfirst(odbc_field_name($result, $i)).&quot;</th>\n&quot;;
}

// Now output each column of the query result as a table cell.
while ($row = odbc_fetch_array ($result, odbc_num))
{
echo &quot;<tr>\n&quot;;
for ($i = 0; $i < odbc_num_fields($result); $i++)
{
echo &quot;\t<td>&quot;.$row[$i].&quot;</td>\n&quot;;
}
echo &quot;</tr>\n&quot;;
}
?>
</table>


Also one quick, semi-unrelated question. (Keep in mind my areas are SQL and Crystal...relatively little HTML and much less PHP)... Why the periods in the 'print' line and the 'echo' line near the end?

Thanks again, Jim, you've already helped immensely.

 

[skicamel]

Sorry, forgot... The error I'm receiving is:

Call to undefined function: odbc_fetch_array() in ...delete.php on line 25

while ($row = odbc_fetch_array ($result, odbc_num))
is line 25.

 

[JimEkleberry]

Yep, I sure did miss the semicolon on the 2nd line. Sorry about that.

Both of the for loops should start with 1. Good catch on that mistake as well.

The mysql_num states that the array returned from mysql_fetch_array should be numeric instead of associative. Check what type of array the odbc_fetch_array command returns and if there are any options for it. If it is associative we can modify the code to work with that.

The periods in the print and echo lines append the information, similar to the + sign in other languages. To get a better visual representation, this is how the print line might look in vbscript:
print &quot;<th>&quot; + ucfirst(odbc_field_name($result, $i)) + &quot;</th>\n&quot;;

I believe that I have responded to the concerns/questions that you had. Please let me know if I missed one. Again, I apologize for providing a faulty sample to you.

 

[DumTech]

>Call to undefined function: odbc_fetch_array() in ...delete.php on line 25

Just thinking 'aloud' here with merely a coupla month's worth of PHP experience, but...

Wouldn't this error mean just what it says? Where is/should the function actually be defined? Are you using an incorrect function name or did you forget an include or something...?

The form of the array doesn't even come into play if the fetch_array function isn't defined...

For that matter, why not use mysql_fetch_row instead of mysql_fetch_array if you want an array with a numeric index?

 

[DumTech]

Just found something else that may be relevant:

http://www.php.net/manual/en/function.odbc-fetch-array.php

Apparently the function currently does not work with PHP 4.1. There are some workarounds posted at that URL.

 

[JimEkleberry]

Okay, checking the PHP manual odbc_fetch_array doesn't work and causes an Undefined Function error; exactly what you are getting.

Change the
while ($row = odbc_fetch_array ($result, odbc_num))

line to be:
while ($row = odbc_fetch_row($result))

and everything else should work fine.

 

[skicamel]

I really can't express how thankful I am, but alas, still a no go. To make sure I'm not missing anything, or changed something since the last time, I'll drop in the script below. Now I'm returning the column names (and oddly half the columns are off the screen and can't be scrolled to. Or so I'm assuming as only the first half of the columns appear on the page. In this example only the Num column name appeared. I tried with a select all statement and only received the first half or so of the column names, but that seems more to do with the table, I'm guessing, than the actual pull.) But that can be dealt with another time. Right now, the issue is that while I'm not getting any error messages, no rows are being returned. I dropped in this line:

odbc_result_all ($result);

just above the first For line to check that the data is there (I'm a lot more reckless on the home machine with backups galore, but still not like me to lose an entire table's data). It returned all the rows. So up to that point everything went smoothly. Here's the current up-to-date script. I went through the rest of the script checking the manual and everything looked right. (By the way, thanks for the information on the periods in the print & echo lines. I haven't seen that before.) Anyway, here it is. Hopefully, you'll see something that I don't. Thanks, again.

<table width=&quot;560&quot; border=&quot;0&quot; cellspacing=&quot;3&quot; cellpadding=&quot;1&quot;>
<?
$query = &quot;select Num, Name from Staff&quot;;
$result = odbc_exec($conn_id,$query);
for ($i = 1; $i < odbc_num_fields($result); $i++)
{
// Print the column names as table column headers.
print &quot;<th>&quot;.ucfirst(odbc_field_name($result, $i)).&quot;</th>\n&quot;;
}

// Now output each column of the query result as a table cell.
while ($row = odbc_fetch_row($result))

{
echo &quot;<tr>\n&quot;;
for ($i = 1; $i < odbc_num_fields($result); $i++)
{
echo &quot;\t<td>&quot;.$row[$i].&quot;</td>\n&quot;;
}
echo &quot;</tr>\n&quot;;
}
?>
</table>

 

[skicamel]

Okay, there are issues...
Adding

echo odbc_num_rows($result);

gives me a -1. Not my favorite number. I'll keep digging, but any ideas would be helpful.

 

[JimEkleberry]

Add the code for your connection to the following and save it as a .php file and then run it. It should let us know where the script is falling down.

<html>
<head>
<title></title>
<style type=&quot;text/css&quot;>
td, body {font-family: helvetica, arial, sans-serif; font-size: 10px; color: #0;}
th {font-family: helvetica, arial, sans-serif; font-size: 11px; color: #0;}
</style>
</head>
<body>
<table width=&quot;100%&quot; border=&quot;0&quot; cellspacing=&quot;3&quot; cellpadding=&quot;1&quot;>
<?
$query = &quot;select num,name from staff&quot;;
$result = odbc_exec($conn_id, $query);
$rsnumfields = odbc_num_fields($result);
echo &quot;We are working with $rsnumfields fields.<br>\n&quot;;
for ($i = 1; $i < $rsnumfields; $i++)
{
// Print the column names as table column headers.
$fieldname = ucfirst(odbc_field_name($result, $i));
echo &quot;The current field name is: $fieldname<br>\n&quot;;
print &quot;<th>$fieldname</th>\n&quot;;
}

// Now output each column of the query result as a table cell.
while ($row = odbc_fetch_row($result))

{
echo &quot;<tr>\n&quot;;
$numfields = odbc_num_fields($result);
for ($i = 1; $i < $numfields; $i++)
{
echo &quot;\t<td>$row[$i]</td>\n&quot;;
}
echo &quot;</tr>\n&quot;;
}
?>
</table>
</body>
</html>

 

[skicamel]

Here's the output:

We are working with 2 fields.
The current field name is: Num

Num ​

I added one more field to the pull and got this:

We are working with 3 fields.
The current field name is: Num
The current field name is: Name
num Name

 

[skicamel]

I changed the for lines to <= and I'm getting all the column names.

 

[JimEkleberry]

Okay so we're getting some data, at least enough to get the column names and print them out. Yes, the for loops should be <=.

The question of the moment is why isn't any of your data printing out. I know that the code works with mysql.

What type of database are you connecting to?

 

[JimEkleberry]

Well duh, reading the manual we have to use odbc_result after making the row available with odbc_fetch_row.

So the lines:
while ($row = odbc_fetch_row($result))
{
echo &quot;<tr>\n&quot;;
$numfields = odbc_num_fields($result);
for ($i = 1; $i < $numfields; $i++)
{
echo &quot;\t<td>$row[$i]</td>\n&quot;;
}
echo &quot;</tr>\n&quot;;
}

should be:
while ($row = odbc_fetch_row($result))
{
echo &quot;<tr>\n&quot;;
$numfields = odbc_num_fields($result);
for ($i = 1; $i <= $numfields; $i++)
{
$fielddata = odbc_result($row, $i);
echo &quot;\t<td>$fielddata</td>\n&quot;;
}
echo &quot;</tr>\n&quot;;
}

That should do the trick nicely.

 

[skicamel]

Hot dang, we've got 'er. There was one hitch, though, so you're aware. I saw that same thing in the manual last night and couldn't get the syntax right. Error after error.

Supplied argument is not a valid ODBC result resource

Turns out what you've got there gives the same error, but was much easier to fix than the poor versions I was toying around with. On the $fieldata line, changing the query_id from $row to $result did the trick.

Thanks, Jim. I don't know how I could have nailed this down without you. It really is greatly appreciated.

Dan

 

[JimEkleberry]

Glad to have been of some help although I wish I could have been better at it.

We both learned a few things out of this.

Have a good one and good luck!

Jim Ekleberry

 

[skicamel]

You couldn't have been better at it. If you dropped the answer in my lap, I wouldn't have learned as much as I did. Don't know about you, but I find there's no better learning method than troubleshooting.

Thanks and God bless!

Dan