Jump to content

How To Create A Mysql Table Grid


creacon
 Share

Recommended Posts

I want to buld a page that displays, in grid form, the records obtained from a MySQL query. Then be able to select certain records for other processing.I've been searching everywhere I can think of, and everything I come up with is close, but no cigar; it just dances around the edges of what I need.Can someone tell me how this is done, and/or where I can find out how to do it?

Link to comment
Share on other sites

I think you know how to connect to a database, so if you have connected to a database with among other a country id and the name of the country it'll look like this.

<html> <head>  ... </head><body>  ...<table> <tr>   <th>id</th>   <th>name</th> </tr><?php $query = "SELECT country_id, country FROM Countries ORDER BY country_id"; $result = mysql_query($query); while($row = mysql_fetch_array($result)) {?> <tr>  <td><?php print($row['country_id']); ?></td>  <td><?php print($row['country']); ?></td> </tr><?php}?></table></body></html>

This should give a table with the country and the country ids. Modify it to fit your database.

Link to comment
Share on other sites

that is a good basic method for creating the 'table grid'.For "other processing", what exactly did you have in mind?Often there might be an Edit or Delete link beside each row. Use the ID number in the link then have the script pull the information for that specific ID number and Edit or Delete it.You will need a script to do the add/edit/delete functions.

Link to comment
Share on other sites

I think you know how to connect to a database, so if you have connected to a database with among other a country id and the name of the country it'll look like this.
<html> <head>  ... </head><body>  ...<table> <tr>   <th>id</th>   <th>name</th> </tr><?php $query = "SELECT country_id, country FROM Countries ORDER BY country_id"; $result = mysql_query($query); while($row = mysql_fetch_array($result)) {?> <tr>  <td><?php print($row['country_id']); ?></td>  <td><?php print($row['country']); ?></td> </tr><?php}?></table></body></html>

This should give a table with the country and the country ids. Modify it to fit your database.

Thank you so much for this. I sort of thought it might have to be done with a table, but I just didn't know how (I'm a newbie).
Link to comment
Share on other sites

that is a good basic method for creating the 'table grid'.For "other processing", what exactly did you have in mind?Often there might be an Edit or Delete link beside each row. Use the ID number in the link then have the script pull the information for that specific ID number and Edit or Delete it.You will need a script to do the add/edit/delete functions.
The MySQL table consists of job applicant records for which we do background checks for various companies. In addition, we process data for "Work Opportunity Tax Credits (WOTC)", on behalf of those companies. The records consist of the company id, applicant's personal information and answers to a government questionnaire. When an applicant is hired, we forward the WOTC information to another agency which performs the verification of the answers to the questionnaire. When the questionnaire is initiaged, its record gets an action code of "New", and the current date. After 60 days of no new action posted, we notify the company of an action due for those applicants. What I had hoped to do is to have a checkbox to the left of each line, so that the user can check the records to be processed, based on an "Action" code and date. The records which have an action due w/be selected and their information emailed to the companies to whichthe applicants belong. The various companies would then post the necessary action codes/dates to the MySQL Applicant Table (e.g. Hired/Rejected). We then follow up on the next scan performing the appropriate action (i.e. delete the records for the Rejected applicants and forward the records for the Hired applicants to another agency for various credential verifications.
Link to comment
Share on other sites

  • 2 weeks later...
I think you know how to connect to a database, so if you have connected to a database with among other a country id and the name of the country it'll look like this.
<html> <head>  ... </head><body>  ...<table> <tr>   <th>id</th>   <th>name</th> </tr><?php $query = "SELECT country_id, country FROM Countries ORDER BY country_id"; $result = mysql_query($query); while($row = mysql_fetch_array($result)) {?> <tr>  <td><?php print($row['country_id']); ?></td>  <td><?php print($row['country']); ?></td> </tr><?php}?></table></body></html>

This should give a table with the country and the country ids. Modify it to fit your database.

YUP, I did that, and modified it according to my database/table, found out how to get the grid lines (aka border), and it works fine. However, I'd like to have the rows numbered, and a checkbox to the left of each row so the user can check the records that require action. Edited by creacon
Link to comment
Share on other sites

Well, to change data in a table its best to always send the ID of the required row. I also like to doe everything within php, it makes the script easier to read. I did not test my version below but it is one of the many solutions:

<html><head>  ...</head><body>  ...<?phpecho "<table>";echo "<tr>";echo "   <th>#</th>";echo "   <th>id</th>";echo "   <th>name</th>";echo "   <th>check</th>";echo "</tr>";echo "<form action='processdata.php' method='post'>";$i=1; //Start counting at 1$query = "SELECT country_id, country FROM Countries ORDER BY country_id";$result = mysql_query($query);while($row = mysql_fetch_array($result)) {  echo "  <tr>  echo "	<td>".$i."</td>"; //Show counter  echo "	<td>".$row['country_name']."</td>";  echo "	<td>".$row['country_id']."</td>";  //Make checkbox with an unique checkbox-ID (based on country_id because it is always the unique key) and give country_name as value  echo "	<td><input type='checkbox' name='".$row['country_id']."' value='".$row['country_name']."'></td>";  echo "  </tr>  $i++; //Add 1 to $i;}echo "</table>";echo "<input type='submit' name='SubmitCountry' value='Modify checked country(s)'>"; // Submit the checked itemsecho "</form>";?></body></html>

The form is made. Now you have to process the checked country's in processdata.php like below:processdata.php

