We help IT Professionals succeed at work.

Moving through a table with gaps

steva
steva asked
on
354 Views
Last Modified: 2012-08-13
I'm wondering what the best way is to move through a  mySql table that has  primary index gaps.  For example, someone may have gone through the table and removed duplicate records, so now the primary index runs 1, 2, 4, 5, 6, 9. . .

I want to do something like below:

for($i=1; $i<=$maxId; $i++) {
	$query = "SELECT email FROM sales WHERE orderID = $i";
	$result = mysqli_query($db, $query) or die ("Couldn't read email from sales table at orderID = $i" . mysqli_error($db));
	$row = mysqli_fetch_assoc($result);
	$email = $row['email'];
             .
                etc. 
	

Open in new window


But I don't want to fall into the "die" hole every time a record is missing.

Thanks for any ideas...

Steve
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hmmmm.  Thanks a lot for that input.  I chose the i++ method because I come from a C++ background and I don't have a feel yet for everything MySQL brings to the table.  

Maybe I should explain a bit more about what I'm trying to do.  The purpose of the script is to  remove duplicate records from the table, based on having an email field that has already appeared.   The loop I showed first gets the email for the "next" record and  then a sub-loop was going to roll  through the remaining records looking for emails that match that one. All the duplicate records would be DELETED from the table as  they're found. While moving down  through the table, though, both in the primary and secondary loop, if I use this $i++ method I will hit records that aren't there so I wanted to know how to continue around them, which you've shown me with the continue option.  

But maybe there's a better way to do this whole thing.  It seems that people must have written scripts to go through tables and remove duplicates about a billion times already.  I don't want  to reinvent any wheels here if there are already best methods around.

Since you already answered my question, though, I'll give you the points now.

Thanks again for your input.

Author

Commented:
By the way, the continue in

for($i=1; $i<=$maxId; $i++) {
	$query = "SELECT email FROM sales WHERE orderID = $i";
	$result = mysqli_query($db, $query) or continue;
	$row = mysqli_fetch_assoc($result);
	$email = $row['email'];

Open in new window


doesn't work.  The continue line throws the PHP error:
Parse error: syntax error, unexpected T_CONTINUE in FindDups.php
K..

well, you might have to experiment/change things up a little.  "or/||" in PHP (as well as other languages has a pretty useful side effect and is often used to short hand "if/then/else"  (so does "and/&&", for that matter).  it's called "short circuit".

since false && true = false, when PHP encounters the first "false" result, it will stop and simply return false, because there is no point in evaluating the second expression, since regardless of the outcome, the overall result is STILL going to be false

like wise, since true || false = true, and PHP encounters the first true, it will stop and simply return true, because regardless of the outcome of evaluating the second expression, the overall result will still be false

when you see "expression or die", the way the original coder intended for the script to work is, if the first expression comes up "true", everything is fine and the script will continue at the next line.  if, for some reason it fails, it will evaluate the second, "die" and return the error.  (hope this is making sense)

the reason i chose "continue" is because i know the intended purpose of continue is to restart the loop with the next iteration, skipping everything else below it.  i didn't test the code, so i may have messed up the syntax.  testing it now, i see that it doesn't work.

what you can do is:

$result = mysqli_query($db, $query);
if (!$result) {
    continue;
}

Open in new window


There are a lot of different camps out there.  Some like to try to fit as much code and functionality on one line because they think it's easier to read.  Some like to be a little more verbose about things and take up 10 lines when 1 will do.  The more advanced users will probably do the former on the expectation that "if you really know what you're doing, this will make perfect sense" or "if you're serious about learning, you'll make a best effort in figuring out what is really going on here".

As to your other question, "whats the best way to take care of the duplicate entries".. that's a tough one.  I'd have to know more about what you're trying to achieve, how did they get there in the first place, etc.  There are a number of different ways of going about that.  Of course, you have to be careful that you don't delete your "last email address".  I'm trying to think of a SQL query that might do that in one fell swoop, but I'm at a loss.

One suggestion I might offer is using a tool to visualize your data.  MySQL has a free GUI that might help with this: MySQL Workbench.  Before I start writing SQL in code, and I don't have a clear idea about how I'm going to do it, I always tinker with it in here first.

Author

Commented:
Thanks for the explanation.  It made perfect sense.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.