Page 1 of 1

Combining two results in a single column?

Posted: Mon Apr 22, 2002 9:34 pm
by Hebbs
Looking for an answer to this one.

I have a script that pulls results from several MySQL tables. What I want to do is combine two of the results into a single column heading.

The query goes something like this;

Code: Select all

SELECT dat_a_mainrequest.Number,RequestDate AS 'Request Date',requestA.CommNumber, requestB.CommNumber,RequestType AS 'Request Type',RequestStatus AS 'Status',location AS 'Office'
I want to post requestA.CommNumber and requestB.CommNumber under the single heading Requested Number.

Is this a possibility?

To further explain, I am creating the resulting columns on the fly using the following script;

Code: Select all

// Execute query which is not NULL.
for($i=1;$i<=3;$i++) {
	$query="query"."$i"; 
	if($$query == "") {
		continue;    // Skipping NULL queries
	}
	if (!($result = mysql_db_query($DB, $$query))) {
		DisplayErrMsg(sprintf("internal error %d:%s
", mysql_errno(), mysql_error()));
		exit() ;
	}
	if(($num_rows = mysql_num_rows($result))!=0) {
		$searchSuccess="YES";
		?>
		<DIV align="center"><CENTER>
			<TABLE border="0" cellpadding="3" cellspacing="2">
        	<TR>
			<?php
			// Find Nr of Fields returned and print as column titles.
			while($field = mysql_fetch_field($result)) {
				echo "<th nowrap bgcolor=$cfgThBgcolor>$field->name</th>";
			}
			echo "</tr>
";
			$rcol = 0; // Alternate row colouring - Pt 1.
			// Print each result row returned from the query.
			while ($row = mysql_fetch_row($result)) {
				$bgcolor = $cfgBgcolorOne; // Alternate row colouring - Pt 2.
        		$rcol % 2  ? 0: $bgcolor = $cfgBgcolorTwo; // Alternate row colouring - Pt 3.
        		echo "<TR bgcolor=$bgcolor>";
				// Traverse along row for number of fields returned by query.
				for ($k = 0; $k < mysql_num_fields($result); $k++) {
					if ($k == 1) {
						// Reformat date in ISO format YYYY-MM-DD to DD/MM/YYYY
						if (ereg ("(&#1111;0-9]{4})-(&#1111;0-9]{1,2})-(&#1111;0-9]{1,2})", $row&#1111;1], $regs)) {
							$row&#1111;1] = "$regs&#1111;3]/$regs&#1111;2]/$regs&#1111;1]";
						}
						if ($row&#1111;1] == "00/00/0000") {
							$row&#1111;1] = "n/a";
						}
					}
					if ($k == 3) {
						if (ereg ("(&#1111;0-9]{4})-(&#1111;0-9]{1,2})-(&#1111;0-9]{1,2})", $row&#1111;3], $regs)) {
							$row&#1111;3] = "$regs&#1111;3]/$regs&#1111;2]/$regs&#1111;1]";
						}
					}
					if ($k == 6) {
						if (ereg ("(&#1111;0-9]{4})-(&#1111;0-9]{1,2})-(&#1111;0-9]{1,2})", $row&#1111;6], $regs)) {
							$row&#1111;6] = "$regs&#1111;3]/$regs&#1111;2]/$regs&#1111;1]";
						}
					}
					if ($k == 0) {
					printf (" <td nowrap><a href="viewdata.php4?bcinum=" . $row&#1111;0] . "">$row&#1111;0]<a/></td> ");
					}else{
					printf (" <td nowrap>&nbsp; %s &nbsp;</td>
 ", htmlspecialchars ($row&#1111;$k]));
					}
				}
				echo "</TR>";
				$rcol++; // Alternate row colouring - Pt 4.
			}	// end of while loop
			?>
			</TABLE>
			</CENTER></DIV>
		<?php
	} // end of if
} // end of for loop
Thanks

Hebbs

combining 2 results in 1 column

Posted: Tue Apr 23, 2002 4:34 pm
by samscripts
Hi, I think what you're looking for is mysql's concat() function? It takes 2 or more fields or strings and returns them as one.

Code: Select all

SELECT dat_a_mainrequest.Number,RequestDate AS 'Request Date',CONCAT(requestA.CommNumber, ' ', requestB.CommNumber) AS 'Requested Number',RequestType AS 'Request Type',RequestStatus AS 'Status',location AS 'Office'
This should return requestA.CommNumber and requestB.CommNumber as one field containing the two fields separated by a space.

Hope this helps,

Sam

Not quite but...

Posted: Wed Apr 24, 2002 2:56 am
by Hebbs
Sam,

Thanks for that. Its not what I was trying to achieve but you answer has fixed another problem for me!

Perhaps to clarify a little,

I have two tables with telephone numbers in.

I want to grab all numbers from both tables and combine the rsults in one.

If I do as my example I end up with two columns titled CommNumber. I need to join the two somehow into a single column - same title.

The data is such that a number only appears in one or the other column, but not both.

I hope this explains it better!

Hebbs

Posted: Wed Apr 24, 2002 6:43 am
by samscripts
Hi Hebbs,

I'm not sure if I entirely understand what you want to do.

Using CONCAT(requestA.CommNumber, requestB.Commnumber) AS 'Requested Number' will combine whats in the two fields as one field.

If one of them is always an empty string, then 'Requested Number' will always be just the number that exists. Or have I missed the point completely?

Maybe you can use IF(,,) or IFNULL(,) to get the value you want?

For example, if CommNumber contains either the number, or NULL, you can use this in the query:

Code: Select all

SELECT dat_a_mainrequest.Number,RequestDate AS 'Request Date',
IFNULL(requestA.CommNumber, requestB.CommNumber) AS 'Requested Number',
RequestType AS 'Request Type',RequestStatus AS 'Status',location AS 'Office'
Alternatively, if one is the number and one is an empty string, get the one containing the number using:

Code: Select all

SELECT dat_a_mainrequest.Number,RequestDate AS 'Request Date',
IF(requestA.CommNumber='', requestB.CommNumber,requestA.CommNumber) AS 'Requested Number',
RequestType AS 'Request Type',RequestStatus AS 'Status',location AS 'Office'
Hope this makes some sense!

Sam

Hmmm, having a blonde day, I was.

Posted: Wed Apr 24, 2002 9:02 pm
by Hebbs
:oops:

Sam,

It was late in the workday for me when I looked at your post and replied.

About 20 mins later I was on the train on the way home when I realised you were right. I just hadnt looked at it properly.

Thanks for the help, must stop working at work, bad habit to get into!

Regards

Hebbs