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 10 of 10
  1. #1
    New Coder
    Join Date
    Dec 2011
    Posts
    86
    Thanks
    1
    Thanked 0 Times in 0 Posts

    SELECT data from one row IF another row = 1?

    Hey,

    I need it so that my site displays the information from the database IF the row: "active" = 1.

    This is what I have so far:

    Code:
    <?php
    mysql_connect("localhost", "", "");
    mysql_select_db("");
    ?>
    <html>
    
    <p>Here at HabFab, we're always looking for new staff to help keep our sites running. Without the staff of HabFab, we wouldn't be open. Fancy working for us? The below job applications are currently open. Remember you must be registered on our forum in order to apply for any job position. Simply click the job that takes your fancy and fill in the application form! You will be notified via Private Message on the forum if you have been successful, so be sure to check your private messages! We wish you the best of luck in your application!</p><br>
    </html>
    <?php
    $data = mysql_query("SELECT * FROM mybb_forms") 
     or die(mysql_error());
     
     $info = mysql_fetch_array( $data );
     
     while($info = mysql_fetch_array( $data )) 
     { 
     Print "<a href=http://habfab.com/forum/jobs.php?id=".$info['form_id'] . " target=_blank>" .$info['name'] . "</a><br><br>"; 
     }
    ?>
    There is no if function so far, I'm very new to mysql :P

    But I need it so that if the row active = 1, the data from that row displays from the database, but only those that say 1, any that say 0 mustn't display.

    Thanks

    ~ David
    Last edited by Vernier; 03-25-2012 at 10:49 AM.

  • #2
    Banned
    Join Date
    Mar 2012
    Posts
    306
    Thanks
    1
    Thanked 28 Times in 28 Posts
    You can put the print in an IF block and if active = 1 in the current row, create the link. Otherwise don't create the link.

  • #3
    New Coder
    Join Date
    Dec 2011
    Posts
    86
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Mishu View Post
    You can put the print in an IF block and if active = 1 in the current row, create the link. Otherwise don't create the link.

    Could you give me an example please, not too good with mysql! :P

    Thanks,

    ~ David

  • #4
    Banned
    Join Date
    Mar 2012
    Posts
    306
    Thanks
    1
    Thanked 28 Times in 28 Posts
    Quote Originally Posted by Vernier View Post
    Could you give me an example please, not too good with mysql! :P
    The IF has nothing to do with mysql. It's php code. You can use the IF to check the current value of active and if it = 1 then create the link like you already do. You are already using column values from a row in your code so getting the value of active will be the same. The php online manual shows how an IF block works.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,885
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    I think Mishu is giving bad advice on this one. Yes, you *CAN* do it the way he says, but it's not optimal. Say you had 100,000 records in your table. And only 7 of them had the active flag equal to 1.

    With his solution, MySQL has to send *ALL 100,000 ROWS* to PHP and then PHP simply throws away all but those 7.

    The better way is to do this in the SQL query:
    Code:
    $sql = "SELECT * FROM mybb_forms WHERE IFNULL(active,0) = 1";
    
    $data = mysql_query( $sql ) or die( mysql_error() );
    
    ... rest of code as you have it...
    You don't need to use the IFNULL( ) function if that active field is *NEVER* null.

    Then you could just do
    Code:
    $sql = "SELECT * FROM mybb_forms WHERE active = 1";
    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.

  • #6
    Banned
    Join Date
    Mar 2012
    Posts
    306
    Thanks
    1
    Thanked 28 Times in 28 Posts
    yes it's better to do it the sql rather than the application, but since the op was enquiring about an IF I was thinking this might be homework and he needs to do it a particular way and so I just gave a description without any code.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,885
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    I suppose it could be homework, but HabFab is a real company and the DB name, etc., seem consistent for it.
    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.

  • #8
    New Coder
    Join Date
    Dec 2011
    Posts
    86
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I think Mishu is giving bad advice on this one. Yes, you *CAN* do it the way he says, but it's not optimal. Say you had 100,000 records in your table. And only 7 of them had the active flag equal to 1.

    With his solution, MySQL has to send *ALL 100,000 ROWS* to PHP and then PHP simply throws away all but those 7.

    The better way is to do this in the SQL query:
    Code:
    $sql = "SELECT * FROM mybb_forms WHERE IFNULL(active,0) = 1";
    
    $data = mysql_query( $sql ) or die( mysql_error() );
    
    ... rest of code as you have it...
    You don't need to use the IFNULL( ) function if that active field is *NEVER* null.

    Then you could just do
    Code:
    $sql = "SELECT * FROM mybb_forms WHERE active = 1";

    PHP Code:
    <?php
    mysql_connect
    ("localhost""""");
    mysql_select_db("");
    ?>
    <html>

    <p>Here at HabFab, we're always looking for new staff to help keep our sites running. Without the staff of HabFab, we wouldn't be open. Fancy working for us? The below job applications are currently open. Remember you must be registered on our forum in order to apply for any job position. Simply click the job that takes your fancy and fill in the application form! You will be notified via Private Message on the forum if you have been successful, so be sure to check your private messages! We wish you the best of luck in your application!</p><br>
    </html>
    <?php
    $sql 
    "SELECT * FROM mybb_forms WHERE IFNULL(active,0) = 1";

    $data mysql_query$sql ) or die( mysql_error() );

    $data mysql_query("SELECT * FROM mybb_forms"
     or die(
    mysql_error());
     
     
    $info mysql_fetch_array$data );
     
     while(
    $info mysql_fetch_array$data )) 
     { 
     Print 
    "<a href=http://habfab.com/forum/jobs.php?id=".$info['form_id'] . " target=_blank>" .$info['name'] . "</a><br><br>"
     }
    ?>
    I've now got that but it still shows all.

    Thanks

    ~ David

  • #9
    New Coder
    Join Date
    Dec 2011
    Posts
    86
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Bump

    Cheers,

    ~ David

  • #10
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,536
    Thanks
    45
    Thanked 259 Times in 256 Posts
    You never use the data where you check if active = 1... you immediately do another query $data = mysql_query("SELECT * FROM mybb_forms") overwriting data with EVERYTHING. Whats the purpose of that second query now that you have the active data?

    You'll also never see the first dataset, as you pull a row, then immediately pull another row without making use of the first one.


  •  

    Tags for this Thread

    Posting Permissions

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