homepage Welcome to WebmasterWorld Guest from 54.83.133.189
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
if statement based on mysql value
Sub_Seven




msg:4180719
 6:28 pm on Aug 2, 2010 (gmt 0)

Hello all,

Could anyone help me figure out why this is not working:

<?php
$sql = "SELECT `field` FROM `table`;
$result = mysql_query($sql) or die (mysql_error());
if(mysql_num_rows($result)) {
$value = mysql_result($result, 0);
if($value == "1") {
echo "<p>This is a test.</p>";
}
}
?>


It makes no difference, it will echo the html even if the value from the db is 0 or 1, thanks everybody...

 

Matthew1980




msg:4180721
 6:36 pm on Aug 2, 2010 (gmt 0)

Hi there Sub_Seven,

<?php
$sql = "SELECT `field` FROM `table`;
$result = mysql_query($sql) or die (mysql_error());
if(mysql_num_rows($result)) {
$value = mysql_result($result, 0);
if($value == "1") {
echo "<p>This is a test.</p>";
}
}
?>


Are you just trying to see if you have returned any rows & if so echo a value, if so, the way you are checking for value from the num_rows function looks odd. Try this:-

<?php
$sql = "SELECT `field` FROM `table`;
$result = mysql_query($sql) or die (mysql_error());
if(mysql_num_rows($result) > 0) {
$value = mysql_result($result, 0);
if(!$value) {
echo "<p>No results.</p>";
}
else{
echo "<p>Working.</p>";
}
}
?>



Hope I understood you right anyway :)

Cheers,
MRb

Sub_Seven




msg:4180732
 6:58 pm on Aug 2, 2010 (gmt 0)

I think you did not, but that's because I failed to explain my self the right way.

Basically what I need is for the if statement to check if the value on that input field is either 0 or 1, if it is 0 I don't need to echo anything but if it is 1 I need to echo some html. Hope that's better now, thanks so much for the help.

Matthew1980




msg:4180742
 7:15 pm on Aug 2, 2010 (gmt 0)

Hi there Sub_Seven,

Ah, so you have an enum field in the DB then, that makes sense :)

Try this:-

$sql = "SELECT `field` FROM `table` ";
$result = mysql_query($sql) or die (mysql_error());
if(mysql_num_rows($result) > 0) {
while($DataReturned = mysql_fetch_object($result)){
if($DataReturned->field == "1") {
echo "<p>Returned 1.(It works)</p>";
}
}
}


Then for each iteration of the rows returned, the if/else will tell you what's there, otherwise you would need to specify an id number (if there is an incremental id) to be more specific.

Hope that makes sense - there are other ways of doing this sort of thing, but that's what comes to mind first..

Cheers,
MRb

Sub_Seven




msg:4180753
 7:36 pm on Aug 2, 2010 (gmt 0)

enum huh!

Is it necessary that for the field to be enum in order for this script to work? If so I wasn't aware of that...

I tried to change this in the db and I get an error saying:

SQL query:

ALTER TABLE `applications` CHANGE `previous_employee` `previous_employee` ENUM NULL

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1

I delete it this and tried re-creating it but I can't do this either, how can this be fixed?

BTW, if enum is not necessary I could change the values for anything else, the value sent to the db is not what matters, what matters is the html displayed on the page, thanks again.

Matthew1980




msg:4180775
 8:10 pm on Aug 2, 2010 (gmt 0)

Hi there Sub_Seven,

Enum field syntax is structured as such: enum("0","1") [dev.mysql.com ]

This means that the database will only have a value of (in this case) 1 or 0, and you can specify it to be a default value. Then when you want to update the cell, you just specify the value as you want ie; 1 or 0, and if you try to put a 2 or 3, the default value will then be used :) Hope that makes sense.

