Need help with checking value in database..

DarkShadowWing · September 3, 2009

Hi all, I'm new here.

What I need to do is check if a user 1st name, last name, and email all exist, if 1 if them or more than 1 of them exists, and the user tries to submit the same thing twice, it does not let them do so, and returns an error message. I'm having a problem with my code though..

Here's the code:

<?php
$conn = "localhost";
$user = "root";
$pass = "mypass";
$dbname = "metaldetect01";
$tbl = "users0001";
$con = mysql_connect($conn,$user,$pass);
if (!$con)
  {
  die('Could not connect to database: "' . $dbname . '" because ' . mysql_error());
  }

mysql_select_db($dbname, $con);

$sql1 = "grant select, insert, update, delete, index, alter, create, drop on
$dbname.* to $user identified by '$pass'";

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

if(mysql_query(recordExists('firstname','firstname',$tbl,$dbname), $con)){
echo "user: ".$firstname.",".$lastname." already exists!";
}else{

$sql="INSERT INTO $tbl (firstName, lastName, email)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[email]')";

}

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

function recordExists($id,$idval,$table,$db) {//check for id=idval in table and return TRUE or FALSE
   $result = mysql_query("SELECT * FROM ".$table." WHERE ".$id."='".$idval."'", $db) or die(mysql_error());
   if($row = mysql_fetch_array($result)) {//if we did return a record
      return 1;
   }//end if row
   return 0;
}//end fuction recordExists

echo "You were successfully added to the database, $firstname, $lastname!";
echo "Returning you to the previous page...";

echo "<script>document.location=\"./ThankYou.html\";</script>";

mysql_close($con)
?>

ANY help is GREATLY appreciated!

~DS~

mikesta707 · September 3, 2009

a bunch of things. firstly this query is invalid


$sql="INSERT INTO $tbl (firstName, lastName, email)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[email]')";

}

should be

$sql="INSERT INTO $tbl (firstName, lastName, email)
VALUES
('".$_POST['firstname']."','".$_POST['lastname']."','".$_POST['email']."')";

}

secondly, this

