Re: [PHP-DB] Code optimization
kranthi wrote: you can avoid using mysql_connect and mysql_close for every query (they are the most costliest functions in your application) You're assuming that. It could be the queries being run are the costliest. you should not use mysql_result for this application http://in2.php.net/function.mysql-result try using mysql_fetch_assoc instead. It works, it gives the right results - it's perfectly fine. finally, use a good profiler, like xdebug to find the bottlenecks in your application. That is a must, xdebug is very very useful. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Code optimization
you can avoid using mysql_connect and mysql_close for every query (they are the most costliest functions in your application) you should not use mysql_result for this application http://in2.php.net/function.mysql-result try using mysql_fetch_assoc instead. finally, use a good profiler, like xdebug to find the bottlenecks in your application. Kranthi. On Fri, Aug 7, 2009 at 06:00, Chris wrote: > Ron Piggott wrote: >> >> Is there a way to optimize this with better mySQL query? > > Step 1 - work out which bit is slow. > > $start_time = time(); > mysql_query () > > echo "That took " . (time() - $start_time) . " seconds\n"; > > > I'd guess the first one is slow because of the order by random() > > but that's just a guess. > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Code optimization
Ron Piggott wrote: Is there a way to optimize this with better mySQL query? Step 1 - work out which bit is slow. $start_time = time(); mysql_query () echo "That took " . (time() - $start_time) . " seconds\n"; I'd guess the first one is slow because of the order by random() but that's just a guess. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: "COUNT() returns 0 if there were no matching rows." .... really?!
Here's an example (snip) from a var_dump of that
$BuildPerUniqueDateArray:
(note that the 'aweber_7solar_aw' table does NOT have a record for
the
date '2009-07-28', so I would expect to see that "1" to be a "0"
there.)
If a table doesn't have a record for a given date, I wouldn't expect
to see "1"
or "0" -- I would expect not to see any row at all for that date/table
combination. You're not looping through all possible dates, you're
looping
through the result-set of your query.
Are you sure that the table in question doesn't have any 2009-07-28
records?
You could add the following column to each SELECT to help
troubleshoot:
GROUP_CONCAT() AS `all_timestamps_for_date`
This will give your result-set an additional column, which will
contain a
comma-separated list of all the records that GROUP BY is gathering
together in
each row (and therefore all the records that COUNT() is counting).
I'm wondering if some sort of timezone discrepancy is maybe causing
a timestamp
record to be attributed to 2009-07-28 unexpectedly.
Ben
Ben
I tried to implement your troubleshooting column like so:
$query = "SELECT GROUP_CONCAT(date(solarLandingDateTime)) AS
`all_timestamps_for_date` COUNT(*) AS `CountRowsThisDateThisTBL`,
date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS
`tableAlias` FROM t7solar_landing GROUP BY date(solarLandingDateTime)
UNION ALL SELECT GROUP_CONCAT(date(solarAweberConfDateTime)) AS
`all_timestamps_for_date` COUNT(*) AS `CountRowsThisDateThisTBL`,
date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm'
AS `tableAlias` FROM aweber_7solar_confirm GROUP BY
date(solarAweberConfDateTime) UNION ALL SELECT
GROUP_CONCAT(date(solarAWDateTime)) AS `all_timestamps_for_date`
COUNT(*) AS `CountRowsThisDateThisTBL`, date(solarAWDateTime) AS
`uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw
GROUP BY date(solarAWDateTime) ORDER BY uniqueDate DESC LIMIT 300";
it returns this error:
query failed: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'COUNT(*) AS `CountRowsThisDateThisTBL`,
date(solarLandingDateTime) AS `uniqueDat' at line 1
..So not to sit here helpless, I troubleshoot according to my current
level of skill this way:
I added this lower down on my page:
$tableDump = "SELECT solarAWDateTime FROM aweber_7solar_aw ORDER BY
solarAWDateTime DESC";
$tableDumpResult = mysql_query($tableDump) or die("query failed:
" .mysql_error());
echo "\n";
while ($row = mysql_fetch_assoc($tableDumpResult)) {
print_r($row);
}
echo "\n";
echo "\n";
it returns this:
Array
(
[solarAWDateTime] => 2009-08-06 13:33:57
)
Array
(
[solarAWDateTime] => 2009-08-06 09:41:54
)
Array
(
[solarAWDateTime] => 2009-08-06 06:06:55
)
Array
(
[solarAWDateTime] => 2009-08-05 16:19:27
)
Array
(
[solarAWDateTime] => 2009-08-05 16:19:25
)
Array
(
[solarAWDateTime] => 2009-08-05 16:19:02
)
Array
(
[solarAWDateTime] => 2009-08-05 06:55:58
)
Array
(
[solarAWDateTime] => 2009-08-04 06:46:42
)
...
Array
(
[solarAWDateTime] => 2009-07-30 06:48:56
)
Array
(
[solarAWDateTime] => 2009-07-29 16:11:20
)
I did not clip my paste here ^^^ the last entry is indeed '2009-07-29
16:11:20'.
..so you can see there is no record in this table with a timestamp on
the date '2009-07-28'.. so HOW in the world does my array get that
element, for that date, as if the while loop was iterating on a record
in this table with that date, when none exists?? (You still have my
OP on this?.. to see the code and var_dump for that array I build from
the iterating results of the original query?)
John Butler (Govinda)
govinda.webdnat...@gmail.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Code optimization
Is there a way to optimize this with better mySQL query?
# Select today's Bible verse
mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM verse_of_the_day_Bible_verses WHERE `assigned_date` =
'-00-00' AND seasonal_use = $verse_application ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
# Checking to see if each verse has been used once and
# if so reset the used column and select a fresh Bible verse
if ( $num == 0 ) {
mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="UPDATE `verse_of_the_day_Bible_verses` SET `assigned_date` =
'-00-00' WHERE `assigned_date` <> '-00-00' AND `seasonal_use` =
$verse_application";
$result=mysql_query($query);
mysql_close();
mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM verse_of_the_day_Bible_verses WHERE `assigned_date` =
'-00-00' AND seasonal_use = $verse_application ORDER BY RAND() LIMIT 1";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
}
$bible_verse_ref=mysql_result($result,0,"bible_verse_ref");
$bible_verse_text=mysql_result($result,0,"bible_verse_text");
$bible_verse_translation=mysql_result($result,0,"bible_verse_translation");
$bible_record=mysql_result($result,0,"record");
$bible_store_catalog_reference=mysql_result($result,0,"store_catalog_reference");
$bible_teaching_devotional_messages_reference=mysql_result($result,0,"store_teaching_devotional_messages_reference");
# Now make the web page show today's Bible verse
# and mark this Bible verse used so it won't be randomly selected from the
database until each verse has been used
mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="UPDATE `verse_of_the_day_Bible_verses` SET `assigned_date` =
'$todays_date' WHERE `record` LIKE '$bible_record'";
$result=mysql_query($query);
mysql_close();
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: "COUNT() returns 0 if there were no matching rows." .... really?!
> Here's an example (snip) from a var_dump of that $BuildPerUniqueDateArray: > (note that the 'aweber_7solar_aw' table does NOT have a record for the > date '2009-07-28', so I would expect to see that "1" to be a "0" there.) If a table doesn't have a record for a given date, I wouldn't expect to see "1" or "0" -- I would expect not to see any row at all for that date/table combination. You're not looping through all possible dates, you're looping through the result-set of your query. Are you sure that the table in question doesn't have any 2009-07-28 records? You could add the following column to each SELECT to help troubleshoot: GROUP_CONCAT() AS `all_timestamps_for_date` This will give your result-set an additional column, which will contain a comma-separated list of all the records that GROUP BY is gathering together in each row (and therefore all the records that COUNT() is counting). I'm wondering if some sort of timezone discrepancy is maybe causing a timestamp record to be attributed to 2009-07-28 unexpectedly. Ben -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] "COUNT() returns 0 if there were no matching rows." .... really?!
Hi all
I am 99.9% sure it is my lack of understanding something, but it sure
seems like my PHP/MySQL code is returning something other than what i
expect from reading the docs.
to explain:
this code is serving me (almost perfectly) well:
---
$query = "SELECT COUNT(*) AS `CountRowsThisDateThisTBL`,
date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS
`tableAlias` FROM t7solar_landing GROUP BY date(solarLandingDateTime)
UNION ALL SELECT count(*) AS `CountRowsThisDateThisTBL`,
date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm'
AS `tableAlias` FROM aweber_7solar_confirm GROUP BY
date(solarAweberConfDateTime) UNION ALL SELECT count(*) AS
`CountRowsThisDateThisTBL`, date(solarAWDateTime) AS `uniqueDate`,
'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw GROUP BY
date(solarAWDateTime) ORDER BY uniqueDate DESC LIMIT 300";
$foundUniqueDateROWS = mysql_query($query) or die("query failed:
" .mysql_error());
while ($uniqueDateROW = mysql_fetch_object($foundUniqueDateROWS)) {
$CountRowsThisDateThisTBL=0;
$uniqueDate=htmlentities($uniqueDateROW->uniqueDate);
$tableAlias=htmlentities($uniqueDateROW->tableAlias);
$CountRowsThisDateThisTBL=htmlentities($uniqueDateROW-
>CountRowsThisDateThisTBL);
$TBLsubarray["$uniqueDateROW->tableAlias"]=$CountRowsThisDateThisTBL;
$BuildPerUniqueDateArray[$uniqueDateROW->uniqueDate]=$TBLsubarray;
}
---
...but I am having the problem, that on iterations of the while loop
where there are NO records in one of those tables on a particular date
(when the *other* tables *do* have records for that date), then the
variable $CountRowsThisDateThisTBL seems to persist its previous value
(from what it was on the last iteration for the same table (previous
date, same table), as opposed to what I would expect (that it should
be set to "0" since count() should be returning "0" for this iteration.
What am I missing?
Here's an example (snip) from a var_dump of that
$BuildPerUniqueDateArray:
(note that the 'aweber_7solar_aw' table does NOT have a record for the
date '2009-07-28', so I would expect to see that "1" to be a "0" there.)
["2009-07-29"]=>
array(3) {
["aweber_7solar_aw"]=>
string(1) "1"
["t7solar_landing"]=>
string(1) "1"
["aweber_7solar_confirm"]=>
string(1) "1"
}
["2009-07-28"]=>
array(3) {
["aweber_7solar_aw"]=>
string(1) "1"
["t7solar_landing"]=>
string(1) "5"
["aweber_7solar_confirm"]=>
string(1) "2"
thanks,
-Govinda
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
