[RESOLVED] Displaying tables with Form fields

[Arnora]

Hi all,

As always thank you for all your assistance so far.

K firstly I will show my session information and my database table

{
	$_SESSION['auth']="yes";
	$logname=$_POST['lUsername'];
	$_SESSION['logname'] = $logname;
	$today = date("Y-m-d H:i:s");
	header("Location: current.php");
	$sql = "INSERT INTO Login (Username,LoginTime)
	VALUES ('$logname','$today')";
	mysql_query($sql) or die("Can't execute query");		
}

K the session stores Today and Username

The table I need displaying (This is how it WILL BE created)

CREATE TABLE Available_Workload (
ID	INT	NOT NULL	AUTO_INCREMENT,
StartDate	DATE	NOT NULL,
EndDate	DATE	NOT NULL,
Areas	VARCHAR(255)	NOT NULL,
Quantity	INT(4)	NOT NULL,
Available	CHAR(3)	NOT NULL	DEFAULT "Yes",
PRIMARY KEY (ID) );

This will eventually store lots of info

I require a page that our distributors can view the available workload and select their own jobs. (1 row = 1 job)
So far I've been updating the page every month with table and form fields created in html.

Anyway Can The above information be pulled from the database (without ID) and displayed in a Table? The table would need to display only the available workload for Today AND the next 28 days from the stored session variable Today.

Also where the field "Available" is "Yes" I would like to have a Form field where the distributor can enter his Username. The distributor could select as many or as few as he/she decides. If the field "Available" is "No" then "No" should be displayed.

Once selected and "Submit" is pressed I need the available field to change to "No".

AND the selected fields to be emailed to our "info" address.

Finally once all that is done the distributor would be directed to success.htm

I do hope that makes sense and even more that it can be accomplished.

Additional questions. Is the above MySQL query correct? Should I use the ID field? Should I create a primary key?

Thank you all
Arnora

[Arnora]

Apologies the SESSION does not store Today, so lets make it.

<?php 
session_start();
if (@$_SESSION['auth'] !="yes")
{
	header("location:index.php");
	exit();
}
	include("db.inc");

$connection = mysql_connect($hostname,$username,$password)
					or die ("Could not connect to server");
$db = mysql_select_db($database,$connection)
				or die ("Could not connect to database");
$sql = "SELECT FirstName,LastName FROM Distributors
           WHERE Username ='{$_SESSION['logname']}'";
$result = mysql_query($sql)
        or die("Couldn't execute query for result");
if(mysql_num_rows($result)>0)   //there are results returned 
{
 $row = mysql_fetch_array($result); 
 extract($row); 
 }
 $today = date("Y-m-d");
 $importantdate = date("Y-m-d", strtotime("+28 days"));
?>

Ok so we have the Session authorisation and we've selected FirstName and LastName from Distributors to echo later. And we have defined $today and $importantdate

I've checked that this works with a few echo's later on the page

<p align="center"><?php echo " Welcome $FirstName $LastName "; ?></p>

<p align="center"><?php echo " Todays date is $today "; ?>  </p>
<p align="center"><?php echo " 28 days from now is $importantdate "; ?>  </p>

Now I have attempted to drag info from the database using

<table>  <?php $sql2 = "SELECT StartDate, EndDate, Areas, Quantity, Available FROM Available_Workload
	 		WHERE StartDate BETWEEN $today AND $importantdate";
	$result2 = mysql_query($sql2)
			or die ("Couldn't execute query for result2");
	if (mysql_num_rows($result2) > 0) {

    while($row = mysql_fetch_array($result)) {
?>
       <tr>
<?php
            foreach($row as $key => $value) {
?>
            <td><?php echo $value; ?></td>
<?php
            }
?>
        </tr>
<?php
        }
    }
?>
</table>

But so far this is showing absolutely nothing which leads me to believe it's not finding any results. I can see all results up to an empty table then remaining html.

If I query the database from within phpmyadmin (replacing $today with 2010-09-11 and $important date with 2010-10-08

I find 6 results (StartDate for these 6 is 2010-10-01).

Anyone able to help here?

Arnora

[NogDog]

You could add an ELSE after the mysql_num_rows() IF block, to output something if no rows were returned, and help focus in on where the problem is or isn't.

[Arnora]

Ok
After I realised the session didn't store the timestamp I created the timestamps required on the page but when selecting from the database these timestamps didn't work (probably my own fault).

So I reverted to a form of selection I've already used once $_SESSION to find information stored in the session and check my results with that.
In order to do this I changed the session to store a little more info.

{
$_SESSION['auth']="yes";
$logname=$_POST['lUsername'];
$_SESSION['logname'] = $logname;
$today = date("Y-m-d H:i:s");
$importantdate = date("Y-m-d", strtotime("+28 days"));
$_SESSION['importantdate'] = $importantdate;
$now = date("Y-m-d");
$_SESSION['now'] = $now;
header("Location: current.php");
$sql = "INSERT INTO Login (Username,LoginTime)
VALUES ('$logname','$today')";
mysql_query($sql) or die("Can't execute query");		
}

My original timestamp required was 'today' but I want only the Y-m-d selection, as you can see 'today' stores current time as well and is used to insert data into another table, rather than changing everything I defined 'now'.

K so I've got my timestamps now to display the info

<?php 
$sql2 = "SELECT * FROM Available_Workload 
WHERE StartDate BETWEEN '{$_SESSION['now']}' 
AND '{$_SESSION['importantdate']}' 
AND Available='Yes' ORDER BY ID";
$result2 = mysql_query($sql2)
or die ("Couldn't execute query for result2");
$row = mysql_fetch_array($result2) or die(mysql_error());
while($row = mysql_fetch_array($result2)) {
echo $row['ID']. " ". $row['StartDate']. " ". $row['EndDate']. " ". $row['Areas']. " ". $row['Quantity']. " ". $row['Available'];}
?>

Now there is two things wrong with this. (more actually but a couple at a time)

1.) The results include only the rows that are between (now) and (importantdate) where available='yes'. Great except that row 1 or ID 1 doesn't display and this row falls within the parameters expressed above.

