Table problems.

[Braveheartt]

Thought I'd post a new thread with new questions, instead of derailing my other thread. Easier for people to help, easier for people to find if having similar problems etc. All round better idea I hope!

 

 

1. I'm trying to get my table to order correctly (newest entry at the top of the HTML table). At the moment for some reason new entries just go to a random spot on my table! ???

 

2. I'm also trying to get the "ID" field to start at 0 and add an increment of 1 per new table row. The method I tried is in the below code. (It didn't work, it just obviously made them all 1!)

 

3. Last problem is the screenshots part, I don't want them to show if the field is empty! Right now it shows a link to well.. nothing . I'm thinking I should use something like

if (isset($screen1))

?

 

 

Anyway. Here's my full code:

 

<?php require("menu.php");	
$id = 0;
$ida = $id += 1;
//Connect to MYSQL server
mysql_connect("Localhost", "*removed*", "*removed*") or die(mysql_error());
   //echo "<center><font color='white'>Successfully connected to MySQL!</font></center><br />"; 

//Connect to MYSQL database   
mysql_select_db("*removed*") or die(mysql_error());
   echo "<center><font color='white'><h4>Successfully connected to the Bugs Database!</h4></font></center>";
   
// Retrieve all the data from the "example" table
$result = mysql_query("SELECT * FROM bugs")
or die(mysql_error());  

// store the record of the "example" table into $row
echo "<CENTER><TABLE BORDER='2' BORDERCOLOR='#336699' CELLPADDING='2' BGCOLOR='#000000' CELLSPACING='2' WIDTH='100%'></CENTER>";
echo "<tr> <th><font color='#FFFFFF'>ID</font></th> <th><font color='#FFFFFF'>Priority</font></th> <th><font color='#FFFFFF'>Bug Type</font></th> <th><font color='#FFFFFF'>Poster</font></th> <th><font color='#FFFFFF'>Bug Description</font></th> <th><font color='#FFFFFF'>Screenshot 1</font></th> <th><font color='#FFFFFF'>Screenshot 2</font></th> <th><font color='#FFFFFF'>Screenshot 3</font></th></tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td align='center'>";
echo "<font color='white'>".$ida."</font>"; 
echo "</td><td align='center'>";
if($row['priority'] == Urgent)
   echo "<strong><font color='#FF0000'>Urgent</font></strong>";
elseif($row['priority'] == Medium)   
     echo "<strong><font color='#FF6600'>Medium</font></strong>";	
  elseif($row['priority'] == Low)   
     echo "<strong><font color='#FFCC00'>Low</font></strong>";
  echo "</td><td align='center'>";
echo "<font color='#FFFFFF'>".$row['type']."</font>";
echo "</td><td align='center'>"; 
echo "<font color='#FFFFFF'>".$row['poster']."</font>";
echo "</td><td align='center'>"; 
echo "<TEXTAREA wrap='hard' name='des' rows=3 cols=60 COLOR='#FFFFFF' readonly='yes' MAXLENGTH=9999>".$row['description']."</TEXTAREA>";
echo "</td><td align='center'>"; 	
echo "<A HREF=".$row['screen1']."><font color='#FFFFFF'>Screenshot1</font></A>";
echo "</td><td align='center'>";  
echo "<A HREF=".$row['screen2']."><font color='#FFFFFF'>Screenshot2</font></A>";
echo "</td><td align='center'>";
echo "<A HREF=".$row['screen3']."><font color='#FFFFFF'>Screenshot3</A>";
  echo "</td></tr>";
} 
echo "</TABLE>";

   ?></font></center></body></html> 

 

 

Thanks!

[Jeremysr]

1. Use this query: "SELECT * FROM bugs ORDER BY ID DESC"

 

2. Set $ida to 0 above the loop, then put "$ida++;" at the very end of the loop (just before it loops back.)

 

3. Use "if (!empty($row['screen1'])) {"

[eddierosenthal]

sounds like you might want to insert date and time stamp to sort on that field,

so that most recent entries can be listed first, let the sql create the sorted list

order by datetime desc etc...

 

as for starting at 1, you can create the table with an autoincrement value that increases

by one each entry, and use that as your key.

again, let the database do the work for you.

hth

 

 

 

[Braveheartt]

Thanks a lot guys!

 

2. Set $ida to 0 above the loop, then put "$ida++;" at the very end of the loop (just before it loops back.)

 

It works!! but one problem, each new bug will push every ID down, I want to "assign" the ID number and have it stay. So the newest input comes first in the list (working now) but will keep the ID that is assigned. So when more additions are made, that entry will always have the ID it was submitted with. So basically the ID's starting at the bottom from 0 and working up, to ensure they're not pushed down. 

[eddierosenthal]

if you are making additional notes to the 'bug' after the primary entry record is created, then you probably

want to create a second table using the id from the original record as a foreign key in the second table .

then you can make lots of records associated with the original 'bug' record.

also you can have a stored procedure always create a new id for  you, as well as insert the current date and time stamp in a field in the record for  you.

 

[Braveheartt]

Alright. Could you help me in doing that ?

 

I saw in the MYSQL table, there is an ID function which assignes each entry it's OWN ID. Is it the "PRIMARY KEY(id),"? How could I call that in PHP and use that for the table bug ID?

[eddierosenthal]

when you create the table set one of the fields to 'autoincrement'. this will be unique to the field, always have a unique id to it, even if you have deleted and inserted any records, when it autoincrements it will always be unique. you can have an index on it when there are lots of records in the db if it appears slow.

integer, autoincrement.

after you insert a record, to get the value of the new key using the function mysql_insert_id()

$query = "insert into tablename ( bla bla bla) values bla bla bla

$result = mysql_query($query);

printf ( "new id is %d ", mysql_insert_id());

 

[Braveheartt]

What is wrong with this code?

 

 if(isset($_POST['fixed']))
       echo "<strong><font color='#00FF00'>Fixed by".$_POST['editn']."!</font></strong>";
    elseif(isset($_POST['work']))   
       echo "<strong><font color='#0033FF'>".$_POST['editn']."is working on it!</font></strong>";
    else
     echo "<strong><font color='#FFCC00'>Not yet taken</font></strong>"; 

 

It should take the POST text input from another page and display whatever was put into the field in "editn". What have I done wrong?

 

 

Input code:

echo "<form method='POST' action='bugs_main.php'> <input type='text' value='Your name' name='editn'></form>";
    echo "<form method='POST' action='bugs_main.php'> <input type='submit' value='Fixed!' name='fixed'><br /></form>";
    echo "<form method='POST' action='bugs_main.php'> <input type='submit' value='Working on' name='work'></form>";

 

Bear in mind those pieces of code are part of a larger script. They work! Just whatever is put into the "editn" field doesn't display with the "Fixed by...!" "... is working on it!" strings.

[eddierosenthal]

try

    <form method='POST' action='bugs_main.php'> <input type='text' value="" name='editn'>

    <input type='submit' value='Fixed!' name='fixed'><br />

    <input type='submit' value='Working on' name='work'>

    </form>