PHP MSSQL - Help Merging Rows?

[novedturn]

$query = "SELECT

Table1.*,

Table2.A,

Table2.B,

Table2.C,

Table2.D,

Table2.E

FROM Table1

INNER JOIN

Table2 ON Table1.A = Table2.A

 

Issue is that there are multiple matches for some instances, like so:

 

Table1.A value = 1777 (only once since it is a UniqueID)

Table2.A value = 1777 (3 records with this ID value)

 

So I will get 3 records returned since Table1.A matches 3 records in Table2.A. I want to merge the 3 records in Table2 into 1 record and then match that with Table1 where the UniqueID is the same. Make sense?

[ChemicalBliss]

I doubt you could "merge" rows that have different values whilst the query is running, or it would be overly complicated imo,

 

Can i ask why you have rows with the same id?

 

Ids need to be unique for a reason i think there is a problem with your table structure.

[novedturn]

I hear ya. I am trying to work with an old DB and I definitely need to rebuild some of these tables. I could do this by combining the 2 tables into 1, using almost the same query I have above, except adding INSERT INTO to make a new table, but even then, the multiple records issue would persist. Any ideas?

[ChemicalBliss]

Can you give soem compelte examples of these multiple items, hwo would you like them to be merged?

 

If you can show something like this:

Table1
   A         B                 C          D
  435        John Edwards      Empoyed    12/08/10

Table2
   A         B                 C          D
  435        Bill Jones        Unemployed 10/01/11

Want to split them up so one of them has a new id.

 

Something like that, show us what you want .

[novedturn]

Does this help?

 

 

Current Table Has The Following Records:

 

ID Name Codes

1 John D S12, S13, S89

1 John D S98, T12

2 Joe D X27, S28

3 Bob S F12, F13, X43

3 Bob S S44, X38, S90

4 Steve F D25

 

 

I want a query that will return the following

 

ID Name Codes

1 John D S12, S13, S89, S98, T12

2 Joe D X27, S28

3 Bob S F12, F13, X43, S44, X38, S90

4 Steve F D25

 

[jcbones]

So, you want to do something like:

$query = "SELECT
Table1.*,
Table2.A,
Table2.B,
Table2.C,
Table2.D,
Table2.E
FROM Table1
INNER JOIN
Table2 ON Table1.A = Table2.A"; //QUERY<- It must be able to pull the right records.
$result = mysql_query($query) or die('Error: ' . $query . '<br />' . mysql_error()); //pull the results, or die and tell us the error.
if(mysql_num_rows($result) > 0) { //if any rows are returned.
  while($r = mysql_fetch_assoc($result)) { //itenerate over them.
	$peeps[ $r['name'] ] = $r['A']; //setting each column in an array according to the user's name.
	$peeps[ $r['name'] ] = $r['B'];
	$peeps[ $r['name'] ] = $r['C'];
	$peeps[ $r['name'] ] = $r['D'];
	$peeps[ $r['name'] ] = $r['E'];
}

if(is_array($peeps)) { //if the array is built.
	echo '<table border="1">
				<tr>
					<th>Names</th>
					<th>Codes</th>
				</tr>'; //start us a table.

	foreach($peeps as $k => $v) { //loop over the results.

		if(is_array($v)) {
		//IF YOU DON"T WANT DUPLICATE CODES, Un-comment the next line:
		// $v = array_unique($v);
			echo '<tr><td>' . $k . '</td><td>' . implode(', ',$v) . '</td></tr>'; //built table rows with data.
		}
		echo '</table>'; //close table.
	}
}
}

 

I took your multiple rows, and returned them into an array in PHP, then imploded them into a string separated by a comma.

[ChemicalBliss]

Here is how you merge your two tables:

$row_array = array();
$row_names = array();

// Go through your result as usual
while($row = mysql_fetch_array($result)){	

/* This is the magic Merger, There is a new array called $row_array
   It starts empty but every time it goes through a row (loops),
   it will add the row to that array UNLESS it already exists in 
   which case it will just add the string to it. */
   
// If this row exists in the new array....
if(isset($row_array[$row['id']])){

	// Add the codes to the current string
	$row_array[$row['id']] .= ",".$row['codes'];
}else{
	// Otherwise add this row to the new array, and also its name in another array (with the same key/id)
	$row_array[$row['id']] = $row['codes]';
	$row_names[$row['id']] = $row['name'];
}
}

// Now we make the query we can give to mysql

// Start with the first part (the next bit we can loop, as it can be the same thing over and over: (id,name,codes) (id,name,codes) .. etc
$sql = "INSERT INTO `newtable` (id,name,codes) VALUES ";

// We need an array of IDS since we are using for(), if we dont we would need to use its $i integer as the id but if you are missing an id it could change ID's of your users (dont want that).
$row_ids_array = array_keys($row_array);

// So, we loop for every item in the $row_array.
for($i=0;$i<count($row_array);$i++){

// Get the current row ID
$rowid = $row_ids_array[$i];

// Get the name using the retrieved row ID
$rowname = $row_names[$rowid];

// Get the code same method
$rowcodes = $row_array[$rowid];

// Make the SQL for this rows insert, and add it to the SQL string.
$sql .= "('".$rowid."','".$rowname."','".$rowcodes."') ";
}

// VOILA! merged tables
echo($sql);

 

Understand this code - i have commented it so you should be able to follow it. Stuff like this can save you lots and i mean lots of time with old projects that you are updating. Among many, many other things.

 

hope this helps