MySQL select query Error

Question

My query is throwing an error like

select id,cHospital from med_patient where cHospital is not null  union
select id,cHospital1 from med_patient where cHospital1 is not null  union
select id,cHospital2 from med_patient where cHospital2 is not null  order by 1

The error is

Notice: Undefined index: cHospital1 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 97

Notice: Undefined index: cHospital2 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 98

Notice: Undefined index: cHospital1 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 97

Notice: Undefined index: cHospital2 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 98

Notice: Undefined index: cHospital1 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 97

Notice: Undefined index: cHospital2 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 98

Notice: Undefined index: cHospital1 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 97

Notice: Undefined index: cHospital2 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 98

Notice: Undefined index: cHospital1 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 97

Notice: Undefined index: cHospital2 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 98

Notice: Undefined index: cHospital1 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 97

Notice: Undefined index: cHospital2 in F:\wamp\www\mmr-final\admin\all_hospital_list.php on line 98`

And also it's throwing Null row.

How can this query be changed to not cause an error?

The problem isn't the query, but the PHP. Can you post the full file or at least more of the surrounding code? — Tom
– Tom, Commented Jan 15, 2010 at 19:08
i tell u what i am trying exactly.. see i have three fields , chospital,chospital1,chospital2, in tbl1 table,i have around 50 patient entries, some patient may have chostial1,chosptail2, some patient have one chopsital code, i want render all code from the tbl2 table, i dont want duplication in my outout, also no null values.. — Bharanikumar
– Bharanikumar, Commented Jan 15, 2010 at 19:26

Peter Mortensen · Accepted Answer · 2011-03-09 23:23:25Z

2

Those are not MySQL errors. They are PHP errors. Basically you are most likely attempting to use variables without declaring them first. Be sure to always declare your variables before using them. It makes you less like to have errors.

edited Mar 9, 2011 at 23:23

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jan 15, 2010 at 19:07

John Conde

220k100 gold badges464 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Karwin · Accepted Answer · 2010-01-15 19:27:13Z

0

In a UNION query, the column names must be the same for all rows. So it uses the column names from the first query in the union. Column names in subsequent queries in the union are ignored.

In other words, in this query:

select id,cHospital from med_patient where cHospital is not null union 
select id,cHospital1 from med_patient where cHospital1 is not null union 
select id,cHospital2 from med_patient where cHospital2 is not null order by 1

The column names on all rows are: id and cHospital.

So in your PHP code, when you fetch the result as an object and try to reference a field of the object like this:

$cHospital1 = $row->cHospital1;
$cHospital2 = $row->cHospital2;

The object doesn't have fields by those names. It only has $row->cHospital, even for rows that came from the second and third queries in the union.

answered Jan 15, 2010 at 19:27

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

3 Comments

Bharanikumar Over a year ago

the column count is same know , what i have to changes here

Bill Karwin Over a year ago

Just use $row->cHospital for all rows.

Bill Karwin Over a year ago

NULL is not the same as an empty string '' in SQL. If you want to restrict against rows where the hospital is blank, put that condition in the WHERE clauses.

Collectives™ on Stack Overflow

MySQL select query Error

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related