Page 1 of 1

IF TRUE then Nothing ELSE Enter

Posted: Tue Jan 25, 2011 9:58 pm
by brassfid
Hello All I am trying to make a code that will look at the database and compare with what has been entered. IF the entered value already equals what is int he database then I would like it to do nothing, but if it is not there, then I would like it to be entered. Also, I would like it NOT to be case sensitive. This is my code so far, which is not working, I believe it is the $sql portion.

Thanks!

Code: Select all

$con=...

$value = mysql_real_escape_string($_POST['value']);

$sql1="IF SELECT * tb WHERE column=$value THEN NULL
ELSE 
INSERT INTO tb (column)
VALUES
('$value')";

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

mysql_close($con)

Re: IF TRUE then Nothing ELSE Enter

Posted: Tue Jan 25, 2011 10:13 pm
by danwguy
Use a php if statement instead. check the database for the value then run your if statement. something like...

Code: Select all

$con=...

$value = mysql_real_escape_string($_POST['value']);

$sql1= mysql_query("SELECT * FROM tb WHERE column=$value"); 
if(!$sql1)
  {
  die('Error: ' . mysql_error());
  }
if(mysql_num_rows($sql) == '0') {
$insert = mysql_query("INSERT INTO tb values ($value)");
}
if($insert) {
echo "Successfully Inserted into db" . $value;
}
mysql_close($con)
not tested and you probably want to change up the values or whatever, but I would say that would be your best option.

Re: IF TRUE then Nothing ELSE Enter

Posted: Tue Jan 25, 2011 10:52 pm
by brassfid
I am getting an error: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

Code: Select all

<?php

$con = mysql_connect(...);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$value = mysql_real_escape_string($_POST['html value']);

$sql1= mysql_query("SELECT * FROM TB WHERE column='$value'"); 
if(!$sql1)
  {
  die('Error: ' . mysql_error());
  }
if(mysql_num_rows($sql) == '0') {mysql_query("INSERT INTO Table (column) VALUES ($value)");
}


mysql_close($con)

?>

Re: IF TRUE then Nothing ELSE Enter

Posted: Tue Jan 25, 2011 11:09 pm
by danwguy
That's why I said you need to change up the values to suit your needs, that was just an example. I will try to write out an actual code that will work. but I need to get out of the office right now, so I'll do it either later tonight or have it for you in the morning.

Re: IF TRUE then Nothing ELSE Enter

Posted: Tue Jan 25, 2011 11:27 pm
by brassfid
Right, I changed the variables, and then changed them back. The specific line that it is complaining about is this line

Code: Select all

