[Resolved] using PHP to query from a MySQL db and display results

mkr

Hi All,

I am developing a PHP / MySQL web application.
For searching the db the user keys in an id in a html form. The form variable is taken to a PHP page which scans through the db to check whether the id is existing or not. If yes. then a select stmt and then neatly display the fields in a tabulated manner. So far so good.
My earlier code went like this :

<?php
$result=mysql_query("select * from gen where id = ".$_POST['id'] );
if (mysql_num_rows($result) == 0) {
print "SORRY"; }
else
{
print "<table>\n";
while ($get_info = mysql_fetch_row($result))
{
print "<tr>";
print :<th>xxxx</th>";
print "<tr>";
foreach ($get_info as $field)
print "<td>$field</td>";
print "</table>";
}
}
?>

The output was in a table of 2 rows as :
Name Address Tel No.
aaa bbb 123

But i wish the output was like :
Name : aaa
Address : bbb
Tel No. : 123
All in a table without a border and the output of the select statement fields should be filled in automatically. When i simply change the layout of the table , then each row is getting filled up with all select fields.
I guess i can't use foreach in the second case. Me totally lost .

Pls. help me with the syntax.
Thank you in advance.

madwormer2

<?php
$result=mysql_query("select * from gen where id = ".$_POST['id'] );
if (mysql_num_rows($result) == 0) {
print "SORRY"; }
else
{
print "<table border='0' cellspacing='1' cellpadding='1'>\n";
while ($get_info = mysql_fetch_row($result))
{
echo "<tr><td> Name:</td><td> " . $get_info->name . "</td></tr>\n";
echo "<tr><td> Address:</td><td> " . $get_info->address . "</td></tr>\n";
echo "<tr><td> Tel. no.:</td><td> " . $get_info->tel . "</td></tr>";
}
echo "</table>";
}
?>

Obviously the things after $get_info-> will be the row names in the MySql table

I think this is what you want 😕

😉

wempy

If you change the fetch statement to use

while ($get_info = mysql_fetch_array($result))

you can then retrieve the fields by name like so

print "<table>\n";
while ($get_info = mysql_fetch_array($result,MYSQL_BOTH))
{
    print "<tr>\n";
    print "<td>Name</td>\n";
    print "<td>" . $get_info["name"] . "</td>\n;
    print "</tr>\n<tr>\n";
    print "<td>Address</td>\n";
    print "<td>" . $get_info["address"] . "</td>\n;
    print "</tr>\n<tr>\n";
print "<td>Phone</td>\n";
    print "<td>" . $get_info["phone"] . "</td>\n;
    print "</tr>\n";
}
print "</table>\n";

two things to note here are that 1. the associative array format of mysql_fetch_array is the not the default, it will actually return an array with keys that are the db column names and also numeric indexes (0-2 in your case). To have it just return the associative array use a second parameter to the function call of MYSQL_ASSOC, the MYSQL_BOTH above is optional.
2. the "\n" inserts a carriage return into the output so that if you view the source in your browser the layout is easier to read, otherwise it will end up on one line.

madwormer2

Thats the same thing.

wempy

Thats the same thing.

what is?

madwormer2

Never mind

wempy

it is so pleasing when people shower you with gratitude, is it not?

madwormer2

Well, you messed up the code you posted anyway 😛

mkr

thanks a lot madwormer2 and wempy for responding and offering solutions.
I am trying them out but both the tricks are not working . I 'll keep trying and keep you posted madwormer2 wempy

AcousticJames

Originally posted by madwormer2

<?php
$result=mysql_query("select * from gen where id = ".$_POST['id'] );
if (mysql_num_rows($result) == 0) {
print "SORRY"; }
else
{
print "<table border='0' cellspacing='1' cellpadding='1'>\n";
while ($get_info = mysql_fetch_row($result))
{
echo "<tr><td> Name:</td><td> " . $get_info->name . "</td></tr>\n";
echo "<tr><td> Address:</td><td> " . $get_info->address . "</td></tr>\n";
echo "<tr><td> Tel. no.:</td><td> " . $get_info->tel . "</td></tr>";
}
echo "</table>";
}
?>

Correct me if I'm wrong, but mysql_fetch_row() returns an array, not an object.

http://www.php.net/manual/en/function.mysql-fetch-row.php

So using a "->" operator won't work, as you're not accessing object values, you're accessing array elements, so you'd use "[]" instead, using integers as array indices (0 based, of course).

Using mysql_fetch_array, however, allows you to access the array values using the table column names defined in MySQL, which is a lot cleaner from a coding point of view. So wempy's suggestion should work by all means. Have you made sure you are using the correct table column names (the same ones your MySQL table has)?

Then again, I'm just a n00b. I could be completely wrong.

James

mkr

Wempy's code works ! Thanks all of you . esp wempy.
The missing double quotes at the end of code was giving trouble.

"</td>\n";

madwormer2

I probably screwed up somewhere... I had been pissing around all day with code and got all muddled up.

I got an idea, try this at the top of any code I give you:

die("Madwormer2 is an idiot");

Oh and I know where I went wrong, I didn't change mysql_fetch_row() to mysql_fetch_object() which is what I had been using for my other really annoying H4X0R scripts.

hits head on wall