Running MySQL Query with Union (2 SELECT) on PHP

[cary1234]

I want to run this query (below) to a php file

 

 

EXPLAIN SELECT tbl_users.fname AS fname, tbl_users.lname AS lname, tbl_users.age AS age,

tbl_users.address AS address, tbl_users.contact_Num AS contact_Num, tbl_users.district_Num AS district_Num,

tbl_dateofmeetings.date AS date FROM tbl_dateofmeetings LEFT OUTER JOIN tbl_users ON

tbl_users.user_ID=tbl_dateofmeetings.user_ID WHERE tbl_dateofmeetings.meeting_Code=1

AND tbl_dateofmeetings.district_Num=1 AND tbl_users.district_Num=1 UNION SELECT

tbl_users.fname AS fname, tbl_users.lname AS lname, tbl_users.age AS age,

tbl_users.address AS address, tbl_users.contact_Num AS contact_Num, tbl_users.district_Num AS district_Num,

tbl_dateofmeetings.date AS date

FROM tbl_dateofmeetings RIGHT OUTER JOIN tbl_users ON tbl_users.user_ID =

tbl_dateofmeetings.user_ID WHERE tbl_dateofmeetings.meeting_Code=1 AND

tbl_dateofmeetings.district_Num=1  AND tbl_users.district_Num=1

 

 

So I changed to something like this...

 

 

 

$district_Num = (isset($_GET['districtNum'])) ? $_GET['districtNum'] : null;


