Code to display fields of a table

[kemper]

My page is displaying all content of the table, but I only want to display the fields from of table.  Assistance is greatly appreciated.  This is what I have thus far:
[code]
<?php

  include("config.inc.php");

  if ($db == 1) {
    echo "Using MySQL database info:

\n\n";
  } elseif ($file_db == 1) {
    echo "Using file-based database:

\n";
  } else {
    echo "Sorry. In order to use the administration portal you must use the MySQL or file-based database option.\n\n";
  }

  if ($file_db == 1 && $db != 1) {
    $filedb = fopen("data.dat","r") or die ("
Could not open data file to read.");
    $columns_str = fgets($filedb,4096);
    $columns = explode("|",$columns_str);
    echo "<table cellspacing=\"0\" cellpadding=\"4\" border=\"1\" width=\"90%\">\n";
    echo "<tr><td colspan=\"" . (sizeof($columns)+2) . "\">\n";
    echo "<table cellspacing=\"0\" cellpadding=\"2\" border=\"0\" width=\"100%\">\n";
    echo "<tr><td class=\"small\" bgcolor=\"#000000\" align=\"center\" valign=\"middle\">\n";
    echo "<font color=\"#ffffff\">Records Table</td></tr></table></td></tr><tr>\n";

    for ($i=0;$i<sizeof($columns);$i++) {
      echo "<td class=\"small\"  align=\"center\" valign=\"middle\">".$columns[$i]."</td>";
    }
    $i=0;
    while (!feof($filedb)) {
      $temp = fgets($filedb,4096);
      if ($temp != "") {
        $records[$i]=$temp;
        $i++;
      }
    }

    for($j=0;$j<$i;$j++) {
      echo "<tr>";
      $holder = explode("|",$records[$j]);
      for ($k=0;$k<sizeof($holder);$k++) {
        echo "<td class='small'><center>$holder[$k]</td>";
      }
    }
    echo "</table>";
  } else if($db==1) {
    // mySQL Table
    $db_con = mysql_connect("MyHost", "MyUser", "MyPassword") or die("Connetion to database failed!");
    mysql_select_db($dbname);
    $query = "select * from ".$table;
    $result = mysql_query($query);
    $i = 0;

    while ($i < mysql_num_fields($result)) {
      $meta = mysql_fetch_field($result);
      $columns[$i] = $meta->name;
      $i++;
    }

    echo "<table cellspacing=\"0\" cellpadding=\"4\" border=\"1\" width=\"90%\">\n";
    echo "<tr><td colspan=\"" . (sizeof($columns)+2) . "\">\n";
    echo "<table cellspacing=\"0\" cellpadding=\"2\" border=\"0\" width=\"100%\">\n";
    echo "<tr><td class=\"small\" bgcolor=\"#000000\" align=\"center\" valign=\"middle\">\n";
    echo "<font color=\"#ffffff\">Records Table</td></tr></table></td></tr><tr>\n";

    for($i=1;$i<sizeof($columns);$i++) {
      echo "<td class='small'><center>".$columns[$i]."</td>";
    }
    $query = "select * from ".$table;
    $result = mysql_query($query);
    $j=0;

    while($row=mysql_fetch_array($result)) {
      echo "<tr>";

      for($i=1;$i<sizeof($columns);$i++) {
        echo "<td class='small'><center>".$row[$columns[$i]]."</td>";
      }

      $j=$row[$columns[0]];
    }
    echo "</table>";
  }

  // mySQL ends

?>
[/code]

[Cep]

You lost me, what is it you want to do?

[kemper]

I am new to this stuff, but I do not know which line of this code states what is to be displayed.  The above code displayes all fields in the preset table.  I only want certain 3 fields of the 12 fields displayed.

[craygo]

This batch of code loops through your fields and data

[code]while ($i < mysql_num_fields($result)) {
      $meta = mysql_fetch_field($result);
      $columns[$i] = $meta->name;
      $i++;
    }

    echo "<table cellspacing=\"0\" cellpadding=\"4\" border=\"1\" width=\"90%\">\n";
    echo "<tr><td colspan=\"" . (sizeof($columns)+2) . "\">\n";
    echo "<table cellspacing=\"0\" cellpadding=\"2\" border=\"0\" width=\"100%\">\n";
    echo "<tr><td class=\"small\" bgcolor=\"#000000\" align=\"center\" valign=\"middle\">\n";
    echo "<font color=\"#ffffff\">Records Table</td></tr></table></td></tr><tr>\n";

    for($i=1;$i<sizeof($columns);$i++) {
      echo "<td class='small'><center>".$columns[$i]."</td>";
    }
    $query = "select * from ".$table;
    $result = mysql_query($query);
    $j=0;

    while($row=mysql_fetch_array($result)) {
      echo "<tr>";

      for($i=1;$i<sizeof($columns);$i++) {
        echo "<td class='small'><center>".$row[$columns[$i]]."</td>";
      }

      $j=$row[$columns[0]];
    }
    echo "</table>";
  }[/code]

