Icy Phoenix :: View topic - This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

AndyGpy [ Fri 28 Aug, 2009 22:28 ]
Post subject: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

Hi Guys,

I have a tpl file :-

Spoiler: [ Show ]

Code: [Hide] [Select]

<style type="text/css">

<!--

.style7 {color: #003300}

.style13 {

	color: #FF0000;

	font-weight: bold;

}

.style14 {color: #003300; font-weight: bold; }

.style15 {

	color: #993300;

	font-weight: bold;

}

.style16 {

	color: #000066;

	font-style: italic;

}

.style18 {color: #FF0000; font-weight: bold; font-size: 24px; }

.style22 {color: #003300; font-style: italic; }

.style23 {color: #666600}

.style31 {color: #003300; font-weight: bold; font-size: 16px; }

-->

</style>

  <p class="style7"><strong>Ticklist system is basic until we move to the new platform, it simple to use:-</strong></p>

  <p class="style16">eg You got a Picture today of a first for the year, simply find the species, click on the picture tick box and 2009 tick box for the specie and then click on the update button next to it.</p>

  <table width="200" border="0" align="center">

    <tr>

      <form action="tickindex.php" method="post">

        <td><span class="style16"><span class="style14"><span class="style31">Region</span></span></span></td>

        <td><span class="style16"><span class="style14">

          <select name="REGION_FILTER" size="1" id="REGION_FILTER" >

            <option value="AllRg">Select</option>

            <option value="{REGION_FILTER}">{REGION_FILTER}</option>

          </select>

        </span></span></td>

        <td><input type="submit" name="submit2" value="Search"  method="post" action="tickindex.php" /></td>

      </form>

    </tr>

  </table>

  <table width="800" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#99CC99">

    <tr>

      <th colspan="2" class="style14" scope="col"><div align="center">Total Species</div></th>

      <th colspan="2" rowspan="2" class="style14" scope="col"><div align="center"><span class="style18">{USERNAME}</span></div></th>

      <th class="style14" scope="col"><div align="center">Picture Count</div></th>

      <th scope="col"><div align="center"><span class="style14">Life Count</span></div></th>

      <th scope="col"><div align="center"><span class="style14">Year Count</span></div></th>

      <th width="80" rowspan="3" scope="col"><div align="center"></div></th>

    </tr>

    <tr>

      <th colspan="2" class="style14" scope="col"><span class="style7">{SPECTOT}</span></th>

      <th class="style14" scope="col"><span class="style7">{PICTTOT}</span></th>

      <th scope="col"><span class="style7">{LIFETOT}</span></th>

      <th scope="col"><span class="style7">{YEARTOT}</span></th>

    </tr>

    <tr>

      <th class="style14" scope="col">No.</th>

      <th class="style14" scope="col">Species</th>

      <th class="style14" scope="col">Scientific</th>

      <th class="style14" scope="col">Type</th>

      <th class="style14" width = "80" scope="col">Picture</th>

      <th width = "80" scope="col"><span class="style14">Life List</span></th>

      <th width = "80" scope="col"><span class="style14">2009 List</span></th>

    </tr>

    <!-- BEGIN life -->

    <tr>

      <th class="style7" scope="col">{life.LIFE_COUNT}</th>

      <th class="style15" scope="col">{life.LIFE_SPECIES}</th>

      <th scope="col"><span class="style22">{life.LIFE_LATIN} </span></th>

      <th scope="col"><span class="style23">{life.LIFE_TYPE} </span></th>

      <form method="POST" action="{S_UPDATE}">

      <th scope="col"><input name="picture:{life.UPDATE_TICKED}" type="checkbox" {life.LIFE_PICTURE}  /></th>

      <th scope="col"><input name="life:{life.UPDATE_TICKED}" type="checkbox" {life.LIFE_TICKED} /></th>

	  <th scope="col"><input name="year:{life.UPDATE_TICKED}" type="checkbox" {life.2009_TICKED} /></th>

	  <th scope="col"><span class="style7"><input name="{life.UPDATE_TICKED}" value="UPDATE" type="submit"></span></th> 

    </form>

    </tr>

    <!-- END life -->

  </table>

<table class="empty-table" width="100%" cellspacing="0" cellpadding="0" border="0">

  <p>

    <label></label>

  </p>

<tr>

		<td align="left"><span class="pagination">{PAGINATION}</span></td>

		<td align="right" valign="top" nowrap="nowrap">

  <form method="POST" action="{S_MODE_ACTION}">

				<span class="genmed">{L_SELECT_SORT_METHOD}:&nbsp;{S_MODE_SELECT}&nbsp;&nbsp;{L_ORDER}&nbsp;{S_ORDER_SELECT}&nbsp;&nbsp;<input type="submit" name="submit" value="{L_SUBMIT}" class="liteoption jumpbox" /></span>

  </form>

		</td>

	</tr>

	<tr>

		<td align="left"><span class="gensmall">{PAGE_NUMBER}</span></td>

		<td valign="top" align="right">{JUMPBOX}</td>

	</tr>

</table>

I have the following script

Spoiler: [ Show ]

Code: [Hide] [Select]

 <?php

/***************************************************************************

 *                                tickindex.php

 *                            -------------------

 *   begin                : Saturday, Aug 22, 2009

 *   copyright         : (C) 2009 Andrew Guppy

 *   email                : Andrew.Guppy@blueyonder.co.uk

 *

 ***************************************************************************/

define('IN_ICYPHOENIX', true); 

if (!defined('IP_ROOT_PATH')) define('IP_ROOT_PATH', './');

if (!defined('PHP_EXT')) define('PHP_EXT', substr(strrchr(__FILE__, '.'), 1)); 

include(IP_ROOT_PATH . 'common.' . PHP_EXT);

include(IP_ROOT_PATH . '/ticklists/ticklistconfig.'.PHP_EXT);

$region_filter = $_REQUEST["REGION_FILTER"];

$update = ( isset($_POST['update']) ) ? true : 0;

// standard session management

$userdata = session_pagestart($user_ip);

if ((!$userdata['session_logged_in']) ) 

   {

        redirect(append_sid(LOGIN_MG . '?redirect=tickindex.' . PHP_EXT));

   }

else

   {

		init_userprefs($userdata);

		// set page title

		$page_title = "Scotbird Ticklists - Members only area !! ";

		// standard page header

		include(IP_ROOT_PATH . 'includes/page_header.'.PHP_EXT);

		// Connect to the database 

		$start = ( isset($_GET['start']) ) ? intval($_GET['start']) : 0;

		$start = ($start < 0) ? 0 : $start;

		$db = new sql_db($ticklist_mysql_host,$ticklist_mysql_username,$ticklist_mysql_password,$ticklist_mysql_db,false);

    		if(!$db) 

			{

        		die("Database Connection Failed:- Please Contact Site Admin" . mysql_error());

    		}

			//Check if user has a ticklist

    	$result = mysql_query("SHOW TABLES LIKE '".$userdata[username] ."'");

			if( !mysql_num_rows($result) ) 

			{

  			// if not then create them

				include_once(IP_ROOT_PATH . '/ticklists/copylist.'.PHP_EXT);

			} 

			// assign template

		$template->set_filenames(array('body' => 'tickindex.tpl'));	

				$tbl = $userdata[username];

				$sql = "SELECT DISTINCT `type` FROM `" . $tbl ."`WHERE 1 ORDER BY `type` ";

		$result = $db->sql_query($sql);

		if (!($result = $db->sql_query($sql)))

			{

  			 	die("Database Query Failed" . mysql_error());

			} 

		// This is where you would add a new VARS Array if you intend to use your own custom VARS.

		while ($row = $db->sql_fetchrow($result))

			{

   				$region_filter_options .= '<option value="' . $row['type'] . '">' . $row['type'] . '</option>';

			}

		$template->assign_vars(array(

				'REGION_FILTER' => $region_filter_options,

			)

		);	

			$sql = "SELECT COUNT(*) AS num FROM `" . $tbl . "`";

			$result = $db->sql_query($sql);

			$row = $db->sql_fetchrow($result);

			$number = $row['num'];

			$sql = "SELECT COUNT(picturetick) AS num FROM `" . $tbl. "` WHERE picturetick ";

			$result = $db->sql_query($sql);

			$row = $db->sql_fetchrow($result);

			$pictnumber = $row['num'];

			$sql = "SELECT COUNT(lifetick) AS num FROM `" . $tbl. "` WHERE lifetick ";

			$result = $db->sql_query($sql);

			$row = $db->sql_fetchrow($result);

			$lifenumber = $row['num'];

			$sql = "SELECT COUNT(2009tick) AS num FROM `" . $tbl. "` WHERE 2009tick ";

			$result = $db->sql_query($sql);

			$row = $db->sql_fetchrow($result);

			$yearnumber = $row['num'];

				if($region_filter == 'AllRg')

				{

					$sql = "SELECT * FROM `" . $tbl."` LIMIT $start, ". 50;

				}

				else

				{

					$sql = "SELECT * FROM `" . $tbl."` WHERE `type` = '". $region_filter ."'	LIMIT $start, ". 50;

				}	

		   $result = $db->sql_query($sql);

			if (!($result = $db->sql_query($sql)))

			{

				die("Database Query Failed" . mysql_error());

			} 

		// This is where you would add a new VARS Array if you intend to use your own custom VARS.

		$i   = 1;

		while ( $row = $db->sql_fetchrow($result) )

		{

		$template->assign_block_vars('life', array( 'POS' => $i ,

			   		'LIFE_SPECIES' => $row['species'],

					'LIFE_LATIN' => $row['Latin_ID'],

					'LIFE_TYPE' => $row['type'],

					'LIFE_COUNT' =>  $start + $i,

					'LIFE_DATE' => $row['Date_Seen'],

					'LIFE_LOCATION' => $row['location'],

					'LIFE_PICTURE' =>($row['picturetick'])? 'checked="checked"' : '',

					'LIFE_TICKED' => ($row['lifetick'])? 'checked="checked"' : '',

					'2009_TICKED' => ($row['2009tick'])? 'checked="checked"' : '',

					'UPDATE_TICKED' => $row['REC_NO'],

							)

				);

						$i++;

			}

	$template->assign_vars(array(

		'USERNAME' => htmlspecialchars($userdata[username]),

		'S_UPDATE' => append_sid('Update_Ticklist.'.PHP_EXT.'?page='.$start ),

		'SPECTOT' => $number,

		'PICTTOT' => $pictnumber,

		'LIFETOT' => $lifenumber,

		'YEARTOT' => $yearnumber,

		)

		);

//An attempt at pagenating the results	

$sql = "SELECT count(*) AS total FROM `" . $tbl . "`";

if ( !($result = $db->sql_query($sql)) )

	{

			message_die(GENERAL_ERROR, 'Error geting total list', '', __LINE__, __FILE__, $sql);

	}

	if ( $total = $db->sql_fetchrow($result) )

		{

		$total_species = $total['total'];

			$pagination = generate_pagination('tickindex.' . PHP_EXT . '?mode=' . $mode . '&amp;order=' . $sort_order, $total_species , 50, $start) . '&nbsp;';

		}

	$db->sql_freeresult($result);

	$template->assign_vars(array(

	'PAGINATION' => $pagination,

	'PAGE_NUMBER' => sprintf($lang['Page_of'], ( floor( $start / 50 ) + 1 ), ceil( $total_species  / 50 )),

	'L_GOTO_PAGE' => $lang['Goto_page']

	)

);

		// Build the page

		$template->pparse('body');

		// standard page footer

		include(IP_ROOT_PATH . 'includes/page_tail.'.PHP_EXT);

   }

?>

Now there is a drop down box in the tpl file that gets populated with this code is the php script:-

$sql = "SELECT DISTINCT `type` FROM `" . $tbl ."`WHERE 1 ORDER BY `type` ";

$result = $db->sql_query($sql);

if (!($result = $db->sql_query($sql)))
{
die("Database Query Failed" . mysql_error());
}

while ($row = $db->sql_fetchrow($result))
{
$region_filter_options .= '<option value="' . $row['type'] . '">' . $row['type'] . '</option>';

}

$template->assign_vars(array(
'REGION_FILTER' => $region_filter_options,
)
);

For some reason it produces double entries for every entry, but it looks as if it has run through this routine twice, can someone tell me how to fix this ???

spydie [ Fri 28 Aug, 2009 22:39 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

are you telling me you´ve got the sql vars assigned twice ??

once in your script and once in dropdown ??

AndyGpy [ Fri 28 Aug, 2009 22:48 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

No it only gets assigned via the PHP script once

spydie [ Fri 28 Aug, 2009 23:04 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

question.

why do you connect to the database then your allready connected via config ??

or are you using an extra db for your ticklist???

AndyGpy [ Sat 29 Aug, 2009 00:03 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

Yes i am using a seperate DB for my ticklist, this way i can port it to new versions of IP easily.

regards

spydie [ Sat 29 Aug, 2009 00:07 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

was just a question , cause i thought that it might be the cause off double output

AndyGpy [ Sat 29 Aug, 2009 19:05 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

Anyone else care to help ?

Mighty Gorgon Help !!

DWho [ Sat 29 Aug, 2009 23:44 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

have you tried removing this part from your code

Code: [Hide] [Select]

$result = $db->sql_query($sql);

Lopalong [ Sun 30 Aug, 2009 07:42 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

Andy,

Is what you are saying is that you have a drop-down box and that is the only thing that is calling this script twice?

Spoiler: [ Show ]

Correct ?

If this MOD is GPL - Would you be kind enough to make available for me a zipped copy of the whole MOD - Because working like this has got "Feathers" on it. :shock:

AndyGpy [ Sun 30 Aug, 2009 11:42 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

DWho,

I have tried that - no Joy.

Lopalong,

It dont know how to put it in zip format, i can zip the files and send them to you but i cannot send the assosciated database.

Basically i have the tpl file in the first post and the php script in the first post. There is a small section in the PHP file that populates the drop down box.

If you log into my site as

Username -- test
Password -- test

browse to Ticklists ( Top navigation bar in home page ) you will see what i mean.

Click here for website

regards

Lopalong [ Sun 30 Aug, 2009 12:57 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

I don't need to log into your site because I understand what you are saying. And physically looking at the problem is not really going to help. ;)

All you need to do is dig out all of the files associated with it and .zip them up, and a copy of the original SQL that you added would be more than useful.

DWho [ Sun 30 Aug, 2009 14:06 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

lots of errors on that page at the moment

> Critical Error!
message_die() was called multiple times.
Error #1
Could not query Icy_jr_admin_users table

Line : 152
File : /var/www/Forum/includes/functions_jr_admin.php
SQL : SELECT * FROM Icy_jr_admin_users WHERE user_id = '47'
Error #2
Error
Could not query log information

Line : 101
File : /var/www/Forum/includes/functions_mg_log.php
SQL : SELECT MAX(log_id) max_log_id FROM Icy_logs

cant see whats going on

AndyGpy [ Sun 30 Aug, 2009 18:29 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

Hi Lopalong,

Have PM you what you requested.

DWho,

i only have those errors with the test account, will look into resolving once i get the duplication thing sorted. Thanks anyway.

regards

Lopalong [ Tue 01 Sep, 2009 08:05 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

After many tests and trying to work the Db thing out. :(

It's appears it's in the .tpl file. ;)

Code: [Hide] [Select]

<option value="{REGION_FILTER}">{REGION_FILTER}</option>

The same VAR twice.

Try this?

Code: [Hide] [Select]

AndyGpy [ Tue 01 Sep, 2009 20:35 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

Cheers Lopalong that fixed the duplicate entries.

With regards to DWho's spotting-

Critical Error!
message_die() was called multiple times.
Error #1
Could not query Icy_jr_admin_users table

Line : 152
File : /var/www/Forum/includes/functions_jr_admin.php
SQL : SELECT * FROM Icy_jr_admin_users WHERE user_id = '13'
Error #2
Error
Could not query log information

Line : 101
File : /var/www/Forum/includes/functions_mg_log.php
SQL : SELECT MAX(log_id) max_log_id FROM Icy_logs

It appears i am getting it for all users, moderators and admins are ok, it was as a result of missing a " ," when parsing variables.

Problems solved, Lopalong your a star and i look forward to your PM.

regards

Lopalong [ Tue 01 Sep, 2009 23:45 ]
Post subject: Re: This Is Driving Me Nuts !! - SQL Query Producing Double Results !!

AndyGpy wrote: [View Post]
Lopalong your a start and i look forward to your PM.

:mryellow:

Don't hold your breath Andy - My agenda in already bursting at the seams. But I will get around to it. :lol:

Icy Phoenix

Old Support Topics - This Is Driving Me Nuts !! - SQL Query Producing Double Results !!