Union Statements

[cmb]

i have this union statement that returns exactly what i want

SELECT c_day_entered FROM Comments WHERE pid = '22'
UNION
SELECT wv_day_entered FROM walk_in_visit WHERE wv_pid = '22'
UNION
SELECT day_entered FROM first_visit WHERE fv_pid = '22'
ORDER BY 1 DESC

Now i want to using php display this info but idk how to do this

help plz

[kickstart]

Hi

 

Alias the column names using a common name in each select, or (nastier) use mysql_fetch_row and refer to the column number.

 

All the best

 

Keith

[cmb]

I tried this but it doesnt give me all the data it should

include_once "connecttodatabase/database.php";
	$id = $_GET['id'];
	$query = "SELECT c_day_entered FROM Comments WHERE pid = $id UNION SELECT wv_day_entered FROM walk_in_visit WHERE wv_pid = $id UNION SELECT day_entered FROM first_visit WHERE fv_pid = $id ORDER BY 1 DESC" or die ('Error With Database ' .mysql_error());

	$result = mysql_query($query) or die("Query failed ($query) - " . mysql_error());

	while(mysql_fetch_row($result)){
		print_r (mysql_fetch_row($result));
		echo "<br />";
	}

	// disconnect
	mysql_close();

?>

[fenway]

If you call mysql_fetch_row() twice, you'll be skipping every other result.

[cmb]

when i get rid of the while and just have it as

print_r(mysql_fetch_row($result));

 

i only get 1 result when i should be getting 8 and im sure i should be getting 8 because ive tested the query with mysql workbench

 

[DavidAM]

Fetch the data into a variable in the while statement, and work with the variable:

 

	while($row = mysql_fetch_row($result)){
		print_r ($row);
		echo "<br />";
	}

[cmb]

Thank you thats what i wanted but one more thing is their anyway to get rid of

Array ( [0] =>

when it shows the results

[DavidAM]

print_r just dumps the array; it is useful for debugging. For output, just print (or echo) the variable. In this case, $row is an array, so you have to print/echo the array elements.

 

print($row[0]);