Jump to content


Photo

SELECT UNION ERROR. I hope I am using the correct function


  • Please log in to reply
11 replies to this topic

#1 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 27 November 2005 - 01:31 PM

Hi,

I need to pick up a number which can occur in any one of two tables. It works well upto the point when i have to output the data to the screen. That is my report file shows a parse error.

I have tried variations of the foll. code and all appear wrong. Any guidance please ?

$sql11 = SELECT * FROM Enq WHERE Enqid='$enq' UNION SELECT * FROM Visit WHERE Visitid='$enq';
$result11 = mysql_query($sql11) or die (mysql_error());

$myrow11 = mysql_fetch_array($result11);
$company = $myrow11["Company"];
printf("<tr>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
</tr>",
$customer);


I have tried the foll. variations with single and double quotes and all throw up an error. $enq will appear either in the Table Enq or in the Table Visit. It will not appear in both. I checked the tables and the value is correctly appearing there so its a matter of outputting it to the screen.

" SELECT * FROM Enq WHERE Enqid='$enq' UNION SELECT * FROM Visit WHERE Visitid='$enq' ";

" SELECT * FROM Enq WHERE Enqid='$enq' "UNION "SELECT * FROM Visit WHERE Visitid='$enq' ";

Thanks. Swati
Shishya

#2 sqlmc

sqlmc
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 27 November 2005 - 03:06 PM

Is your table structure for Enq and Visit is the same? If not, the UNION with a SELECT * may cause problems. If you only need a particualr value, or even multiple values try using aliases that match and that should work. So it would go something like this.

SELECT Attribute_A as NeededValue
FROM Enq
WHERE Enqid='$enq'

UNION

SELECT Attribute_B as NeededValue
FROM Visit 
WHERE Visitid='$enq


#3 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 27 November 2005 - 03:27 PM

The table structures are not the same. No luck chanfging the code. I get the foll. error

Parse error: parse error, unexpected T_STRING in /home/virtual/site126/fst/var/www/html/report_visit.php on line 148 which is the 1st line of the foll. code :
I tried backticks for the field names and the table names but that didnt work either.
Any thing else i could try pl ? Or maybe another mysql function that is more appropriate ?


$sql11 = SELECT Company as '$customer'
FROM Enq
WHERE Enqid='$enq'

UNION

SELECT Company as '$customer'
FROM Visit
WHERE Visitid='$enq';
$myrow11 = mysql_fetch_array($result11);
$customer = $myrow11["Company"];

Shishya

#4 sqlmc

sqlmc
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 27 November 2005 - 03:40 PM

It looks like your using a varialbe for an alias. Try this.


$sql11 =  "SELECT Company FROM Enq WHERE Enqid='$enq'
UNION
SELECT Company FROM Visit WHERE Visitid='$enq'";

$result11 = mysql_query($sql11) or die (mysql_error());

$myrow11 = mysql_fetch_array($result11);
$company = $myrow11["Company"];


Also what is the datatype for Visitid and Enqid? You're using single quotes, but if the field is numeric then this may cause an error. If these fileds are INTEGER for example remove the single quotes from the WHERE clause.

#5 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 27 November 2005 - 03:56 PM


Both the id fields are integers so i got rid of the single quotes but i now get this error:

You have an error in your SQL syntax near 'UNION SELECT `Company` FROM `Visit` WHERE Visitid=' at line 2

I removed all the backticks and the error remains...any thing else i can try pl ?

thanks
Shishya

#6 sqlmc

sqlmc
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 27 November 2005 - 04:00 PM

Can you paste the code.

#7 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 27 November 2005 - 04:09 PM

Yes. Here it is:

<? include("protection.php"); ?>
<font face="Tahoma" size="2">
<?
if (!$month || !$year) die("Mandatory information not entered. Please use back button of browser.");
?>
<div align="left">
<font face="Tahoma" size="2">

