PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Thu Jun 29, 2017 2:10 am

All times are UTC - 5 hours




Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 29 posts ]  Go to page Previous  1, 2
Author Message
 Post subject:
PostPosted: Thu Mar 11, 2004 12:36 am 
Offline
DevNet Evangelist
User avatar

Joined: Tue May 07, 2002 9:48 am
Posts: 8391
Location: Berlin, ger
also note this section in your php.ini
Quote:
; Trace mode. When trace_mode is active (=On), warnings for table/index scans and
; SQL-Erros will be displayed.
mysql.trace_mode = Off
a well hidden parameter for debugging mysql-driven sites. Maybe it's not mentioned in the documentation because it's subject to change, dunno.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 31, 2004 7:33 pm 
Offline
Forum Regular
User avatar

Joined: Tue Sep 23, 2003 11:07 pm
Posts: 769
Location: The Republic of Texas
And I agree on the off the wall variable names, like laffy and taffy or cheese and cracker, but never foo and bar.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 29, 2004 5:40 am 
Offline
Forum Newbie

Joined: Tue Jun 29, 2004 5:40 am
Posts: 2
Location: Pune, India
Agreed@PHPMyAdmin, but in case you are Prompt-lover.., you have commands like mysql and mysqladmin with all its set of options to this bunch of work other than just checkd,ing for the query.

This especially helps in performance tunning and validating by taking a snapshot of the work MySQL is executing.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 22, 2005 7:56 am 
Offline
Forum Contributor
User avatar

