[SOLVED] Any ideas????

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

[SOLVED] Any ideas????

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Moved to Databases.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

Post 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]
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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!
stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

Post by stebut05 »

Hi,

I want to put the value of TaskId into a hidden value of a form. I usuall do the following:

Code: Select all

<?php echo $taskID; ?>
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
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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().
stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

Post 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
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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!
stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

Post by stebut05 »

sorry printf,

I changed have changed that, but still same problem, no vlalue is getting echoed for

Code: Select all

<?php echo $row['taskID']; ?>
Any ideas??? Thanks for your patience, help and advice

Ste
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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...
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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);
Post Reply