[PHP-DB] php-db-unsubscr...@lists.php.net
php-db-unsubscr...@lists.php.net -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Slooooow query in MySQL.
Seeing the query would help. Are you using sub-queries? I believe that those can make the time go up exponentially. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada College www.wnc.edu 775-445-3326 P.S. Please note that my e-mail and website address have changed from wncc.edu to wnc.edu. On Jul 19, 2007, at 2:19 PM, Rob Adams wrote: I have a query that I run using mysql that returns about 60,000 plus rows. It's been so large that I've just been testing it with a limit 0, 1 (ten thousand) on the query. That used to take about 10 minutes to run, including processing time in PHP which spits out xml from the query. I decided to chunk the query down into 1,000 row increments, and tried that. The script processed 10,000 rows in 23 seconds! I was amazed! But unfortunately it takes quite a bit longer than 6*23 to process the 60,000 rows that way (1,000 at a time). It takes almost 8 minutes. I can't figure out why it takes so long, or how to make it faster. The data for 60,000 rows is about 120mb, so I would prefer not to use a temporary table. Any other suggestions? This is probably more a db issue than a php issue, but I thought I'd try here first. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] First and Last ID of a table
I have a table where I need to figure out the very first ID and the very last ID, so here is what I wrote: $first_query = SELECT id FROM mytable ORDER BY id LIMIT 1; $first_result = mysql_query($first_query,$con); $first_id = mysql_result($first_result,0,'id'); $last_query = SELECT id FROM mytable ORDER BY id DESC LIMIT 1; $last_result = mysql_query($last_query,$con); $last_id = mysql_result($last_result,0,'id'); I'm just wondering if there was any way to do this more efficiently, like with one query instead of two. Or is this about as simple as I can do it? Thanks. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada College www.wnc.edu 775-445-3326
Re: [PHP-DB] error logging MySQL syntax errors?
put: echo mysql_error(); right after the sql query. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 On Apr 27, 2007, at 10:15 AM, Tim McGeary wrote: I am getting semi-ambiguous messages in the browser like: 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 '' at line 4 Is there a way in my PHP script to turn on a logging that is more specific about which MySQL statement in the page is the problem? line 4 certainly doesn't help. Tim -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
Unfortunately, I'm on 4.0.x so sub-queries are out. And yeah, I should get my host to upgrade but we both work for the government so that isn't happening. ;-) Any other thoughts. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 On Dec 21, 2006, at 9:30 AM, Naintara wrote: Depending on your MySQL version you could use a subquery by combining the two queries you mentioned, for a fairly straight-forward query. http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html http://mysqld.active-venture.com/Subqueries.html You could read about optimizing subqueries for optimum queries. -Original Message- From: Kevin Murphy [mailto:[EMAIL PROTECTED] Sent: Thursday, December 21, 2006 10:49 PM To: php-db@lists.php.net Subject: [PHP-DB] Order By [blank] I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Order By [blank]
I haven't tried the union method the query i have is actually quite a bit more complicated than just a simple select * from a single table, so while it may work, it might take a while to write it if I am reading all this right. But yes, the ifnull() method works just fine. Thanks for your help. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 On Dec 21, 2006, at 10:12 AM, [EMAIL PROTECTED] tg- [EMAIL PROTECTED] wrote: You shouldn't have to do that. the IFNULL() handles all that. If the item is null, it returns an emptry string ''. If it's blank/ empty, it returns an empty string. This is just used for the comparison = ''. This determines if it's empty or null and if so, returns 'ZZ', if not, it returns the unaltered value. And again, this returned value is only used for the sorting. The values you get from select * will be unaltered. Is this more efficient than doing two SELECTs and a UNION? I have no idea. But I like to keep things as clean as possible and in my reading and experience, letting the server handle an IFNULL() function should be quicker than doing four value checks (is null, is not null, = '' and != ''), collecting the values of two SELECTS then checking to see if it's able to UNION them together. Also, less code/typing typically means less chance of typos. BTW: In my example, realistically you could probably shorten the morphed value to ZZ or ZZZ unless you think you'll values in your database that will start with and get bumped lower on the sorting. -TG = = = Original message = = = In case the blank is a null or is really a blank: select * from blank where tchar_10 is not null and tchar_10 != '' union all select * from blank where tchar_10 is null or tchar_10 = '' - Dave On 12/21/06, [EMAIL PROTECTED] tg- [EMAIL PROTECTED] wrote: This is a little weird looking, but should do the job. Remember that items in your 'order by' can be manipulated conditionally. In this case, I'm looking for NULL as well as '' (empty) and changing it to something that should come after all your normal alphabetical values, but it doesn't change what appears in your results. This only affects the sorting: select * from sometable order by if(ifnull(somecolumn, '') = '', '', somecolumn) Hope that helps. -TG = = = Original message = = = I have this column in mysql: A F D [ empty ] A C If I do an order by on that column, this is what I get: [ empty ] A A C D F What I would like is this: A A C D F [ empty ] Is there any way to achieve this in a single MySQL query? Using DESC in this case doesn't work, because while it puts the empty row in the last place, it does the rest as well. I could also do 2 queries where it calls it once in order WHERE !='', and then do another query to get the empty ones, but that seems a bit cumbersome. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Special Character
A solution I use is to do apply this function to all POST data
collected. The key here for your problem is the chr(150) and chr(151)
that are replaced with a normal hyphen. This also takes care of MS
Words Smart Quotes. If there are other MS Characters you need to
convert, just add them to the pattern field as the numeric version,
and then add one to the array what the replacement is.
function sanitize($data)
{
$pattern = array(chr(145),chr(146),chr(147),chr(148),chr(150),chr
(151));
$replacements = array(',','','','-','--');
$data = str_replace($pattern,$replacements,$data);
$data = trim($data);
$data = preg_replace(/ +/, , $data);
$data = addslashes($data);
return $data;
}
--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada Community College
www.wncc.edu
775-445-3326
On Nov 16, 2006, at 7:32 AM, David Skyers wrote:
Thanks,
The problem is, we will have hundreds of users using Microsoft Word
and
we cannot switch it off for all of them. Ideally I need some type of
string replace function, so no matter what they enter it gets
trapped an
replaced.
It's not the normal hyphens that cause a problem but the long hyphens.
Regards
David
-Original Message-
From: Dan Shirah [mailto:[EMAIL PROTECTED]
Sent: 16 November 2006 15:13
To: php-db@lists.php.net
Subject: Re: [PHP-DB] Special Character
To turn off the auto formatting of hyphens:
In Microsoft Word 2003:
Open a new document.
Go to ToolsAuto Correct Options
Select the Auto Format As You Type tab Deselect the Hyphens (--)
with
Dash (-) option.
Even though it says it will replace a double hyphen (--) with a Dash
(This is an em dash) it also does the same thing for a single hyphen
depending on the sentance structure.
Hope this helps!
Dan
On 11/16/06, Niel Archer [EMAIL PROTECTED] wrote:
Hi David
What you describe sounds like Word is auto replacing hyphens with
either
en- or em-dashes. This is a configurable option in Word that often
defaults to on. Try using double quotes, If they get switched to
66's
and 99's style quotes, then that is likely the problem. I no longer
use MS Office for these and other reasons, so cannot tell you how to
switch off this formatting. But it can be switched off, somewhere
within it.
The only other option I can think of would be to change your Db
character set to one that can accept these extended characters. That
might also mean changing some of Window's/Word's behaviour (to be
using
UTF-8 for example).
Niel
--
PHP Database Mailing List (http://www.php.net/) To unsubscribe,
visit:
http://www.php.net/unsub.php
[PHP-DB] Distinct Partial Matches: RegExp
This might be really easy, but I'm just not sure how to write this query and my searching on google isn't finding me things, probably because I am searching for the wrong terms. I have a bunch of records where the area column is like: animal-dog-5 animal-dog-3 animal-cat-1 animal-cat-22 animal-bird-5 What I want to do is run a distinct query on just the part previous to the number. animal-dog animal-cat animal-bird So in other words, something like this, but I am not sure if this is the right way to go: $query = SELECT DISTINCT area FROM table WHERE REGEXP 'anynumberofletters dash anynumberofletters dash ' Of course, I could be barking up the wrong tree with the REGEXP thing. Anyone care to point me in the right direction? -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326
Re: [PHP-DB] Distinct Partial Matches: RegExp
Well, its not really a search that would be way easier. :-) What I'm looking for is a query that will give me the complete list of items that are distinct, minus the last number after the last hyphen. animal-dog animal-cat animal-bird -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 On Aug 30, 2006, at 4:34 PM, Micah Stevens wrote: Select DISTINCT area from table like '$searchterms%'; In SQL, you can use the 'LIKE' keyword along with the '%' and '_' wildcards.. '_' is one character, '%' is any number of chars. -Micah Kevin Murphy wrote: This might be really easy, but I'm just not sure how to write this query and my searching on google isn't finding me things, probably because I am searching for the wrong terms. I have a bunch of records where the area column is like: animal-dog-5 animal-dog-3 animal-cat-1 animal-cat-22 animal-bird-5 What I want to do is run a distinct query on just the part previous to the number. animal-dog animal-cat animal-bird So in other words, something like this, but I am not sure if this is the right way to go: $query = SELECT DISTINCT area FROM table WHERE REGEXP 'anynumberofletters dash anynumberofletters dash ' Of course, I could be barking up the wrong tree with the REGEXP thing. Anyone care to point me in the right direction? --Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 --Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326
[PHP-DB] In_Array in Query
I'm wondering if something like this is possible, where $array is an array. $query = select id from table where in_array(row,'$user_area'); Is it possible to see if the value of a particular row is in an array? I know I could create a loop where it would go through each one, but I was hoping not to do something like the following: $query = select id from table where row = $user_area[0] OR row = $user_area[1] OR row = $user_area[2] -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326
Re: [PHP-DB] Count Many Records
Thanks for your help, Chris. You certainly got me going in the right
direction. In case anyone is interested, below is the completed
solution, with design elements, turned into a function so I can
easily call it several times.
function counter_maker ($database,$name,$link)
{
echo tr;
echo td align=\left\ valign=\top\a href=\$link\ target=
\content\b$name/b/a/td;
$query = select status, count(id) AS count from $database where
status in ('', 'h', 'p') group by status;
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
$status_code = $row['status'];
if ($status_code == )
{ $status_code = n; }
$count_array[$status_code] = $row['count'];
}
echo td align=\center\ valign=\top\;
if (isset($count_array['n']))
{ echo style=\background-color: #F3CF45; color: #00599E; font-
weight: bold;\;
echo $count_array['n']; }
else
{ echo 0;}
echo /td;
echo td align=\center\ valign=\top\;
if (isset($count_array['h']))
{ echo style=\background-color: #00599E; color: #F3CF45; font-
weight: bold;\;
echo $count_array['h']; }
else
{ echo 0;}
echo /td;
echo td align=\center\ valign=\top\;
if (isset($count_array['p']))
{ echo $count_array['p']; }
else
{ echo 0; }
echo /td/tr;
}
counter_maker (data1,Form Name,link.php);
--
Kevin Murphy
Webmaster: Information and Marketing Services
Western Nevada Community College
www.wncc.edu
775-445-3326
On Jun 28, 2006, at 5:39 PM, Chris wrote:
Kevin Murphy wrote:
Actually the design is in the code below I need to display the
counts from all of those queries in a grid. Basically, its just a
summary of a bunch of information. Another way to look at it would
be a several lines that say something like this:
You have $data1_count NEW records in data1,
You have $data1p_count PROCESSED records in data1,
etc.
Instead of
$data1_query = select id from data1 WHERE status = '';
$data1_results = mysql_query($data1_query);
$data1_count = mysql_num_rows($data1_results);
$data1p_query = select id from data1 WHERE status = 'p';
$data1p_results = mysql_query($data1p_query);
$data1p_count = mysql_num_rows($data1p_results);
$data1h_query = select id from data1 WHERE status = 'h';
$data1h_results = mysql_query($data1h_query);
$data1h_count = mysql_num_rows($data1h_results);
You could do it all in one query:
$query = select status, count(id) AS count from data1 where status
in ('', 'p', 'h') group by status;
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
echo status: . $row['status'] . br/;
echo count: . $row['count'] . br/;
}
--
Postgresql php tutorials
http://www.designmagick.com/
[PHP-DB] Count Many Records
Thanks in advance for your help, and forgive me if this is me being bone-headed. :-) The following code works and accomplishes what I need it to do, but I am wondering if there is a better way to accomplish the same task. I have several tables of records and I need to count and then display. Even if I could just combine the first three queries (below) into one that would probably be a lot better than what I am doing now. $data1_query = select id from data1 WHERE status = ''; $data1_results = mysql_query($data1_query); $data1_count = mysql_num_rows($data1_results); $data1p_query = select id from data1 WHERE status = 'p'; $data1p_results = mysql_query($data1p_query); $data1p_count = mysql_num_rows($data1p_results); $data1h_query = select id from data1 WHERE status = 'h'; $data1h_results = mysql_query($data1h_query); $data1h_count = mysql_num_rows($data1h_results); echo tr; echo td align=\center\ valign=\top\$data1_count/td; echo td align=\center\ valign=\top\$data1h_count/td; echo td align=\center\ valign=\top\$data1p_count/td; echo /tr; Then do the same thing for the second table, and so on. $data2_query = select id from data2 WHERE status = ''; $data2_results = mysql_query($data2_query); $data2_count = mysql_num_rows($data2_results); $data2p_query = select id from data2 WHERE status = 'p'; $data2p_results = mysql_query($data2p_query); $data2p_count = mysql_num_rows($data2p_results); $data2h_query = select id from data2 WHERE status = 'h'; $data2h_results = mysql_query($data2h_query); $data2h_count = mysql_num_rows($data2h_results); echo tr; echo td align=\center\ valign=\top\$data2_count/td; echo td align=\center\ valign=\top\$data2h_count/td; echo td align=\center\ valign=\top\$data2p_count/td; echo /tr; -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326
Re: [PHP-DB] Count Many Records
Actually the design is in the code below I need to display the counts from all of those queries in a grid. Basically, its just a summary of a bunch of information. Another way to look at it would be a several lines that say something like this: You have $data1_count NEW records in data1, You have $data1p_count PROCESSED records in data1, etc. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada Community College www.wncc.edu 775-445-3326 On Jun 28, 2006, at 1:56 PM, [EMAIL PROTECTED] wrote: Kevin Murphy asks: The following code works and accomplishes what I need it to do, but I am wondering if there is a better way to accomplish the same task. I have several tables of records and I need to count and then display. Even if I could just combine the first three queries (below) into one that would probably be a lot better than what I am doing now. $data1_query = select id from data1 WHERE status = ''; $data1_results = mysql_query($data1_query); $data1_count = mysql_num_rows($data1_results); $data1p_query = select id from data1 WHERE status = 'p'; $data1p_results = mysql_query($data1p_query); $data1p_count = mysql_num_rows($data1p_results); $data1h_query = select id from data1 WHERE status = 'h'; $data1h_results = mysql_query($data1h_query); $data1h_count = mysql_num_rows($data1h_results); echo tr; echo td align=\center\ valign=\top\$data1_count/ td; echo td align=\center\ valign=\top\ $data1h_count/td; echo td align=\center\ valign=\top\ $data1p_count/td; echo /tr; Then do the same thing for the second table, and so on. $data2_query = select id from data2 WHERE status = ''; $data2_results = mysql_query($data2_query); $data2_count = mysql_num_rows($data2_results); $data2p_query = select id from data2 WHERE status = 'p'; $data2p_results = mysql_query($data2p_query); $data2p_count = mysql_num_rows($data2p_results); $data2h_query = select id from data2 WHERE status = 'h'; $data2h_results = mysql_query($data2h_query); $data2h_count = mysql_num_rows($data2h_results); echo tr; echo td align=\center\ valign=\top\$data2_count/ td; echo td align=\center\ valign=\top\ $data2h_count/td; echo td align=\center\ valign=\top\ $data2p_count/td; echo /tr; Kevin, You haven't said what you want the page to look like, but you could have a html select box of letters (a,b,c,d...) that would allow for the selection of multiple items, then grab the values out of the array created and add them to the select query, and loop through it: $data1_query = select id from data1 WHERE status = '$value_from_letter_array'; Then process as you have done. Or you could use a checkbox array... Does that help any? David