<?php//Check if the user pressed the submit-buttonif(isset($_POST['SubmitCountry'])){  //Show message that a country list will be displayed below  echo "<strong>The following country's were checked:</strong>";  //Get every POST variable  foreach($_POST AS $key => $value)  {	//Filter the posted submit-button	if($key != SubmitCountry)	{	 //Show list of checked country's. (off-course you can do anything in this part) 	  echo $value."<br />";	}  }}?>

Link to comment
Share on other sites

Well, to change data in a table its best to always send the ID of the required row. I also like to doe everything within php, it makes the script easier to read. I did not test my version below but it is one of the many solutions:
I tried this, but am getting errors. I think it should work, however, because I'm sure the error is mine. Here's my code:At the top of the page is the following line:
<?php require_once('Connections/login.php'); ?>

Which is the call to my connection script. Here's what it contains:

<?php# FileName="Connection_php_mysql.htm"# Type="MYSQL"# HTTP="true"$hostname_login = "localhost";		// Change to "mysql" when uploaded to Yahoo$database_login = "infoondemand";$username_login = "root";			// Change to "creacontech" when uploaded to Yahoo$password_login = "**********";$login = mysql_pconnect($hostname_login, $username_login, $password_login) or trigger_error(mysql_error(),E_USER_ERROR);$_SESSION['MM_login'] = $login;?>

Now the above was working in my previous attempt, which was copied from your previous post. Then I copied/modified the code in your most recent post, as follows:

<div id="apDiv1">  <p> </p>  <p> </p>  <p> </p><?phpecho "<table>";echo "<tr>";echo "   <th>#</th>";echo "   <th>DLName</th>";echo "   <th>SSN</th>";echo "   <th>Actn Cd</th>";echo "   <th>Actn Dt</th>";echo "   <th>check</th>";echo "</tr>";echo "<form action='processdata.php' method='post'>";$i=1; //Start counting at 1$query = "SELECT * FROM infoondemand ORDER BY DLName";$result = mysql_query($query);while($row = mysql_fetch_array($result))     //   THIS IS LINE 101 THAT THE ERROR MESSAGE REFERENCES{  echo "  <tr>";  echo "    <td>".$i."</td>"; //Show counter  echo "    <td>".$row['DLName']."</td>";  echo "    <td>".$row['SSN']."</td>";  echo "    <td>".$row['ActionCode']."</td>";  echo "    <td>".$row['ActionDate']."</td>";  //Make checkbox with an unique checkbox-ID (based on country_id because it is always the unique key) and give country_name as value  echo "    <td><input type='checkbox' name='".$row['country_id']."' value='".$row['country_name']."'></td>";  echo "  </tr>";  $i++; //Add 1 to $i;}echo "</table>";echo "<input type='submit' name='SubmitCountry' value='Modify checked country(s)'>"; // Submit the checked itemsecho "</form>";?></div>

I haven't added your second (processing) part, because every time I execute the page, I get the following error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\IOD\WOTCGridTest.php on line 101

I put a comment on line 101. Can you tell me what could be causing this problem. I really like your approach using php, but also can you tell me how to get a vertical scroll bar included? The table can grow quite large (tens of thousands of records by the end of a year). I'll need to present these by customer (i.e. CustID), then within customer, by applicant's names. I guess that's going to require a double loop (i.e. a loop within a loop) right?OK, I found out what's causing the error, but don't know how to fix it. I changed the "$result=" line to read$result = mysql_query($query) or die(mysql_error());and the error I now get is "No database selected", and I don't know what could be causing that. I copied/pasted the database name from my connection script, so I know the name is correct. Could it be that the connection isn't getting executed, and if so , why not? The call is at the very top of the page above the <html> tag. I understood that any php code there would execute immediately as the page loads/opens(?!), or am I supposed to overtly execute it somehow. If so, how?

Edited by creacon
Link to comment
Share on other sites

You need to use mysql_select_db after mysql_connect or mysql_pconnect:http://us.php.net/manual/en/function.mysql-select-db.php
Again, "Here comes JSG to save the day". I guess I must have my head where the sun never shines; where's the dunce cap? I copied the php connection script from another page that I had working, and apparently overlooked that all important one line of code that was nestled in the very next script.Thanks again for unconfusing me.Now all I have to do is to find out how to add a vertical scroll bar. The list will become quite long as job seekers apply for jobs. This page is to be able to notify the employers of applicants whose questionnaires are over n days old and ask them to post a new action code ("hired", "dropped" or "hold").Also, I'd like to include the second script, which processes the checked rows at the top of the page to process via a form action= "$_SERVER['PHP_SELF']", but since the form itself is written in php, I can't seem to get the right notation for that.
echo "<form action='processdata.php' method='post'>";

In previous pages I was able to use that since the form was in html (e.g.):

<form name="QstnrPg1" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" id="QstnrPg1">

But in this case the form is:

echo "<form action='processdata.php' method='post'>";

and every way I try to enter the action= I get an error. What would be the proper way to do that???

Link to comment
Share on other sites

Either of these will work:

echo "<form action='{$_SERVER['PHP_SELF']}' method='post'>";echo "<form action='" . $_SERVER['PHP_SELF'] . "' method='post'>";

For the scrollbars, if you use CSS to set the overflow property of a container to "auto", when the content gets too large for the container scrollbars will appear automatically.

Link to comment
Share on other sites

Either of these will work:
// the code for the data grid is hereecho "</table>";echo "<br /><br /><br />";echo "<input type='submit' name='Submit' value='Process checked applicant(s)'>"; // Submit the checked itemsecho "</form>";

My question is this, will the expanding table push the button down until the AP Div is full, or will the button be overwritten? Since I only have three records in my test table, I can't test that question, and it's a lengthy process to add more records since that table has a large number of fields.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...