if(mysql_query(recordExists('firstname','firstname',$tbl,$dbname), $con)){

makes no sense. the recordExists function already does a mysql query, and will return true or false. You want to just do

if(recordExists('firstname','firstname',$tbl,$dbname)){

oh and instead of returning 0 or 1 in your function, return true of false

DarkShadowWing · September 3, 2009

Now I'm getting "mysql_query(): supplied argument is not a valid MySQL-Link resource on line 40.....

DarkShadowWing · September 3, 2009

<?php
$conn = "localhost";
$user = "root";
$pass = "mypass";
$dbname = "metaldetect01";
$tbl = "users0001";
$con = mysql_connect($conn,$user,$pass);
if (!$con)
  {
  die('Could not connect to database: "' . $dbname . '" because ' . mysql_error());
  }

mysql_select_db($dbname, $con);

$sql1 = "grant select, insert, update, delete, index, alter, create, drop on
$dbname.* to $user identified by '$pass'";

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

if(recordExists('firstname','lastname','users0001','metaldetect01') or die(mysql_error())){
echo "user: ".$firstname.",".$lastname." already exists!";
}else{

$sql="INSERT INTO $tbl (firstName, lastName, email)
VALUES
('".$_POST['firstname']."','".$_POST['lastname']."','".$_POST['email']."')";

}

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

function recordExists($id,$idval,$table,$db) {//check for id=idval in table and return TRUE or FALSE
   $result = mysql_query("SELECT * FROM ".$table." WHERE ".$id."='".$idval."'", $db) or die(mysql_error());
   if($row = mysql_fetch_array($result)) {//if we did return a record
      return true;
   }//end if row
   return false;
}//end fuction recordExists

echo "You were successfully added to the database, $firstname, $lastname!";
echo "Returning you to the previous page...";

echo "<script>document.location=\"./ThankYou.html\";</script>";

mysql_close($con)
?>

mikesta707 · September 3, 2009

im not going to count the lines... which line is line 40?

I'm going to take a guess and say its this one?

if (!mysql_query($sql,$con))

Since you only create the query if the if statement here

if(recordExists('firstname','lastname','users0001','metaldetect01') or die(mysql_error())){

runs false, at least half the time this script runs you $sql string isn't being create. you are going to have to check if the the $sql exists, then try to run the query, or just have the sql created everytime. Or you can just put that check inside the else statement, ala

else{

$sql="INSERT INTO $tbl (firstName, lastName, email)
VALUES
('".$_POST['firstname']."','".$_POST['lastname']."','".$_POST['email']."')";

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

}

but you are going to have to change it because that logic currently is all wrong

also

if(recordExists('firstname','lastname','users0001','metaldetect01') or die(mysql_error())){

is wrong. you can't, and shouldn't stick an or die() clause an if statement like that (well you can, but you shouldn't. You should put it where the mysql query is taking place, which is inside the function)

oh and in your function

function recordExists($id,$idval,$table,$db) {//check for id=idval in table and return TRUE or FALSE
   $result = mysql_query("SELECT * FROM ".$table." WHERE ".$id."='".$idval."'", $db) or die(mysql_error());
   if($row = mysql_fetch_array($result)) {//if we did return a record
      return true;
   }//end if row
   return false;
}

you may want to use mysql_affected_rows instead of fetching an array. You may run into unexpected problems

if(mysql_affected_rows($result) > 0) {//if we did return a record
      return true;

DarkShadowWing · September 3, 2009

Now its line 41.. -.-

Line 41 is either this:

   $result = mysql_query("SELECT * FROM ".$table." WHERE ".$id."='".$idval."'", $db) or die(mysql_error());

inside the function called recordExists().

OR:

this:

if(recordExists('firstname','firstname','users0001','metaldetect01')){

mikesta707 · September 3, 2009

The MYSQL link resource is the second, optional parameter when you do a mysql query.

IE

$go = mysql_query($query, $link); //the $link variable

in your function it would be the last parameter you pass in. Mysql is looking for a link resource (that would be the $con variable that is used a lot of your specific script) but you supply it with a string. Either pass the $con variable, or remove the parameter, and don't pass the $con variable at all (You don't actually need to, since the query will take the current link resource and use that if none is provided.

DarkShadowWing · September 3, 2009

Ok, that did it. This works.. BUT. the only prob i'm having now is that it still can't tell if theres more than 1 of an entry..

Here is the code for my website:

formrequest.html:

<script>
function submitonce(theform){
//if IE 4+ or NS 6+
if (document.all||document.getElementById){
//screen thru every element in the form, and hunt down "submit" and "reset"
for (i=0;i<theform.length;i++){
var tempobj=theform.elements[i]
if(tempobj.type.toLowerCase()=="submit"||tempobj.type.toLowerCase()=="reset")
//disable em
tempobj.disabled=true
}
}
}
</script>
<Center><H1>Metal Detecting - Contact Us</H1></Center><br>
Contact Us: <br><br>
*Please note you can submit the form ONLY once. Any double form submissions will be deleted.
<form name="test" method="post" onsubmit="submitonce(this);" action="send.php">
  Firstname: <input name="firstname" type="text" /><br />
  Lastname: <input name="lastname" type="text" /><br />
  Email: <input name="email" type="text" /><br />
  Message: <br />
  <textarea name="message" rows="15" cols="40">
  </textarea><br />
  <input name="t1" id="t1" value="Submit" type="submit" /> <input type="reset" name="rset" value="Reset" />
</form>

And here's the php code:

send.php:

<?php
$conn = "localhost";
$user = "myuser";
$pass = "mypass";
$dbname = "metaldetect01";
$tbl = "users0001";
$con = mysql_connect($conn,$user,$pass);
if (!$con)
  {
  die('Could not connect to database: "' . $dbname . '" because ' . mysql_error());
  }

mysql_select_db($dbname, $con);

$sql1 = "grant select, insert, update, delete, index, alter, create, drop on
$dbname.* to $user identified by '$pass'";

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

if(recordExists('firstname','firstname','users0001','metaldetect01')){
echo "user: ".$firstname." already exists!";

}else{

$sql="INSERT INTO $tbl (firstName, lastName, email)
VALUES
('".$_POST['firstname']."','".$_POST['lastname']."','".$_POST['email']."')";

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

}

function recordExists($id,$idval,$table,$db) {//check for id=idval in table and return TRUE or FALSE
   $result = mysql_query("SELECT * FROM ".$table." WHERE ".$id."='".$idval."'") or die(mysql_error());
if(mysql_affected_rows($result) > 0) {//if we did return a record
      return true;
   }//end if row
   return false;
}//end fuction recordExists

echo "You were successfully added to the database, $firstname, $lastname!";
echo "Returning you to the previous page...";

echo "<script>document.location=\"./ThankYou.html\";</script>";

mysql_close($con)
?>

change the $user and $pass variables above to ur mysql username and password!

use the below code FIRST to CREATE the database.

Here's a code to auto-create the database, just change $user to ur mysql username, and $pass to ur mysql password:

dbcreate.php:

<?php
$conn = "localhost";
$user = "myuser";
$pass = "mypass";
$dbname = "metaldetect01";
$tbl = "users0001";
$con = mysql_connect($conn,$user,$pass);
if (!$con)
  {
  die('Could not connect to database "'.$dbname. '" because ' . mysql_error());
  }

// Drop database
mysql_query("DROP DATABASE IF EXISTS $dbname",$con);

// Create database
if (mysql_query("CREATE DATABASE $dbname",$con))
  {
  echo "Database \"".$dbname."\" created";
  }
else
  {
  echo "Error creating database: \"".$dbname."\"\nError msg: " . mysql_error();
  }

// Create table
mysql_select_db($dbname, $con);
$sql = "CREATE TABLE $tbl
(
id int(11) NOT NULL auto_increment, 
Firstname varchar(32) NOT NULL default '', 
Lastname varchar(32) NOT NULL default '', 
email varchar(50) NOT NULL default '', 
PRIMARY KEY (id)
)TYPE=MyISAM;";

// Execute query
mysql_query($sql,$con);

mysql_close($con);
?>

and finally, here's a code to delete the whole database when ur done w/ it:

deletedb.php:

<?php
$conn = "localhost";
$user = "myuser";
$pass = "mypass";
$dbname = "image_metaldetect01";
$tbl = "users0001";
$con = mysql_connect($conn,$user,$pass);
if (!$con)
  {
  die('Could not connect to database "'.$dbname. '" because ' . mysql_error());
  }

// Drop database
$del=mysql_query("DROP DATABASE IF EXISTS $dbname");
if(!$del){
die("Database still exists!");
}else{
die("Database \"".$dbname."\" has been deleted successfully!");
}
?>

As usual, change $user to ur mysql username, and $pass to ur sql password!

Use AFTER testing the database code.

Thanks again!

~DS~

mikesta707 · September 3, 2009

Sorry I don't have MySQL set up on my localhost, im at my job and they use Oracle here. But what part of the code are you trying to see where there are multiple entries?

DarkShadowWing · September 3, 2009

This part:

if(recordExists('firstname','firstname','users0001','metaldetect01')){
echo "user: ".$firstname." already exists!";

}else{

$sql="INSERT INTO $tbl (firstName, lastName, email)
VALUES
('".$_POST['firstname']."','".$_POST['lastname']."','".$_POST['email']."')";

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

}

mikesta707 · September 3, 2009

in which part of that part. in the if part, or the else part? just make a function to return how many entries there are

DarkShadowWing · September 3, 2009

The if part. for some reason it doesn't make this line work:

if(recordExists('firstname','firstname','users0001','metaldetect01')){
echo "user: ".$firstname." already exists!";
}else{
//Code here
}

DarkShadowWing · September 3, 2009

i want it to catch multiple first names, last names, and emails

mikesta707 · September 3, 2009

wait you want your recordExists function to return multiple firstnames and stuff?

DarkShadowWing · September 3, 2009

No. But SORTA like that.

I want it to return IF it finds more than 1 firstname, AND OR lastname AND OR email in the database, and if it finds it, stop it from sending the form data in.

so basically return an error if it finds the SAME firstname, lastname, AND OR email ALREADY IN the database.

~DS~

mikesta707 · September 3, 2009

hmmm. OK so let me see if I have this straight. You want this function to send allow the data sending if there is only 1 firstnam, lastname or email in the database. if there is more or zero you don't want it to send? Thats easy enough, just a slight change in the function

function recordExists($id,$idval,$table,$db) {//check for id=idval in table and return TRUE or FALSE
   $result = mysql_query("SELECT * FROM ".$table." WHERE ".$id."='".$idval."'") or die(mysql_error());
if(mysql_num_rows($result) == 1) {//if we found exactly 1 record
      return true;
   }//end if row
   return false;
}

If you want it to return an array of the data back though, that will be a little harder. There are two options, you can, instead of sending boolean values back, send back a string that you test, or, which I think is the better way, pass an array by reference.

the by reference way

note you must pass a variable into the last parameter.
function recordExists($id,$idval,$table,$array) {//check for id=idval in table and return TRUE or FALSE
   $array = null;//reset its value, and make it an array
    $array = array();
   $result = mysql_query("SELECT * FROM ".$table." WHERE ".$id."='".$idval."'") or die(mysql_error());
if(mysql_num_rows($result) > 0) {//if we do have more than 0 results, aka they exist on the table

    if (mysql_num_rows != 1){//if there is more than 1 row, we want to create an array
    while($row = mysql_fetch_assoc($result)){
     $array[] = $row;//this will push every row into the array,
    }
    }
      return true;
   }
else {
   return false;
}

Now when you were to call that function you would do it like

if(recordExists('firstname','firstname','users0001',$failedArray)){

and you could use $failedArray as if it was any other array. If that if statement were to run true you could use that array in the if statement, ala

if(recordExists('firstname','firstname','users0001',$arr)){
echo "user: ".$firstname." already exists!";
if ($arr != null){//it will only not be null if there were more than 1 entry
echo "The Array of the data: <br />";
print_r($arr);
}

}else{

$sql="INSERT INTO $tbl (firstName, lastName, email)
VALUES
('".$_POST['firstname']."','".$_POST['lastname']."','".$_POST['email']."')";

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

}

I didn't test this though, and there may be some syntactical/logical errors so let me know if it doesn't work

DarkShadowWing · September 3, 2009

testing now

DarkShadowWing · September 3, 2009

UGH! >< It STILL wont work..

It still lets u submit more than 1 of the same 1st name, lastname, and email! ><

here:

if(recordExists('firstname','firstname','users0001','metaldetect01') or recordExists('lastname','lastname','users0001','metaldetect01') or recordExists('email','email','users0001','metaldetect01')){
echo "user: ".$firstname.", ".$lastname.", email: ".$email." already exists!";

}else if(! recordExists('firstname','firstname','users0001','metaldetect01') or recordExists('lastname','lastname','users0001','metaldetect01') or recordExists('email','email','users0001','metaldetect01')){

$sql="INSERT INTO $tbl (firstName, lastName, email)
VALUES
('".$_POST['firstname']."','".$_POST['lastname']."','".$_POST['email']."')";

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

}

mikesta707 · September 3, 2009

first of all, when doing if statements, you signify an or clause by || not the world or

if(recordExists('firstname','firstname','users0001','metaldetect01') || recordExists('lastname','lastname','users0001','metaldetect01') || recordExists('email','email','users0001','metaldetect01')){

and again, remember, you can't pass that string into the last parameter of that function like that. it has to be a variables. Im suprised you aren't getting boatloads of parse errors

recordExists('firstname','firstname','users0001',$firstnameArray)

calls to the function have to look like that.

and your else if is kind of pointless, and is also incorrect. Again, you can't use the word or in if statements, and you haev to use the NOT boolean operator next to a function. just take out that entire clause and change it to just else.

DarkShadowWing · September 3, 2009

It's still doin' it.. Here's what shows in the database:

id  Firstname  Lastname  email  
1  test          user         blah@blah.blah 
2  test          user         blah@blah.blah 
3  test          user         blah@blah.blah 
4       
5       
6       
7

And here's the fixed code:

mysql_select_db($dbname, $con);

$sql1 = "grant select, insert, update, delete, index, alter, create, drop on
$dbname.* to $user identified by '$pass'";


if(recordExists('firstname','firstname','users0001','metaldetect01') || recordExists('lastname','lastname','users0001','metaldetect01') || recordExists('email','email','users0001','metaldetect01')){

echo "user: ".$firstname.", ".$lastname.", email: ".$email." already exists!";

}else{ 

$sql="INSERT INTO $tbl (firstName, lastName, email)
VALUES
('".$_POST['firstname']."','".$_POST['lastname']."','".$_POST['email']."')";

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

}

mikesta707 · September 3, 2009

post your recordExists() function. is it the same as the one I gave you? If you you can't use a string as the last parameter, as I have said.

recordExists('firstname','firstname','users0001','metaldetect01')

that /\/\

needs to look like

recordExists('firstname','firstname','users0001',$someVar)

DarkShadowWing · September 3, 2009

function recordExists($id,$idval,$table,$db) {//check for id=idval in table and return TRUE or FALSE
   $result = mysql_query("SELECT * FROM ".$table." WHERE ".$id."='".$idval."'") or die(mysql_error());
if(mysql_num_rows($result) == 1) {//if we found exactly 1 record
      return true;
   }//end if row
   return false;
}

mikesta707 · September 3, 2009

oh my bad dude.

function recordExists($id,$idval,$table,$db) {//check for id=idval in table and return TRUE or FALSE
   $result = mysql_query("SELECT * FROM ".$table." WHERE ".$id."='".$idval."'") or die(mysql_error());
if(mysql_num_rows($result) > 0) {//if we found more than 0
      return true;
   }//end if row
   return false;
}

change it back to that. I don't know why I wrote it that why...

DarkShadowWing · September 3, 2009

It's still doin' it.. >< Here, I'll let u try it for urself:

Simply type in ur firstname, make up a fake last name, and a fake email address:

then in the textarea, type:

blah

http://shadowice.no-ip.org/imageposeidon/backup/detect/detectrequest.html

DarkShadowWing · September 3, 2009

do the same names and email twice

Sign In

Need help with checking value in database..

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information