My php script only reads the first row from mysql and doesn't check the rest of the rows for matches

Question

I'm trying to write a script for users to register to a club, and it does all the validation stuff properly and works great until it gets to the part where its supposed to check for duplicates. I'm not sure what is going wrong. HELP PLEASE!!! Thank you in Advance,

  <?php 
  mysql_connect ("sqlhost", "username", "password") or die(mysql_error());

  mysql_select_db ("databasename") or die(mysql_error());

  $errormsgdb = "";
  $errordb = "Sorry but that ";
  $error1db = "Name";
  $error2db = "email";
  $error3db = "mobile number";
  $errordbe = " is already registered";
  $pass1db = "No Matching Name";
  $pass2db = "No Matching Email";
  $pass3db = "No Matching Mobile";
  $errorcount = 0;
  $qResult = mysql_query ("SELECT * FROM table");
  $nRows = mysql_num_rows($qResult);
  for ($i=1; $i< $nRows+1; $i++){

$result = mysql_query("SELECT id,fname,lname,dob,email,mobile,agree,code,joindate FROM table WHERE fname = '$ffname");
if ($result > 0) {
$errorcount = $errorcount++;
$passdb = 0;
$errormsgdb = $error1db;
echo "<div class=\"box red\">$errordb $errormsgdb
} else {
$pass = 1;
$errormsgdb = $pass1db; 
echo "<div class=\"box green\">$errormsgdb</div><br />";
}



  //--------------- Check if DB checks returned errors ------------------------------------>
        if($errorcount <= 0){
        $dobp = $_REQUEST['day'].'/'.$_REQUEST['month'].'/'.$_REQUEST['year'];
        $dob = $_REQUEST['year'].$_REQUEST['month'].$_REQUEST['day'];
        //header('Location: thankyou.php?ffname='.$ffname.'&flname='.$flname.'&dob='.$dob.'&femail='.$femail.'&fmobile='.$fmobile.'&agree='.$agree.'&code='.$code.'&dobp='.$dobp);
        echo "<div class='box green'>Form completed! Error Count = $errorcount</div>";
        } else {
        echo "<div class='box red'>There was an Error! Error Count = $errorcount</div>";
            }
  }
     ?>

It only reads the first row and then echos out the error boxes, so if you try to put in a name and email etc that isn't identical to the first row it let's it pass (which would add duplicate records in the db) — RobertH
– RobertH, Commented Mar 18, 2012 at 21:55

RobertH · Accepted Answer · 2012-03-19 13:01:12Z

Thank you for all your help ManseUK and Toad your answers where invaluble especially ManseUK. The solution I ended up with is bellow, if anyone has a solution that would be prettier and/or more efficient that would be great as well.

<?php
mysql_connect ("mysqlhost", "username", "password") or die(mysql_error());

mysql_select_db ("database") or die(mysql_error());

$errormsgdb = "";
$errordb = "Sorry but that ";
$error1db = "Name";
$error2db = "email";
$error3db = "mobile number";
$errordbe = " is already registered";
$pass1db = "No Matching Name";
$pass2db = "No Matching Email";
$pass3db = "No Matching Mobile";

// Formulate Name Query
$queryname = sprintf("SELECT * FROM table 
WHERE fname='%s' AND mname='%s' AND lname='%s'", 
mysql_real_escape_string($ffname),
mysql_real_escape_string($fmname), 
mysql_real_escape_string($flname));

// Perform Name Query
$resultname = mysql_query($queryname);

// Check result
if (!$resultname) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $queryname;
    die($message);
}

// Use result
while ($row = mysql_fetch_assoc($resultname)) {
    $dbfullname = strtoupper($row['fname'].$row['mname'].$row['lname']);
    $fullname = strtoupper($ffname.$fmname.$flname);
    if ($dbfullname == $fullname){
        $passdb = 0;
        $errormsgdb = $error1db;
        echo "<div class=\"box red\">$errordb $errormsgdb $errordbe</div><br />";
    } else {
        $pass = 1;
        $errormsgdb = $pass1db; 
        echo "<div class=\"box green\">$errormsgdb</div><br />";
    } 
}