Joined: Thu Jan 08, 2004 9:28 am
Posts: 404
Location: Helsinki
I sometime had problems with different quotes. Fieldnames should be surrounded with ` s while values should have ' s around.


Top
 Profile  
 
PostPosted: Tue Apr 04, 2006 1:55 am 
Offline
Forum Contributor
User avatar

Joined: Wed Oct 19, 2005 5:14 am
Posts: 491
Location: Nepal
if we log the errors like php syntax errors or mysql syntax errors, it will be a great time saving.

The best way to see the errors at a real time is by either add a die(mysql_error()) or trace a error of mysql by using the following command :

# tail -f /var/log/mysql.err
# tail -f /var/log/mysql.log

The above method saved a lots of time for me!

Happy Debugging!!!

Dibyendra


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 06, 2007 1:19 pm 
Offline
Forum Regular
User avatar

Joined: Fri May 03, 2002 3:33 am
Posts: 512
Location: Edinburgh, Scotland
This is a tip for use when optimising your queries.

Obviously everyone uses 'explain' on their queries :wink: If not I suggest you do a bit of reading up.

It can be handy to see what the optimiser has done to your queries before it runs them.

As an example I have 2 tables

Syntax: [ Download ] [ Hide ]

test

+--------+-------------+------+-----+---------+-------+

| FIELD  | Type        | NULL | KEY | DEFAULT | Extra |

+--------+-------------+------+-----+---------+-------+

| field1 | tinyint(4)  | NO   |     |         |       |

| field2 | varchar(20) | NO   |     |         |       |

+--------+-------------+------+-----+---------+-------+

 


and

Syntax: [ Download ] [ Hide ]

test2

+--------+-------------+------+-----+---------+-------+

| FIELD  | Type        | NULL | KEY | DEFAULT | Extra |

+--------+-------------+------+-----+---------+-------+

| field1 | tinyint(4)  | NO   |     |         |       |

| fk1    | tinyint(4)  | NO   |     |         |       |

| field2 | varchar(20) | NO   |     |         |       |

+--------+-------------+------+-----+---------+-------+

 


I'll do a RIGHT JOIN to illustrate my point

Syntax: [ Download ] [ Hide ]

mysql> SELECT * FROM test2 RIGHT JOIN test ON (test2.fk1 = test.field1);

+--------+------+---------------+--------+--------+

| field1 | fk1  | field2        | field1 | field2 |

+--------+------+---------------+--------+--------+

|      1 |    1 | first linked  |      1 | first  |

|      2 |    1 | second linked |      1 | first  |

|      3 |    2 | first linked  |      2 | second |

|   NULL | NULL | NULL          |      3 | third  |

+--------+------+---------------+--------+--------+

 


so that gives me all records in test and those related records in test2.

RIGHT JOIN is not standard, behind the scenes the optimiser turns this into a LEFT JOIN.

How do I know?

run an explain plan with the 'extended' key word

Syntax: [ Download ] [ Hide ]

mysql> EXPLAIN extended SELECT * FROM test2 RIGHT JOIN test ON (test2.fk1 = test

.field1);

 


immediately after this outputs the explain plan run show warnings

Syntax: [ Download ] [ Hide ]

mysql> SHOW warnings;

 


This will show you exactly the query that was run

Syntax: [ Download ] [ Hide ]

SELECT `example`.`test2`.`field1` AS `field1`,`example`.`test2`

.`fk1` AS `fk1`,`example`.`test2`.`field2` AS `field2`,`example`.`test`.`field1`

 AS `field1`,`example`.`test`.`field2` AS `field2` FROM `example`.`test` LEFT jo

IN `example`.`test2` ON((`example`.`test2`.`fk1` = `example`.`test`.`field1`)) w

here 1

 


as you can see the optimiser turned this into a LEFT JOIN.

Enjoy.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 06, 2007 5:06 pm 
Offline
Forum Commoner

Joined: Sun Mar 04, 2007 6:35 am
Posts: 53
code_monkey wrote:
These days I always use this bit of code for generating ids rather than relying on numbers;

Syntax: [ Download ] [ Hide ]
<?php

$unique_id = md5(uniqid(rand(),1));

$token_result = mysql_query("SELECT id FROM table WHERE id='$unique_id'",$db);

while($num_rows = mysql_num_rows($token_result)){
                                               
        $unique_id = md5(uniqid(rand(),1));    
                                               
}


?>


Then you just use $unique_id when you insert the data into your database. You get a value of something like 'c6127375320d85153ba1b334ab18330a'.


By theory, not a great idea. It doesn't check for duplications.


Top
 Profile  
 
 Post subject: Re:
PostPosted: Tue Jun 10, 2008 8:46 am 
Offline
Forum Newbie
User avatar

Joined: Tue Jun 10, 2008 8:30 am
Posts: 15
Location: Washington, DC
Pyrite wrote:
And I agree on the off the wall variable names, like laffy and taffy or cheese and cracker, but never foo and bar.


Off the wall variables are good when doing inhouse development, but if you're not inhouse and you use crazy vars, you need to make sure you use GOOD documentation for code maintainability. Nobody wants to come in after somebody and try to figure out what the hell $skittles is.


Top
 Profile  
 
PostPosted: Tue Jun 10, 2008 10:33 am 
Offline
DevNet Master
User avatar

Joined: Sun Jan 21, 2007 12:06 am
Posts: 4135
Will anyone ever let this thread die in peace? o_O
~twigletmac posted in this one for Christ's sake. :P


Top
 Profile  
 
PostPosted: Fri Sep 02, 2011 5:56 am 
Offline
Forum Newbie

Joined: Thu Sep 01, 2011 11:05 pm
Posts: 2
Thnks for the tips. Really like it. ;) Some days ago Mr. Kyle Gush help me in sme problem of my mysql database. Kyle Gush of Atlanta GA is well known in php and mysql. My problem was similar to your tips.


Top
 Profile  
 
PostPosted: Wed Jan 11, 2012 8:59 pm 
Offline
Jack of Zircons
User avatar

Joined: Thu Nov 09, 2006 9:30 pm
Posts: 4484
Location: California, USA
This thread is a classic example of hijacking the topic. That's not a felony, but it would really serve everyone's needs better if those who want to expand on the original topic (and what is implied by the Subject line) would simply start a new thread. Many of the suggestions are very helpful, but beyond the scope of the original poster's topic. Those who want to answer a poster's question, or challenge what a poster stated are welcome to Reply to the topic. But if you want to bend the topic to a related (or unrelated) discussion, you are encouraged to start a new thread. I thought that markl999 was pretty clear in his opening post that he wasn't trying to offer a comprehensive list of good practices, but rather to suggest that by following 3 specific tips a large proportion of "problems" that forum users post about MySQL queries could be avoided. I think he's pretty much right on that.

It might be worthwhile for several of you to work up a comprehensive list of good practices and post it as a new thread, which you could request be made a 'sticky' thread, and as people propose modifications, it could be updated. All of the comments have merit, it's just that we end up with a wandering thread that will soon fade into oblivion.


Top
 Profile  
 
PostPosted: Sun Jul 28, 2013 5:28 pm 
Offline
Forum Commoner
User avatar

Joined: Thu Dec 15, 2011 2:40 pm
Posts: 85
Location: Nelson, NZ
Quote:
Debugging MySQL code .. a few tips.


To get this (ancient) mostly off-topic thread back on track, two tips I didn't find above:

1. Add a comment to the top of each query, for instance:
Syntax: [ Download ] [ Hide ]
$myquery =
"# from jamesbond.php | get spy names  query
SELECT name
FROM....
"
;


When you have a large app with many files and many more queries, you're grateful to see an error message telling you not just the SQL of the query, but exactly where that query lives—especially if you have a lot of dynamic queries.

2. In your error-catching procedure, reformat the error (which will show the query) by replacing line breaks (\n) with br tags, and by running it through htmlentities for those cases when the query outputs html (such as links). Nothing like looking at a query in the nice format of your source code, as opposed to everything on one line.


Top
 Profile  
 
PostPosted: Mon Feb 13, 2017 3:08 am 
Offline
Forum Newbie

Joined: Mon Feb 13, 2017 2:25 am
Posts: 7
Enable general_log and sudo tail the log file

Two ways to do this:

You can update the my.cnf and restart MySQL. Enable GL:
http://stackoverflow.com/questions/6479 ... -query-log

Easier way with this command:
mysql.server restart --general_log=1 --general_log_file=/usr/local/var/mysql/filename.gen.log


Top
 Profile  
 
PostPosted: Mon Feb 13, 2017 10:56 am 
Offline
Moderator
User avatar

Joined: Tue Nov 09, 2010 3:39 pm
Posts: 6249
Location: Montreal, Canada
This is a 13 year old thread. I'm just going to close this now.

_________________
Supported PHP versions No longer supported versions


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 29 posts ]  Go to page Previous  1, 2

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 4 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group