Page 1 of 1

problem with select statement

Posted: Mon Oct 10, 2011 3:55 am
by timoteo
does anyone know what is wrong with this select statement?
$querynew = "SELECT DISTINCT recoentryid FROM businessdetails WHERE recoentryid IN('1','73','20','39','40','41','48','50','71','72') AND (city IN('queretaro') OR city LIKE '%queretaro%')
this gives no result yet if I write
SELECT DISTINCT recoentryid FROM businessdetails WHERE (city IN('queretaro') OR city LIKE '%queretaro%')
I get Array ( [0] => 58 [1] => 67 [2] => 73 ). And if I write:
SELECT DISTINCT recoentryid FROM businessdetails WHERE recoentryid IN('1','73','20','39','40','41','48','50','71','72')
I get Array ( [0] => 73 )
Therefore by my logic the first statement above should give Array ( [0] => 73 ) but it gives nothing..... why?

Re: problem with select statement

Posted: Mon Oct 10, 2011 4:14 am
by ouchiko
I tried your top query - it works fine.

Try running through a mysql client [HeidiSQL or some such] and make sure the query work then look into the PHP code - which I suspect will be the problem area .. as the query looks a-ok.

Re: problem with select statement

Posted: Mon Oct 10, 2011 4:29 am
by aravona
If you're having problems with a query, best to eliminate the obvious first and try the query in your MySQL command line, or in phpmyadmin or some other such control panel.

Re: problem with select statement

Posted: Mon Oct 10, 2011 5:49 am
by timoteo
thanks for the tips - checked it in php myadmin - works perfect. So what's wrong - apparently the array is not recording the first result therefore no result when there is only one. this is what comes next in my code:

Code: Select all

$resultnew = mysql_query($querynew, $recommendingpeople) or die(mysql_error());
$rownew = mysql_fetch_array($resultnew);


if(isset($rownew['recoentryid'])) {

while ($rownew = mysql_fetch_array($resultnew)) {
   
    $ranew[] = $rownew['recoentryid'];
   }}
    
     $renew = array_unique($ranew);
echo $querynew;
print_r($renew);
die();
any ideas?

Re: problem with select statement

Posted: Mon Oct 10, 2011 5:56 am
by timoteo
got it!!!!

Code: Select all

if(isset($rownew['recoentryid'])) {
$ranew[] = $rownew['recoentryid'];
while ($rownew = mysql_fetch_array($resultnew)) {
   
    $ranew[] = $rownew['recoentryid'];
   }}
    
     $renew = array_unique($ranew);
thank you for your help. I was missing the first result to my array before looping.

Re: problem with select statement

Posted: Mon Oct 10, 2011 7:10 am
by ouchiko
You'd be better off doing something like:

Code: Select all

while ( $row = @mysql_fetch_array($resultnew)) if ( $row ) $ranew[]=$row;
Still, glad it worked out