[SOLVED] subquery (nested) select error in php4

[APuppyDog]

I have a page that works fine in php5 but the same code does not work in php4. Due to the circumstances, i have to make it work in php4 for the time being... can you gurus suggest how I can resolve this to work with php4?

 

$sql="select distinct car_auctions.leading_bidder from car_auctions where car_auctions.auction_closed=1 and car_auctions.buyer_fees>0 and car_auctions.buyer_fees_charged_status<>1 and car_auctions.leading_bidder in (SELECT user_master.user_name from user_details,user_master where user_master.user_id=user_details.user_id and user_details.user_status=2)";

$rec=mysql_query($sql);  
$rs=mysql_fetch_array($rec);

 

i get the following error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT user_master.user_name from user_details,user_master wher 

 

[dawsba]

if your backtracking to php4 what version of mysql are you now using, the problem may be in the nested selects

 

[APuppyDog]

hi do you mean the following?

 

MySQL

Client API version  5.0.18 

[trq]

No, we need to know the version of the mysql server in question.

[APuppyDog]

ok, please bear with me as i have novice experience with php. Where can i find this info?

I got the 5.0.18 from phpinfo()

[trq]

mysql_get_server_info.

[APuppyDog]

ok running that i get an error:

 

Warning: mysql_get_server_info(): Can't connect to local MySQL server through socket '/usr/local/mysql-5.0/data/mysql.sock' (2) in /home/path/to/phpInfo.php on line 5

 

 

[APuppyDog]

well i was able to find this in phpmyAdmin

 

Server version: 4.0.27-max-log

[trq]

Ok, mysql versions < 5 do not support subqueries. I'm moving this to mysql help.

[APuppyDog]

thanks for pointing that out.. is it possible to get the same result some other way with the version of mysql i have now?

[APuppyDog]

i found a solution.. but i don't know how to apply it to my code.

 

eg. someone had asked to convert the following to mysql 4.0 standard

 

so the code was

SELECT books.idbook, books.title 
FROM books
WHERE EXISTS (SELECT * FROM out_of_stock WHERE out_of_stock.city =  'london' 
AND out_of_stock.idbook = books.idbook)

 

SELECT books.idbook, books.title
FROM books LEFT OUTER JOIN out_of_stock
ON out_of_stock.city = 'london'
AND books.idbook = out_of_stock.idbook
WHERE out_of_stock.idbook IS NULL;

 

now i need to know how to get

select distinct car_auctions.leading_bidder from car_auctions where car_auctions.auction_closed=1 and car_auctions.buyer_fees>0 and car_auctions.buyer_fees_charged_status<>1 and car_auctions.leading_bidder in (SELECT user_master.user_name from user_details,user_master where user_master.user_id=user_details.user_id and user_details.user_status=2)

 

to the way they wrote it in the second set of codes..

 

any help? thanks in advance!

 

[fenway]

Same way... switch to a join... you have a working example (but use INNER).

[APuppyDog]

i'm not too familiar with the change but i tried the following but i got an error:

 

SELECT distinct car_auctions.leading_bidder 
FROM car_auctions 
WHERE car_auctions.auction_closed=1 
AND car_auctions.buyer_fees>0 
AND car_auctions.buyer_fees_charged_status<>1 
AND car_auctions.leading_bidder 
in LEFT INNER JOIN user_master.user_name 
      FROM user_details,user_master 
      ON user_master.user_id=user_details.user_id 
      AND user_details.user_status=2

 

 

the error i got was "#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT INNER JOIN user_master.user_name FROM user_details"

 

[fenway]

Yikes... that doesn't even look the working code one bit.  Sigh.

 

select distinct 
ca.leading_bidder 
from car_auctions AS ca
inner join user_master AS um on ( um.user_name = ca.leading_bidder )
inner join user_details AS ud using (user_id)
where ca.auction_closed=1 
and ca.buyer_fees>0 
and ca.buyer_fees_charged_status<>1 
and ud.user_status=2

 

[APuppyDog]

whoa fenway! Thanks! that piece of code.. i don't think i could have figured all those changes at all myself, but it worked.

 

Could you explain the code above..

 

I have another (last) query that i tried to play around with using your example but it still gives an error

 

that code is

select distinct CA.user_id from car_auctions AS CA,user_master UM where CA.user_id=UM.user_id and CA.auction_closed=1 and seller_fees_charged_status<>1 and winning_bidder<>'' AND seller_fees<>0 AND CA.user_id in (SELECT ud.user_id from user_details ud,user_master u where u.user_id=ud.user_id and ud.user_status=2) and (seller_fees>0 OR UM.user_discount=100)

 

thanks in advance!

[fenway]

Could you explain the code above..

Sure... you need data from all three tables.  You use JOINs to relate those table, and use ON clauses or USING clauses to tell the server how your tables are linked.  USING means that the field name is the same in both related tables; ON allows you to be more specific.  I use table aliases to make it easier to type and read.  Then I just added the WHERE conditions that you had provided.  DISTINCT will simply remove duplicates -- but is probably better replaced with GROUP BY.

 

Your latest query doesn't use JOINs... you should be able to easily model it.

 

