Combining two results in a single column?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Hebbs
Forum Commoner
Posts: 43
Joined: Mon Apr 22, 2002 9:34 pm
Location: Perth, Western Australia

Combining two results in a single column?

Post 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
samscripts
Forum Commoner
Posts: 57
Joined: Tue Apr 23, 2002 4:34 pm
Location: London, UK

combining 2 results in 1 column

Post 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
Hebbs
Forum Commoner
Posts: 43
Joined: Mon Apr 22, 2002 9:34 pm
Location: Perth, Western Australia

Not quite but...

Post 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
samscripts
Forum Commoner
Posts: 57
Joined: Tue Apr 23, 2002 4:34 pm
Location: London, UK

Post 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
Hebbs
Forum Commoner
Posts: 43
Joined: Mon Apr 22, 2002 9:34 pm
Location: Perth, Western Australia

Hmmm, having a blonde day, I was.

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