// Formulate Email Query
$queryemail = sprintf("SELECT * FROM table 
WHERE email='%s'",  
mysql_real_escape_string($femail));

// Perform Email Query
$resultemail = mysql_query($queryemail);

// Check result
if (!$resultemail) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $queryemail;
    die($message);
}

// Use result
while ($row = mysql_fetch_assoc($resultemail)) {
    $cemail = strtoupper($femail);
    $dbemail = strtoupper($row['email']);
    if ($cemail != $dbemail) {
        $passdb = 1;
        $errormsgdb = $pass2db ;
        echo "<div class=\"box green\">$errormsgdb</div><br />";
    } else {
        $passdb = 0;
        $errormsgdb = $error2db;
        echo "<div class=\"box red\">$errordb $errormsgdb $errordbe</div>";
    } 
}

// Formulate Mobile Query
$querymobile = sprintf("SELECT * FROM table 
WHERE mobile='%s'",  
mysql_real_escape_string($fmobile));

// Perform Mobile Query
$resultmobile = mysql_query($querymobile);

// Check result
if (!$resultmobile) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $queryemail;
    die($message);
}

// Use result
while ($row = mysql_fetch_assoc($resultmobile)) {
$cmobile = ereg_replace("[^0-9]", "", $fmobile );
    if ($cmobile != $row['mobile']) {
        $passdb = 1;
        $errormsgdb = $pass3;
        echo "<div class=\"box green\">$errormsgdb</div><br />";
    } else {
        $passdb = 0;
        $errormsgdb = $error3db;
        echo "<div class=\"box red\">$errordb $errormsgdb $errordbe</div>";
    } 
}
?>

Thank you again :)

You could do the 3 queries all in 1 query. In the WHERE clause use the OR operator to combine the 3 queries. So: WHERE (fname='%s' AND mname='%s' AND lname='%s') OR email='%s' OR mobile='%s'. Then cycle through the responses to see which of the clauses applies.

Manse · Accepted Answer · 2012-03-18 21:47:55Z

1

There are quite a few things that don't make sense in your script, the multiple queries for one ! why not just query for the data and see if you get a match - instead of looping each user and checking it ?? ... but I think your main problem is

$errorcount = " ";

then you do

$errorcount = $errorcount++;

this will NOT work ... you created $errorcount as a string so incrementing it won't work. You should initialise it as a number :

$errorcount = 0;

then to increment :

$errorcount++;

Example of it not working and then working here

answered Mar 18, 2012 at 21:47

Manse

38.1k11 gold badges88 silver badges112 bronze badges

9 Comments

RobertH Over a year ago

Well originally it was $errorcount = 0 but I've been testing the script for a couple days, sorry about that, didn't work either ways. How can I query the data in the way your talking about? Thanks

Manse Over a year ago

@RobertH run a query to check if the values you are looking for are present - so something like SELECT * FROM table where fname = $fname then if the number of results returned is greater than 0 you have a match. There are lots of string functions in mysql to help you concatenate and change case

RobertH Over a year ago

Can use a query to check for multiple variables at the same time like SELECT * FROM table WHERE fname = $fname && lname = $lname?

RobertH Over a year ago

Sorry, hit enter by accident, I types $result=mysql_query("SELECT * FROM table WHERE fname=ffname"); echo $result; if($result > 0) { echo "There is a match"; And it returned no match and $result =

RobertH Over a year ago

Sorry sorry it worked I forgot to put the $ffname variable between ' '. Thank you very much you really saved the day :)

|

Toad · Accepted Answer · 2012-03-18 21:49:45Z

1

you are using the database in a strange manner.

At first you are querying all data in the table.

And then in a loop, you are again querying all the rows in the table but this time row for row. This all is highly inefficient.

what you should do is query the database directly to ask for any duplicates

answered Mar 18, 2012 at 21:49

Toad

16k16 gold badges87 silver badges129 bronze badges

1 Comment

Toad Over a year ago

say you want to check if the name 'robert' is already in the database. You'd query the database like so: SELECT * FROM table WHERE fname = 'robert'. If you get results, then you have a duplicate.

Collectives™ on Stack Overflow

My php script only reads the first row from mysql and doesn't check the rest of the rows for matches

3 Answers 3

1 Comment

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related