[APuppyDog]

hi fenway, thanks for explaining the code... i'm still having issues with the code. So instead of using inner join, i used "USING".. but i still get a syntax error

 

here is the code i tried:

 

SELECT DISTINCT ca.user_id
FROM car_auctions AS ca, user_master UM
WHERE CA.user_id = UM.user_id
AND CA.auction_closed =1
AND seller_fees_charged_status <>1
AND winning_bidder <> ''
AND seller_fees <>0
AND CA.user_id
USING user_id AS ud
FROM user_details ud, user_master u
WHERE u.user_id = ud.user_id
AND ud.user_status =2
AND (
seller_fees >0
OR UM.user_discount =100
)

 

any suggestions?

[fenway]

I suggest you copy my example, not yours ;-)

[APuppyDog]

fenway.. i really am stuck. I have no real clue how to apply your code. Honestly, i don't know much about php/mysql and string implementation. I had asked someone to write the code, and they gave me a mysql 5 solution.. where i had mysql 4.0 on the host.. they can't seem to do it either.

 

Since you have provided a working code for the first request.. could you please provide the code for the second one? Pretty please?

 

I took your advice, where you said "Your latest query doesn't use JOINs" so i tried to follow your code without using Inner JOIN but i really have no clue what i'm doing.. so your assistance would be REALLY appreciated 

[fenway]

select distinct
ca.user_id
from car_auctions AS ca
inner join user_master AS um using (user_id)
inner join 
(SELECT ud.user_id from user_details ud inner join user_master u using (user_id) where ud.user_status=2) as sub using ( user_id )
where ca.auction_closed=1
and seller_fees_charged_status<>1
and winning_bidder<>'' 
AND seller_fees<>0 
and (seller_fees>0 OR UM.user_discount=100)

 

Untested... and missing table prefixes in some places.

[APuppyDog]

hi fenway, thanks for your reply.. with the code you sent, i see it still has a sub query, but i tried it anyway and it still gives the same error (error in mySQL syntax)

 

where as the first solution you provided didn't have any subqueries..

[fenway]

Oops, my bad; try this:

 

select distinct
ca.user_id
from car_auctions AS ca
inner join user_master AS um using (user_id)
inner join user_details AS ud on ( ud.user_id = um.user_id AND ud.user_status=2 )
where ca.auction_closed=1
and seller_fees_charged_status<>1
and winning_bidder<>''
AND seller_fees<>0
and (seller_fees>0 OR UM.user_discount=100)

[APuppyDog]

hi fenway, thanks again for your prompt reply. Even after the code above, i get "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/k/l/i/klickcarcom/html/_admin/unpaid_accounts.php on line 56"

 

Perhaps it would be easier if you could see the whole code.. here it is:

<?php session_start();?>
<?php include("../includes/config.inc"); 
include_once("../settings/settings.php");
include("../includes/func.php");?>
<?php if (empty($_SESSION["admin"])) redirectS("index.php?e=1");
$tmon = date("m");
$tday=  date("d");
$tyr=  date("Y");
$nextmonth = time() - (30 * 24 * 60 * 60);

$fmon = date('m', $nextmonth);
$fday=  date('d', $nextmonth);
$fyr=  date('Y', $nextmonth);

$sql="select distinct 
ca.leading_bidder 
from car_auctions AS ca 
inner join user_master AS um on ( um.user_name = ca.leading_bidder )
inner join user_details AS ud using (user_id)
where ca.auction_closed=1 
and ca.buyer_fees>0 
and ca.buyer_fees_charged_status<>1 
and ud.user_status=2
";

$rec=mysql_query($sql);  
$rs=mysql_fetch_array($rec);
if(!$rs)
{}
else{
do
{
$lead=$rs["leading_bidder"];

if($idlist!="")
		{
		$idlist=$idlist .",";
		}
		$idlist=$idlist."'".$lead."'";

}while($rs=mysql_fetch_array($rec));
}

$sql2="select distinct
ca.user_id
from car_auctions AS ca
inner join user_master AS um using (user_id)
inner join user_details AS ud on ( ud.user_id = um.user_id AND ud.user_status=2 )
where ca.auction_closed=1
and seller_fees_charged_status<>1
and winning_bidder<>''
AND seller_fees<>0
and (seller_fees>0 OR UM.user_discount=100)";

$rec2=mysql_query($sql2);
$rs2=mysql_fetch_array($rec2);
if(!$rs2)
{}
else{
do
{
$user_id=$rs2["user_id"];

if($idlist2!="")
		{
		$idlist2=$idlist2 .",";
		}
		$idlist2=$idlist2."'".$user_id."'";

}while($rs2=mysql_fetch_array($rec2));
}