If you would only like 3 fields you would have to do it manually.
[code]<?php
echo "<table>
<tr>
<td>First filed name</td>
<td>Second Field name</td>
<td>Third Field Name</td>
</tr>";
while($row=mysql_fetch_array($result)) {
echo "<tr>
<td>".$row['first_field_name']."</td>
<td>".$row['second_field_name']."</td>
<td>".$row['third_field_name']."</td>
</tr>";
}
echo "</table>";
?>[/code]

I do not know your field names so hopefully you can figure this out

I also notices you have 2 queries doing the same thing. might want to get rid of one.

Ray

[kemper]

Thanks Ray.

I will give this a try.  I do know the fields names.  I just could not figure out the coding.

Jay

[kemper]

OK.  I think I am close, but still getting an error.  Field names are populated correctly.  This is what I have so far:

[code]<?php

  include("config.inc.php");

  if ($db == 1) {
    echo "Using MySQL database info:<br><br>\n\n";
  }
 
  if($db==1) {
    // mySQL Table
    $db_con = mysql_connect("MyHost", "MyUser", "MyPassword") or die("Connetion to database failed!");
    mysql_select_db("MyDatabase");
    $query = "select * from "."MyTable";
    $result = mysql_query($query);
    $i = 0;

echo "<table>
<tr>
<td>Age</td>
<td>Age & Division</td>
<td>Team</td>
</tr>";

while($row=mysql_fetch_array($result)) {
  echo "<tr>
<td>".$row['Age']."</td>
<td>".$row['AgeDivision']."</td>
<td>".$row['Team']."</td>
</tr>";
}

echo "</table>";
  }

  // mySQL ends

?>[/code]

The resulting display is:

[code]Using MySQL database info:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/kemper/public_html/MySite/conflicts.php on line 24

Age  Age & Division  Team
[/code]

Line 24 is: while($row=mysql_fetch_array($result)) {


What am I doing wrong?

[Cep]

I imagine its this line,

[code=php:0]
$query = "select * from "."MyTable";
[/code]

I think something like

[code=php:0]
$query = "SELECT * FROM MyTable";

or

$query = "SELECT * FROM ".$table;
[/code]

would be more appropriate, also for future reference its best to captilise your SQL commands in a statement. It makes it clearer to read for one thing.

[kemper]

I have attempted those options.
Do I change the ".$table" to my table name (conflicts)? as in:
    $query = "SELECT * FROM "conflicts;

I do not see anywhere else that designated the Table to query from.

Thanks!

[simcoweb]

The correct syntax is as follows:

"SELECT [i]what[/i] FROM [i]where[/i]";

So, select what data from what table. In your case it would be like his first example:

[code]<?php
$query = "SELECT * (which means everything) FROM MyTable (the table name)";
?>[/code]

To expand it as an example it would be like:

[code]<?php
$query = "SELECT * FROM MyTable WHERE userid='Bob'";
?>[/code]

The WHERE clause specifies a precise group of data instead of just everything. And, furthermore, say you have 20 fields in your database but only wanted those 3. Instead of selecting everything using the * you could name the columns you want to extract from. Like this:

[code]<?php
$query = "SELECT field1, field2, field3 FROM MyTable";
?>[/code]

Make sense?

Also, in your last post you had this:

[quote]$query = "SELECT * FROM "conflicts;[/quote]

The double quotes for your SELECT statements go on both ends. Like this

[quote]$query = "SELECT * FROM conflicts";[/quote]

Your query would fail using what you had.

[kemper]

I want only select fields form a specific table to be displayed.  My Table name is "conflict" so is the correct code:

[code]<?php
$query = "SELECT * FROM conflict";
?>[/code]

?

And, since I only want select fields details form the table, do I need to select the specific fields when you say: "SELECT what FROM where";  ?

Thanks!

[simcoweb]

By using the * command you pull all the fields into an array. What you do with them is up to you. Say there's 20 fields. Using the * would summon all 20 into the array. If you want to display just 3 of them like you have then that's fine. If you want to display just 1 of them that's fine. But, that would be some lazy coding and wouldn't be very clear if you had to go back to it some day and see what you were trying to do there. If there's more than the 3 fields you might want to use the example I gave where you name the fields you want the query to pull from. No sense putting more burden on the server to pull a bunch of data you don't need :)

[code]<?php
$query = "SELECT Age, AgeDivision, Team FROM conflict";
?>[/code]

[kemper]

obviously, I have a lot to learn.  All was fine when things were lready setup for me and I was just making minor adjustments.

Thanks!

[kemper]

That is not the problem.  I am still getting:

[code]Using MySQL database info:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/kemper/public_html/MySite/conflicts.php on line 24

Age   Age & Division   Team[/code]

Any suggestions?  Line 24 is:
[code]while($row=mysql_fetch_array($result)) {[/code]

Thanks!

Jay

[kemper]

Again here is the code, with error being stated in line 24:

[code]<?php

  include("config.inc.php");

  if ($db == 1) {
    echo "Using MySQL database info:<br><br>\n\n";
  }
 
  if($db==1) {
    // mySQL Table
    $db_con = mysql_connect("MyHost", "MyUser", "MyPassword") or die("Connetion to database failed!");
    mysql_select_db("MyDatabase");
$query = "SELECT Age, AgeDivision, Team FROM conflict";
    $result = mysql_query($query);
    $i = 0;

echo "<table>
<tr>
<td>Age</td>
<td>Age & Division</td>
<td>Team</td>
</tr>";

while($row=mysql_fetch_array($result)) {
  echo "<tr>
<td>".$row['Age']."</td>
<td>".$row['AgeDivision']."</td>
<td>".$row['Team']."</td>
</tr>";
}

echo "</table>";
  }

  // mySQL ends

?>[/code]

Any suggestions?  Line 24 is:
[code]while($row=mysql_fetch_array($result)) {[/code]

Thanks!

Jay

[hitman6003]

change:

[code]$result = mysql_query($query);[/code]

to

[code]$result = mysql_query($query) or die(mysql_error());[/code]

Let us know what the new error is...

[kemper]

Error:

Using MySQL database info:

No database selected

I am a complete n00bie, but two lines above that line, I have:
[code]    mysql_select_db("MyDatabaseName");[/code]

No one has corrected me, if this is incorrect.

Thanks!

Yeaaaaaaaaaaa!   I am no longer a n00bie!  I'm a NonSpammer!

[simcoweb]

Well, if your database is truly named MyDatabaseName then you're golden. But, I seriously doubt that is the case. There's 4 things you have to have in order to create a database connection. Well, five if you count the fact you have to have a database to begin with.

1. database host - typically this is 'localhost' but may differ depending on hosting company
2. database username - every database have to have a user assigned to it that has specific permissions to operate it
3. database password - self explanatory
4. [b]database name[/b] - every database has a name. You need to find out what this is

So, typical connect code is:

mysql_connect('databasehost', 'databaseuser', 'databasepassword') or die(mysql_error());
mysql_db_select('databasename') or die(mysql_error());

Plug in the actual information for each of those items and voila!

[kemper]

Damn!  I am a frick'n idiot!  I have no idear what I was thinking.  All's well.

[kemper]

Next Question...

Using the same data and table, I want to be able to display results of a certain variable.  My variable would be a field containing a certain result.  Since two of my fields contain preset results from a dropdown menu, the choices are simple.

If my table consists of:
[table border="0" cellpadding="0" style="border-collapse: collapse" width="100%" id="table1"]
  [table][tr]
    [td][b]age[/b][/td]
    [td][b]division[/b][/td]
    [td][b]team[/b][/td]
  [/tr]
  [tr]
    [td]7[/td]
    [td]A[/td]
    [td]Team 1[/td]
  [/tr]
  [tr]
    [td]8[/td]
    [td]A[/td]
    [td]Team 2[/td]
  [/tr]
  [tr]
    [td]8[/td]
    [td]B[/td]
    [td]Team 3[/td]
  [/tr]
  [tr]
    [td]7[/td]
    [td]A[/td]
    [td]Team 4[/td]
  [/tr]
  [tr]
    [td]9[/td]
    [td]A[/td]
    [td]Team 5[/td]
  [/tr]
  [tr]
    [td]8[/td]
    [td]A[/td]
    [td]Team6[/td]
  [/tr]
[/table]
[/table]

How would I be able to allow my visitors to select to see all content of rows with 7 in "age" field of table?

My overall intentions are to have an option to have visitor select which "age" and show content of the selected "age" via selection from dropdown menu or something to that effect.

Assistance is greatly appreciated!

[simcoweb]

Simple, you'd provide them with a link that places the age in it. Like this:

http://www.mysite.com/view.php?age=7  (or similar to this example)

When they click on the link it summons a script (in this case I named it view.php) and the script uses a $_GET statement to capture the age. Like this:

$age = $_GET['age'];

Then your mysql statement would be something like:

$sql = "SELECT * FROM nameoftable WHERE age='$age'";

Then run it through a while loop:

echo "<table width='500' border='0'><tr>
        <th>Age></th><th>Division</th><th>Team</th></tr>";

while ($row = mysql_fetch_array($sql) {
echo "<tr><td>" . $row['age'] . "</td><td>" . $row['division'] . "</td><td>" . $row['team'] . "</td></tr>";

}
echo "</table>\n";

[kemper]

I am doing something wrong here.

Code:
[code]<?php

  include("config.inc.php");

  if ($db == 1) {
    echo "<center><font face='Arial' size='4' color='#000000'><b>Results:</b></font></center><br><br>\n\n";
  }
 
  if($db==1) {
    // mySQL Table
    $db_con = mysql_connect(MyHost, MyUser, MyPassword) or die("Connetion to database failed!");
    mysql_select_db(MyDatabase);
$age = $_GET['age'];
$sql = "SELECT age, division, team FROM MyTable";
$result = mysql_query($query) or die(mysql_error());
$i = 0;

echo "<table width='500' border='0'><tr>
         <th>Age></th><th>Division</th><th>Team</th></tr>";

while ($row = mysql_fetch_array($sql)) {
echo "<tr><td>" . $row['age'] . "</td><td>" . $row['division'] . "</td><td>" . $row['team'] . "</td></tr>";

}
echo "</table>\n";
  }

  // mySQL ends

?>[/code]

This got me error:
[code]Parse error: syntax error, unexpected '{' in /home/kemper/public_html/MySite/view.php on line 21[/code]

Any suggestions?

[craygo]

I copy and pasted your code and it looks fine. Also why would you check the &db variable twice???

why not do this
[code]<?php
include("config.inc.php");

if($db==1) {
    echo "<center><font face='Arial' size='4' color='#000000'><b>Results:</b></font></center><br><br>\n\n";

    // mySQL Table
    $db_con = mysql_connect(MyHost, MyUser, MyPassword) or die("Connetion to database failed!");
    mysql_select_db(MyDatabase);
$age = $_GET['age'];
$sql = "SELECT age, division, team FROM MyTable";
$result = mysql_query($query) or die(mysql_error());
$i = 0;

echo "<table width='500' border='0'><tr>
        <th>Age></th><th>Division</th><th>Team</th></tr>";

    while ($row = mysql_fetch_array($sql)) {
    echo "<tr><td>" . $row['age'] . "</td><td>" . $row['division'] . "</td><td>" . $row['team'] . "</td></tr>";
    }
echo "</table>\n";
}
  // mySQL ends
?>[/code]

Ray

[simcoweb]

craygo's right on that. No sense running multiple queries for the same thing.

Just for fun, let's change this:

[quote]while ($row = mysql_fetch_array($sql)) {[/quote]

to this and try it:

[quote]while ($row = mysql_fetch_array($result)) {[/quote]

[kemper]

Man, I am confused here as to what I am doing wrong.

I copy and pasted the Code you included, including changing
[code]while ($row = mysql_fetch_array($sql)) {[/code] to read $result

and received:
[quote]Query was empty[/quote]

[simcoweb]

Yeah, that's because the variable name used for the query is not $result. In your code it's $query.

$result = mysql_query($query) or die(mysql_error());

But your SQL statement uses $sql as your variable name:

$sql = "SELECT age, division, team FROM MyTable";

SO! Your $result variable should be this:

$result = mysql_query($sql) or die(mysql_error());

If you read it as stated what it's saying is 'set this variable name '$result' to equal the mysql_query generated in my $sql SELECT statment'

Make sense? So, change that statement to:

$result = mysql_query($sql) or die(mysql_error());

Otherwise, as it sits right now, there's no such query as $result. That's why it says the query is empty.