If I use an SQL query, replacing the timestamps with dates,

SELECT * FROM Available_Workload 
WHERE StartDate BETWEEN "2010-09-12" 
AND "2010-10-10" 
AND Available="Yes" ORDER BY ID

row 1 or ID 1 shows correctly? (baffled now)

2.) The results that do show are all on one line. Great if you don't actually want to read the info and are just hopeing to fill your pages with mumbo jumbo.

Please help
Arnora

[Arnora]

Ok I've managed to display the info in a table.

<?php 
	$sql2 = "SELECT * FROM Available_Workload 
			WHERE StartDate BETWEEN '{$_SESSION['now']}' 
			AND '{$_SESSION['importantdate']}' 
			AND Available='Yes' ORDER BY ID";
	$result2 = mysql_query($sql2)
			or die ("Couldn't execute query for result2");
	$row = mysql_fetch_array($result2) or die(mysql_error());
	/*Display results in a table*/
	echo "<form action=' ' method='post' name='login' id='login'>";
	echo "<table cellspacing='2' border='0' cellpadding='0' width='702'>";
	echo "<tr><td colspan='6' align='center'>
			<hr>
			</td></tr>\n";			
	while($row = mysql_fetch_array($result2,MYSQL_ASSOC)) 
	{
		/*Display row for each available job*/
		echo "<tr>\n";
		echo "<td>{$row['ID']}</td>\n";
		echo "<td>{$row['StartDate']}</td>\n";
		echo "<td>{$row['EndDate']}</td>\n";
		echo "<td>{$row['Areas']}</td>\n";
		echo "<td>{$row['Quantity']}</td>\n";
		echo "<td><input name='{$row['ID']}' type='text' size='20'></td>\n";
		echo "</tr>\n";
	}
	echo "<tr><td colspan='6' align='center'>
			<hr>
			</td></tr>\n";
	echo "<tr><td colspan='6' align='center'>
			<input type='submit' name='submit' id='submit' value='Confirm Selection'>
			</td></tr>\n";			
	echo "</table></form>\n";

?>

Which looks great, I've got my selection fields and submit button. Lovely.
Though my selection is still not showing (row 1 or ID1) still confused here.

Ok so how can I format the dates? I would like to see d/m/Y though currently I'm viewing Y-m-d as stored in the database.

Once these little nigles are sorted I'll move onto the form action (currently blank)

Any help at all please
Arnora

[NogDog]

I'd recommend formatting it in the query:

SELECT *, DATE_FORMAT(`date_column_here`, '%d/%m/%Y') AS `date_dmy` FROM . . .

[Arnora]

Thanks NogDog

I had managed this before I saw your comment 😛

<?php 
	$sql2 = "SELECT * FROM Available_Workload 
			WHERE StartDate BETWEEN '{$_SESSION['now']}' 
			AND '{$_SESSION['importantdate']}' 
			AND Available='Yes' ORDER BY ID";
	$result2 = mysql_query($sql2)
			or die ("Couldn't execute query for result2");
	$row = mysql_fetch_array($result2) or die(mysql_error());
	/*Display results in a table*/
		echo "<form action=' ' method='post' name='login' id='login'>";
		echo "<table cellspacing='2' border='0' cellpadding='0' width='702' style='text-align: center'>";
		echo "<tr>\n";
		echo "<td width='107'><h3>StartDate</h3></td>\n";
		echo "<td width='132'><h3>EndDate</h3></td>\n";
		echo "<td width='156'><h3>Areas</h3></td>\n";
		echo "<td width='95'><h3>Quantity</h3></td>\n";
		echo "<td width='200'><h3>Email Selection</h3></td>\n";
		echo "</tr>\n";
		echo "<tr>
				<td colspan='5' align='center'>
				<hr>
				</td>
				</tr>\n";			
	while($row = mysql_fetch_array($result2,MYSQL_ASSOC)) 
	{
		$StartDate = date_create($row['StartDate']);
		$f_StartDate = date_format(date_create($row['StartDate']), 'j,M,Y');
		$EndDate = date_create($row['EndDate']);
		$f_EndDate = date_format(date_create($row['EndDate']), 'j,M,Y');
		/*Display row for each available job*/
		echo "<tr>\n";
		echo "<td>$f_StartDate</td>\n";
		echo "<td>$f_EndDate</td>\n";
		echo "<td>{$row['Areas']}</td>\n";
		echo "<td>{$row['Quantity']}</td>\n";
		echo "<td><input name='{$row['ID']}' type='text' size='20'></td>\n";
		echo "</tr>\n";
	}
		echo "<tr>
				<td colspan='6'>
				<hr>
				</td>
				</tr>\n";
		echo "<tr>
				<td colspan='6' align='center'>
				<input type='submit' name='submit' id='submit' value='Confirm Selection'>
				</td>
				</tr>\n";			
		echo "</table></form>\n";
?>

A little change to requested format but I actually like this layout a little better.

I still have the issue of my first result (ID=1) not showing in the table. But am gonna try and add a duplicate row to the database (think the next ID increment would be 31) and see if it finds that one.

Now for the form action to store posted username and email results then redirect user.

I think I can lay this discussion to bed as resolved

Thanks again NogDog
Arnora