?>
<?php include("includes/header.php"); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.myform
{  font-family:Arial, Helvetica, sans-serif;
font-size:12;
}
-->
</style>
</head>
<script type="text/javascript">
function validate()
{
if(document.frm1.f_day.value=="")
{
     alert("Select From Day ");
 document.frm1.f_day.focus();
  return false;
}
else if(document.frm1.f_month.value=="")
{
 alert("Select From Month ");
 document.frm1.f_month.focus();
  return false;
}
else if(document.frm1.f_year.value=="")
{
 alert("Select From Year ");
 document.frm1.f_year.focus();
  return false;
}
else if(document.frm1.t_day.value=="")
{
 alert("Select To Day ");
 document.frm1.t_day.focus();
  return false;
}
else if(document.frm1.t_month.value=="")
{
 alert("Select From Month ");
 document.frm1.t_month.focus();
  return false;
}
else if(document.frm1.t_year.value=="")
{
 alert("Select From Year ");
 document.frm1.t_year.focus();
 return false;
}
else if(document.frm1.f_year.value>document.frm1.t_year.value)
{
 alert("From Date Cannot be greater then To Date ");
 return false;
}
else if(document.frm1.client.value=="")
{
 alert("Select client ");
 document.frm1.t_year.focus();
 return false;
}

return true;
}

</script>
<body>
<form  name="frm1"  action="invoice_detail.php"  method="post">
<table width="800" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="50"><span style="font-weight: bold">Search Unpaid Accounts  </span></td>
</tr>
<tr><td><?php if($_GET["err"]=="1"){ ?><div><font color="#FF0000"><b>No Records Found</b></font></div><?php } ?></td></tr>
  <tr height="25" class="myform">
  <td >From: 
    <select name="f_month" class="myform">
			<option value="">Month</option>
			<? for($i=1; $i<=12; $i++){?> 
			<option value="<?=$i?>"<? if($fmon==$i) { echo "selected";} ?>><?=$i?></option>
			<? }?>
      </select>
    <select name="f_day" class="myform">
      <option value="">Day</option>
      <? for($i=1; $i<=31; $i++){?>
      <option value="<?=$i?>"<? if($fday==$i) { echo "selected";} ?>>
        <?=$i?>
        </option>
      <? }?>
    </select>
    <select name="f_year" class="myform">
			<option value="">Year</option>
			<? for($i=2006; $i<=(date(Y)+0); $i++){?> 
			<option value="<?=$i?>"<? if($fyr==$i) { echo "selected";} ?>><?=$i?></option>
			<? } ?>
      </select>

			    
			To:
			<select name="t_month" class="myform">
                  <option value="">Month</option>
                  <? for($i=1; $i<=12; $i++){?>
                  <option value="<?=$i?>"<? if($tmon==$i) { echo "selected";} ?>>
                    <?=$i?>
                  </option>
                  <? }?>
                </select>
			<select name="t_day" class="myform">
                  <option value="">Day</option>
                  <? for($i=1; $i<=31; $i++){?>
                  <option value="<?=$i?>"<? if($tday==$i) { echo "selected";} ?>>
                    <?=$i?>
                  </option>
                  <? }?>
                </select>
			<select name="t_year" class="myform">
                  <option value="">Year</option>
                 	<? for($i=2006; $i<=(date(Y)+0); $i++){?> 
                  <option value="<?=$i?>"<? if($tyr==$i) { echo "selected";} ?>>
                    <?=$i?>
                  </option>
                  <? } ?>
    </select></td>
  </tr>
  <tr height="25"><td class="myform">Client: <select name="client"><option>Select Client</option>
  <?php
  $sql="SELECT distinct u.user_id,ud.user_dealer_name from user_details ud,user_master u 
	where u.user_id=ud.user_id  and ud.user_status=2 and u.user_name in(".$idlist.") ORDER BY user_dealer_name ASC";
	$rs=mysql_query($sql);
	$rec=mysql_fetch_array($rs);
	if($rec)
	{
	do
	{
  
  ?>
  <option value="<?=$rec["user_id"]?>"> B- <?=$rec["user_dealer_name"]?></option>
  

  <?php
  }
  while($rec=mysql_fetch_array($rs));
  }
  
    $sql4="SELECT distinct u.user_id,ud.user_dealer_name from user_details ud,user_master u 
	where u.user_id=ud.user_id  and ud.user_status=2 and u.user_id in(".$idlist2.") ";
	$rs4=mysql_query($sql4);
	$rec4=mysql_fetch_array($rs4);
	if($rec4)
	{
	do
	{
  
  ?>
  <option value="<?=$rec4["user_id"]?>"> S- <?=$rec4["user_dealer_name"]?></option>
  <?php
  }
  while($rec4=mysql_fetch_array($rs4));
  }
  ?>
  </select>
  

  
  
  </td></tr>
  <tr><Td align="center" colspan="2"><input type="submit" value="Search" onclick="return validate();" /></Td></tr>
</table>
</body>
</html>




<?php include("includes/footer.php"); ?>

[fenway]

hi fenway, thanks again for your prompt reply. Even after the code above, i get "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/content/k/l/i/klickcarcom/html/_admin/unpaid_accounts.php on line 56"

 

Perhaps it would be easier if you could see the whole code.. here it is:

It's NEVER easier to see the whole code... please don't do that again.

 

Check mysql_error() after the query, but before mysql_fetch_array()... also, try lower UM.

[APuppyDog]

genius!

 

it had to be a lower case UM.. err um.

 

thanks for your help..