CODEWITHSUNDEEP
phpmysql
Jaeger
Jaeger

Reputation: 1716

Commands out of sync in A correct code

I've the following query executed via PHP, it used to work like yesterday, and stopped working today, here's it : 

SELECT 
    *, 
    (SELECT IF (pro.uid= '4342','True','False') FROM pro) AS 'uid' 
FROM 
    Episodes, pro 
ORDER BY 
    Episodes.id DESC 
LIMIT 9

The '4342' is changed with GET param, I'm having the following error :

Commands out of sync; you can't run this command now

My PHP API is as the following ( snippets ) : 

<?php 
include_once "db_config.php"; 

DEFINE ('DB_HOST', $host); 
DEFINE ('DB_USER', $user);      
DEFINE ('DB_PASSWORD', $pass); 
DEFINE ('DB_NAME', $database); 

$mysqli = @($GLOBALS["___mysqli_ston"] = mysqli_connect(DB_HOST,  DB_USER,  DB_PASSWORD)) OR die ('Could not connect to MySQL'); 
@((bool)mysqli_query($GLOBALS["___mysqli_ston"], "USE " . constant('DB_NAME'))) OR die ('Could not select the database'); 

?> 

<?php 

 if(isset($_GET['meaE'], $_GET['uid']))
    { 
         $query="SELECT * , (SELECT  IF (pro.uid=    '".$_GET['uid']."','True','False') FROM pro ) AS 'uid' FROM Episodes,pro  ORDER   BY Episodes.id DESC LIMIT 9
";             
          $resouter = mysqli_query($GLOBALS["___mysqli_ston"], $query); 

} 

$set = array(); 

$total_records = mysqli_num_rows($resouter); 
if($total_records >= 1){ 

  while ($link = mysqli_fetch_array($resouter,  MYSQLI_ASSOC)){ 

    $set['APITest'][] = $link; 
  } 
} 
 echo $val= str_replace('\\/', '/', json_encode($set)); 
 ?>

I want to have the following output : 

    TABLE A       -  TABLE B 
ID NAME TEXT  UID -   Check_ID

The query I want :

Select * From Table A, Check if "134123123" is exists in B, if yes / no add a record for all the taken records from Table A with the value of checking (True/False) .

What's happening with @sagi query is :

There are 3 records in Table B, and 10 records in Table A, when the query is executed, the result is 30 records, each record in Table B has 10 records from Table A, and the "uid" record value is True where the uid is '134123123', and it's false where it's not '134123123' .

Upvotes: 1

Views: 73

Answers (1)

sagi
sagi

Reputation: 40481

Your query seems wrong, I don't know how it worked yesterday, but right now your are cross joining with out a condition (could be correct but I doubt it) - that means each record in episodes will be joined to each record in pro . In addition, your subquery with the IF will return more then 1 record unless this table contains only 1 record (which again - I doubt it) .

Try this:

SELECT distinct t.*, IF (s.uid is null,'True','False')  AS 'uid'
FROM Episodes t
Left JOIN pro s
 ON(s.uid = '4342') 
ORDER BY t.id DESC
LIMIT ?????

Try using a sub query instead of a join:

Select t.*,
       Case when exists(select 1 from pro s where s.uid = '4342')
            Then 'true' else 'false' 
       End as 'uid'
From episodes t
Order by t.id desc
Limit 9;

Upvotes: 2

Related Questions