The only reason I asked if it was enum was because it sounds like you want to display html depending on the value of that field, enums are usefull to clear up ambiguity of data to I find - it either one or the other, but you can theoretically have lots of options, I generally stick to just the 2 (but it can be situation dependant) for logged in (1) or out (0) then check that against a cookie - there are other methods, I just prefer that...

To be honest it's easier to edit your mysql through phpmyadmin, I try to avoid command line, as you can do lots of damage with a wrongly constructed statement...

>>Is it necessary that for the field to be enum in order for this script to work?
No it's not 'necessary' but it's advantageous IMO that's all.

I didn't mean to confuse the issue there, It just sounded the logical choice to me :)

Cheers,
MRb

Readie




msg:4180777
 8:16 pm on Aug 2, 2010 (gmt 0)

Just a thought here...
Basically what I need is for the if statement to check if the value on that input field is either 0 or 1, if it is 0 I don't need to echo anything but if it is 1 I need to echo some html. Hope that's better now, thanks so much for the help.


If you're echoing nothing when the value is not 1, why retrieve values where the value is not one at all?

$sql = 'SELECT field FROM table WHERE field = "1"';
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)) {
// Echo some HTML here
}

rocknbil




msg:4180782
 8:34 pm on Aug 2, 2010 (gmt 0)

Precisely, why do a num_rows count if you don't need one . . . . another manifestation:

$sql = 'SELECT field FROM table';
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
$value=($row[0]==1)?'Yes':'No';
echo $value;
}

_array returns both indexed and associative, $row['field'] is identical output.

Matthew1980




msg:4180788
 8:49 pm on Aug 2, 2010 (gmt 0)

^^^
>>why do a num_rows count if you don't need one

I usually do that just in case I need to see if there is anything returned :) It's down to preference I think - then again were all here to learn, so if there is a better way I have my pen at the ready ;-p

while($row = mysql_fetch_array($result)) {
$value=($row[0]==1)?'Yes':'No';
echo $value;
}

I never even considered the ternary approach Eek! I must be tired (I have been doing VB today Ack!)

Ah well I think we were all pointing in the same area anyway, though enum's are very useful, and provide that little bit more security.

Cheers,
MRb

Sub_Seven




msg:4180805
 9:12 pm on Aug 2, 2010 (gmt 0)

Matthew1980, Readie,

I believe there has got to be something wrong in my side because both methods keep echoing the html whether the value on the db is 0 or 1.

I tested by using a different input field where the values are either "yes" or "no" and I keep seeing the html echoed on both cases (for testing purposes I have only two entries in the db).

This is really weird, do any of you know why this happens?

I am more than willing to clarify any questions in order to get help, thanks so much guys.

Matthew1980




msg:4180811
 9:41 pm on Aug 2, 2010 (gmt 0)

Hi there Sub_Seven,

Don't forget that you don't need to quote numerical values when using comparison operators to evaluate a variable to a value - hope that makes sense :)

When you say input field, what do you mean? Is this a form submission or extracted from a DB?

Just try running the query directly:-

$sql = "SELECT `field` FROM `table` ";
$result = mysql_query($sql);
while($values = mysql_fetch_array($result)){
print_r($values);
}

That should give you an array with all the keys of 'field'=>'THE_VALUE' where the value is or should be either 1 or 0.

From there you should be able to see what's going on.

Cheers,
MRb

Sub_Seven




msg:4180821
 10:31 pm on Aug 2, 2010 (gmt 0)

Hi,

I've tried with and without quotes, and I'm sorry I meant field (from a db) not input field, although data comes from a form submission I am working with results from the db only to get this done.

When running the query directly it prints the following:

Array ( [0] => 1 [previous_employee] => 1 ) Array ( [0] => 0 [previous_employee] => 0 )

Should I be able to resolve my issue by seeing something in that line?

too much information




msg:4180952
 5:38 am on Aug 3, 2010 (gmt 0)

I think your problem is that "mysql_num_rows" returns a value on success and False on failure. So instead just check to see if the value is > 0 so for example:

if(mysql_num_rows($result) > 0) {
// do something
}

Oops, spotted another problem:

$value = mysql_result($result, 0);

should be

$value = mysql_result($result, 0, 'field');

Matthew1980




msg:4180984
 7:15 am on Aug 3, 2010 (gmt 0)

Hi there Sub_Seven,

From that array output you can see the value you need to evaluate:-

Array ( [0] => 1 [previous_employee] => 1 ) Array ( [0] => 0 [previous_employee] => 0 )


You need to check for the column named 'previous_employee' so something like this would suffice:-

$sql = "SELECT `field` FROM `table` ";
$result = mysql_query($sql);
while($values = mysql_fetch_array($result)){
if($values['previous_employee'] == 1){
echo "Woo! A match";
}
}


I assume at this point that the array output was from the code I posted last night? If that's so, you see the idea of what I have done, try that, it *should* work.

Good Luck,

Cheers,
MRb

Sub_Seven




msg:4181129
 1:27 pm on Aug 3, 2010 (gmt 0)

@too much information:

I tried this:

<?php
$sql = 'SELECT `previous_employee` FROM `applications`';
$result = mysql_query($sql) or die (mysql_error());
if(mysql_num_rows($result) > 0) {
$value = mysql_result($result, 0, 'previous_employee');
if($value == 1) {
echo "<p>This is a test.</p>";
}
}
?>

and it echoes the test tag on both cases again.

@Matthew1980:

The array output was from the code you posted last night, that is correct.

I tried your latest suggestion and I got the same problem, it echoes the message in both cases where the value is 0 and 1.

@Everybody:

I know the thread is becoming rather large, I appreciate all the help and patience, I don't understand why this is not working, all the different suggestions are good and should work, I'm becoming to think that there is something wrong somewhere else but I just don't know what to look for...

Matthew1980




msg:4181147
 1:57 pm on Aug 3, 2010 (gmt 0)

Hi there Sub_seven,

Have you seen some of the threads on here :) they can be spanned over lots of pages until the solution is found...

Your (modified) Code:

<?php

$sql = "SELECT `previous_employee` FROM `applications` ";
$result = mysql_query($sql) or die (mysql_error());

//check the query returns a result
if(mysql_num_rows($result) > 0) {

//see how many rows are returned
echo "There are: <b>".mysql_num_rows($result)."</b> Rows of results";

//It does, loop through the data returned
while($values = mysql_fetch_array($result)){

//debug ensure that the values exist and the names are correct
print_r($values);

//echo the value you are after no matter what its value is
echo $values['previous_employee'];

//try the 'catch'
if($values['previous_employee'] == 1) {//try with the quotes or without, result may change
echo "<p>This is a test</p>";
}

}//close while

}
else{
//no rows returned
echo "No information was recieved";
exit;
}
?>

Other than that I am officially stumped, without seeing the structure of the DB I can't really see much else that could affect this.

Cheers,
MRb

Sub_Seven




msg:4181228
 3:44 pm on Aug 3, 2010 (gmt 0)

Thanks Mathew

I really don’t want to give up on this but I don’t know what’s the level of patience here, so far it is way more than I expected and thank you and everyone else for that.

So, based on your last reply I created and whole new test environment where some things have changed, new db is called “test”, the only table in it is called ”ttable” and there are only two fields: “name” (text) and “active” (int(1)), that’s all there is to the db.

I have noticed how I’ve been getting different behaviors with the field named “previous_employee” so that’s why I changed that to that field named “active”. Question, are there limitations when naming fields in mysql? Is there a limit on the amount of characters or can you not use _ for some reason?

I inserted your last code suggestion in my applicants.php page, this is what I can see on the browser:

(Whatever person) There are: 2 Rows of resultsArray ( [0] => 0 [active] => 0 ) 0Array ( [0] => 1 [active] => 1 ) 1

This is a test

Now, for the sake of resolving this (and just in case it is important) I’m posting the code of all 4 files relevant to this test environment

