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

[swatisonee]

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

[sqlmc]

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

[swatisonee]

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"];

[sqlmc]

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.

[swatisonee]

 

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

[sqlmc]

Can you paste the code.

[swatisonee]

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>

 

[sqlmc]

What version of MySQL are you running?

[ryanlwh]

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--]

[swatisonee]

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

 

[sqlmc]

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.

[ryanlwh]

sqlmc was right, UNION is not supported until mysql 4...