PHP MYSQL NUM ROWS FUNCTION

[jbwebdesign]

hello, i have a question about this script i am making.....

 

I am using the mysql_num_rows function and i am trying to validate a form (if the field already exists, show an error message).....

but for some reason PHP is working backwards.....

 

am i doing something wrong?

 

basically i have the $num_rows==0 because it seems to work this way and its supposed to be $num_rows > 0

 

 

here is my code:

<?php 
//if the add a client form is submitted then add client information to database.
if($_GET['sm'] == "new_form_field" && $_GET['new'] == "yes"){

$status = "";//default value
$query = "SELECT * FROM ". TABLE_FORMS ." WHERE field_name = ".FIELD_PREFIX.$_POST['field_name'];
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

if ($num_rows == 0) {
 // do something
 $status = $status . "Field Name Already Exists!";
 //echo $query;
 //echo $num_rows;

}
else{

$data['field_name'] = FIELD_PREFIX . $_POST['field_name']; 
$data['form_field'] = '<input type="text" name="'.FIELD_PREFIX.$_POST['field_name'].'">';
$data['user_var'] = '{#'.FIELD_PREFIX.$_POST['field_name'].'}'; 

 // do something else

$db->query_insert(TABLE_FORMS, $data); 

//first we check if the field exists in that table(didn't code the checking part yet)....if not then do this
$sql = "ALTER TABLE ".TABLE_USERS." ADD ".$data['field_name']." VARCHAR(100);";
$db->query($sql);

$status = $status . "New Form Field Has Been Created Successfully!<br /><br />
<a href=\"?m=form_fields\">GO BACK</a>";
}
} 
?>

[falkencreative]

The code you posted above seems fine. If you want to use $numrows > 0, then you'd need to switch the order of the if/else:

 

if ($num_rows > 0) {

$data['field_name'] = FIELD_PREFIX . $_POST['field_name']; 
$data['form_field'] = '<input type="text" name="'.FIELD_PREFIX.$_POST['field_name'].'">';
$data['user_var'] = '{#'.FIELD_PREFIX.$_POST['field_name'].'}'; 

$db->query_insert(TABLE_FORMS, $data); 

//first we check if the field exists in that table(didn't code the checking part yet)....if not then do this
$sql = "ALTER TABLE ".TABLE_USERS." ADD ".$data['field_name']." VARCHAR(100);";
$db->query($sql);

$status = $status . "New Form Field Has Been Created Successfully!<br /><br />
       <a href=\"?m=form_fields\">GO BACK</a>";  
}
else
{

 $status = $status . "Field Name Already Exists!";
 //echo $query;
 //echo $num_rows;
       }
} 

[jbwebdesign]

oh okay, i thought i was doing something wrong.... thanks

[jbwebdesign]

i have another code now that is similar to the first one and it doesn't seem to work.... do you have any idea why?

 

basically it's not letting me insert anything into the database even if it doesn't exist.....

when i switch the code like you showed above then it doesn't validate anything even if it does exist.....

 

here is the code:

 

<?php 
//if the add a client form is submitted then add client information to database.
if($_GET['sm'] == "new_variable" && $_GET['new'] == "yes"){

$status = "";//default value
$query = "SELECT * FROM ". TABLE_VARIABLES ." WHERE variable = " . $_POST['variable'];
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);

if ($num_rows > 0) {

$data['variable'] = $_POST['variable']; 
$data['value'] = $_POST['value']; 

$db->query_insert(TABLE_VARIABLES, $data); 
$status = "New Variable Has Been Created Successfully!<br /><br />
<a href=\"?sm=my_variables\">GO BACK</a>";	

}
else{

$status = $status . "Variable Already Exists!<br /><br />
<a href=\"?sm=my_variables\">GO BACK</a>";
}

}

?>

[falkencreative]

when i switch the code like you showed above then it doesn't validate anything even if it does exist.....

So what currently happens? What does $num_rows hold? So it tries to insert data every time, whether or not it exists?

 

I'm assuming that is because your query itself is returning 0 results or an error... I think you mentioned that in a different post. Once that gets resolved, I imagine this issue will go away as well?

[jbwebdesign]

well it doesn't return an error but the num_rows doesn't return a value either because i tried to echo it out.

 

the only thing that i can think of is the fact that the $_POST['variable'] contains special characters {#variable_name}

 

maybe this is messing up the query.

 

i'm really not sure what to do in this case.

[falkencreative]

Like I said in the other topic that you started, I think the first step would be to confirm that your query doesn't have any issues. I would create a sample query based on your code, something like:

 

SELECT * FROM [your table name here] WHERE variable = {#test}

 

and plug that into the SQL query textarea within PHPMyAdmin. Before troubleshooting any more, make sure that the query is returning (or not returning) results correctly.

[jbwebdesign]

Like I said in the other topic that you started, I think the first step would be to confirm that your query doesn't have any issues. I would create a sample query based on your code, something like:

 

SELECT * FROM [your table name here] WHERE variable = {#test}

 

and plug that into the SQL query textarea within PHPMyAdmin. Before troubleshooting any more, make sure that the query is returning (or not returning) results correctly.

 

it keeps returning: "num rows is NOT greater then 0"

 

attached is an image of my table and i tried to query it like you said. Everything worked fine

 

here is my code:

<?php 
//if the add a client form is submitted then add client information to database.
if($_GET['sm'] == "new_form_field" && $_GET['new'] == "yes"){
$block = mysql_real_escape_string(FIELD_PREFIX . $_POST['field_name']);

$result = mysql_query("SELECT * FROM '".TABLE_FORMS."' WHERE form_field='".$block."'");
$num_rows = mysql_num_rows($result);

if ($num_rows > 0) {
 // do something
 echo 'num rows is greater than 0';
}
else {
 // do something else
 echo 'num rows is NOT greater then 0';
}
}
?>

Edited November 19, 2010 by jbwebdesign

[jbwebdesign]

i just put my code live instead of on my wamp server and now i get an error message: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home1/moneypok/public_html/affordablewebdev/ContractGenerator/modules/form_fields/process.php on line 7

 

seems that my wamp has errors turned off

[jbwebdesign]

after everything, i found my error. The problem was the query.

 

$result = mysql_query("SELECT * FROM '".TABLE_FORMS."' WHERE form_field='".$block."'");

 

i needed to remove the single quotes before the table.

 

the new and working query is:

 

$result = mysql_query("SELECT * FROM ".TABLE_FORMS." WHERE form_field='".$block."'");

[falkencreative]

i just put my code live instead of on my wamp server and now i get an error message: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home1/moneypok/public_html/affordablewebdev/ContractGenerator/modules/form_fields/process.php on line 7

 

seems that my wamp has errors turned off

 

That's what I figured. mysql_num_rows() should return SOMETHING unless you have an error somewhere.

 

Glad to hear you got it figured out.