Page 1 of 1

MySQL db record being processed twice

Posted: Tue Oct 25, 2016 8:56 am
by tcarp
Code worked at one time. Now acting like it's reading the same record twice.

Looked at the main code and it seems ok (just a do-while loop). Looking at the connect and query code, I think I may be using deprecated code, which might be contributing. (Am researching what the current coding approach is. I'm a php mysql novice).

The connection looks OK and I've checked the db to make sure it doesn't have dup records.

These are the queries: one for groups and one for maps. Does it look ok? Where should I be using current coding?

Code: Select all

<?php 
require_once('Connections/atlas.php');  // NOTE: Also may require change in href for detail

// New group code
mysql_select_db($database_atlas, $atlas);
$groups = mysql_query("SELECT * FROM tblMapGroups ORDER BY DispSeq ASC");  // Get the group records
$row_groups = mysql_fetch_assoc($groups);
	if (!$_GET)
		$selectGroup = $row_groups['MapGroupID'];  // First time through; set initial default group
	else $selectGroup = $_GET['groupselect'];  // Otherwise use GET to retrieve the submit button chosen
mysql_data_seek($groups, 0);  // Reset to first record for Form loop
// End new group code

// Select map records
$query_maps1 = "SELECT * FROM tblMaps";
$query_maps2 = "ORDER BY tblMaps.MapGroup, tblMaps.Area, tblMaps.Community, tblMaps.DispSeq ";
$query_maps = sprintf("%s WHERE MapGroup ='%s' %s", $query_maps1, $selectGroup, $query_maps2);
$maps = mysql_query($query_maps, $atlas) or die(mysql_error());
$row_maps = mysql_fetch_assoc($maps);

?>

Re: MySQL db record being processed twice

Posted: Tue Oct 25, 2016 5:27 pm
by Christopher
It looks ok, but you say it is in a do..while loop which you don't show. Not a good idea to put a query in a loop. Better to loop to build the WHERE condition and then do a single query.

Re: MySQL db record being processed twice

Posted: Sun Oct 30, 2016 8:59 am
by tcarp
Thanks for taking the time to post. It appears the issue was using MySQL vs. MySQLi.

The symptom I failed to mention was that it started all of a sudden without code changes. In retrospect, this could happen if the host updated PHP and db API.

Today I went back over all the db code, updated it to MySQLi, and the problem went away.

Don't understand MySQLi well enough to understand what might have caused the problem.

Re: MySQL db record being processed twice

Posted: Sun Oct 30, 2016 10:38 am
by Celauran
Could the host have upgraded their PHP version? mysql_ functions have been deprecated since forever, started throwing errors in 5.5, and were removed in 7.0. Aside from requiring the connection to be passed as a parameter, mysqli should mostly be interchangeable. Might be a good opportunity to look at modernizing this code and moving toward PDO or some query builder or ORM.

Re: MySQL db record being processed twice

Posted: Sun Oct 30, 2016 10:58 am
by tcarp
The host change is exactly what I suspect.

The code was written over 6 years ago so not surprising its showing its age.

I did convert all the code to MySQLi which "fixed" the problem with each db record being processed twice.

Pretty much a novice so migration to something more generalized like PDO will need to wait, although I'm interested in working with PostGris so may be driven to PDO when I do.

For now, the site can easily stay on MySQL. There is another site that uses PHP and a MySQL db that isn't working right now. It's more complicated than this site, which simply uses the db records to select images to display (map thumbs with links). The other site also takes updates to the db records.

I'll be completing a similar MySQLi update for that site, now that I've done it here.

I'm grateful that these forums are available and that people participate. They're invaluable for folk like me that need so learning direction from time to time.