kemper Posted December 12, 2006 Share Posted December 12, 2006 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] Quote Link to comment Share on other sites More sharing options...
Cep Posted December 12, 2006 Share Posted December 12, 2006 You lost me, what is it you want to do? Quote Link to comment Share on other sites More sharing options...
kemper Posted December 12, 2006 Author Share Posted December 12, 2006 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. Quote Link to comment Share on other sites More sharing options...
craygo Posted December 12, 2006 Share Posted December 12, 2006 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]<?phpecho "<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 outI also notices you have 2 queries doing the same thing. might want to get rid of one.Ray Quote Link to comment Share on other sites More sharing options...
kemper Posted December 12, 2006 Author Share Posted December 12, 2006 Thanks Ray.I will give this a try. I do know the fields names. I just could not figure out the coding.Jay Quote Link to comment Share on other sites More sharing options...
kemper Posted December 13, 2006 Author Share Posted December 13, 2006 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 24Age Age & Division Team [/code] Line 24 is: while($row=mysql_fetch_array($result)) {What am I doing wrong? Quote Link to comment Share on other sites More sharing options...
Cep Posted December 13, 2006 Share Posted December 13, 2006 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. Quote Link to comment Share on other sites More sharing options...
kemper Posted December 13, 2006 Author Share Posted December 13, 2006 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! Quote Link to comment Share on other sites More sharing options...
simcoweb Posted December 13, 2006 Share Posted December 13, 2006 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. Quote Link to comment Share on other sites More sharing options...
kemper Posted December 13, 2006 Author Share Posted December 13, 2006 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! Quote Link to comment Share on other sites More sharing options...
simcoweb Posted December 13, 2006 Share Posted December 13, 2006 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] Quote Link to comment Share on other sites More sharing options...
kemper Posted December 13, 2006 Author Share Posted December 13, 2006 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! Quote Link to comment Share on other sites More sharing options...
kemper Posted December 13, 2006 Author Share Posted December 13, 2006 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 24Age Age & Division Team[/code]Any suggestions? Line 24 is: [code]while($row=mysql_fetch_array($result)) {[/code]Thanks!Jay Quote Link to comment Share on other sites More sharing options...
kemper Posted December 13, 2006 Author Share Posted December 13, 2006 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 Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted December 13, 2006 Share Posted December 13, 2006 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... Quote Link to comment Share on other sites More sharing options...
kemper Posted December 13, 2006 Author Share Posted December 13, 2006 Error:Using MySQL database info:No database selectedI 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! Quote Link to comment Share on other sites More sharing options...
simcoweb Posted December 13, 2006 Share Posted December 13, 2006 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 company2. database username - every database have to have a user assigned to it that has specific permissions to operate it3. database password - self explanatory4. [b]database name[/b] - every database has a name. You need to find out what this isSo, 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! Quote Link to comment Share on other sites More sharing options...
kemper Posted December 14, 2006 Author Share Posted December 14, 2006 Damn! I am a frick'n idiot! I have no idear what I was thinking. All's well. Quote Link to comment Share on other sites More sharing options...
kemper Posted December 17, 2006 Author Share Posted December 17, 2006 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! Quote Link to comment Share on other sites More sharing options...
simcoweb Posted December 17, 2006 Share Posted December 17, 2006 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"; Quote Link to comment Share on other sites More sharing options...
kemper Posted December 18, 2006 Author Share Posted December 18, 2006 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? Quote Link to comment Share on other sites More sharing options...
craygo Posted December 18, 2006 Share Posted December 18, 2006 I copy and pasted your code and it looks fine. Also why would you check the &db variable twice???why not do this[code]<?phpinclude("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 Quote Link to comment Share on other sites More sharing options...
simcoweb Posted December 18, 2006 Share Posted December 18, 2006 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] Quote Link to comment Share on other sites More sharing options...
kemper Posted December 19, 2006 Author Share Posted December 19, 2006 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 $resultand received:[quote]Query was empty[/quote] Quote Link to comment Share on other sites More sharing options...
simcoweb Posted December 19, 2006 Share Posted December 19, 2006 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.