$result = mysqli_query($db_Connection,"SELECT tbl_users.fname AS fname, tbl_users.lname

AS lname, tbl_users.age AS age, tbl_users.address AS address, tbl_users.contact_Num AS

contact_Num, tbl_users.district_Num AS district_Num, tbl_dateofmeetings.date AS date

FROM tbl_dateofmeetings LEFT OUTER JOIN tbl_users ON tbl_users.user_ID =

tbl_dateofmeetings.user_ID WHERE tbl_dateofmeetings.meeting_Code = 1 AND

tbl_dateofmeetings.district_Num = $district_Num AND tbl_users.district_Num

$district_Num UNION SELECT tbl_users.fname AS fname, tbl_users.lname AS lname,

tbl_users.age AS age, tbl_users.address AS address, tbl_users.contact_Num AS contact_Num,

tbl_users.district_Num AS district_Num, tbl_dateofmeetings.date AS date FROM

tbl_dateofmeetings RIGHT OUTER JOIN tbl_users ON tbl_users.user_ID = 

tbl_dateofmeetings.user_ID WHERE tbl_dateofmeetings.meeting_Code=1 AND

tbl_dateofmeetings.district_Num = $district_Num  AND tbl_users.district_Num = $district_Num");

 

I usually run a query just by using codes like this

 

<?php

$id_Num = 0;

$name_Num = 0;

while($row = mysqli_fetch_array($result))

   {

 $id_Num += 1;

 $name_Num += 1;


 echo"<tr>";

  echo "<td><div align='center'><input type='text' name='id_Num".$id_Num."' value=".$id_Num."></div></td>";

echo "<td><div align='center'><input type='text' name='name_Num".$id_Num."' value='".$row['fname']."'>  </div></td>";

echo "<td><div align='center'><input type='text' name='name_Num".$id_Num."' value='".$row['lname']."'>  </div></td>";

echo "<td><div align='center'><input type='text' name='name_Num".$id_Num."' value='".$row['age']."'>  </div></td>";

echo "<td><div align='center'>" . $row['address'] . "</div></td>";

echo "<td><div align='center'>" . $row['contact_Num'] . "</div></td>";

echo "<td><div align='center'>" . $row['district_Num'] . "</div></td>";

echo "<td><div align='center'>" . $row['date'] . "</div></td>";

  }

echo "</table>";


mysqli_close($db_Connection);

?>

 

But I think due to the use of union and also I have 2 "SELECT" in a query the php seems to be confuse. I dont have any idea how to fix this, I'm stuck I try googling but I dont have any results.

 

[kicken]

What kind of a problem are you having? You process a UNION query the same way you'd do any select query. No special handling is necessary.

[cary1234]

Yeah, thats my problem. I think that query doesnt need special handling but it didnt work. The error states that mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in ......

[cary1234]

By the way I'm sure the query is working because I tested it on phpmyadmin 

[Barand]

You have at least one syntax error - a missing "=" at the end of the first half of the query.

 

Check the value of mysqli_error() after running the query.

[cary1234]

Oh yes, it worked. Oh I'm sorry. I thought its a big problem because I already checked it for more thatn 5 times. Sorry and anyways, I really want to than you.

[mac_gyver]

i'm going to go with your php $_GET variable isn't set and results in php null being put into the query statement, which will result in invalid mysql syntax. you shouldn't run the query at all if $district_Num doesn't actually contain an expected value. and if you are trying for a mysql null value, you need to handle that using a mysql function in the query that works with mysql null values.

 

you also need to clean up and format your query statement. the alias names in it now aren't doing anything (the select fields names will be exactly the same as what you are adding the alias names to do) and you should use alias names for the table names. i would also recommend that you rename your table names to drop the tbl_ in each of them. you know you are referencing a database table, you don't need tbl_ in the names of them.

[cary1234]

Ahh. Thank you for your tips, yeah I think you are right I need to remove thos 'tbl_'. Hmmm. I have another problem sir. How can I put 'COUNT' if the query is like this.



 

<?php
	  		$qry_attendanceLtm = ("SELECT tbl_users.fname, tbl_users.lname, tbl_users.age,
			tbl_users.address, tbl_users.contact_Num, tbl_users.district_Num,
			tbl_dateofmeetings.date FROM tbl_dateofmeetings LEFT OUTER JOIN tbl_users ON
			tbl_users.user_ID=tbl_dateofmeetings.user_ID WHERE tbl_dateofmeetings.meeting_Code=1
			AND tbl_dateofmeetings.district_Num=1 AND tbl_users.district_Num=1 UNION SELECT
			tbl_users.fname, tbl_users.lname, tbl_users.age, tbl_users.address,
			tbl_users.contact_Num, tbl_users.district_Num, tbl_dateofmeetings.date
			FROM tbl_dateofmeetings RIGHT OUTER JOIN tbl_users ON tbl_users.user_ID =
			tbl_dateofmeetings.user_ID WHERE tbl_dateofmeetings.meeting_Code=1 AND
			tbl_dateofmeetings.district_Num=1  AND tbl_users.district_Num=1");
			$temp_AttendanceLtm = mysqli_query($db_Connection, $qry_attendanceLtm);
			$temp1_AttendanceLtm = mysqli_fetch_assoc($temp_AttendanceLtm);
			$final_AttendanceLtm1 = $temp1_AttendanceLtm['COUNT(date)']; 
			echo $final_AttendanceLtm1; ?>

As you can see on the 2nd to the last line I have this "$final_AttendanceLtm1 = $temp1_AttendanceLtm['COUNT(date)'];"

Because I put "COUNT" on that variable I also need to put "COUNT" on the query itself, but how?

[cary1234]

Ahh. Thank you for your tips, yeah I think you are right I need to remove thos 'tbl_'. Hmmm. I have another problem sir. How can I put 'COUNT' if the query is like this.

 

 

 

 

<?php
	  		$qry_attendanceLtm = ("SELECT tbl_users.fname, tbl_users.lname, tbl_users.age,
			tbl_users.address, tbl_users.contact_Num, tbl_users.district_Num,
			tbl_dateofmeetings.date FROM tbl_dateofmeetings LEFT OUTER JOIN tbl_users ON
			tbl_users.user_ID=tbl_dateofmeetings.user_ID WHERE tbl_dateofmeetings.meeting_Code=1
			AND tbl_dateofmeetings.district_Num=1 AND tbl_users.district_Num=1 UNION SELECT
			tbl_users.fname, tbl_users.lname, tbl_users.age, tbl_users.address,
			tbl_users.contact_Num, tbl_users.district_Num, tbl_dateofmeetings.date
			FROM tbl_dateofmeetings RIGHT OUTER JOIN tbl_users ON tbl_users.user_ID =
			tbl_dateofmeetings.user_ID WHERE tbl_dateofmeetings.meeting_Code=1 AND
			tbl_dateofmeetings.district_Num=1  AND tbl_users.district_Num=1");
			$temp_AttendanceLtm = mysqli_query($db_Connection, $qry_attendanceLtm);
			$temp1_AttendanceLtm = mysqli_fetch_assoc($temp_AttendanceLtm);
			$final_AttendanceLtm1 = $temp1_AttendanceLtm['COUNT(date)']; 
			echo $final_AttendanceLtm1; ?>

As you can see on the 2nd to the last line I have this "$final_AttendanceLtm1 = $temp1_AttendanceLtm['COUNT(date)'];"

 

Because I put "COUNT" on that variable I also need to put "COUNT" on the query itself, but how?

 

Now I know. I just make the code like this

 

 

<?php
	  		$qry_attendanceLtm = ("SELECT COUNT(*) FROM (SELECT tbl_users.fname, tbl_users.lname, tbl_users.age,
			tbl_users.address, tbl_users.contact_Num, tbl_users.district_Num,
			tbl_dateofmeetings.date FROM tbl_dateofmeetings LEFT OUTER JOIN tbl_users ON
			tbl_users.user_ID=tbl_dateofmeetings.user_ID WHERE tbl_dateofmeetings.meeting_Code=1
			AND tbl_dateofmeetings.district_Num=1 AND tbl_users.district_Num=1 UNION SELECT
			tbl_users.fname, tbl_users.lname, tbl_users.age, tbl_users.address,
			tbl_users.contact_Num, tbl_users.district_Num, tbl_dateofmeetings.date
			FROM tbl_dateofmeetings RIGHT OUTER JOIN tbl_users ON tbl_users.user_ID =
			tbl_dateofmeetings.user_ID WHERE tbl_dateofmeetings.meeting_Code=1 AND
			tbl_dateofmeetings.district_Num=1  AND tbl_users.district_Num=1) AS Result");
			$temp_AttendanceLtm = mysqli_query($db_Connection, $qry_attendanceLtm);
			$temp1_AttendanceLtm = mysqli_fetch_assoc($temp_AttendanceLtm);
			$final_AttendanceLtm1 = $temp1_AttendanceLtm['COUNT(date)']; 
			echo $final_AttendanceLtm1; ?>