if(mysql_num_rows($sql) == '0') {mysql_query("INSERT INTO Table (column) VALUES ($value)");
I think that it is misreading the '0' or something. im not sure

Re: IF TRUE then Nothing ELSE Enter

Posted: Wed Jan 26, 2011 12:38 am
by brassfid
For the life of me I cannot figure this issue out. I keep getting different errors at the 34th row. I think it is simply a missplaced semi-colon, but I can't figure it out. Thanks for your help!

Code: Select all

<?php

$con = mysql_connect("localhost","user","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("werebeer_Beers", $con);

$username = mysql_real_escape_string($_POST['username']);
$beername = mysql_real_escape_string($_POST['beername']);
$brewery = mysql_real_escape_string($_POST['brewery']);
$style = mysql_real_escape_string($_POST['style']);
$aroma = mysql_real_escape_string($_POST['aroma']);
$srm = mysql_real_escape_string($_POST['srm']);
$appearance = mysql_real_escape_string($_POST['appearance']);
$flavor = mysql_real_escape_string($_POST['flavor']);
$mouthfeel = mysql_real_escape_string($_POST['mouthfeel']);
$overallimpression = mysql_real_escape_string($_POST['overallimpression']);

//$sql1 adds all characteristics of beer into the Beers table
$sql1 = "INSERT INTO Beers (username, beername, brewery, style, aroma, srm, appearance, flavor, mouthfeel, overallimpression)
VALUES
('$username', '$beername','$brewery','$style', '$aroma', '$srm', '$appearance', '$flavor', '$mouthfeel', '$overallimpression')";

if (!mysql_query($sql1,$con))
  {
  die('Error: ' . mysql_error());
  }
  
//sql2 adds breweries to brewery database, but only allows new one. [size=150]!!This is where the errors are occuring, above this line everything works fine!![/size]
$sql2 = "SELECT * FROM Breweries WHERE brewery=$brewery"; 
$sql3 = if(mysql_num_rows($sql2) == 0) {mysql_query("INSERT INTO Breweries (brewery) 
VALUES 
('$brewery')");
}

if (!mysql_query($sql3,$con))
  {
  die('Error: ' . mysql_error());
  }
 
header( 'Location: http://www.werebeer.com/addnewbeer/' );
 
mysql_close($con)

?>

Re: IF TRUE then Nothing ELSE Enter

Posted: Wed Jan 26, 2011 2:28 pm
by danwguy
as for the mysql_num_rows command try it like this...

Code: Select all

if(mysql_num_rows == 0) {
or you could try leaving it blank, i.e.

Code: Select all

if(mysql_num_rows == '') {
you could also try using

Code: Select all

if(!mysql_num_rows >= 1) {
that way whatever it is passing that isn't one or above it will add the insert statement

Re: IF TRUE then Nothing ELSE Enter

Posted: Wed Jan 26, 2011 2:49 pm
by John Cartwright
Firstly, mysql_num_rows() is a function, and requires parentheses, otherwise it will be treated as a constant.

Secondly, mysql_num_rows() expects the return resource from mysql_query(), and does not expect a raw SQL string.

I.e.,

Code: Select all

$resource = mysql_query($sql) or die(mysql_error());
$numRows = mysql_num_rows($resource);

Re: IF TRUE then Nothing ELSE Enter

Posted: Wed Jan 26, 2011 3:11 pm
by danwguy
Sorry forgot that part in my last post, had it right in the first post though right? just wasn't supposed to have the quotes around the 0? so it should be ...

Code: Select all

if(mysql_num_rows($result) ==0) {
//put stuff here
}
right?

Re: IF TRUE then Nothing ELSE Enter

Posted: Wed Jan 26, 2011 3:40 pm
by John Cartwright
None of your examples after a quick look shows the correct usage. In fact, you have some extremely weird stuff going on.

This is the bit of code were interested in.

Code: Select all

$sql2 = "SELECT * FROM Breweries WHERE brewery=$brewery";
$sql3 = if(mysql_num_rows($sql2) == 0) {
   mysql_query("INSERT INTO Breweries (brewery) VALUES('$brewery')");
}
#1 - if() statements do NOT have a return. This is a fatal error in PHP.
#2 - You are passing raw SQL to mysql_num_rows(), pass the SQL to mysql_query() and pass the result of that function to mysql_num_rows()
#3 - You are not escaping your input values -- big mysql injection potential

Re: IF TRUE then Nothing ELSE Enter

Posted: Thu Jan 27, 2011 1:18 pm
by brassfid
Thanks for all your help, and criticisms. This community is great! This is the final result of my if/then quest, and it is working quite well:

Code: Select all

$brewery = mysql_real_escape_string($_POST['brewery']);

//adds breweries to brewery database, but only allows new one. 
$query = "SELECT * FROM Breweries WHERE brewery='$brewery'"; 

$result = mysql_query($query,$con) or die("Error: ".mysql_error()." with query: $query");

if (mysql_num_rows($result) == 0) {
// insert the  brewery
$insquery = "INSERT INTO Breweries (brewery) VALUES ('$brewery')";
$insresult = mysql_query($insquery, $con) or die("Error: ".mysql_error()." with query: $insquery");
}