SELECT data from one row IF another row = 1?

Vernier · 03-25-2012, 12:23 AM

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

Mishu · 03-25-2012, 12:28 AM

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.

Vernier · 03-25-2012, 12:42 AM

Quote:

Originally Posted by Mishu

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

Mishu · 03-25-2012, 12:59 AM

Quote:

Originally Posted by Vernier

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.

Old Pedant · 03-25-2012, 01:58 AM

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";

Mishu · 03-25-2012, 02:06 AM

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.

Old Pedant · 03-25-2012, 02:16 AM

I suppose it could be homework, but HabFab is a real company and the DB name, etc., seem consistent for it.

Vernier · 03-27-2012, 10:00 PM

Quote:

Originally Posted by Old Pedant

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

Vernier · 03-30-2012, 04:54 PM

Bump

Cheers,

~ David

Keleth · 03-30-2012, 08:47 PM

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.