adding a column which creates a number (104) based upon date created [Archive]

View Full Version : adding a column which creates a number (104) based upon date created

webguync

April 3rd, 2009, 12:46 PM

Hi,

I have an HTML table with extracted data from a MySQL table and I still need to figure out one more thing.

What I have is a set of scores uploaded via an application into the DB, and I want to have a column based on when they were created (earliest to latest) which adds a 1-4 into that column. I already have a column which captures the date created, so I am hoping this will be fairly easy. I want the 1-4 to start over whenever there is a different four letters in the employee_id column, so it would order 1-4 for someone with an employee ID of ADEC, and then start over 1-4, with an employeeID of ADKI.

to help better understand here is the code I have thus far. Everything work well, just need to add the extra column with data I mention above.

<html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Scores</title>
<link href="report.css" rel="stylesheet" type="text/css" />
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
</head>
<body>
<?php
$con = mysql_connect("localhost","username","pw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("nnprinceton_p1", $con);

$result = mysql_query("SELECT *
FROM tablename ORDER BY employee_id");

echo "<table>
<tr>
<th>Score ID</th>
<th>Employee ID</th>
<th>Employee Name</th>
<th>score 1</th>
<th>score 2</th>
<th>score 3</th>
<th>score 4</th>
<th>score 5</th>
<th>score 6</th>
<th>Assessor Name</th>
<th>Assessor ID</th>
<th>Call Number (1-4)</th>
<th>Date Created</th>
<th>Date Uploaded</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['score_id'] . "</td>";
echo "<td>" . $row['employee_id'] . "</td>";
echo "<td>" . $row['employee_name'] . "</td>";
echo "<td>" . $row['score1'] . "</td>";
echo "<td>" . $row['score2'] . "</td>";
echo "<td>" . $row['score3'] . "</td>";
echo "<td>" . $row['score4'] . "</td>";
echo "<td>" . $row['score5'] . "</td>";
echo "<td>" . $row['score6'] . "</td>";
echo "<td>" . $row['assessor_name'] . "</td>";
echo "<td>" . $row['assessor_id'] . "</td>";
echo "<td>" . $row['Need code to increment 1-4 based on date_created and employee_id'] . "</td>";
echo "<td>" . $row['date_created'] . "</td>";
echo "<td>" . $row['date_uploaded'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);

?>
</body>
</html>

NeoDreamer

April 5th, 2009, 07:16 PM

Please give a concrete example of the this 1-4 "ordering" and "starting overing". I'm not getting what you're trying to say.

webguync

April 6th, 2009, 10:24 AM

Sorry, I wasn't as clear as I needed to be in my first post.

Basically what I am looking for is this:

score ID Employee ID Assessor ID Score 1 Score 2 Call Number date created

1 AGLC RBYS 5 6 1 2009-02-19 07:21
2 AGLC KVJS 6 6 2 2009-02-19 08:15
3 AGLC MLDQ 4 4 4 2009-02-19 08:30
4 AGLC MYUG 5 5 3 2009-02-19 08:17
1 JCZS RBYS 5 6 1 2009-02-19 07:45
2 JCZS KVJS 6 6 2 2009-02-19 08:45
3 JCZS MLDQ 4 4 3 2009-02-19 08:30
4 JCZS MYUG 5 5 2 2009-02-19 08:17

where in the column call number the numbers 1-4 are generated based upon the date created column where 1 would go in the earliest date and increment up to 4. The 1-4 incrementation would start over for each new employee ID. In this example AGLC, and JCZS.

Hope what I am trying to accomplish makes more sense!

NeoDreamer

April 6th, 2009, 11:59 AM

<?php

$count = 0;
$numberThatStartsOver = 1;

while($row = mysql_fetch_array($query))
{
$currentID = $row['employeeID'];

if($count == 0 || $currentID != $previousID)
$numberThatStartsOver = 1;
else
$numberThatStartsOver++;

echo 'the crazy number that starts over is: ' . $currentID;

$previousID = $currentID;

$count++;
}

?>

webguync

April 6th, 2009, 01:45 PM

thanks, a couple of questions on this.

how would I add the $numberThatStartsOver to my HTML output.

the other columns pull from the MySQL table are are like this

echo "<td>" . $row['employee_id'] . "</td>";

also how would I have that column order by the timestamp in the date_created field (earliest to latest)?

NeoDreamer

April 6th, 2009, 02:38 PM

Answer to question 1:

$count = 0;
$numberThatStartsOver = 1;

while($row = mysql_fetch_array($result))
{

$currentID = $row['employee_id'];

if($count == 0 || $currentID != $previousID)
$numberThatStartsOver = 1;
else
$numberThatStartsOver++;

echo "<tr>";
echo "<td>" . $row['score_id'] . "</td>";
echo "<td>" . $row['employee_id'] . "</td>";
echo "<td>" . $row['employee_name'] . "</td>";
echo "<td>" . $row['score1'] . "</td>";
echo "<td>" . $row['score2'] . "</td>";
echo "<td>" . $row['score3'] . "</td>";
echo "<td>" . $row['score4'] . "</td>";
echo "<td>" . $row['score5'] . "</td>";
echo "<td>" . $row['score6'] . "</td>";
echo "<td>" . $row['assessor_name'] . "</td>";
echo "<td>" . $row['assessor_id'] . "</td>";
echo "<td>" . $numberThatStartsOver . "</td>";
echo "<td>" . $row['date_created'] . "</td>";
echo "<td>" . $row['date_uploaded'] . "</td>";
echo "</tr>";

$previousID = $currentID;

$count++;

}
echo "</table>";

Answer to question 2:

$result = mysql_query("SELECT *
FROM tablename ORDER BY date_created ASC");

webguync

April 6th, 2009, 03:40 PM

I'm not getting the CallNumber column to increment (the $numberThatStartsOver, which I changed to $CallNumber), also the sorting by date_created ASC needs to pertain only per employee_id, so I only want to sort 1-4, per user_ID.

here is the code as I currently have it.

<html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
</head>
<body>
<?php
$con = mysql_connect("localhost","userName","pw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("dbName", $con);

$result = mysql_query("SELECT *
FROM rpc012009 ORDER BY employee_id");
$count = 0;
$CallNumber = 1;

while($row = mysql_fetch_array($result))
{

$currentID = $row['employee_id'];

if($count == 0 || $currentID != $previousID)
$CallNumber = 1;
else
$CallNumber++;

echo "<table>
<tr>
<th>Score ID</th>
<th>Employee ID</th>
<th>Employee Name</th>
<th>score 1</th>
<th>score 2</th>
<th>score 3</th>
<th>score 4</th>
<th>score 5</th>
<th>score 6</th>
<th>Assessor Name</th>
<th>Assessor ID</th>
<th>Call Number (1-4)</th>
<th>Date Created</th>
<th>Date Uploaded</th>
</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['score_id'] . "</td>";
echo "<td>" . $row['employee_id'] . "</td>";
echo "<td>" . ucwords($row['employee_name']) . "</td>";
echo "<td>" . $row['score1'] . "</td>";
echo "<td>" . $row['score2'] . "</td>";
echo "<td>" . $row['score3'] . "</td>";
echo "<td>" . $row['score4'] . "</td>";
echo "<td>" . $row['score5'] . "</td>";
echo "<td>" . $row['score6'] . "</td>";
echo "<td>" . $row['assessor_name'] . "</td>";
echo "<td>" . $row['assessor_id'] . "</td>";
echo "<td>" . $CallNumber . "</td>";
echo "<td>" . $row['date_created'] . "</td>";
echo "<td>" . $row['date_uploaded'] . "</td>";
echo "</tr>";

$previousID = $currentID;

$count++;
}
}
echo "</table>";

mysql_close($con);

?>
</body>
</html>

NeoDreamer

April 6th, 2009, 04:10 PM

That's because your code has a double nested while loop. Mine didn't. Remove your inner while.

Just to make it clear, I haven't been providing the entire code for your solution. I've just been programming the specific area that you're having trouble with. It's meant to be adapted and integrated into your code. You seem to think otherwise.

And for your second question:

$result = mysql_query("SELECT *
FROM tablename ORDER BY employee_id, date_created ASC");

webguync

April 7th, 2009, 11:34 AM

I think I have what I need now more or less. Thanks for the help!