Page 1 of 2
[SOLVED] Any ideas????
Posted: Mon Apr 17, 2006 3:17 pm
by stebut05
Hi all,
Thanks for looking at my thread, would appreciate as much help as possible even though its a bank holiday!! Here it goes, it have a really difficult query to perform, well difficult for me. I need to query a few tables to show repeated results.
Cards - CardID (PK)
Tasks - TaskID (PK), CardID (FK)
SignOff - TaskID (FK), NoBoxes
InspecSign - TaskID (FK)
MechSign - TaskID (FK)
Basically i want to display all the rows from the Task table where the CardID in the table is equal to the cardID on card table and where CardID in card table is equal to '$cardID'.
This is where its gets complicated. In the task table some task might have a signOff if so then i want to display the NoBoxes, but some tasks dont have signOff. Do i do a join on this query, if so, any ideas??? thanks in advance for any help, advice, etc
Regards,
Steven
Posted: Mon Apr 17, 2006 3:26 pm
by John Cartwright
Moved to Databases.
Posted: Mon Apr 17, 2006 3:29 pm
by feyd
Yes, you will use a JOIN or three to perform what you want.
Try you hand at a query for a bit. Post what you try, what the database returns and how that compares to what you want. We'll nudge you toward the right things as you go.
Posted: Mon Apr 17, 2006 3:37 pm
by stebut05
Hi,
I have tried this, but only the taskLetter is getting returned.
Code: Select all
SELECT jobcardTask.cardID, jobcardTask.TaskID, jobcardTask.taskLetter, jobcardTask.taskNotes, jobcardTask.taskWarning, jobcardTask.mechanicSignOff, jobcardTask.inspectorSignOff, signOff.inspecNoBoxes, signOff.mechNoBoxes FROM jobcardTask LEFT JOIN signOff ON signOff.taskID = jobcardTask.taskID WHERE jobcardTask.cardID = '$cardID'
any ideas??? Thanks for your help
Regards,
Steven
Jcart | changed Code: Select all
tags to [syntax="sql"] tags[/color][/size]
Posted: Mon Apr 17, 2006 4:38 pm
by printf
Can you please show your table scheme so I can see what your relationships. You said you want to JOIN
Tasks, based on the
CardID in the
Cards table equaling the
CardID in the
Tasks table, which you example query does not do! You have to create your
JOIN, so that you include all the relationships, so you return exactly what you want...
Say if you want select items in the tasks table, based on the cardid matching what is in the cards table, you could do something like this!
Code: Select all
$sql = "SELECT tt.* FROM Cards AS ct
LEFT JOIN Tasks AS tt ON(ct.CardID = tt.CardID)
WHERE ct.CardID = '" . $cardID . "'";
Which would return all rows from the tasks table matching the cardid in the cards table! Now you also want to include the signoff table, but you want to do a certain thing based on what is found in the tasks table. So please show your db scheme for all these tables, so we can see all the relationships, because your example is confusing!
printf!
Posted: Mon Apr 17, 2006 5:30 pm
by stebut05
Hi,
sorry for not explaining very well. I dont need to query the Card Table i can complete the query with a variable '$cardID'. I have managed to run the query:
Code: Select all
"SELECT jobcardTask.cardID, jobcardTask.TaskID, jobcardTask.taskLetter, jobcardTask.taskDescription, jobcardTask.taskNotes, jobcardTask.taskWarning, jobcardTask.mechanicSignOff, jobcardTask.inspectorSignOff, signOff.inspecNoBoxes, signOff.mechNoBoxes FROM jobcardTask LEFT JOIN signOff ON signOff.taskID = jobcardTask.taskID WHERE jobcardTask.cardID = '$cardID'"
A quick question, how do i echo the taskID now. I am trying to echo taskID to put has a hidden value into a form, but no scuh luck. any ideas??
Thanks again for any help, advice, etc
Regards,
Steven
Posted: Mon Apr 17, 2006 6:09 pm
by RobertGonzalez
stebut05 wrote:A quick question, how do i echo the taskID now. I am trying to echo taskID to put has a hidden value into a form, but no scuh luck. any ideas??
When you say echo, do you mean print to a browser? Or do you mean something else?
Posted: Mon Apr 17, 2006 6:20 pm
by stebut05
Hi,
I want to put the value of TaskId into a hidden value of a form. I usuall do the following:
This will normally put the value of taskId into the form variable, but i have tried to echo the value of taskID from the query above. Any ideas???
Thanks, Ste
Posted: Mon Apr 17, 2006 6:24 pm
by printf
You could use
extract();
Code: Select all
$result = mysql_query ( "......query_in_here....");
if ( mysql_num_rows ( $result ) > 0 )
{
extract ( mysql_fetch_array ( $result, MYSQL_ASSOC ) );
echo '<input type="hidden" name="hidden_input" value="' . htmlentities ( $taskID, ENT_COMPAT ) . '" />';
}
Or just use the
['index'] from the result array...
Code: Select all
$result = mysql_query ( "......query_in_here....");
if ( mysql_num_rows ( $result ) > 0 )
{
$row = mysql_fetch_array ( $result, MYSQL_ASSOC );
echo '<input type="hidden" name="hidden_input" value="' . htmlentities ( $row['taskID'], ENT_COMPAT ) . '" />';
}
pif!
Posted: Mon Apr 17, 2006 6:29 pm
by RobertGonzalez
What is your complete query code? It should something along the lines of run a query, read the query result into an array (or string value), then at some point echo that value. I won't write the code for you (as there are many, many examples of this throughout this community), but I will say that you should look at
the MySQL family of functions for PHP (specifically
mysql_query(),
mysql_result() and
mysql_fetch_array().
Posted: Mon Apr 17, 2006 6:31 pm
by stebut05
Hi,
I thought that i could do the following using the query mentioned above
Code: Select all
$query = "SELECT jobcardTask.cardID, jobcardTask.TaskID, jobcardTask.taskLetter, jobcardTask.taskDescription, jobcardTask.taskNotes, jobcardTask.taskWarning, jobcardTask.mechanicSignOff, jobcardTask.inspectorSignOff, signOff.inspecNoBoxes, signOff.mechNoBoxes FROM jobcardTask LEFT JOIN signOff ON signOff.taskID = jobcardTask.taskID WHERE jobcardTask.cardID = '$cardID'"
$query = mysql_query($result, $mro) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$totalRows = mysql_num_rows($result);
Then in my form i though i could do the following:
Code: Select all
<input name="assigntaskID" type="hidden" value="<?php echo $row_query2['taskID']; ?>" />
Is this incorrect???
Thanks, ste
Posted: Mon Apr 17, 2006 6:35 pm
by printf
No, $row is the result array, not $row_query2!
So...
Code: Select all
<input name="assigntaskID" type="hidden" value="<?php echo $row['taskID']; ?>" />
//or, short tags
<input name="assigntaskID" type="hidden" value="<?=$row['taskID'];?>" />
pif!
Posted: Mon Apr 17, 2006 6:42 pm
by stebut05
sorry printf,
I changed have changed that, but still same problem, no vlalue is getting echoed for
Any ideas??? Thanks for your patience, help and advice
Ste
Posted: Mon Apr 17, 2006 10:39 pm
by RobertGonzalez
Code: Select all
<?php
$query = "SELECT j.*, s.inspecNoBoxes, s.mechNoBoxes
FROM jobcardTask j
LEFT JOIN signOff s ON s.taskID = j.taskID
WHERE j.cardID = '$cardID'";
$result = mysql_query($query, $mro) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
$my_array = $row;
}
$totalRows = count($my_array);
?>
<input name="assigntaskID" type="hidden" value="<?php echo $my_array['taskID']; ?>" />
See if this works for you...
Posted: Tue Apr 18, 2006 6:41 am
by printf
It's because you keep changing your result handles ($query, $result);
change this...
Code: Select all
$query = "SELECT jobcardTask.cardID, jobcardTask.TaskID, jobcardTask.taskLetter, jobcardTask.taskDescription, jobcardTask.taskNotes, jobcardTask.taskWarning, jobcardTask.mechanicSignOff, jobcardTask.inspectorSignOff, signOff.inspecNoBoxes, signOff.mechNoBoxes FROM jobcardTask LEFT JOIN signOff ON signOff.taskID = jobcardTask.taskID WHERE jobcardTask.cardID = '$cardID'"
$query = mysql_query($result, $mro) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$totalRows = mysql_num_rows($result);
to this...
Code: Select all
$query = "SELECT jobcardTask.cardID, jobcardTask.TaskID, jobcardTask.taskLetter, jobcardTask.taskDescription, jobcardTask.taskNotes, jobcardTask.taskWarning, jobcardTask.mechanicSignOff, jobcardTask.inspectorSignOff, signOff.inspecNoBoxes, signOff.mechNoBoxes FROM jobcardTask LEFT JOIN signOff ON signOff.taskID = jobcardTask.taskID WHERE jobcardTask.cardID = '$cardID'"
$result = mysql_query($query, $mro) or die(mysql_error());
$row = mysql_fetch_assoc($result);
$totalRows = mysql_num_rows($result);