Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts

    include blank rows for unfound items.

    I'm trying to figure out how to get my query to create a row in the result even if a result didn't come back from the database... I need to have exactly the number of rows I submitted. Basically, I want to do this...

    SELECT fieldname FROM tablename
    WHERE tpl_id = '100373333'
    OR tpl_id = '26613376'
    OR tpl_id = '26613377'
    (...)


    and supposing 26613376 isn't in the database, I want the result to be:

    result_100373333
    null
    result_26613377

    (...)

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,297
    Thanks
    23
    Thanked 612 Times in 611 Posts
    if tpl_id = '26613376' is not in the DB the query will never be run and nothing will be returned.
    The best I can think of here is to run a query on each tpl_id and echo a null if nothing is returned.
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    Sunfighter is dead wrong. As the query is formulated, you will indeed get back two rows.

    What you are after *could* be done--within some severe limitations--using stored procedure and temp table. But that's probabably not the right approach. I think you'd be much better off doing most of the work here in PHP.

    So you'd create an array of values in PHP, order them by numerical order.
    Then you'd query to get records for those values from the DB, order by tpl_id
    Then you would loop through the ORIGINAL ARRAY testing to see if each value is in the results from the query. Pretty easy, really.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    Example code.

    I don't use PHP, but I think the logic here is right. Play with it if not.
    Code:
    $ids = array(100373333,26613376,26613377,188371,8891); // the raw numbers
    $ids = sort( $ids ); // in numeric order
    
    $sql = "SELECT tpl_id, fieldname FROM tablename WHERE tpl_id IN ( " . implode(",",$ids) . ")";
    
    $result = mysql_query( $sql );
    
    $row = mysql_fetch_assoc( $result );
    
    foreach ( $ids as $id )
    {
        // assume no match on id vs tpl_id:
        $val = "--NULL--";
    
        // but then check to see if there is a match
        if ( $row && $id == $row["tpl_id"] ) {
            $val = $row["fieldname"];
            $row = mysql_fetch_assoc( $result );
        }
        echo "id of $id has value of $val <br/>";
    }
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,297
    Thanks
    23
    Thanked 612 Times in 611 Posts
    Quote Originally Posted by Old Pedant View Post
    Sunfighter is dead wrong. As the query is formulated, you will indeed get back two rows.
    That is not what I said Of course you get back two rows but you don't get a row if tpl_id = '26613376' is not in the DB.
    I can see where I was not clear enough here.

    I have worked on this for some time and even though I though I could get results based on a query return, it does not seem to be the case. What I did post
    The best I can think of here is to run a query on each tpl_id and echo a null if nothing is returned.
    Maybe the only way of doing this but at a high cost in execution time.
    For what it's worth I found this:
    If given condition does not match any record in the table, then query would not return any row.
    from this site http://www.tutorialspoint.com/mysql/...ere-clause.htm But can find nothing in the 5.5 manual to back this up. This is the code I have run over and over. It does not detect the anything when the id does not exist. It's my database and 2862 does not exist. If 2864 is subbed I get three rows.

    PHP Code:
    <?php
    require ('./inc/DB_connectsqli.php');

    $ids = array(186728622873);

    for(
    $i 0$i <= 2;$i++){
        
    $query "SELECT rsn FROM clan_members WHERE id = $ids[$i]";
        
    $result mysqli_query($mysqli$query);
        while(
    $row mysqli_fetch_assoc($result)){
            if(isset(
    $row['rsn'])){
                echo 
    $row['rsn'].'<br>';
            }else{
                echo 
    'Dead'.'<br>';
            }
        }
    }
    ?>
    I have also tried these lines:
    PHP Code:
    if(isset($row)){
    if(isset(
    $row['rsn'])){
    if(!isset(
    $row)){
    if(!isset(
    $row['rsn'])){
    if(
    $row['rsn'] != ''){
    if(
    $row['rsn'] == ''){
    if(
    $row['rsn'] == null){
    if(
    $row == null){ 
    What has to be done is a query run first to collect the id's that don't exist.
    This works fairly:
    PHP Code:
    <?php
    require ('./inc/DB_connectsqli.php');

    $ids = array(186728622873);
    $bad $ids;

    for(
    $i 0$i <= 2;$i++){
        
    $query "SELECT id FROM clan_members WHERE id = $ids[$i]";
        
    $result mysqli_query($mysqli$query);
        while(
    $row mysqli_fetch_assoc($result)){
            
    $pos array_search($row['id'], $bad);
            unset(
    $bad[$pos]);
        }
    }
    for(
    $i 0$i <= 2;$i++){
        if(@
    $bad[$i] != $ids[$i]){
            
    $query "SELECT rsn FROM clan_members WHERE id = $ids[$i]";
            
    $result mysqli_query($mysqli$query);
            while(
    $row mysqli_fetch_assoc($result)){
                echo 
    $row['rsn'].'<br>';
            }
        }else{
            echo 
    $bad[$i].' __DEAD<br>';
        }
    }
    ?>
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    Sorry about misunderstanding you.

    ******
    I think the fix for your first code is simple. Just change one keyword:
    Code:
    for($i = 0; $i <= 2;$i++){
        $query = "SELECT rsn FROM clan_members WHERE id = $ids[$i]";
        $result = mysqli_query($mysqli, $query);
        if($row = mysqli_fetch_assoc($result)){
            if(isset($row['rsn'])){
                echo $row['rsn'].'<br>';
            }else{
                echo 'Dead'.'<br>';
            }
        }
    }
    You want to test if there is a row only ONCE per id.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,297
    Thanks
    23
    Thanked 612 Times in 611 Posts
    The above code does not work. Still only getting two rows.
    The last bit of code that I posted works. It's just convoluted.
    PHP Code:
     <?php
    require ('./inc/DB_connectsqli.php');

    $ids = array(186728622873);
    $bad $ids;

    for(
    $i 0$i <= 2;$i++){
        
    $query "SELECT id FROM clan_members WHERE id = $ids[$i]";
        
    $result mysqli_query($mysqli$query);
        while(
    $row mysqli_fetch_assoc($result)){
            
    $pos array_search($row['id'], $bad);
            unset(
    $bad[$pos]);
        }
    }
    for(
    $i 0$i <= 2;$i++){
        if(@
    $bad[$i] != $ids[$i]){
            
    $query "SELECT rsn FROM clan_members WHERE id = $ids[$i]";
            
    $result mysqli_query($mysqli$query);
            while(
    $row mysqli_fetch_assoc($result)){
                echo 
    $row['rsn'].'<br>';
            }
        }else{
            echo 
    $bad[$i].' __DEAD<br>';
        }
    }
    ?>
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,901
    Thanks
    79
    Thanked 4,422 Times in 4,387 Posts
    I am an an idiot. But I'm also a NON-PHP user.

    Try *THIS*:
    Code:
    for($i = 0; $i <= 2;$i++){
        $query = "SELECT rsn FROM clan_members WHERE id = $ids[$i]";
        $result = mysqli_query($mysqli, $query);
        if($row = mysqli_fetch_assoc($result)){
            echo $row['rsn'].'<br>';
        }else{
            echo 'Dead'<br>';
        }
    }
    The "if" needs to be applied to the fetch of the row! If there is no data, there is no row.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    turpentyne (01-16-2014)

  • #9
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,297
    Thanks
    23
    Thanked 612 Times in 611 Posts
    Great! this did it.
    So am I to assume that doing the while() before the if() in my original post set $row to something although we couldn't see what that was?
    And don't sell yourself short on using PHP, you do alright.
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #10
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts
    Hmmm... this doesn't quite seem to work for me - though it's seems awfully close. I'm getting 'no result' for everything, even though the entries do exist. I checked the query in mysql to verify results and it worked there.

    Here's my slightly modified version:

    Code:
    $ids = array(
    'tro-100373333',
    'tro-26613376',
    'tro-26613377',
    'tro-26609094',
    'tro-26613434',
    'tro-26621977',
    'tro-26600291',
    'tro-26601297',
    'tro-26621983',
    'tro-26617746',
    'tro-26609850');
    
    
    $sql = "SELECT tpl_id, plant_name FROM tablename WHERE tpl_id IN ('".implode("','",$ids)."')";
    
    
    $result = mysql_query($sql);
    
    $row = mysql_fetch_assoc($result);
    
    for($i = 0; $i <= 10;$i++){
        $query = "SELECT plant_name,tpl_id FROM tablename WHERE tpl_id = '$ids[$i]'";
        $result = mysqli_query($query);
        $which = $ids[$i];
        if($row = mysqli_fetch_assoc($result)){
            echo $row['plant_name'].' is '.$row['tpl_id'].'<br>';
        }else{
            echo $which.': no result <br>';
        }
    }
    Last edited by turpentyne; 01-16-2014 at 11:22 PM.

  • #11
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,067
    Thanks
    2
    Thanked 319 Times in 311 Posts
    I suspect your code is not working because you are mixing mysql and mysqli statements. Do you have php's error_reporting/display_errors turned full on so that php will help you avoid wasting time with code that won't work at all?

    You are making this much harder than it is. Untested but should work -

    Code:
    $ids = array(
    'tro-100373333',
    'tro-26613376',
    'tro-26613377',
    'tro-26609094',
    'tro-26613434',
    'tro-26621977',
    'tro-26600291',
    'tro-26601297',
    'tro-26621983',
    'tro-26617746',
    'tro-26609850');
    
    
    $sql = "SELECT tpl_id, plant_name FROM tablename WHERE tpl_id IN ('".implode("','",$ids)."')";
    
    $result = mysqli_query($mysqli,$sql);
    
    $data = array();
    while($row = mysqli_fetch_assoc($result)){
    	$data[$row['tpl_id']] = $row; // store data using the tpl_id as a key
    }
    
    foreach($ids as $id){
    	if(isset($data[$id])){
    		// the data/row for this id exists
            echo $data[$id]['plant_name'].' is '.$id.'<br>';
    	} else {
    		// the data/row for this id does not exist
    		echo "$id: no result<br>";
    	}
    }
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    turpentyne (01-16-2014)

  • #12
    Regular Coder
    Join Date
    Aug 2010
    Posts
    418
    Thanks
    18
    Thanked 2 Times in 2 Posts
    ah!.. didn't catch that..

    Yeah. bad habit of mine, that error checking. ok. Now it works. thanks!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •