[SOLVED] Help with a somewhat complicated script idea...

[h2oskierc]

I have a databse where I want to compare parts of a few records.  I am thinking I need to store each record to an array, and then compare all the arrays that have the same proofID number.

 

Basically, I have three tables, a user table (storing each customer's information), a proof table (containing our invoice number, a proof ID, and some other information) and a sign table, storing information about each sign for the proof (including the image for the proof, size, colors, and the proof's status).  The proof's status is either a (approved), c (changes needed) or d (declined).  What I am trying to do is make a list of the customer's proofs, that has an overview of their job's and proofs' status.

 

http://www.flickr.com/photos/h2oskierc/2682648759/

 

Basically, what I need to do is write a script that looks at all the records for a given proof, and displays Approved if all the signs have an approved status, Approved with changes if one or more signs are approved with changes, but none are declined and lasty declined if one or more signs has a declined status.  This would be the last column in the table above.  I would also like it to say something like sent, or No if the status hasn't been set.

 

Can anybody help me out with where to start with this?

 

Would I be best off to add a new column to the proofs database for a, c, or d and then on the page that the customer updates the database record for each sign, find a way to have that go through the logic and assign a status to each individual proof as well?

[Barand]

SELECT p.invoice_number, p.po_number,p.proofID, p.proof_date,
     SUM( IF(s.status='a', 1, 0)) as approved,
     SUM( IF(s.status='c', 1, 0)) as changed,
     SUM( IF(s.status='d', 1, 0)) as declined
FROM proof p INNER JOIN sign s ON p.proofID = s.proofID
GROUP BY proofID

 

This will give how many of each status for the proofs. Now it's just a matter of checking the counts.

 

[h2oskierc]

mysql_select_db($database_proofs, $proofs);
$query_getProofStatus = sprintf("SELECT proofs.proofID, proofs.proofNum, proofs.userID, proofs.invoice, proofs.po,
DATE_FORMAT(proofs.created, "%c/%e/%y %l:%i%p") AS created, signs.status, signs.proofID, users.userID, users.pass, users.email,
SUM ( IF(signs.status='a', 1, 0)) as approved,
SUM ( IF(signs.status='c', 1, 0)) as changes,
SUM ( IF(signs.status='d', 1, 0)) as declined
FROM proofs, signs, users
INNER JOIN signs.status ON proofs.proofID = signs.proofID
WHERE users.email = %s  AND users.pass = %s  AND users.userID = proofs.userID AND proofs.proofID = signs.proofID",

 

Why doesn't this work? Server Error Below"

 

Warning: sprintf() [function.sprintf]: Too few arguments in D:\dellsigns\proofs\current_proofs_WIP.php on line 54

Query was empty

 

I am trying to display the date as 7/20/08 7:07pm.  Not sure if the date portion is messing this up.  I am using dreamweaver, and I noticed that it put the sprintf(... in there.  I still haven't mastered coding SQL myself yet.

 

Thanks again, you all have been such a great help!

[h2oskierc]

If I remove the sprintf(...

 

I have:

mysql_select_db($database_proofs, $proofs);
$query_getProofStatus = "SELECT proofs.proofID, proofs.proofNum, proofs.userID, proofs.invoice, proofs.po,
DATE_FORMAT(proofs.created, '%c/%e/%y %l:%i%p') AS created, signs.status, signs.proofID, users.userID, users.pass, users.email,
SUM ( IF(signs.status='a', 1, 0)) as approved,
SUM ( IF(signs.status='c', 1, 0)) as changes,
SUM ( IF(signs.status='d', 1, 0)) as declined
FROM proofs, signs, users
INNER JOIN signs.status ON proofs.proofID = signs.proofID
WHERE users.email = '$email' AND users.pass = '$pass'  AND users.userID = proofs.userID AND proofs.proofID = signs.proofID";
GetSQLValueString($var1_getProofStatus, "text");
GetSQLValueString($var2_getProofStatus, "text");
$getProofStatus = mysql_query($query_getProofStatus, $proofs) or die(mysql_error());
$row_getProofStatus = mysql_fetch_assoc($getProofStatus);
$totalRows_getProofStatus = mysql_num_rows($getProofStatus);

 

And the result from the server is:

 

Table 'signs.status' doesn't exist

 

Any ideas with that code?

[h2oskierc]

After some playing around, I was able to find a SQL query that worked:

 

mysql_select_db($database_proofs, $proofs);
$query_getProofStatus = sprintf("
SELECT proofs.invoice, proofs.po, proofs.proofID, DATE_FORMAT(proofs.created,  '%%c/%%e/%%y %%l:%%i%%p') AS date,
     SUM(IF(signs.status='a', 1, 0)) AS approved,
     SUM(IF(signs.status='c', 1, 0)) AS changes,
     SUM(IF(signs.status='d', 1, 0)) AS declined,
users.userID, users.pass, users.email, users.firstName, proofs.proofNum
FROM proofs, signs, users
WHERE signs.proofID = proofs.proofID AND users.email = %s AND users.pass = %s AND users.userID = proofs.userID GROUP BY proofID", GetSQLValueString($var1_getProofStatus, "text"),GetSQLValueString($var2_getProofStatus, "text"));

$query_limit_getProofStatus = sprintf("%s LIMIT %d, %d", $query_getProofStatus, $startRow_getProofStatus, $maxRows_getProofStatus);
$getProofStatus = mysql_query($query_limit_getProofStatus, $proofs) or die(mysql_error());
$row_getProofStatus = mysql_fetch_assoc($getProofStatus);

 

This is the way that Dreamweaver codes the statement.  Then I used a if, elseif, else statement to set the status the way I needed.

 

Thanks for getting me pointed in the right direction!