<? print(date("l F d, Y")); ?>
<p>
<b>Monthly Travel  Report</b><br><br>
<b>Name : 
<?php
    $month = $HTTP_POST_VARS["month"];
    $year = $HTTP_POST_VARS["year"];
    $serial = 0;
    mysql_connect("localhost", $dbname, $dbpasswd )
        or die ("Unable to connect to server.");

    mysql_select_db($database)
        or die ("Unable to select database.");

    $result = mysql_query("SELECT * FROM `users` WHERE staff='$staff' ");
    $myrow = mysql_fetch_array($result);
    $first_name = $myrow["firstname"];
    $last_name = $myrow["lastname"];
    echo "<font color=\"RED\">$first_name $last_name</font><br>Month : <font color=\"RED\">";
    if ($month == 1)
    {
        $month_name = "January";
        echo $month_name;
    }
    if ($month == 2)
    {
        $month_name = "February";
        echo $month_name;
    }
    if ($month == 3)
    {
        $month_name = "March";
        echo $month_name;
    }
    if ($month == 4)
    {
        $month_name = "April";
        echo $month_name;    
    }
    if ($month == 5)
    {
        $month_name = "May";
        echo $month_name;    
    }
    if ($month == 6)
    {
        $month_name = "June";
        echo $month_name;    
    }
    if ($month == 7)
    {
        $month_name = "July";
        echo $month_name;    
    }
    if ($month == 8)
    {
        $month_name = "August";
        echo $month_name;    
    }
    if ($month == 9)
    {
        $month_name = "September";
        echo $month_name;    
    }
    if ($month == 10)
    {
        $month_name = "October";
        echo $month_name;    
    }
    if ($month == 11)
    {
        $month_name = "November";
        echo $month_name;    
    }
    if ($month == 12)
    {
        $month_name = "December";
        echo $month_name;    
    }
    echo ", $year</font)";

?>
</b><p>
<table border="1" style="border-collapse: collapse" bordercolor="#111111" width="100%">
<tr bgcolor="#C7F1FE">
<td><b><font face="Tahoma" size="2">Serial</b></td>
<td><b><font face="Tahoma" size="2">Start<br>Date</b></td>
<td><b><font face="Tahoma" size="2">End<br>Date</b></td>
<td><b><font face="Tahoma" size="2">Company</b></td>
<td><b><font face="Tahoma" size="2">From</b></td>
<td><b><font face="Tahoma" size="2">To</b></td>

</tr>

<?php
$serial = 0;
mysql_connect("localhost", $dbname, $dbpasswd )
        or die ("Unable to connect to server.");

mysql_select_db($database)
        or die ("Unable to select database.");
        

$result = mysql_query("SELECT * FROM `Travel` WHERE staff='$staff' && MONTH(`Ending`)=$month && YEAR(`Ending`)=$year ORDER BY `Ending` asc");

if ($myrow = mysql_fetch_array($result)) {


  do {

$enq=$myrow["Enqid"];


$sql11 =  "SELECT `Company` FROM `Enq` WHERE `Enqid`=$enq
UNION
SELECT `Company` FROM `Visit` WHERE `Visitid`=$enq";

$result11 = mysql_query($sql11) or die (mysql_error());

$myrow11 = mysql_fetch_array($result11);
$company = $myrow11["Company"];

$serial+=1;
printf("<tr>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
<td><font face=\"Tahoma\" size=\"2\"> %s </td>
</tr>",
 $serial,
 calculatedate($myrow["Starting"]),
 calculatedate($myrow["Ending"]),
 $company,
 $myrow["From"],
 $myrow["To"]
 );


} else {

    echo "Sorry, no records were found!";    
}
?>
</table>      
</div>

Shishya

#8 sqlmc

sqlmc
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 27 November 2005 - 05:04 PM

What version of MySQL are you running?

#9 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 27 November 2005 - 06:25 PM

yeah, older versions of mysql do not support union. also try to put () around the select statements
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]([span style=\'color:blue;font-weight:bold\']SELECT[/span] ....)
UNION
([span style=\'color:blue;font-weight:bold\']SELECT[/span] ....) [!--sql2--][/div][!--sql3--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#10 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 28 November 2005 - 02:41 AM

Ver. 3.23.58 of My Sql. Isnt this dependent on what the site host gives you ? I use mysql front 2.5 to see my db.

Tried putting thr () brackets but that gives the same error.....anyother suggestions pl ?

Thanks.

Swati

Shishya

#11 sqlmc

sqlmc
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 28 November 2005 - 03:51 PM

I think UNION wasn't supported until version 4, I ran a similar query on a 3.23 system and a 4.1 system and the UNION worked on the 4.1 system and failed on the 3.23. You may have to separate the queries and then put them together after you gathered the appropriate info.

#12 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 28 November 2005 - 04:46 PM

sqlmc was right, UNION is not supported until mysql 4...
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users