Index.php
<html>
<head></head>
<body>
<form action="insert.php" name="form" method="post">
Name: <input type="text" name="name" />
Active: Yes<input type="radio" name="active" value="1" /> No<input type="radio" name="active" value="0" />
<input type="submit" value="Send"></input>
</form>
</body>
</html>

Insert.php
<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("test", $con);

//$previous_employee = isset($_POST['previous_employee']) ? $_POST['previous_employee'] : '';

$sql="INSERT INTO ttable (name, active)

VALUES
('{$_POST['name']}', '{$_POST['active']}')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}

echo "Data sent";

mysql_close($con)
?>

Select.php
<html>
<head>
</head>
<body>
<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("test", $con);

$result = mysql_query("SELECT * FROM ttable");
?>

<p>Name | Active</p>
<?php
while($row = mysql_fetch_array($result))
{
?>
<div class='input'>
<a href="applicants.php?name=<?php echo rawurlencode($row['name']);?>">
<?php echo $row['name']; ?>
</a>
</div>
<div class='input'>
<?php echo $row['active']; ?>
</div>
<br />
<?php }
?>

<?php
mysql_close($con);
?>
</body>
</html>

Applicants.php
<html>
<head>
</head>
<body>
<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("test", $con);

$result = mysql_query("SELECT * FROM ttable");
?>

<?php
if(isset($_GET['name']) && !empty($_GET['name'])){

//captured the data now display contents from db
$SqlQuery = "SELECT * FROM `ttable` WHERE `name` = '".strip_tags(mysql_real_escape_string($_GET['name']))."' ";

$SqlSent = mysql_query($SqlQuery) or die(mysql_error());

//loop through returned data
while($result = mysql_fetch_array($SqlSent)){ ?>

<p><a class="name"><?php echo $result['name'];?></a>


<?php

$sql = "SELECT `active` FROM `ttable` ";
$result = mysql_query($sql) or die (mysql_error());

//check the query returns a result
if(mysql_num_rows($result) > 0) {

//see how many rows are returned
echo "There are: <b>".mysql_num_rows($result)."</b> Rows of results";

//It does, loop through the data returned
while($values = mysql_fetch_array($result)){

//debug ensure that the values exist and the names are correct
print_r($values);

//echo the value you are after no matter what its value is
echo $values['active'];

//try the 'catch'
if($values['active'] == 1) {//try with the quotes or without, result may change
echo "<p>This is a test</p>";
}

}//close while

}
else{
//no rows returned
echo "No information was recieved";
exit;
}
?>


<?php }//close while loop

}
else{
//redirect back to form as $_GET doesn't have a value
header("location: select.php");
exit;
}
?>

<?php
mysql_close($con);
?>
</body>
</html>

Hope this helps finding the problem, thanks.

Sub_Seven




msg:4181354
 6:30 pm on Aug 3, 2010 (gmt 0)

Guys,

I have resolved the issue, well, not me technically but the script is now working fine.

There are some very bright people here at work and I looked for help here as a last resource...

This is what made it:


if($result['previous_employee'] == 1)
echo "<p>This is a test.</p>";


The explanation was that I was already accessing the db here

$SqlQuery = "SELECT * FROM `applications`

And that it was not necessary to access it again from that query...

Oh well! I appreciate all the help provided, and I am pretty sure I will have more questions as I keep learning this evil code, I'll see you soon, thanks.

Matthew1980




msg:4181372
 7:00 pm on Aug 3, 2010 (gmt 0)

Hi there Sub_Seven,

Well, that's progress I suppose ;-p

I don't fully understand the issue though, but I am glad that you are accessing the correct name out of the table now.

Have fun with the rest, and next week you will look back and laugh at this :)

Cheers,
MRb

Sub_Seven




msg:4181405
 7:51 pm on Aug 3, 2010 (gmt 0)

I'm laughing already :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved